PDO准备好的语句是否足以防止SQL注入? [英] Are PDO prepared statements sufficient to prevent SQL injection?

问题描述

假设我有这样的代码:

$dbh = new PDO("blahblah");

$stmt = $dbh->prepare('SELECT * FROM users where username = :username');
$stmt->execute( array(':username' => $_REQUEST['username']) );

PDO文档说:

预处理语句的参数不需要加引号；司机会为您处理.

The parameters to prepared statements don't need to be quoted; the driver handles it for you.

我真的需要做一切以避免SQL注入吗?真的那么容易吗?

如果可以发挥作用，则可以假定MySQL.另外，我真的只是对针对SQL注入使用准备好的语句感到好奇.在这种情况下，我不在乎XSS或其他可能的漏洞.

You can assume MySQL if it makes a difference. Also, I'm really only curious about the use of prepared statements against SQL injection. In this context, I don't care about XSS or other possible vulnerabilities.

推荐答案

简短的回答是否，PDO准备将无法保护您免受所有可能的SQL注入攻击.对于某些晦涩的边缘情况.

The short answer is NO, PDO prepares will not defend you from all possible SQL-Injection attacks. For certain obscure edge-cases.

我正在适应此答案以谈论PDO ...

I'm adapting this answer to talk about PDO...

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

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

所以，让我们从攻击开始...

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

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

在某些情况下，它将返回多于1行.让我们剖析这里发生的事情:

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

1. 选择字符集

$pdo->query('SET NAMES gbk');

要使此攻击起作用，我们需要服务器期望连接时使用的编码既要编码'也要像ASCII中那样编码0x27 和，以使其某些字符的最终字节为ASCII \，即0x5c.事实证明，默认情况下，MySQL 5.6默认支持5种此类编码:big5，cp932，gb2312，gbk和sjis.我们将在此处选择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. 0x27 and to 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, gbk and sjis. We'll select gbk here.

现在，在这里注意SET NAMES的使用非常重要.这将在服务器上上设置字符集.还有另一种方法，但是我们会尽快到达那里.

Now, it's very important to note the use of SET NAMES here. This sets the character set ON THE SERVER. There is another way of doing it, but we'll get there soon enough.

有效载荷

我们将用于此注入的有效负载从字节序列0xbf27开始.在gbk中，这是一个无效的多字节字符；在latin1中，它是字符串¿'.请注意，在latin1 和 gbk中，0x27本身是文字'字符.

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 latin1 and gbk, 0x27 on its own is a literal ' character.

之所以选择此有效负载，是因为如果在其上调用addslashes()，则会在'字符之前插入ASCII \，即0x5c.因此，我们将以0xbf5c27结尾，在gbk中是两个字符序列:0xbf5c后跟0x27.换句话说，是 valid 字符，后跟未转义的'.但是我们没有使用addslashes().继续下一步...

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 gbk is a two character sequence: 0xbf5c followed by 0x27. Or in other words, a valid character followed by an unescaped '. But we're not using addslashes(). So on to the next step...

$ stmt-> execute()

这里要意识到的重要一点是，默认情况下PDO不会不执行真正的预准备语句.它模拟它们(对于MySQL).因此，PDO在内部构建查询字符串，对每个绑定的字符串值调用mysql_real_escape_string()(MySQL C API函数).

The important thing to realize here is that PDO by default does NOT do true prepared statements. It emulates them (for MySQL). Therefore, PDO internally builds the query string, calling mysql_real_escape_string() (the MySQL C API function) on each bound string value.

对mysql_real_escape_string()的C API调用与addslashes()的不同之处在于，它知道连接字符集.因此，它可以为服务器期望的字符集正确执行转义.但是，到目前为止，客户端认为我们仍在使用latin1进行连接，因为我们从未告诉过它.我们确实告诉服务器我们正在使用gbk，但是 client 仍然认为它是latin1.

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 latin1 for the connection, because we never told it otherwise. We did tell the server we're using gbk, but the client still thinks it's latin1.

因此，对mysql_real_escape_string()的调用将插入反斜杠，并且我们的转义"内容中有一个自由悬挂的'字符！实际上，如果我们要查看gbk字符集中的$var，我们会看到:

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 $var in the gbk character set, we'd see:

縗' OR 1=1 /*

这正是攻击所需要的.

查询

这只是一个形式，但这是呈现的查询:

This part is just a formality, but here's the rendered query:

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

恭喜，您刚刚使用PDO预准备语句成功攻击了一个程序...

Congratulations, you just successfully attacked a program using PDO Prepared Statements...

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

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

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

这通常会 产生一个真正的准备好的语句(即，数据从与查询分开的数据包中发送出去).但是，请注意，PDO会静静地 fallback 模拟MySQL本身无法准备的语句:可以

This will usually result 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 fallback to emulating statements that MySQL can't prepare natively: those that it can are listed in the manual, but beware to select the appropriate server version).

这里的问题是我们没有调用C API的mysql_set_charset()而不是SET NAMES.如果这样做的话，如果我们从2006年开始使用MySQL版本，就可以了.

The problem here is that we didn't call the C API's mysql_set_charset() instead of SET NAMES. If we did, we'd be fine provided we are using a MySQL release since 2006.

如果您使用的是较早的MySQL版本，请在其中输入 bug mysql_real_escape_string()意味着出于逃避目的，即使有效载荷中的无效多字节字符也被视为单个字节，即使客户端已被正确告知连接编码，所以这种攻击仍然会成功.该错误已在MySQL 4.1.20 中修复. a>， 5.0.22 和 5.1.11

If you're using an earlier MySQL release, then a bug in 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 encoding and so this attack would still succeed. The bug was fixed in MySQL 4.1.20, 5.0.22 and 5.1.11.

但是最糟糕的是，PDO直到5.3.6才公开mysql_set_charset()的C API，因此在以前的版本中，无法阻止所有可能的命令的这种攻击！ 现在，它已显示为 DSN参数，该参数应为使用代替 SET NAMES ...

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, which should be used instead of SET NAMES...

正如我们在一开始所说的那样，要使此攻击起作用，必须使用易受攻击的字符集对数据库连接进行编码. utf8mb4 不容易受到攻击并且可以支持每个 Unicode字符:因此，您可以选择使用它，但只能从MySQL 5.5.3开始使用.另一种选择是 utf8 ，它也是 ，并且可以支持整个Unicode 基本多语言平面.

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.

或者，您可以启用 NO_BACKSLASH_ESCAPES SQL模式，(其中包括)更改mysql_real_escape_string()的操作.启用此模式后，0x27将替换为0x2727而不是0x5c27，因此转义过程不能用以前不存在的任何易受攻击的编码创建有效字符(即0xbf27仍然是0xbf27等)，因此服务器仍将字符串视为无效.但是，请参阅 @eggyal的答案，以了解使用此SQL模式(尽管不是PDO)可能引起的其他漏洞.

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 (albeit not with PDO).

以下示例是安全的:

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

因为服务器期望utf8 ...

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

因为MySQLi一直在执行真正的预备语句.

Because MySQLi does true prepared statements all the time.

如果您:

• 使用MySQL的现代版本(5.1版，所有5.5版，5.6版等) AND PDO的DSN字符集参数(在PHP≥5.3.6中)

• Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) AND PDO's DSN charset parameter (in PHP ≥ 5.3.6)

OR

• 请勿使用易受攻击的字符集进行连接编码(您只能使用utf8/latin1/ascii/等)

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

OR

• 启用NO_BACKSLASH_ESCAPES SQL模式

• Enable NO_BACKSLASH_ESCAPES SQL mode

您100％安全.

否则，即使您使用的是PDO预准备语句，也容易受到攻击

Otherwise, you're vulnerable even though you're using PDO Prepared Statements...

我一直在缓慢地开发一个补丁程序，以更改默认值，以不模仿将来的PHP版本.我遇到的问题是，当我这样做时，很多测试都失败了.一个问题是，模拟的Prepare只会在执行时抛出语法错误，而真正的Prepare则会在Prepare上抛出错误.这样可能会导致问题(这是测试很乏味的部分原因).

I've been slowly working on a patch to change the default to not emulate prepares for a future version of PHP. The problem that I'm running into is that a LOT of tests break when I do that. One problem is that emulated prepares will only throw syntax errors on execute, but true prepares will throw errors on prepare. So that can cause issues (and is part of the reason tests are borking).

这篇关于PDO准备好的语句是否足以防止SQL注入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！