Escaping quotes in SQL

Question

According to php.net I should use mysql_real_escape_string() and turn off magic quotes, because it's deprecated.

So I turned it off and I used mysql_real_escape_string(), but is it enough to use it just like in the following code?

$value = "It's Time!";
$escaped_value = mysql_real_escape_string($value);

mysql_query("INSERT INTO table (column, column2) VALUES ('{$escaped_value}', "0")");

When I check the data in the database it looks the same way as in $value so "It's Time" and not "It\'s Time". Is this normal? Shouldn't this add a slash before the quotes?

Answer 1

That's the correct behaviour, and it works! Quotes are escaped in the query, not in the database. \' in the SQL query must be converted to ' inside the database.

You escape strings, so the SQL query doesn't get messed up and lets input quotes to the database without the SQL query interpreting them as control characters.

Your escaped query would be:

mysql_query("INSERT INTO table (column, column2) VALUES ('It\'s time', "0")");

Your database data should be "It's time".

If it weren't escaped it would be:

mysql_query("INSERT INTO table (column, column2) VALUES ('It's time', "0")");

It will spit out an error regarding "s time'" (because it will only interpret until the next quote character, and will only see 'It'.) And "It's time!" is just an inoffensive string, but lots of attacks could derive from that if you don't escape your characters.