Injeksi SQL yang mengelilingi mysql_real_escape_string ()

Apr 21 2011

Apakah ada kemungkinan injeksi SQL bahkan saat menggunakan mysql_real_escape_string()fungsi?

Pertimbangkan contoh situasi ini. SQL dibangun di PHP seperti ini:

$login = mysql_real_escape_string(GetFromPost('login')); $password = mysql_real_escape_string(GetFromPost('password'));

$sql = "SELECT * FROM table WHERE login='$login' AND password='$password'";

Saya telah mendengar banyak orang mengatakan kepada saya bahwa kode seperti itu masih berbahaya dan mungkin diretas bahkan dengan mysql_real_escape_string()fungsi yang digunakan. Tapi saya tidak bisa memikirkan kemungkinan eksploitasi?

Suntikan klasik seperti ini:

aaa' OR 1=1 --

tidak bekerja.

Apakah Anda mengetahui adanya kemungkinan injeksi yang akan melewati kode PHP di atas?

Jawaban

393 WesleyvanOpdorp Apr 21 2011 at 15:05

Pertimbangkan pertanyaan berikut:

$iId = mysql_real_escape_string("1 OR 1=1"); $sSql = "SELECT * FROM table WHERE id = $iId";

mysql_real_escape_string()tidak akan melindungi Anda dari ini. Fakta bahwa Anda menggunakan tanda kutip tunggal ( ' ') di sekitar variabel Anda di dalam kueri Anda adalah yang melindungi Anda dari hal ini. Berikut ini juga merupakan pilihan:

$iId = (int)"1 OR 1=1";
$sSql = "SELECT * FROM table WHERE id = $iId";
652 ircmaxell Aug 25 2012 at 09:08

Jawaban singkatnya adalah ya, ya, ada cara untuk menyiasatimysql_real_escape_string() . #Untuk KASUS TEPI YANG SANGAT AMAN !!!

Jawaban panjangnya tidaklah mudah. Itu berdasarkan serangan yang ditunjukkan di sini .

Serangan itu

Jadi, mari kita mulai dengan menunjukkan serangannya ...

mysql_query('SET NAMES gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Dalam keadaan tertentu, itu akan menghasilkan lebih dari 1 baris. Mari kita membedah apa yang terjadi di sini:

  1. Memilih Set Karakter

    mysql_query('SET NAMES gbk');
    

    Agar serangan ini bekerja, kita memerlukan pengkodean yang diharapkan server pada koneksi untuk dikodekan 'seperti dalam ASCII yaitu 0x27 dan memiliki beberapa karakter yang byte terakhirnya adalah ASCII \yaitu 0x5c. Ternyata, ada 5 pengkodean tersebut didukung di MySQL 5.6 secara default: big5, cp932, gb2312, gbkdan sjis. Kami akan memilih di gbksini.

    Sekarang, sangat penting untuk diperhatikan penggunaannya di SET NAMESsini. Ini menetapkan set karakter ON THE SERVER . Jika kami menggunakan panggilan ke fungsi C API mysql_set_charset(), kami akan baik-baik saja (pada rilis MySQL sejak 2006). Tetapi lebih lanjut tentang mengapa dalam satu menit ...

  2. Payload

    Payload yang akan kita gunakan untuk injeksi ini dimulai dengan urutan byte 0xbf27. Di gbk, itu adalah karakter multibyte yang tidak valid; di latin1, itu string ¿'. Perhatikan bahwa dalam latin1 dan gbk , 0x27dengan sendirinya adalah 'karakter literal .

    Kami telah memilih payload ini karena, jika kami memanggilnya addslashes(), kami akan memasukkan ASCII \yaitu 0x5c, sebelum 'karakter. Jadi kita akan menyelesaikannya 0xbf5c27, yang gbkmerupakan urutan dua karakter: 0xbf5cdiikuti oleh 0x27. Atau dengan kata lain, karakter yang valid diikuti dengan unescaped '. Tapi kami tidak menggunakan addslashes(). Lanjutkan ke langkah selanjutnya ...

  3. mysql_real_escape_string ()

    Panggilan C API mysql_real_escape_string()berbeda dari addslashes()yang ia tahu set karakter koneksi. Sehingga dapat melakukan escaping dengan benar untuk set karakter yang diharapkan server. Namun, hingga saat ini, klien berpikir bahwa kami masih menggunakan latin1untuk koneksi tersebut, karena kami tidak pernah memberi tahu sebaliknya. Kami memang memberi tahu server yang kami gunakan gbk, tetapi klien masih menganggapnya latin1.

    Oleh karena itu panggilan untuk mysql_real_escape_string()menyisipkan garis miring terbalik, dan kami memiliki 'karakter gantung bebas dalam konten "lolos" kami! Bahkan, jika kita melihat $vardi gbkset karakter, kita akan melihat:

    縗 'ATAU 1 = 1 / *

    Itulah yang dibutuhkan serangan itu.

  4. Kueri

    Bagian ini hanya formalitas, tetapi inilah kueri yang diberikan:

    SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1
    

Selamat, Anda baru saja berhasil menyerang program menggunakan mysql_real_escape_string()...

Keburukan

Lebih buruk. PDOdefault untuk meniru pernyataan yang disiapkan dengan MySQL. Itu berarti bahwa di sisi klien, ini pada dasarnya melakukan sprintf melalui mysql_real_escape_string()(di pustaka C), yang berarti berikut ini akan menghasilkan injeksi yang berhasil:

$pdo->query('SET NAMES gbk');
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Sekarang, perlu dicatat bahwa Anda dapat mencegahnya dengan menonaktifkan pernyataan siap yang diemulasi:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Ini biasanya akan menghasilkan pernyataan yang benar-benar disiapkan (yaitu data dikirim dalam paket terpisah dari kueri). Namun, menyadari bahwa PDO akan diam-diam mundur untuk meniru pernyataan bahwa MySQL tidak bisa mempersiapkan native: orang-orang yang dapat secara tercantum di manual, tapi hati-hati untuk memilih versi server yang sesuai).

Jelek

Saya katakan di awal bahwa kami dapat mencegah semua ini jika kami menggunakan mysql_set_charset('gbk')alih-alih SET NAMES gbk. Dan itu benar asalkan Anda menggunakan rilis MySQL sejak 2006.

Jika Anda menggunakan rilis MySQL sebelumnya, maka bug di mysql_real_escape_string()berarti bahwa karakter multibyte valid seperti yang di payload kami diperlakukan sebagai byte tunggal untuk melarikan diri tujuan bahkan jika klien telah benar diberitahu tentang encoding koneksi dan serangan ini akan masih berhasil. Bug telah diperbaiki di MySQL 4.1.20 , 5.0.22 dan 5.1.11 .

But the worst part is that PDO didn't expose the C API for mysql_set_charset() until 5.3.6, so in prior versions it cannot prevent this attack for every possible command! It's now exposed as a DSN parameter.

The Saving Grace

As we said at the outset, for this attack to work the database connection must be encoded using a vulnerable character set. utf8mb4 is not vulnerable and yet can support every Unicode character: so you could elect to use that instead—but it has only been available since MySQL 5.5.3. An alternative is utf8, which is also not vulnerable and can support the whole of the Unicode Basic Multilingual Plane.

Alternatively, you can enable the NO_BACKSLASH_ESCAPES SQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27 will be replaced with 0x2727 rather than 0x5c27 and thus the escaping process cannot create valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27 is still 0xbf27 etc.)—so the server will still reject the string as invalid. However, see @eggyal's answer for a different vulnerability that can arise from using this SQL mode.

Safe Examples

The following examples are safe:

mysql_query('SET NAMES utf8');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because the server's expecting utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("\xbf\x27 OR 1=1 /*"); mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

Because we've properly set the character set so the client and the server match.

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $pdo->query('SET NAMES gbk');
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Because we've turned off emulated prepared statements.

$pdo = new PDO('mysql:host=localhost;dbname=testdb;charset=gbk', $user, $password);
$stmt = $pdo->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$stmt->execute(array("\xbf\x27 OR 1=1 /*"));

Because we've set the character set properly.

$mysqli->query('SET NAMES gbk');
$stmt = $mysqli->prepare('SELECT * FROM test WHERE name = ? LIMIT 1');
$param = "\xbf\x27 OR 1=1 /*"; $stmt->bind_param('s', $param); $stmt->execute();

Because MySQLi does true prepared statements all the time.

Wrapping Up

If you:

  • Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) AND mysql_set_charset() / $mysqli->set_charset() / PDO's DSN charset parameter (in PHP ≥ 5.3.6)

OR

  • Don't use a vulnerable character set for connection encoding (you only use utf8 / latin1 / ascii / etc)

You're 100% safe.

Otherwise, you're vulnerable even though you're using mysql_real_escape_string()...

190 eggyal Apr 25 2014 at 02:15

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

  • Incompatible Change: A new C API function, mysql_real_escape_string_quote(), has been implemented as a replacement for mysql_real_escape_string() because the latter function can fail to properly encode characters when the NO_BACKSLASH_ESCAPES SQL mode is enabled. In this case, mysql_real_escape_string() cannot escape quote characters except by doubling them, and to do this properly, it must know more information about the quoting context than is available. mysql_real_escape_string_quote() takes an extra argument for specifying the quoting context. For usage details, see mysql_real_escape_string_quote().

     Note

    Applications should be modified to use mysql_real_escape_string_quote(), instead of mysql_real_escape_string(), which now fails and produces an CR_INSECURE_API_ERR error if NO_BACKSLASH_ESCAPES is enabled.

    References: See also Bug #19211994.

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).

19 Slava Apr 21 2011 at 15:01

Well, there's nothing really that can pass through that, other than % wildcard. It could be dangerous if you were using LIKE statement as attacker could put just % as login if you don't filter that out, and would have to just bruteforce a password of any of your users. People often suggest using prepared statements to make it 100% safe, as data can't interfere with the query itself that way. But for such simple queries it probably would be more efficient to do something like $login = preg_replace('/[^a-zA-Z0-9_]/', '', $login);