Does mysql_real_escape_string() FULLY protect against SQL injection?
According to Stefan Esser, "mysql_real_escape_string()
[is] not safe when SET NAMES
is used."
His explanation, from his blog:
SET NAMES is usually used to switch the encoding from what is default to what the application needs. This is done in a way that
mysql_real_escape_string
doesn’t know about this. This means if you switch to some multi byte encoding that allows backslash as 2nd 3rd 4th… byte you run into trouble, becausemysql_real_escape_string
doesn’t escape correctly. UTF-8 is safe…Safe way to change encoding is
mysql_set_charset
, but that is only available in new PHP versions
He does mention that UTF-8 is safe, though.
This is a MySQL server bug that was reportedly fixed way back in May 2006.
See:
- MySQL bug #8303: String literals with multi-byte characters containing \ are lexed incorrectly
- MySQL Bug #8317: Character set introducer in query fails to override connection character set
- MySQL Bug #8378: String escaped incorrectly with client character set 'gbk'
- MySQL 5.1.11 changelog.
The bug was reported fixed in MySQL 4.1.20, 5.0.22, 5.1.11.
If you use 4.1.x, 5.0.x, or 5.1.x, make sure you have upgraded at least to the minor revision numbers.
As a workaround, you can also enable the SQL mode NO_BACKSLASH_ESCAPES
which disables backslash as a quote-escape character.
I'm pretty sure it only doesn't work if you use SQL to change the char encoding.