3

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?

Asad Saeeduddin
  • 46,193
  • 6
  • 90
  • 139
  • Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/q0gwD). See the [red box](http://goo.gl/OWwr2)? Instead you should learn about [prepared statements](http://goo.gl/orrj0) and use either [PDO](http://goo.gl/TD3xh) or [MySQLi](http://in3.php.net/mysqli). If you can't decide which, [this article](http://goo.gl/YXyWL) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/b2ATO). Also see [Why shouldn't I use mysql functions in PHP?](http://goo.gl/J5jAo) – NullPoiиteя Oct 23 '12 at 08:40
  • Maybe you should replace `mysql_escape_string()` with `mysqli_real_escape_string()` to get better answers. – martinstoeckli Oct 23 '12 at 08:43
  • 1
    @martinstoeckli: That change also makes a difference for the answers :) – Jon Oct 23 '12 at 08:54
  • @Jon how does it make a difference for the answers? If I were to ask a hypothetical question about B language, I would prefer not to recieve answers that tell me to use C++ instead. – Asad Saeeduddin Oct 23 '12 at 08:59
  • @Jon - Agreed, but i think the question was about "escaping strings" versus "parametrized queries" from the start. It was just a inattention of Asad and the "unuseful" answers didn't stop otherwise. – martinstoeckli Oct 23 '12 at 09:00
  • Gaah, @Jon, I was all bubbly and happily writing my answer, and not only you beat me to the punch, my answer *does not even apply anymore*. `*cries*` – LSerni Oct 23 '12 at 09:04
  • @Iserni Jon - I'm sorry to have ruined your answers, but maybe you know an answer to the new question as well? – martinstoeckli Oct 23 '12 at 09:07
  • @martinstoeckli: I have updated the answer to cover the current question. – Jon Oct 23 '12 at 11:02
  • from the manual: > This function has been DEPRECATED as of PHP 5.3.0. Relying on this > feature is highly discouraged. Do not put effort in deprecated code. Use parameter binding, no worries about SQL injection. – JvdBerg Oct 23 '12 at 08:39
  • 1
    I know this already, I am not advocating the use of the mysql extension. I am simply asking you to illustrate **how** it is vulnerable. – Asad Saeeduddin Oct 23 '12 at 08:41
  • This is because it was replaced with `mysqli_real_escape_string()`, not because you should not escape strings anymore. – martinstoeckli Oct 23 '12 at 08:41
  • @Asad: You never know, as it is a internal function of PHP. You would have to look in the source code to figure it out – JvdBerg Oct 23 '12 at 08:42
  • Check out this slideshow of [**SQL myths and fallacies**](http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies). It should answer any SQL injection questions you have. – William The Dev Oct 23 '12 at 08:46

2 Answers2

4

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.

Jon
  • 428,835
  • 81
  • 738
  • 806
  • 2
    +1 for being pretty much the only on topic answerer. Must have been downvoted because you didn't include the words **HIGHLY DISCOURAGED** in bold capitals in your answer – Asad Saeeduddin Oct 23 '12 at 08:54
  • @Asad: You specifically did not ask for something like that, so I guess it's your fault :) – Jon Oct 23 '12 at 08:56
  • I specifically asked for examples of how SQL injection could occur when using mysql_escape_string. This is supported by the fact that my question asks `Specifically, can you think of any examples of SQL injection that would succeed despite the input strings being escaped` – Asad Saeeduddin Oct 23 '12 at 08:58
4

The bad answer:

Specifically, can you think of any examples of SQL injection that would succeed despite the input strings being escaped (using mysql_escape_string)?

Not reliably. You are referring to mysql_escape_string(), which does not take into account the connection encoding (while mysql_real_escape_string() does).

So maybe a carefully crafted string, with a carefully crafted incomplete UTF8 codepoint in front, might result in, say, a quote sign being escaped by mysql_escape_string() but the escape itself being ignored by MySQL since it will "see" it as an UTF8 character.

E.g.:

0xC2' OR 1=1 ;--

would be escaped by mysql_escape_string() as

0xC2\' OR 1=1 ;--

which would be assembled to

WHERE password='0xC2\' OR 1=1 ;--';

and seen by MySQL (if the proper connection encoding was in effect) as, say,

WHERE password='€' OR 1=1 ;[--';]    <-- the bracketed part is considered a comment and ignored

which would be a classic SQL injection.

But this plays on the fact that you specified, maybe through distraction, a doubly deprecated function. If you really were referring to mysql_real_escape_string(), then it would not work.

Also, this assumes that neither the server, nor the application layer (e.g. PHP) employs any kind of charset validation when populating the input. If they did, the invalid UTF8 would be deleted on arrival, and never even be seen by mysql_escape_string, which would then of course be enough.

The real answer:

Do not use mysql_escape_string (or mysql_whatever) at all. They've been deprecated and your code might stop working. Use PDO functions instead.

LSerni
  • 55,617
  • 10
  • 65
  • 107