How can mysqli_real_escape_string fail to prevent a SQL injection?

Question

First of all, I get that people want to use stored procedures so that they reuse queries and have the escaping taken care of. However, I have read many developers say that mysqli_real_escape_string can not 100% prevent SQL injections. Can someone please provide an example of this?

From my limited knowledge on the subject I would say that mysqli_real_escape_string would always be fine for strings but for numerical values you could be caught out unless you check the number is an int, float, double, etc.

EDIT: I forgot to add something critical: assume that the charset is UTF8 and mysqli_set_charset has been called accordingly. The only injecting I've seen rely a handful of charsets (none of which are UTF8).

Answer 1

As long as you are using mysqli_set_charset() to set client encoding, and mysqli_real_escape_string() is used to format strings only, it is perfectly safe.

However, if your question implied using this function right in the application code, instead of behind-the-scenes processing of placeholder-based query or at least in the form of PDO's quote()-like function (which does escaping and quoting at once) it is straight way to injection.

It is not function itself being a problem, but the way it is used:

• as it does only part of required formatting, one can easily forget another part and slip into trouble
• or even it can be easily misused, to format not a string but another literal which will no benefit from escaping at all.
• second, when it's used right in the application code, it is usage become inconsistent or occasional, as there is no way to force a developer to format every literal properly and without fail. This again may lead to inaccuracy and injection.

That's why you have to always use a placeholder to represent data in the query (while mysqli_real_escape_string can be used to process this placeholder all right)