Monday 8 October 2018

SQL injection that gets around mysql_real_escape_string()

Is there an SQL injection possibility even when using mysql_real_escape_string() function?
Consider this sample situation. SQL is constructed in PHP like this:
$login = mysql_real_escape_string(GetFromPost('login'));
$password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";
I have heard numerous people say to me that a code like that is still dangerous and possible to hack even with mysql_real_escape_string() function used. But I cannot think of any possible exploit?
Classic injections like this:
aaa' OR 1=1 --
do not work.
Do you know of any possible injection that would get through the PHP code above?

Consider the following query:
$iId = mysql_real_escape_string("1 OR 1=1");    
$sSql = "SELECT * FROM table WHERE id = $iId";
mysql_real_escape_string() will not protect you against this. The fact that you use single quotes (' ') around your variables inside your query is what protects you against this. The following is also an option:
$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";


TL;DR

mysql_real_escape_string() will provide no protection whatsoever (and could furthermore munge your data) if:
  • MySQL's NO_BACKSLASH_ESCAPES SQL mode is enabled (which it might be, unless you explicitly select another SQL mode every time you connect); and
  • your SQL string literals are quoted using double-quote " characters.
This was filed as bug #72458 and has been fixed in MySQL v5.7.6 (see the section headed "The Saving Grace", below).

This is another, (perhaps less?) obscure EDGE CASE!!!

In homage to @ircmaxell's excellent answer (really, this is supposed to be flattery and not plagiarism!), I will adopt his format:

The Attack

Starting off with a demonstration...
mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"'); // could already be set
$var = mysql_real_escape_string('" OR 1=1 -- ');
mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');
This will return all records from the test table. A dissection:
  1. Selecting an SQL Mode
    mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"');
    As documented under String Literals:
    There are several ways to include quote characters within a string:
    • A “'” inside a string quoted with “'” may be written as “''”.
    • A “"” inside a string quoted with “"” may be written as “""”.
    • Precede the quote character by an escape character (“\”).
    • A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.
    If the server's SQL mode includes NO_BACKSLASH_ESCAPES, then the third of these options—which is the usual approach adopted by mysql_real_escape_string()—is not available: one of the first two options must be used instead. Note that the effect of the fourth bullet is that one must necessarily know the character that will be used to quote the literal in order to avoid munging one's data.
  2. The Payload
    " OR 1=1 --
    The payload initiates this injection quite literally with the " character. No particular encoding. No special characters. No weird bytes.
  3. mysql_real_escape_string()
    $var = mysql_real_escape_string('" OR 1=1 -- ');
    Fortunately, mysql_real_escape_string() does check the SQL mode and adjust its behaviour accordingly. See libmysql.c:
    ulong STDCALL
mysql_real_escape_string(MYSQL *mysql, char *to,const char *from,
             ulong length)
{
  if (mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES)
    return escape_quotes_for_mysql(mysql->charset, to, 0, from, length);
  return escape_string_for_mysql(mysql->charset, to, 0, from, length);
}
    Thus a different underlying function, escape_quotes_for_mysql(), is invoked if the NO_BACKSLASH_ESCAPES SQL mode is in use. As mentioned above, such a function needs to know which character will be used to quote the literal in order to repeat it without causing the other quotation character from being repeated literally.
    However, this function arbitrarily assumes that the string will be quoted using the single-quote ' character. See charset.c:
    /*
  Escape apostrophes by doubling them up

// [ deletia 839-845 ]

  DESCRIPTION
    This escapes the contents of a string by doubling up any apostrophes that
    it contains. This is used when the NO_BACKSLASH_ESCAPES SQL_MODE is in
    effect on the server.

// [ deletia 852-858 ]
*/

size_t escape_quotes_for_mysql(CHARSET_INFO *charset_info,
                               char *to, size_t to_length,
                               const char *from, size_t length)
{
// [ deletia 865-892 ]

    if (*from == '\'')
    {
      if (to + 2 > to_end)
      {
        overflow= TRUE;
        break;
      }
      *to++= '\'';
      *to++= '\'';
    }
    So, it leaves double-quote " characters untouched (and doubles all single-quote ' characters) irrespective of the actual character that is used to quote the literal! In our case $var remains exactly the same as the argument that was provided to mysql_real_escape_string()—it's as though no escaping has taken place at all.
  4. The Query
    mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');
    Something of a formality, the rendered query is:
    SELECT * FROM test WHERE name = "" OR 1=1 -- " LIMIT 1
As my learned friend put it: congratulations, you just successfully attacked a program using mysql_real_escape_string()...

The Bad

mysql_set_charset() cannot help, as this has nothing to do with character sets; nor can mysqli::real_escape_string(), since that's just a different wrapper around this same function.
The problem, if not already obvious, is that the call to mysql_real_escape_string() cannot know with which character the literal will be quoted, as that's left to the developer to decide at a later time. So, in NO_BACKSLASH_ESCAPES mode, there is literally no way that this function can safely escape every input for use with arbitrary quoting (at least, not without doubling characters that do not require doubling and thus munging your data).

The Ugly

It gets worse. NO_BACKSLASH_ESCAPES may not be all that uncommon in the wild owing to the necessity of its use for compatibility with standard SQL (e.g. see section 5.3 of the SQL-92 specification, namely the <quote symbol> ::= <quote><quote> grammar production and lack of any special meaning given to backslash). Furthermore, its use was explicitly recommended as a workaround to the (long since fixed) bug that ircmaxell's post describes. Who knows, some DBAs might even configure it to be on by default as means of discouraging use of incorrect escaping methods like addslashes().
Also, the SQL mode of a new connection is set by the server according to its configuration (which a SUPER user can change at any time); thus, to be certain of the server's behaviour, you must always explicitly specify your desired mode after connecting.

The Saving Grace

So long as you always explicitly set the SQL mode not to include NO_BACKSLASH_ESCAPES, or quote MySQL string literals using the single-quote character, this bug cannot rear its ugly head: respectively escape_quotes_for_mysql() will not be used, or its assumption about which quote characters require repeating will be correct.
For this reason, I recommend that anyone using NO_BACKSLASH_ESCAPES also enables ANSI_QUOTES mode, as it will force habitual use of single-quoted string literals. Note that this does not prevent SQL injection in the event that double-quoted literals happen to be used—it merely reduces the likelihood of that happening (because normal, non-malicious queries would fail).
In PDO, both its equivalent function PDO::quote() and its prepared statement emulator call upon mysql_handle_quoter()—which does exactly this: it ensures that the escaped literal is quoted in single-quotes, so you can be certain that PDO is always immune from this bug.
As of MySQL v5.7.6, this bug has been fixed. See change log:

Functionality Added or Changed

Safe Examples

Taken together with the bug explained by ircmaxell, the following examples are entirely safe (assuming that one is either using MySQL later than 4.1.20, 5.0.22, 5.1.11; or that one is not using a GBK/Big5 connection encoding):
mysql_set_charset($charset);
mysql_query("SET SQL_MODE=''");
$var = mysql_real_escape_string('" OR 1=1 /*');
mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');
...because we've explicitly selected an SQL mode that doesn't include NO_BACKSLASH_ESCAPES.
mysql_set_charset($charset);
$var = mysql_real_escape_string("' OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");
...because we're quoting our string literal with single-quotes.
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(["' OR 1=1 /*"]);
...because PDO prepared statements are immune from this vulnerability (and ircmaxell's too, provided either that you're using PHP≥5.3.6 and the character set has been correctly set in the DSN; or that prepared statement emulation has been disabled).
$var  = $pdo->quote("' OR 1=1 /*");
$stmt = $pdo->query("SELECT * FROM test WHERE name = $var LIMIT 1");
...because PDO's quote() function not only escapes the literal, but also quotes it (in single-quote ' characters); note that to avoid ircmaxell's bug in this case, you must be using PHP≥5.3.6 and have correctly set the character set in the DSN.
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "' OR 1=1 /*";
$stmt->bind_param('s', $param);
$stmt->execute();
...because MySQLi prepared statements are safe.

Wrapping Up

Thus, if you:
  • use native prepared statements
OR
  • use MySQL v5.7.6 or later
OR
  • in addition to employing one of the solutions in ircmaxell's summary, use at least one of:
    • PDO;
    • single-quoted string literals; or
    • an explicitly set SQL mode that does not include NO_BACKSLASH_ESCAPES
...then you should be completely safe (vulnerabilities outside the scope of string escaping aside).

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)