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

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.

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.