keep textarea input format after using mysql_real_escape_string to store

Question

I am using php5.3.6 and mysql 5.1.56 and CodeIgniter. Here is what I did.

1. Input some text in textarea, something like this:

   ---

   what's this?

   I'm bob.

   ---

2. $string = $_POST['name'];

3. $insertdata = mysql_real_escape_string($string);

4. Insert $insertdata into database. It shows "what\'s this?\n\n\nI\'m bob."(without double quotes) in the table.

5. Query the data stored in database, use stripslashes on it and then put it back to the textarea. It shows "what's this?nnnI'm bob."(without double quotes) in the textarea.

My questions are:

• In step 4, shouldn't it be "what\'s this?\n\n\n I\'m bob." stored in the table? I checked php manual. It says:

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, ,円 ', " and \x1a.

• How am I supposed to keep the textarea input format after using mysql_real_escape_string()?

• Is there anyway to choose which slash to strip and which not to?

Notes:

• magic quotes option is off
• I did not use stripslashes() before using mysql_real_escape_string()
• If I use addslashes() instead of mysql_real_escape_string(), everything works fine.
• I don' want to use addslashes() since it is not as secure as mysql_real_escape_string(), as far as I know.

Thanks, Milo

• David Thomas: have you tried using nl2br, when you retrieve the text from the database? ($textToShow = nl2br($textFromDatabase);)
• hoyomi: @DavidThomas Yes. Somehow nl2br() wouldn't work on the strings proccessed by mysql_real_escape_string()
• CVM: nl2br() shouldn't be used here - you want real newlines in your textarea, not <br /> tags (this is invalid HTML).
• hoyomi: @CVM Yes. You are right. Another question not related to this topic. Any idea why nl2br() does no effect on strings that have been processed by mysql_real_escape_string()?
• Chirag Jain: Possible duplicate of Preserve Line Breaks From TextArea When Writing To MySQL

Answer 1

This really does feel a lot like magic_quotes_gpc = On. Are you disabling it in php.ini or at runtime? It needs to be the former, otherwise it'll remain on.

http://www.php.net/manual/en/security.magicquotes.disabling.php

The magic_quotes_gpc directive may only be disabled at the system level, and not at runtime. In otherwords, use of ini_set() is not an option.

• hoyomi: I double checked. All options regarding magic_quotes are off. I think they are default to be "off" in php 5.3. I really don't know what is going wrong.
• CVM: Hmmm, in that case is there a chance you might be applying mysql_real_escape_string() more than once on the same string? This would account for the \n being visible in the values retrieved from the database.

Answer 2

Short answer:

// double quotes are *very* important, or chars are not interpreted
$text_from_db=str_replace("\\r","\r",str_replace("\\n","\n",$text_from_db));

Long answer

Pretty simple but tricky. You write your textarea and hit the "return" key, there is placed a \r\n (on Windows systems) with slashes that escape the "r" and "n" letter rising their special meaning of carriage return and newline. You actually can't see them because they are "not printable" chars. The slash char itself (0x1B) is invisible, that is a single slash is a "not printable" char, to make it visible you have to "transform" it in a printable slash char (0x5C) and to achieve that you have to double it "\\". Now back to the question: if you can read the slash, probably that's beacuse that slash is not the 0x1B but rather 0x5C, so the "n" and "r" lose their special meaning and you get them as mere strings. The code I posted does this conversion, converting the "[0x5C]n" string in a "[0x1B]" char.

Notes

Hope this helps, it did for me. IMPORTANT : it is not normal that the text that comes from the db has this issue if it has been stored correctly. My suggestion is to triple check insertion and retrieving because (given from the issue) you could be applying the quoting twice somewhere.

Answer 3

The Best Solution..

$insertdata = mysql_real_escape_string($string); (You can insert it in your database if you want)

echo stripslashes(str_replace('\r\n',PHP_EOL,$insertdata)); (The output is exactly as your input was)

Answer 4

You must escape data before inserting it into the database, to ensure you do not produce broken queries and to avoid SQL injections. However, when you retrieve that data via a SELECT, you'll receive the data unescaped, ready to be used.

• hoyomi: So how can I put the format back when output it to the textarea? I tried not using stripslashes() on the select result, it will just show the content with "\n" instead of new lines.
• Riccardo Galli: This is a whole different argument, and depends on the way you try to insert that value on your textarea

Answer 5

MySQL escapes the string, but when displaying the result back to you it will give you the same result as if it was unescaped.

• hoyomi: So how can I put the format back when output it to the textarea? If I don't use stripslashes(), it will just show the content with "\n" instead of new lines.