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

I re开发者_运维问答ad 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?

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).

mysql_real_escape_string will save you from sql injections on strings. It is an alternative to addslashes(). If you look at addslashes you will see that is what magic_quotes does. It simply calls addslashes automatically on various sources of input. mysql_real_escape_string is better for use with mysql because it is aware of the mysql character sets being used AND it escapes additional characters specific to mysql's SQL dialect.

For other datatypes, you still need to take precautions... for example, if you're passing an integer to be stored, you should cast it to int or use sprintf and the appropriate type character strings with it.

As previously stated, the best prevention is to use mysqli or pdo rather than mysql, and use bind variables. That approach is impervious across the board to sql injection.

The point of using mysql_real_escape_string is to escape characters for your query, so what you see in your database is correct (otherwise when you use an SQL SELECT it would give you the string with the backslashes).

So your query looks like: INSERT INTO test (text) VALUES ("<a href=\"http://foo.com\">foo</a>") which inserts it into the text column as <a href="http://foo.com">foo</a>.

The \ is just to tell the mysql query that you're using a " character instead of closing the string. When you have magic quotes on it's sort of like doing mysql_real_escape_string twice, making the variable in your query <a href=\\"http://foo.com\\">foo</a>. This is why magic_quotes is deprecated and should be turned off. This is unsafe and leaves you open to attack.

To answer your question, with magic quotes off and using mysql_real_escape_string you are safe from SQL injection