mysql_escape_string vulnerabilities
mysql, php, prepared-statement
Solution
Updated answer
The question was edited (after my answer was posted) to specifically target `mysqli_escape_string`, which is an alias of `mysql_real_escape_string` and therefore takes the connection encoding into account. This makes the original answer non-applicable anymore, but I 've left it for completeness.
The new answer, in short: `mysqli_escape_string` is as good security-wise as parameterized queries, provided you don't shoot yourself in the foot.
Specifically, what you must not do is highlighted in the giant warning on the PHP doc page:
The character set must be set either at the server level, or with the API function `mysqli_set_charset()` for it to affect `mysqli_real_escape_string()`.
If you don't heed this warning (i.e. if you change the character set with a direct `SET NAMES` query) and you change the character set from a single-byte encoding to a "convenient" (from the attacker's perspective) multibyte encoding, you will have in effect emulated what the dumb `mysql_escape_string` does: attempt to escape characters without knowing which encoding the input is in.
This situation leaves you potentially vulnerable to SQL injection as described by the original answer below.
Important note: I remember reading somewhere that recent MySql versions have plugged this hole on their end (in the client libraries?), which means that you might be perfectly safe even if using `SET NAMES` to switch to a vulnerable multibyte encoding. But please don't take my word for it.
Original answer
In contrast to `mysql_real_escape_string`, the bare `mysql_escape_string` does not take into account the connection encoding. This means that it assumes the input is in a single-byte encoding, when in fact it can legitimately be in a multibyte encoding.
Some multibyte encodings have byte sequences that correspond to a single character where one of the bytes is the ASCII value of the single quote (`0x27`); if fed such a string, `mysql_escape_string` will happily "escape the quote", which means substituting `0x27` with `0x5c` `0x27`. Depending on the encoding rules, this could result in mutating the multibyte character into another that includes the `0x5c` and leaving the "remaining" `0x27` as a stand-alone single quote in the input. Voilà, you have injected an unescaped quote into the SQL.
For more details see this blog post.
Problem
I was explaining parametrization and its advantages to my friend recently, and he asked how it was any better than `mysqli_escape_string` in terms of security. Specifically, can you think of any examples of SQL injection that would succeed despite the input strings being escaped (using mysqli_escape_string)? UPDATE: I apologise for not being clear enough in my original question. The general question being asked here is, is SQL injection possible despite escaping input strings?