Possible Duplicate:
mysql_real_escape_string VS addslashes
If they don't do exactly the same, what's the difference? The delimiter for values inside a MySQL query is the '
isn't it? Or maybe the "
but that's also escaped with addslashes.
In other database engines I understand (and definitely inside a db wrapper like PDO), but why are so many people so adament on using mysql(i)_escape_string instead of addslashes?
First of all: do not use
mysql_escape_string
, it is deprecated (for a reason)!If you have to support a legacy application that connects to the database through the
mysql
extension (which has been deprecated), usemysql_real_escape_string
instead. Otherwise switch immediately tomysqli
, where prepared statements and bound parameters provide a more robust mechanism for escaping user input.That said, the answer can be found by reading the description of
mysql_real_escape_string
andaddslashes
:Difference #1
addslashes
does not know anything about MySql connection encodings. If you pass it a string containing bytes representing an encoding other than the encoding used by the MySql connection, it will happily escape all bytes having the values of the characters'
,"
,\
and\x00
. This may not be the same as all the characters'
,"
,\
and\x00
if you are using an encoding other than 8-bit encodings and UTF-8. The result will be that the string received by MySql will be corrupted.To trigger this bug, try using
iconv
to convert your variable to UTF-16 and then escape it withaddslashes
. See what your database receives.This is one reason why
addslashes
should not be used for escaping.Difference #2
In contrast to
addslashes
,mysql_real_escape_string
also escapes the characters\r
,\n
, and\x1a
. It appears that these characters have to be escaped as well when talking to MySql, otherwise a malformed query may be the resultThis is the other reason why
addslashes
should not be used for escaping.They are identical in that you should be using neither of them, because you should be using placeholders rather than building SQL from untrusted data.
See http://bobby-tables.com/php.html for how to do it the right way.
Chris Shiflett demonstrates a real world case where escaping SQL using
addslashes()
fails, andmysql_real_escape_string()
is the only way to go.now this is admittedly a rare edge case, but a demonstration of why people are so adamant about using the database specific escape functions. Only the database library can know for sure what kind of escaping is needed. Different wrappers, character sets and SQL flavours (like MS SQL server) need different escaping. Ignoring that fact is how vulnerabilities are born.
It's foremost a charset issue. What
addslashes()
does would be sufficient for escaping data intermixed with SQL commands, if the data was purely ASCII. But besides UTF-8 encoding variations, MySQL in some configuration also allows fringe charsets like UCS2 (UTF-16) or GB/Big5 chinese charsets. There it's insufficient to just escape the raw ascii code of'
into\'
. And then the MySQL way of escaping strings was never very much SQL-standards-compliant to begin with. It might be deprecated in future releases of the MySQL server.