what does mysql_real_escape_string() really do?

Question

One thing that I hate about documentation at times (when you're a beginner) is how it doesn't really describe things in english. Would anyone mind translating this documentation for me? I'd like to know how exactly this makes things harder for a hacker to insert characters.

http://php.net/manual/en/function.mysql-real-escape-string.php

Also, if this is the case, how would a hacker try to insert characters?

Answer 1

The function adds an escape character, the backslash, \, before certain potentially dangerous characters in a string passed in to the function. The characters escaped are

\x00, \n, \r, \, ', " and \x1a.

This can help prevent SQL injection attacks which are often performed by using the ' character to append malicious code to an SQL query.

Answer 2

Say you want to save the string I'm a "foobar" in the database.
Your query will look something like INSERT INTO foos (text) VALUES ("$text").
With the $text variable replaced, this will look like this:

INSERT INTO foos (text) VALUES ("I'm a "foobar"") 

Now, where exactly does the string end? You may know, an SQL parser doesn't. Not only will this simply break this query, it can also be abused to inject SQL commands you didn't intend.

mysql_real_escape_string makes sure such ambiguities do not occur by escaping characters which have special meaning to an SQL parser:

mysql_real_escape_string($text)  =>  I\'m a \"foobar\" 

This becomes:

INSERT INTO foos (text) VALUES ("I\'m a \"foobar\"") 

This makes the statement unambiguous and safe. The \ signals that the following character is not to be taken by its special meaning as string terminator. There are a few such characters that mysql_real_escape_string takes care of.

Escaping is a pretty universal thing in programming languages BTW, all along the same lines. If you want to type the above sentence literally in PHP, you need to escape it as well for the same reasons:

$text = 'I\'m a "foobar"'; // or $text = "I'm a \"foobar\"";