I was explaining parametrization and its advantages to my friend recently, and he asked how it was any better than
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
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.
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. 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.