Value escaped with mysql_real_escape_string() before inserting into database, but stored value is not escaped

Question

I read in some tutorials/example that this function will save in database the

<a href="http://foo.com">foo</a>

as

<a href=\"http://foo.com\">foo</a>

In my case (i have magic_quotes off as many people suggests),

$text = mysql_real_escape_string($_POST['text']);
$sql="INSERT INTO test (text) VALUES ('$text')";

it is saved as <a href="http://foo.com">foo</a> in the DB which I see through phpMyAdmin.

I guess this is because of magic_quotes because when I had them on, it was saved as <a href=\"http://foo.com\">foo</a>.

With magic_quotes turned off and my example above, am I safe against SQL injections?

Answer 1

The point of escaping is to make ambiguous syntax unambiguous.

INSERT INTO test (text) VALUES ("<a href="http://foo.com">foo</a>")

This is ambiguous/wrong because the database cannot tell whether the " inside the " are to be taken literally or whether they signal the end of the string you want to put in the database. Notice the screwed up syntax highlighting here on SO as well. mysql_real_escape_string escapes that to:

INSERT INTO test (text) VALUES ("<a href=\"http://foo.com\">foo</a>")

This is now unambiguous, the inner " do not signal the end of the string.

That's the only thing escaping does, it does not permanently insert backslashes into the string.

Please read The Great Escapism (Or: What You Need To Know To Work With Text Within Text).