Inserting Row in MySQL From Existing Row That Contains Quotes

Question

Hi,

Just wondering, to sanitize user input, I use mysql_real_escape_string() on data before it is inserted into a table. Therefore when a user enters something like this:

Hi I'm just testing this

It gets placed into the table just fine, exactly as above. Question is, if I were to pull that data and place it into a variable via PHP, say $string, what would happen if I then used that variable to insert data into a new row in the table? Such as:

<?php

$result = mysql_query( "SELECT data FROM table WHERE id='1'" ); //data = Hi I'm just testing this
$result_array = mysql_fetch_array( $result );

$string = $result_array['data'];  //string = Hi I'm just testing this

$insert = mysql_query( "INSERT INTO table (data) VALUES ('$string')" ) or die(mysql_error());

?>

Would the single quote (') cause problems in this scenario? Should I be using $string = mysql_real_escape_string( $result_array['data'] ) in this case as well?

Thanks!

Answer 1

Once the data's pulled out of MySQL, it's just like any other piece of data that you want to use in a query: You have to do proper escaping/quoting, or use a prepared statement. There's no magical flag within PHP that says "this came from the database and shall return whence it came".

The alternative is to use the INSERT INTO ... SELECT FROM syntax to do the operation completely within the database, if you can meet the conditions.

Answer 2

mysql_real_escape_string() simply prepares it for insertion into the database, once you request that data again it will be in its original form, i.e. you will have to sanitize it again before trying to insert it like your example above.