Question

Because SQL injections are so devastating to an application’s security, you must take action to prevent them. One way you can prevent SQL injections is by using prepared statements. Prepared statements are also called parameterized queries , and they make SQL injections virtually impossible.

由于 SQL 注入对应用程序的安全性有如此巨大的破坏力，所以您必须采取措施来防止它们。其中一种方式是使用准备语句进行预防 SQL 注入攻击。准备语句也称作参数化查询，它们几乎可以使 SQL 注入攻击变成不可能。

Before we dive into how prepared statements work, it’s important to understand how SQL queries are executed. SQL is a programming language, and your SQL query is essentially a program. When the SQL program arrives at the SQL server, the server will parse, compile, and optimize it. Finally, the server will execute the program and return the results of the execution ( Figure 11-1 ) .

在我们深入了解预处理语句的工作方式之前，重要的是要了解 SQL 查询的执行方式。SQL 是一种编程语言，你的 SQL 查询本质上就是一个程序。当 SQL 程序到达 SQL 服务器时，服务器将对其进行解析、编译和优化。最后，服务器将执行程序并返回执行结果（图 11-1）。

Figure 11-1 : Life of a SQL query

图 11-1：SQL 查询的生命周期

When you insert user-supplied input into your SQL queries, you are basically rewriting your program dynamically, using user input. An attacker can supply data that interferes with the program’s code and alter its logic ( Figure 11-2 ).

当您将用户提供的输入插入到 SQL 查询中时，您基本上是动态地重写程序，使用用户输入。攻击者可以提供干扰程序代码并改变其逻辑的数据（图 11-2）。

Figure 11-2 : A SQL query that concatenates user input into the query before compilation will make the database treat user input as code.

图 11-2：在编译之前将用户输入连接到查询中的 SQL 查询将使数据库将用户输入视为代码。

Prepared statements work by making sure that user-supplied data does not alter your SQL query’s logic. These SQL statements are sent to and compiled by the SQL server before any user-supplied parameters are inserted. This means that instead of passing a complete SQL query to the server to be compiled, you define all the SQL logic first, compile it, and then insert user-supplied parameters into the query right before execution ( Figure 11-3 ). After the parameters are inserted into the final query, the query will not be parsed and compiled again.

预处理语句通过确保用户提供的数据不会改变您的 SQL 查询逻辑来工作。在任何用户提供的参数被插入之前，这些 SQL 语句将被发送到 SQL 服务器并编译。这意味着你首先定义所有 SQL 逻辑、编译它，然后在执行之前将用户提供的参数插入查询中（图 11-3）。在参数插入到最终的查询之后，查询不会再次被解析和编译。

Figure 11-3 : A SQL query that concatenates user input into the query after compilation allows the database to distinguish between the code part and the data part of the SQL query.

图 11-3：编译后将用户输入连接到查询中的 SQL 查询允许数据库区分 SQL 查询的代码部分和数据部分。

Anything that wasn’t in the original statement will be treated as string data, not executable SQL code, so the program logic part of your SQL query will remain intact. This allows the database to distinguish between the code part and the data part of the SQL query, regardless of what the user input looks like.

任何原陈述中没有的内容将被视为字符串数据，而非可执行的 SQL 代码，因此 SQL 查询的程序逻辑部分将保持不变。这使得数据库能够区分 SQL 查询的代码部分和数据部分，无论用户输入的是什么样子。

Let’s look at an example of how to execute SQL statements safely in PHP. Say that we want to retrieve a user’s ID by using their provided username and password, so we want to execute this SQL statement:

让我们看一下 PHP 中如何安全执行 SQL 语句的示例。假设我们想通过使用提供的用户名和密码检索用户 ID，因此我们想要执行以下 SQL 语句：

SELECT Id FROM Users
WHERE Username=USERNAME AND Password=PASSWORD;

Here’s how to do that in PHP:

这是如何在 PHP 中实现的方法：

$mysqli = new mysqli("mysql_host", "mysql_username", "mysql_password", "database_name"); 1

$username = $_POST["username"]; 2
$password = $_POST["password"]; 3

In PHP, we first establish a connection with our database 1 , and then retrieve the username and password as POST parameters from the user 2 3 .

在 PHP 中，我们首先与数据库 1 建立连接，然后从用户 2 3 检索用户名和密码作为 POST 参数。

To use a prepared statement, you would define the structure of the query first. We’ll write out the query without its parameters, and put question marks as placeholders for the parameters:

要使用预处理语句，您需要首先定义查询的结构。我们会先将查询写出来，而将参数用问号作为占位符：

$stmt = $mysqli->prepare(    "SELECT Id FROM Users WHERE Username=? AND Password=?" );

This query string will now be compiled by the SQL server as SQL code. You can then send over the parameters of the query separately. The following line of code will insert the user input into the SQL query:

此查询字符串现在将由 SQL 服务器编译为 SQL 代码。然后，您可以单独发送查询参数。以下代码行将将用户输入插入 SQL 查询中：

$stmt->bind_param("ss", $username, $password);

Finally, you execute the query:

最终，你执行查询：

$stmt->execute();

The username and password values provided by the user aren’t compiled like the statement template, and aren’t executed as the logic part of the SQL code. Therefore, if an attacker provides the application with a malicious input like this one, the entire input would be treated as plain data, not as SQL code:

用户提供的用户名和密码值未编译成语句模板，并且不作为 SQL 代码的逻辑部分执行。因此，如果攻击者向应用程序提供恶意输入，整个输入将被视为纯数据，而不是 SQL 代码：

Password12345';--

How to use prepared statements depends on the programming language you are using to code your applications. Wikipedia provides a few examples: https://en.wikipedia.org/wiki/Prepared_statement .

如何使用预处理语句取决于您使用的编程语言来编写应用程序。维基百科提供了一些示例：https://en.wikipedia.org/wiki/Prepared_statement。

Another way of preventing SQL injections is to use an allowlist for allowed values. For example, the SQL ORDER BY clause allows a query to specify the column by which to sort the results. Therefore, this query will return all of the user’s emails in our table, sorted by the Date column, in descending order:

另外一种防止 SQL 注入的方法是使用允许值的允许列表。例如，SQL ORDER BY 子句允许查询指定用于排序结果的列。因此，此查询将按日期列的降序返回表中所有用户的电子邮件。

SELECT Title, Body FROM Emails
WHERE Username='vickie' AND AccessKey='ZB6w0YLjzvAVmp6zvr';
ORDER BY Date DESC;

If the application allows users to specify a column to use for ordering their email, it can rely on an allowlist of column names for the ORDER BY clause instead of allowing arbitrary input from the user. For example, the application can allow only the values Date , Sender , and Title , and reject all other user-input values.

如果应用程序允许用户指定用于排序电子邮件的列，则可以依赖于允许列表中的列名进行 ORDER BY 子句，而不是允许用户提供任意输入。例如，应用程序只能允许 Date、Sender 和 Title 这些值，并拒绝所有其他用户输入的值。

Finally, you can carefully sanitize and escape user input. However, this approach isn’t entirely bulletproof, because it’s easy to miss special characters that attackers could use to construct a SQL injection attack. Special characters that should be sanitized or escaped include the single quote ( ' ) and double quote ( " ), but special characters specific to each type of database also exist. For more information about SQL input sanitization, read OWASP’s cheat sheet at https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html .

最后，你可以仔细地对用户输入进行消毒和转义。然而，这种方法并不完全安全，因为很容易忽略攻击者可能用来构建 SQL 注入攻击的特殊字符。应该消毒或转义的特殊字符包括单引号 (') 和双引号 (")，但每种类型的数据库都存在特定的特殊字符。关于 SQL 输入消毒的更多信息，请阅读 OWASP 的作弊表，网址为 https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html。