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.

推荐答案

简短的回答是NO，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 = "xbfx27 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 的使用.这将设置字符集ON THE SERVER.还有另一种方法，但我们很快就会到达那里.

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.

我们选择这个payload是因为，如果我们在上面调用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，但客户端仍然认为它是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 Prepared Statements 的程序......

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 将默默地回退 模拟 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 版本，则错误mysql_real_escape_string() 意味着无效的多字节字符(例如我们的有效负载中的那些字符)被视为单字节用于转义即使客户端已被正确通知连接编码等等这次攻击还是会成功的.该错误已在 MySQL 4.1.20, 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 Basic多语言平面.

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("xbfx27 OR 1=1 /*");
mysql_query("SELECT * FROM test WHERE name = '$var' LIMIT 1");

因为服务器期待utf8...

mysql_set_charset('gbk');
$var = mysql_real_escape_string("xbfx27 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("xbfx27 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("xbfx27 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 = "xbfx27 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)

或

• 不要使用易受攻击的字符集进行连接编码(你只使用utf8/latin1/ascii/etc)

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

或

• 启用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 的未来版本做准备.我遇到的问题是，当我这样做时，很多测试都会中断.一个问题是模拟准备只会在执行时抛出语法错误，而真正的准备会在准备时抛出错误.所以这可能会导致问题(这也是测试很无聊的部分原因).

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屋！