need to call mysql_real_escape_string() twice

Question

I have some user-input data that is put into a MySQL database (version 5.5.8 according to phpMyAdmin) - I find that mysql_real_escape_string() in my example below must be called twice or no backslashes get added to a user input text such as "SHE'S A GREAT BOAT ISN'T SHE"

The problem occurs when I try to write that type of "text string containing quotes" into my database -- the user input string is written to the database with no backslashes -- unless I call mysql_real_escape_string() twice.

I use phpMyAdmin to view the newly-written database record, and the user's text-string-with-quotes has NO ESCAPING on the quotes (no backslashes). If I read that text string back out of the database, the quotes are not escaped, ie. I don't have to call stripslashes() to reverse the use of mysql_real_escape_string().

The code is below. When I call get_magic_quotes_gpc() it shows they are disabled.

My expectation was this: if a user inputs a text string like "SHE'S A GREAT BOAT ISN'T SHE" -- and I then call mysql_real_escape_string() one time on that user input text, and write that to the database, this one call to mysql_real_escape_string() would create a text string that looks like this:

\"SHE\'S A GREAT BOAT ISN\'T SHE\"

But the text written to the database (see below) shows no escaping, just the original user-input text with all its unescaped quotes.

This is the PHP code that writes the user text string to the database:

// theUsersInputText contains "SHE'S A GREAT BOAT ISN'T SHE"
$theText = $_POST['theUsersInputText'];

$theDB = connectToDb();

  // THIS ALWAYS REPORTS THAT MAGIC QUOTES ARE DISABLED
if(get_magic_quotes_gpc())
echo "Magic quotes are enabled";
else
echo "Magic quotes are disabled";  

$theEscapedText =  mysql_real_escape_string($theText);
$newInsertQuery = "INSERT INTO " . "myDatabaseTable" 
        . " VALUES "
        . "('" . $theEscapedText . "')";

When I use phpMyAdmin to look at the database, NO SLASHES ARE IN THE TEXT STRING. And when retrieve this string from the database -- it looks like this: "SHE'S A GREAT BOAT ISN'T SHE"

It makes me think I'm open to injection attack then.

So I modified the code above by adding a second call to mysql_real_escape_string, and now when I look at the database, FINALLY the text string looks like this:

\"SHE\'S A GREAT BOAT ISN\'T SHE\"

Here's the modified code:

$theText = $_POST['theUsersInputText'];

  // NEW LINE OF CODE HERE
$theStrangelyUnescapedText =  mysql_real_escape_string($theText);    

$theDB = connectToDb();

  // THIS ALWAYS REPORTS THAT MAGIC QUOTES ARE DISABLED
if(get_magic_quotes_gpc())
showAlertBox("Magic quotes are enabled");
else
showAlertBox("Magic quotes are disabled");  


$theFinallyEscapedText =  mysql_real_escape_string($theStrangelyUnescapedText);
$newInsertQuery = "INSERT INTO " . "myDatabaseTable" 
        . " VALUES "
        . "('" . $theFinallyEscapedText . . "')";

After the above, finally in phpMyAdmin, when I look at the just-written database record, the text looks like:

\"SHE\'S A GREAT BOAT ISN\'T SHE\"

Why do I have to call mysql_real_escape_string() twice here?

Answer 1

Written in this way

$theEscapedText =  mysql_real_escape_string($theText);
$newInsertQuery = "INSERT INTO `myDatabaseTable`  VALUES ('$theEscapedText')";

i get the query output

INSERT INTO `myDatabaseTable` VALUES ('\"SHE\'S A GREAT BOAT ISN\'T SHE\"')

the slashes is to make the query syntactically correct and try to avoid sql injection, in the db the query will stored the original text

"SHE'S A GREAT BOAT ISN'T SHE"

As side Note: Mysql_* extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used.

A useful link Why shouldn't I use mysql_* functions in PHP

Answer 2

I decided to use a heredoc and output to a file to determine what effect, if any, my first call to mysql_real_escape_string() was having. I used the following code -- the php variable "theTextWithManyQuotes" was read from user input, it was a text string such as "Isn't O'Malley's parents' children's "choices" atypical"

  $theTextWithManyQuotes = mysql_real_escape_string($_POST['userInput']);

  $html = <<<HEREDOC

  <!DOCTYPE html>
  <body>
    <textarea readonly name="adPreviewText" id="adPreviewText" rows="4" cols="60"
      style="border: none; border-style: none">$theTextWithManyQuotes</textarea>
  /body>
  </html>
HEREDOC;

file_put_contents("testfileonly", $html); 

I then dumped the $html variable to a file by way of file_put_contents() and opened the "testfileonly" file -- and the backslashes were in fact present in the text.

My surmise is that when I build a mysql query string and it contains escaped text strings that were escaped by a single call to mysql_real_escape_string(), the database somehow 'hides' the backslashes so that they're not visible in phpMyAdmin when looking at the database record.

The fact that my heredoc's contents, when output to a file, shows that the backslashes are present, it proved to me that only a single call to mysql_real_escape_string() was required, and the mysql database is somehow not showing (or stripping?) the backslashes in the database records. When the data is read back out of the database, no call to stripslashes() is required, the backslashes are not present in the text strings when read back out from the database.