14

Some people believe that mysql_real_escape_string() has some flaws and cannot protect your query even when properly used.
Bringing some fossilized articles as a proof.

So, the question is: is mysql[i]_real escape_string() totally unacceptable?
Or is it's still possible to use this function to create your own kind of prepared statements?

With proofcode, please.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345

3 Answers3

26

From the MySQL’s C API function mysql_real_escape_string description:

If you need to change the character set of the connection, you should use the mysql_set_character_set() function rather than executing a SET NAMES (or SET CHARACTER SET) statement. mysql_set_character_set() works like SET NAMES but also affects the character set used by mysql_real_escape_string(), which SET NAMES does not.

So don’t use SET NAMES/SET CHARACTER SET but PHP’s mysql_set_charset to change the encoding as that is the counterpart to MySQL’s mysql_set_character_set (see source code of /ext/mysql/php_mysql.c).

Gumbo
  • 643,351
  • 109
  • 780
  • 844
  • 2
    Okay, so what happens if we're using mysqli or PDO, which don't have a native charset setting method? Are prepared statements safe? – Charles Mar 13 '11 at 15:37
  • 1
    @Charles: MySQLi does have a corresponding function: [`mysqli_set_charset`](http://php.net/mysqli_set_charset). And for prepared statements, use the [`character_set_client` system variable](http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_character_set_client) that is taken into account when [preparing a string type](http://dev.mysql.com/doc/refman/5.5/en/c-api-prepared-statement-type-codes.html): “`MYSQL_TYPE_STRING` indicates character input string data. The value is assumed to be in the character set indicated by the `character_set_client` system variable.” – Gumbo Mar 13 '11 at 16:28
  • 2
    thanks! PDO seems to be the only one lacking, then. It has [a `charset` param in the DSN](http://www.php.net/manual/en/ref.pdo-mysql.connection.php), but it's marked as "currently ignored.." – Charles Mar 13 '11 at 16:32
  • @Charles, Does setting the charset in the connection string work? Does real_escape_string work properly by that? – Pacerier Oct 18 '14 at 23:22
  • @Pacerier, as of when I wrote that comment four years ago, no, it used to not work. "DSN" == "connection string." Since then, as noted in the linked manual page, it has been made to work. – Charles Oct 19 '14 at 05:17
8

However, even with legacy code and old server versions, the vulnerability can only be triggered if the character set of the database connection is changed from a single-byte one like Latin-1 to a multibyte one that allows the value 0x5c (ASCII single quote) in the second or later byte of a multibyte character.

Specifically, UTF-8 does not allow that, unlike older Asian encodings like GBK and SJIS. So if your application does not change the connection character set, or changes it only to UTF-8 or single-byte ones like Latin-n, you're safe from this exploit.

But best practice is still to run the newest server version, use the correct interface to change character sets, and use prepared queries so you don't forget to escape stuff.

LHMathies
  • 2,384
  • 16
  • 21
3

In the comments there is a link to a bugfix in mySQL 5.0.22 (24 May 2006), where this has been addressed.

ontrack
  • 2,963
  • 2
  • 15
  • 14