mysql_real_escape_string VS addslashes

Can someone shed some light on the differences between these 2 functions, from the PHP manual:

addslashes: Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash () and NUL (the NULL byte).

mysql_real_escape_string: mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, , ', " and \x1a.

From what I gather the major difference is \x00, \n \r \x1a which addslashes doesn't escape, can you tell me what the significance of that is?

Solution 1:

What you quote is probably from the doc, but as far as I know it's not necessarily true.

addslashes adds slashes to characters that are commonly disturbing. mysql_real_escape_string escapes whatever MySQL needs to be escaped. This may be more or less characters than what addslashes takes care of.

Also, mysql_real_escape_string will not necessarily add slashes to escape. While I think it works if you do it that way, recent versions of MySQL escape quotes by putting two of them together instead of by putting a slash before it.

I believe you should always use your data provider's escape function instead of addslashes, because addslashes may either do too much or not enough work for the purpose you use it. On the other hand, mysql_real_escape_string knows what to do to prepare a string for embedding it in a query. Even if the specs change about how to escape stuff and suddenly it's not backslashes that you should use anymore, your code will still work because mysql_real_escape_string will be aware of it.

Solution 2:

mysql_real_escape_string() also takes into account the character set used by the current connection to the database.

The PHP function mysql_real_escape_string() uses the MySQL C API function of the same name: http://dev.mysql.com/doc/refman/5.1/en/mysql-real-escape-string.html

Also read addslashes() Versus mysql_real_escape_string() by noted PHP security expert Chris Shiflett, for a demonstration that you can get SQL injection exploits even if you use addslashes().

Other folks recommend using query parameters, and then you don't have to do any escaping of dynamic values. I recommend this too, but in PHP you'd have to switch to PDO or ext/mysqli, because the plain ext/mysql API doesn't support query parameters.

Also there may be some corner cases where you can't use query parameters for a dynamic string value, like your search pattern in a fulltext search.