php PDO 准备好的语句是否足以防止 SQL 注入？

Are PDO prepared statements sufficient to prevent SQL injection?

提问by Mark Biek

Let's say I have code like this:

假设我有这样的代码：

$dbh = new PDO("blahblah");

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

The PDO documentation says:

PDO 文档说：

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

准备好的语句的参数不需要被引用；司机为您处理。

Is that truly all I need to do to avoid SQL injections? Is it really that easy?

这真的是我避免 SQL 注入所需要做的全部吗？真的那么容易吗？

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.

如果它有所作为，您可以假设 MySQL。另外，我真的只是对使用准备好的语句来对抗 SQL 注入感到好奇。在这种情况下，我不关心 XSS 或其他可能的漏洞。

回答by ircmaxell

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

简短的回答是否定的，PDO 准备不会保护您免受所有可能的 SQL 注入攻击。对于某些模糊的边缘情况。

I'm adapting this answerto talk about PDO...

我正在调整这个答案来谈论 PDO ...

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

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

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

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

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

1. Selecting a Character Set

   $pdo->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. There is another way of doing it, but we'll get there soon enough.

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

3. $stmt->execute()

   The important thing to realize here is that PDO by default does NOTdo 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.

   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 what the attack requires.

4. The Query

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

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

1. 选择字符集

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

   为了使这种攻击起作用，我们需要服务器在连接上期望的编码，既要编码'为 ASCII ie 0x27，又要有一些字符的最后一个字节是 ASCII \ie 0x5c。事实证明，会默认在MySQL 5.6支持5个这样的编码：big5，cp932，gb2312，gbk和sjis。我们会选择gbk这里。

   现在，注意SET NAMES这里的使用非常重要。这将设置字符集ON THE SERVER。还有另一种方法，但我们很快就会到达那里。

2. 有效载荷

   我们将用于此注入的有效负载以字节序列开头0xbf27。在 中gbk，这是一个无效的多字节字符；在 中latin1，它是字符串?'。请注意， inlatin1和gbk,0x27本身就是一个文字'字符。

   我们选择了这个有效载荷，因为如果我们调用addslashes()它，我们会在字符之前插入一个 ASCII \ie 。所以我们最终会得到，它是一个两个字符的序列：后跟. 或者换句话说，一个有效字符后跟一个未转义的. 但我们没有使用. 那么进入下一步...0x5c'0xbf5c27gbk0xbf5c0x27'addslashes()

3. $stmt->execute()

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

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

   因此调用mysql_real_escape_string()插入反斜杠，我们'在“转义”内容中有一个自由悬挂字符！事实上，如果我们看一下$var在gbk字符集，我们会看到：

   縗' OR 1=1 /*

   这正是攻击所需要的。

4. 查询

   这部分只是一种形式，但这里是呈现的查询：

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

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

恭喜，你刚刚成功攻击了一个使用 PDO Prepared Statements 的程序......

The Simple Fix

简单的修复

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 Correct Fix

正确的修复

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.

这里的问题是我们没有调用 C APImysql_set_charset()而不是SET NAMES. 如果我们这样做了，如果我们使用自 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, which should be used instead ofSET NAMES...

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

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

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

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) ANDPDO's DSN charset parameter (in PHP ≥ 5.3.6)

• 使用 MySQL 的现代版本（5.1 后期，所有 5.5、5.6 等）和PDO 的 DSN 字符集参数（在 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/等）

OR

或者

• Enable NO_BACKSLASH_ESCAPESSQL mode

• 启用NO_BACKSLASH_ESCAPESSQL 模式

You're 100% safe.

你是 100% 安全的。

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

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

Addendum

附录

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

我一直在慢慢研究一个补丁，将默认设置更改为不模拟为未来版本的 PHP 做准备。我遇到的问题是，当我这样做时，很多测试都会中断。一个问题是模拟准备只会在执行时抛出语法错误，而真正的准备会在准备时抛出错误。所以这可能会导致问题（并且是测试失败的部分原因）。

回答by Joel Coehoorn

Prepared statements / parameterized queries are generally sufficient to prevent 1st orderinjection on that statement^*. If you use un-checked dynamic sql anywhere else in your application you are still vulnerable to 2nd orderinjection.

准备好的语句/参数化查询通常足以防止对该语句的一阶注入^*。如果您在应用程序的其他任何地方使用未经检查的动态 sql，您仍然容易受到二阶注入的影响。

2nd order injection means data has been cycled through the database once before being included in a query, and is much harder to pull off. AFAIK, you almost never see real engineered 2nd order attacks, as it is usually easier for attackers to social-engineer their way in, but you sometimes have 2nd order bugs crop up because of extra benign 'characters or similar.

二阶注入意味着数据在被包含在查询中之前已经在数据库中循环了一次，并且更难实现。AFAIK，您几乎从未见过真正设计的二阶攻击，因为攻击者通常更容易通过社交工程进入，但有时您会因为额外的良性'字符或类似字符而出现二阶错误。

You can accomplish a 2nd order injection attack when you can cause a value to be stored in a database that is later used as a literal in a query. As an example, let's say you enter the following information as your new username when creating an account on a web site (assuming MySQL DB for this question):

当您可以将一个值存储在数据库中，然后在查询中用作文字时，您就可以完成二阶注入攻击。例如，假设您在网站上创建帐户时输入以下信息作为新用户名（假设此问题使用 MySQL DB）：

' + (SELECT UserName + '_' + Password FROM Users LIMIT 1) + '

If there are no other restrictions on the username, a prepared statement would still make sure that the above embedded query doesn't execute at the time of insert, and store the value correctly in the database. However, imagine that later the application retrieves your username from the database, and uses string concatenation to include that value a new query. You might get to see someone else's password. Since the first few names in users table tend to be admins, you may have also just given away the farm. (Also note: this is one more reason not to store passwords in plain text!)

如果对用户名没有其他限制，准备好的语句仍将确保上述嵌入式查询在插入时不执行，并将值正确存储在数据库中。但是，假设稍后应用程序从数据库中检索您的用户名，并使用字符串连接将该值包含在一个新查询中。您可能会看到其他人的密码。由于用户表中的前几个名字往往是管理员，您可能也刚刚放弃了农场。（另请注意：这是不以纯文本形式存储密码的另一个原因！）

We see, then, that prepared statements are enough for a single query, but by themselves they are notsufficient to protect against sql injection attacks throughout an entire application, because they lack a mechanism to enforce all access to a database within an application uses safe code. However, used as part of good application design — which may include practices such as code review or static analysis, or use of an ORM, data layer, or service layer that limits dynamic sql — prepared statementsare the primary tool for solving the Sql Injection problem.If you follow good application design principles, such that your data access is separated from the rest of your program, it becomes easy to enforce or audit that every query correctly uses parameterization. In this case, sql injection (both first and second order) is completely prevented.

我们看到，那么，准备语句是足以让一个单一的查询，但它们本身是不足够的，以防止SQL注入攻击遍及整个应用程序，因为他们缺乏一种机制来执行应用程序中的所有对数据库的访问使用安全代码。但是，作为良好应用程序设计的一部分——可能包括代码或静态分析等实践，或者使用限制动态 sql 的 ORM、数据层或服务层——准备好的语句是解决 Sql 注入的主要工具问题。如果您遵循良好的应用程序设计原则，例如将数据访问与程序的其余部分分开，则很容易强制或审核每个查询正确使用参数化。在这种情况下，完全阻止了sql注入（一阶和二阶）。

---

---

_{^*It turns out that MySql/PHP are (okay, were) just dumb about handling parameters when wide characters are involved, and there is still a rarecase outlined in the other highly-voted answer herethat can allow injection to slip through a parameterized query.}

_{^*事实证明，当涉及宽字符时，MySql/PHP 只是（好吧，曾经）在处理参数方面很愚蠢，并且这里的另一个高票答案中仍然概述了一种罕见的情况，它可以允许注入通过参数化询问。}

回答by Tower

No, they are not always.

不，它们并非总是如此。

It depends on whether you allow user input to be placed within the query itself. For example:

这取决于您是否允许将用户输入放置在查询本身中。例如：

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];

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

would be vulnerable to SQL injections and using prepared statements in this example won't work, because the user input is used as an identifier, not as data. The right answer here would be to use some sort of filtering/validation like:

将容易受到 SQL 注入的攻击，并且在此示例中使用准备好的语句将不起作用，因为用户输入被用作标识符，而不是数据。这里的正确答案是使用某种过滤/验证，例如：

$dbh = new PDO("blahblah");

$tableToUse = $_GET['userTable'];
$allowedTables = array('users','admins','moderators');
if (!in_array($tableToUse,$allowedTables))    
 $tableToUse = 'users';

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

Note: you can't use PDO to bind data that goes outside of DDL (Data Definition Language), i.e. this does not work:

注意：您不能使用 PDO 绑定超出 DDL（数据定义语言）的数据，即这不起作用：

$stmt = $dbh->prepare('SELECT * FROM foo ORDER BY :userSuppliedData');

The reason why the above does not work is because DESCand ASCare not data. PDO can only escape for data. Secondly, you can't even put 'quotes around it. The only way to allow user chosen sorting is to manually filter and check that it's either DESCor ASC.

之所以上述不起作用，是因为DESC和ASC不是数据。PDO 只能转义数据。其次，你甚至不能'在它周围加上引号。允许用户选择排序的唯一方法是手动过滤并检查它是否为DESC或ASC。

回答by PeeHaa

No this is not enough (in some specific cases)! By default PDO uses emulated prepared statements when using MySQL as a database driver. You should always disable emulated prepared statements when using MySQL and PDO:

不，这还不够（在某些特定情况下）！默认情况下，当使用 MySQL 作为数据库驱动程序时，PDO 使用模拟准备好的语句。使用 MySQL 和 PDO 时，您应该始终禁用模拟准备好的语句：

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

Another thing that always should be done it set the correct encoding of the database:

另一件始终应该做的事情是设置数据库的正确编码：

$dbh = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'pass');

Also see this related question: How can I prevent SQL injection in PHP?

另请参阅此相关问题：如何防止 PHP 中的 SQL 注入？

Also note that that only is about the database side of the things you would still have to watch yourself when displaying the data. E.g. by using htmlspecialchars()again with the correct encoding and quoting style.

另请注意，这仅与您在显示数据时仍需注意的事情的数据库方面有关。例如，通过使用htmlspecialchars()正确的编码和引用样式再次使用。

回答by troelskn

Yes, it is sufficient. The way injection type attacks work, is by somehow getting an interpreter (The database) to evaluate something, that should have been data, as if it was code. This is only possible if you mix code and data in the same medium (Eg. when you construct a query as a string).

是的，这就足够了。注入类型攻击的工作方式是以某种方式让解释器（数据库）评估某些东西，它应该是数据，就好像它是代码一样。这只有在您将代码和数据混合在同一介质中时才有可能（例如，当您将查询构造为字符串时）。

Parameterised queries work by sending the code and the data separately, so it would neverbe possible to find a hole in that.

参数化查询通过分别发送代码和数据来工作，因此永远不可能在其中找到漏洞。

You can still be vulnerable to other injection-type attacks though. For example, if you use the data in a HTML-page, you could be subject to XSS type attacks.

但是，您仍然可能容易受到其他注入式攻击。例如，如果您在 HTML 页面中使用数据，您可能会受到 XSS 类型的攻击。

回答by JimmyJ

Personally I would always run some form of sanitation on the data first as you can never trust user input, however when using placeholders / parameter binding the inputted data is sent to the server separately to the sql statement and then binded together. The key here is that this binds the provided data to a specific type and a specific use and eliminates any opportunity to change the logic of the SQL statement.

就我个人而言，我总是首先对数据运行某种形式的卫生，因为您永远不能相信用户输入，但是当使用占位符/参数绑定时，输入的数据会分别发送到服务器的 sql 语句，然后绑定在一起。这里的关键是，这将提供的数据绑定到特定类型和特定用途，并消除了更改 SQL 语句逻辑的任何机会。

回答by snipershady

Eaven if you are going to prevent sql injection front-end, using html or js checks, you'd have to consider that front-end checks are "bypassable".

即使您要防止 sql 注入前端，使用 html 或 js 检查，您必须考虑前端检查是“可绕过的”。

You can disable js or edit a pattern with a front-end development tool (built in with firefox or chrome nowadays).

您可以禁用 js 或使用前端开发工具（现在内置 firefox 或 chrome）编辑模式。

So, in order to prevent SQL injection, would be right to sanitize input date backend inside your controller.

因此，为了防止 SQL 注入，清理控制器内的输入日期后端是正确的。

I would like to suggest to you to use filter_input() native PHP function in order to sanitize GET and INPUT values.

我想建议您使用 filter_input() 原生 PHP 函数来清理 GET 和 INPUT 值。

If you want to go ahead with security, for sensible database queries, I'd like to suggest to you to use regular expression to validate data format. preg_match() will help you in this case! But take care! Regex engine is not so light. Use it only if necessary, otherwise your application performances will decrease.

如果您想继续安全，对于明智的数据库查询，我建议您使用正则表达式来验证数据格式。在这种情况下，preg_match() 会帮助你！但要小心！正则表达式引擎不是那么轻。仅在必要时使用它，否则您的应用程序性能将下降。

Security has a costs, but do not waste your performance!

安全是有代价的，但不要浪费你的性能！

Easy example:

简单的例子：

if you want to double check if a value, received from GET is a number, less then 99 if(!preg_match('/[0-9]{1,2}/')){...} is heavyer of

如果你想仔细检查从 GET 收到的值是否是一个数字，小于 99 if(!preg_match('/[0-9]{1,2}/')){...} 是更重的

if (isset($value) && intval($value)) <99) {...}

So, the final answer is: "No! PDO Prepared Statements does not prevent all kind of sql injection"; It does not prevent unexpected values, just unexpected concatenation

所以，最终的答案是：“不！PDO Prepared Statements 并不能阻止所有类型的 sql 注入”；它不会阻止意外的值，只是意外的串联