php 绕过 mysql_real_escape_string() 的 SQL 注入

Question

提问by Richard Knop

Is there an SQL injection possibility even when using mysql_real_escape_string()function?

即使使用mysql_real_escape_string()函数，是否也有 SQL 注入的可能性？

Consider this sample situation. SQL is constructed in PHP like this:

考虑这个示例情况。SQL 在 PHP 中是这样构造的：

$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 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?

我听到很多人对我说这样的代码仍然很危险，即使使用了mysql_real_escape_string()函数也有可能被破解。但我想不出任何可能的漏洞？

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?

你知道任何可能的注入可以通过上面的 PHP 代码吗？

Answer 1

采纳答案by Wesley van Opdorp

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:

mysql_real_escape_string()不会保护您免受此侵害。 您在查询中的变量周围使用单引号 ( ' ')的事实是保护您免受此影响的原因。以下也是一个选项：

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

Answer 2

回答by ircmaxell

The short answer is yes, yes there is a way to get around mysql_real_escape_string().

简短的回答是肯定的，是的，有办法绕过mysql_real_escape_string()。

For Very OBSCURE EDGE CASES!!!

对于非常模糊的边缘情况！！！

The long answer isn't so easy. It's based off an attack demonstrated here.

长答案并不那么容易。它基于此处演示的攻击。

The Attack

攻击

So, let's start off by showing the attack...

所以，让我们从展示攻击开始……

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");

In certain circumstances, that will return more than 1 row. Let's dissect what's going on here:

在某些情况下，这将返回超过 1 行。让我们剖析一下这里发生了什么：

Selecting a Character Set
```
mysql_query('SET NAMES gbk');
```
For this attack to work, we need the encoding that the server's expecting on the connection both to encode 'as in ASCII i.e. 0x27andto have some character whose final byte is an ASCII \i.e. 0x5c. As it turns out, there are 5 such encodings supported in MySQL 5.6 by default: big5, cp932, gb2312, gbkand sjis. We'll select gbkhere.
Now, it's very important to note the use of SET NAMEShere. This sets the character set ON THE SERVER. If we used the call to the C API function mysql_set_charset(), we'd be fine (on MySQL releases since 2006). But more on why in a minute...
The Payload
The payload we're going to use for this injection starts with the byte sequence 0xbf27. In gbk, that's an invalid multibyte character; in latin1, it's the string ?'. Note that in latin1andgbk, 0x27on its own is a literal 'character.
We have chosen this payload because, if we called addslashes()on it, we'd insert an ASCII \i.e. 0x5c, before the 'character. So we'd wind up with 0xbf5c27, which in gbkis a two character sequence: 0xbf5cfollowed by 0x27. Or in other words, a validcharacter followed by an unescaped '. But we're not using addslashes(). So on to the next step...
mysql_real_escape_string()
The C API call to mysql_real_escape_string()differs from addslashes()in that it knows the connection character set. So it can perform the escaping properly for the character set that the server is expecting. However, up to this point, the client thinks that we're still using latin1for the connection, because we never told it otherwise. We did tell the serverwe're using gbk, but the clientstill thinks it's latin1.
Therefore the call to mysql_real_escape_string()inserts the backslash, and we have a free hanging 'character in our "escaped" content! In fact, if we were to look at $varin the gbkcharacter set, we'd see:
```
縗' OR 1=1 /*
```
Which is exactly whatthe attack requires.
The Query
This part is just a formality, but here's the rendered query:
```
SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1
```

选择字符集
```
mysql_query('SET NAMES gbk');
```
对于这种攻击的工作，我们所需要的编码服务器的期望既编码的连接'为ASCII即0x27和有一些文字，其最后一个字节是一个ASCII\即0x5c。事实证明，会默认在MySQL 5.6支持5个这样的编码：big5，cp932，gb2312，gbk和sjis。我们会选择gbk这里。
现在，注意SET NAMES这里的使用非常重要。这将设置字符集ON THE SERVER。如果我们使用对 C API 函数的调用mysql_set_charset()，我们会很好（自 2006 年以来的 MySQL 版本）。但更多关于为什么在一分钟...
有效载荷
我们将用于此注入的有效负载以字节序列开头0xbf27。在中gbk，这是一个无效的多字节字符；在中latin1，它是字符串?'。请注意， inlatin1和gbk,0x27本身就是一个文字'字符。
我们选择了这个有效载荷，因为如果我们调用addslashes()它，我们会在字符之前插入一个 ASCII \ie 。所以我们最终会得到，它是一个两个字符的序列：后跟. 或者换句话说，一个有效字符后跟一个未转义的. 但我们没有使用. 那么进入下一步...0x5c'0xbf5c27gbk0xbf5c0x27'addslashes()
mysql_real_escape_string()
C API 调用的mysql_real_escape_string()不同之处addslashes()在于它知道连接字符集。因此它可以对服务器期望的字符集正确执行转义。然而，到目前为止，客户端认为我们仍在使用latin1连接，因为我们从来没有告诉过它。我们确实告诉了我们正在使用的服务器gbk，但客户端仍然认为它是latin1。
因此调用mysql_real_escape_string()插入反斜杠，我们'在“转义”内容中有一个自由悬挂字符！事实上，如果我们看一下$var在gbk字符集，我们会看到：
```
縗' OR 1=1 /*
```
这正是攻击所需要的。
查询
这部分只是一种形式，但这里是呈现的查询：
```
SELECT * FROM test WHERE name = '縗' OR 1=1 /*' LIMIT 1
```

Congratulations, you just successfully attacked a program using mysql_real_escape_string()...

恭喜，你刚刚成功攻击了一个程序，使用mysql_real_escape_string()......

The Bad

坏的

It gets worse. PDOdefaults to emulatingprepared statements with MySQL. That means that on the client side, it basically does a sprintf through mysql_real_escape_string()(in the C library), which means the following will result in a successful injection:

它变得更糟。PDO默认使用 MySQL模拟准备好的语句。这意味着在客户端，它基本上通过mysql_real_escape_string()（在 C 库中）执行 sprintf ，这意味着以下将导致成功注入：

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

Now, it's worth noting that you can prevent this by disabling emulated prepared statements:

现在，值得注意的是，您可以通过禁用模拟准备好的语句来防止这种情况：

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

This will usuallyresult in a true prepared statement (i.e. the data being sent over in a separate packet from the query). However, be aware that PDO will silently fallbackto emulating statements that MySQL can't prepare natively: those that it can are listedin the manual, but beware to select the appropriate server version).

这通常会产生一个真正的准备好的语句（即数据在与查询分开的数据包中发送）。但是，请注意 PDO 将默默地回退到模拟 MySQL 无法在本地准备的语句：手册中列出了它可以准备的语句，但要注意选择适当的服务器版本）。

The Ugly

丑陋的

I said at the very beginning that we could have prevented all of this if we had used mysql_set_charset('gbk')instead of SET NAMES gbk. And that's true provided you are using a MySQL release since 2006.

我一开始就说过，如果我们使用mysql_set_charset('gbk')而不是SET NAMES gbk. 如果您使用的是自 2006 年以来的 MySQL 版本，那就是正确的。

If you're using an earlier MySQL release, then a bugin mysql_real_escape_string()meant that invalid multibyte characters such as those in our payload were treated as single bytes for escaping purposes even if the client had been correctly informed of the connection encodingand so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22and 5.1.11.

如果您使用的是较早的MySQL版本，那么错误的mysql_real_escape_string()意思是无效的多字节字符，例如那些在我们的有效载荷被视为转义目的单字节，即使客户端已正确通知连接编码的，因此这种攻击还是成功了。该错误已在 MySQL 4.1.20、5.0.22和5.1.11 中修复。

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

但最糟糕的是，直到 5.3.6PDO才公开 C API mysql_set_charset()，因此在以前的版本中，它无法针对每个可能的命令阻止这种攻击！它现在作为DSN 参数公开。

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. utf8mb4is not vulnerableand yet can support everyUnicode 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 vulnerableand can support the whole of the Unicode Basic Multilingual Plane.

正如我们一开始所说的，要使这种攻击起作用，必须使用易受攻击的字符集对数据库连接进行编码。 utf8mb4是不容易，但可以支持所有的Unicode字符：所以你可以选择使用的是代替，但它只是可利用从MySQL 5.5.3。另一种选择是utf8，它也不容易受到攻击，并且可以支持整个 Unicode基本多语言平面。

Alternatively, you can enable the NO_BACKSLASH_ESCAPESSQL mode, which (amongst other things) alters the operation of mysql_real_escape_string(). With this mode enabled, 0x27will be replaced with 0x2727rather than 0x5c27and thus the escaping process cannotcreate valid characters in any of the vulnerable encodings where they did not exist previously (i.e. 0xbf27is still 0xbf27etc.)—so the server will still reject the string as invalid. However, see @eggyal's answerfor a different vulnerability that can arise from using this SQL mode.

或者，您可以启用NO_BACKSLASH_ESCAPESSQL 模式，该模式（除其他外）会改变mysql_real_escape_string(). 启用此模式后，0x27将被替换为0x2727而不是0x5c27，因此转义过程无法在之前不存在的任何易受攻击的编码中创建有效字符（即0xbf27仍然0xbf27等），因此服务器仍将拒绝该字符串为无效. 但是，请参阅@eggyal对使用此 SQL 模式可能产生的不同漏洞的回答。

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

因为服务器期待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.

因为 MySQLi 一直在做真正的准备好的语句。

Wrapping Up

包起来

If you:

如果你：

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

MySQL的（晚5.1，所有5.5，5.6，等）使用的现代版本和mysql_set_charset()/ $mysqli->set_charset()/ PDO的DSN charset参数（在PHP 5.3.6≥）

OR

或者

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

不要使用有漏洞的字符集，用于连接编码（只使用utf8/ latin1/ ascii/等）

You're 100% safe.

你是 100% 安全的。

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

否则，即使您使用mysql_real_escape_string()...

Answer 3

回答by eggyal

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

TL; 博士
mysql_real_escape_string()如果出现以下情况，将不提供任何保护（并且可能还会篡改您的数据）：
MySQL 的NO_BACKSLASH_ESCAPESSQL 模式已启用（可能是这样，除非您每次连接时都明确选择另一种 SQL 模式）；和
您的 SQL 字符串文字使用双引号"字符引用。
这已作为错误 #72458提交并已在 MySQL v5.7.6 中修复（请参阅下面标题为“ The Saving Grace”的部分）。

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

这是另一个，（也许更少？）晦涩的 EDGE CASE！！！

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

为了向@ircmaxell 的出色回答致敬（真的，这应该是奉承而不是抄袭！），我将采用他的格式：

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 testtable. A dissection:

这将返回test表中的所有记录。剖析：

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.
The Payload
```
" OR 1=1 -- 
```
The payload initiates this injection quite literally with the "character. No particular encoding. No special characters. No weird bytes.

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_ESCAPESSQL 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 assumesthat 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 $varremains 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.

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

选择 SQL 模式
```
mysql_query('SET SQL_MODE="NO_BACKSLASH_ESCAPES"');
```
如字符串文字所述：
有几种方法可以在字符串中包含引号字符：
- A“ '”加引号的字符串内“ '”可以写为“ ''”。
- A“ "”加引号的字符串内“ "”可以写为“ ""”。
- 在引号字符之前加上转义字符 (“ \”)。
- A“ '”加引号的字符串内“ "”也不需要特殊对待而且不必增加一倍或逃脱。同样，用“ "”引用的字符串中的“ '”不需要特殊处理。
如果服务器的 SQL 模式包含NO_BACKSLASH_ESCAPES，则这些选项中的第三个（这是由通常采用的方法）mysql_real_escape_string()不可用：必须改用前两个选项之一。请注意，第四个项目符号的作用是必须知道将用于引用文字的字符，以避免修改数据。
有效载荷
```
" OR 1=1 -- 
```
有效载荷从字面上用"字符启动了这种注入。没有特别的编码。没有特殊字符。没有奇怪的字节。

mysql_real_escape_string()

$var = mysql_real_escape_string('" OR 1=1 -- ');

幸运的是，mysql_real_escape_string()确实会检查 SQL 模式并相应地调整其行为。见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);
}

因此escape_quotes_for_mysql()，如果NO_BACKSLASH_ESCAPES正在使用 SQL 模式，则会调用不同的底层函数。如上所述，这样的函数需要知道将使用哪个字符来引用文字，以便重复它而不会导致其他引用字符被逐字重复。

但是，此函数任意假定将使用单引号'字符引用字符串。见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++= '\'';
    }

因此，无论用于引用文字的实际字符如何，它都"不会改变双引号字符（并将所有单引号'字符加倍）！在我们的例子遗体完全一样，这是所提供的说法-它的，就像没有逃逸已经发生的一切。$varmysql_real_escape_string()

查询

mysql_query('SELECT * FROM test WHERE name = "'.$var.'" LIMIT 1');

某种形式，呈现的查询是：

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

正如我博学的朋友所说：恭喜你，你刚刚成功攻击了一个使用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.

mysql_set_charset()无济于事，因为这与字符集无关；也不能mysqli::real_escape_string()，因为这只是围绕同一功能的不同包装。

The problem, if not already obvious, is that the call to mysql_real_escape_string()cannot knowwith which character the literal will be quoted, as that's left to the developer to decide at a later time. So, in NO_BACKSLASH_ESCAPESmode, there is literally no waythat 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).

问题（如果还不是很明显）是调用mysql_real_escape_string()不知道将用哪个字符引用文字，因为这由开发人员稍后决定。因此，在NO_BACKSLASH_ESCAPESmode 中，这个函数实际上无法安全地转义每个输入以用于任意引用（至少，不是没有不需要加倍的加倍字符，从而修改数据）。

The Ugly

丑陋的

It gets worse. NO_BACKSLASH_ESCAPESmay 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 workaroundto the (long since fixed) bugthat 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().

它变得更糟。 NO_BACKSLASH_ESCAPES由于必须使用它与标准 SQL 兼容（例如，请参阅SQL-92 规范的第 5.3 节，即<quote symbol> ::= <quote><quote>语法产生式和没有赋予反斜杠的任何特殊含义），在野外可能并不少见。此外，明确推荐使用它作为ircmaxell 的帖子描述的（很久以来已修复的）错误的解决方法。谁知道呢，有些 DBA 甚至可能将其配置为默认启用，以阻止使用不正确的转义方法，例如addslashes().

Also, the SQL mode of a new connectionis set by the server according to its configuration (which a SUPERuser can change at any time); thus, to be certain of the server's behaviour, you must alwaysexplicitly specify your desired mode after connecting.

此外，新连接的SQL 模式由服务器根据其配置设置（SUPER用户可以随时更改）；因此，要确定服务器的行为，您必须始终在连接后明确指定所需的模式。

The Saving Grace

拯救的恩典

So long as you always explicitlyset 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.

只要你总是显式地设置 SQL 模式不包含NO_BACKSLASH_ESCAPES，或者使用单引号字符引用 MySQL 字符串文字，这个错误就不会引起它的丑陋：分别escape_quotes_for_mysql()不会被使用，或者它关于哪些引号字符需要重复的假设会是正确的。

For this reason, I recommend that anyone using NO_BACKSLASH_ESCAPESalso enables ANSI_QUOTESmode, 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).

出于这个原因，我建议任何使用的人NO_BACKSLASH_ESCAPES也启用ANSI_QUOTES模式，因为它会强制习惯性地使用单引号字符串文字。请注意，如果碰巧使用了双引号文字，这并不能阻止 SQL 注入——它只是降低了发生这种情况的可能性（因为正常的非恶意查询会失败）。

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.

在 PDO 中，它的等效函数PDO::quote()和它的准备语句模拟器都调用mysql_handle_quoter()——这正是这样做的：它确保转义的文字用单引号引用，因此您可以确定 PDO 始终不受此错误的影响。

As of MySQL v5.7.6, this bug has been fixed. See change log:

从 MySQL v5.7.6 开始，此错误已得到修复。查看更改日志：

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_ESCAPESSQL 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_ERRerror if NO_BACKSLASH_ESCAPESis enabled.
References: See also Bug #19211994.

添加或更改的功能
不兼容的更改：mysql_real_escape_string_quote()已实现新的 C API 函数，以替代，mysql_real_escape_string()因为当NO_BACKSLASH_ESCAPES启用 SQL 模式时，后一个函数可能无法正确编码字符。在这种情况下，mysql_real_escape_string()不能转义引号字符，除非将它们加倍，并且要正确执行此操作，它必须了解有关引用上下文的更多信息。mysql_real_escape_string_quote()需要一个额外的参数来指定引用上下文。有关使用详细信息，请参阅mysql_real_escape_string_quote()。
？笔记
应用程序应修改为使用mysql_real_escape_string_quote()，而不是mysql_real_escape_string()，CR_INSECURE_API_ERR如果NO_BACKSLASH_ESCAPES启用，它现在会失败并产生错误。
参考资料：另见错误 #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):

结合ircmaxell解释的bug，下面的例子是完全安全的（假设一个是使用4.1.20、5.0.22、5.1.11之后的MySQL；或者一个没有使用GBK/Big5连接编码）：

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.

...因为我们明确选择了不包含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).

...因为 PDO 准备好的语句不受此漏洞的影响（并且 ircmaxell 也是如此，前提是您使用的是 PHP≥5.3.6 并且字符集已在 DSN 中正确设置；或者准备好的语句模拟已被禁用） .

$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 mustbe using PHP≥5.3.6 andhave correctly set the character set in the DSN.

...因为 PDO 的quote()函数不仅转义了文字，而且还引用了它（在单引号'字符中）；请注意，在这种情况下，为了避免 ircmaxell 的错误，您必须使用 PHP≥5.3.6并在 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.

...因为 MySQLi 准备好的语句是安全的。

Wrapping Up

包起来

Thus, if you:

因此，如果您：

use native prepared statements

使用原生准备好的语句

OR

或者

use MySQL v5.7.6 or later

使用 MySQL v5.7.6 或更高版本

OR

或者

in additionto 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

在除于使用在ircmaxell的总结的解决方案之一，使用，并在至少一个：
- PDO;
- 单引号字符串文字；或者
- 显式设置的 SQL 模式，不包括 NO_BACKSLASH_ESCAPES

...then you shouldbe completely safe (vulnerabilities outside the scope of string escaping aside).

...那么你应该是完全安全的（除了字符串转义范围之外的漏洞）。

Answer 4

回答by Slava

Well, there's nothing really that can pass through that, other than %wildcard. It could be dangerous if you were using LIKEstatement 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);

好吧，除了%通配符之外，没有任何东西可以通过它。如果您使用LIKE语句，则可能会很危险，%因为如果您不过滤掉它，攻击者可以将其作为登录名，并且只需要对您的任何用户的密码进行暴力破解。人们通常建议使用准备好的语句使其 100% 安全，因为数据不会以这种方式干扰查询本身。但是对于这样简单的查询，执行类似的操作可能会更有效$login = preg_replace('/[^a-zA-Z0-9_]/', '', $login);

php 绕过 mysql_real_escape_string() 的 SQL 注入

提问by Richard Knop

采纳答案by Wesley van Opdorp

回答by ircmaxell

For Very OBSCURE EDGE CASES!!!

对于非常模糊的边缘情况！！！

The Attack

攻击

The Bad

坏的

The Ugly

丑陋的

The Saving Grace

拯救的恩典

Safe Examples

安全示例

Wrapping Up

包起来

回答by eggyal

TL;DR

TL; 博士

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

这是另一个，（也许更少？）晦涩的 EDGE CASE！！！

The Attack

攻击

The Bad

坏的

The Ugly

丑陋的

The Saving Grace

拯救的恩典

Functionality Added or Changed

?Note

添加或更改的功能

？笔记

Safe Examples

安全示例

Wrapping Up

包起来

回答by Slava

相关推荐

php 如何用php从文件中删除一行？

php 调用未定义的函数：simplexml_load_string()

php 如何在php中取消链接图像

如何使用 PHP 发送 HTML 格式的邮件

相关推荐

最近更新

标签