[RESOLVED] Mysql_real_escape_string breaks \n, need a fix

Sykoi

The title says it all... I have just added mysql_real_escape_string to my _POST parsing function, and it breaks \n, making nl2br() completely unable to read them... And according to php.net:

Note that mysql_real_escape_string doesn't prepend backslashes to \x00, \n, \r, and and \x1a as mentionned in the documentation, but actually replaces the character with a MySQL acceptable representation for queries (e.g. \n is replaced with the '\n' litteral). (\, ', and " are escaped as documented) This doesn't change how you should use this function, but I think it's good to know.

So how do I fix this? I've looked around quite a bit but can't find anything 🙁

bradgrafelman

Are you saying you apply this function to all POST'ed data?

Don't use [man]mysql_real_escape_string/man on data unless you plan on entering it into a database... and if you're trying to store the <br> tags in the database instead of the new lines, just run [man]nl2br/man on the data before you use the escaping function.

Sykoi

All data passed through $_POST is added into the database, if it isn't I don't use the portion of the function that sanitizes it for mysql... But this is unrelated to my post.

And I don't want to store <br> in the database, I need to store \n in the database and handle the \n's when/if I need to, on output - therein lies the problem, mysql_real_escape_string replaces \n with a mysql version that php can't read correctly... I need to find out how to convert this back into true \n.

bradgrafelman

So... you're storing the data in the database, but then you're also outputting it on the same page?

If so, you might need to use [man]str_replace/man to replace '\n' with a literal line break. This being after you use it in your DB query, of course.

Sykoi

Actually I store it on an edit or new item page, then view it again on... Well, edit or just plain view item.
And I'll try this, however I was hoping there was already a php function for this, as its driving me insane and made me spend close to 4 hours trying to fix a function that wasn't broken.

Thanks I just tried it and it worked, however I really don't want to have to do it like this as its incredibly messy if theres already a function for it....
Also, I have to use:

$data = str_replace("\\n", "\n", $data);

Rather than

$data = str_replace("\n", "\n", $data);

Even though when using

$data = stripslashes($data);

Strips the slashes altogether and returns "n" instead of "\n" like you'd expect.... And even when I just print the data, or view the data by hand from the table, it shows "\n" and not "\n"

MarkR

You're doing it all wrong. Don't apply mysql_real_escape_string to $_POST, as that's completely the wrong level to be doing it at. What about data you're not passing to the DB? Logic in PHP will break by having this mangling done.

Also as there is no "undo" function for mysql_real_escape_string, you won't be able to fix it.

mysql_real_escape_string should be used ONLY just before you put something into a SQL string and nowhere. Else. Any other use of it is semantically nonsensical.

You don't need to worry or care what representation changes mysql_real_escape_string makes, because when the data come back out, they will be right.

You're repeating the mistakes of magic_quotes_gpc

Mark

Sykoi

MarkR wrote:
You're doing it all wrong. Don't apply mysql_real_escape_string to $_POST, as that's completely the wrong level to be doing it at. What about data you're not passing to the DB? Logic in PHP will break by having this mangling done.

Also as there is no "undo" function for mysql_real_escape_string, you won't be able to fix it.

mysql_real_escape_string should be used ONLY just before you put something into a SQL string and nowhere. Else. Any other use of it is semantically nonsensical.

You don't need to worry or care what representation changes mysql_real_escape_string makes, because when the data come back out, they will be right.

You're repeating the mistakes of magic_quotes_gpc

Mark

My god would you actually READ before you reply?

Sykoi wrote:
All data passed through $_POST is added into the database, if it isn't I don't use the portion of the function that sanitizes it for mysql... But this is unrelated to my post.

And I don't want to store <br> in the database, I need to store \n in the database and handle the \n's when/if I need to, on output - therein lies the problem, mysql_real_escape_string replaces \n with a mysql version that php can't read correctly... I need to find out how to convert this back into true \n.

As for the fix, once again - read before you reply... I had found a fix in the post right above yours

bradgrafelman

But MarkR does have a valid point... if I'm understanding your setup. Example of what he's saying:

$name = (isset($_POST['name']) ? $_POST['name'] : NULL);
$age = (isset($_POST['age']) ? intval($_POST['age']) : NULL);

$query = 'INSERT INTO `myTable` (`name`, `age`) VALUES (\'' . mysql_real_escape_string($name) . '\', ' . $age . ')';
$exec = mysql_query($query);

echo "Thanks for submitting your name, $name!";

He's just saying don't use [man]mysql_real_escape_string/man until you're putting the data in a query.

Sykoi

I have my $POST/$GET parser setup in such a way that it doesn't break logic... And after a month of working with it, I have yet to run into any sort of problem related to this, as mysql_real_escape_string handles string escaping intelligently, and any logic that requires a post or get that could have a character escaped, is gotten through $POST, or $GET, not my $parser->get_post_data(); function.

(Which is used almost solely for mysql, although it is also used for my mod_rewrite parsing/url creation/reader function set)

Weedpacket

Sykoi wrote:
And after a month of working with it, I have yet to run into any sort of problem related to this

Except for the one that led you to post here 🙂