Question

To understand SQL injections, let’s start by understanding what SQL is. Structured Query Language (SQL) is a language used to manage and communicate with databases.

了解 SQL 注入，首先需要了解 SQL 是什么。SQL 是一种管理和与数据库通信的语言。

Traditionally, a database contains tables, rows, columns, and fields. The rows and columns contain the data, which gets stored in single fields. Let’s say that a web application’s database contains a table called Users ( Table 11-1 ). This table contains three columns: ID, Username, and Password. It also contains three rows of data, each storing the credentials of a different user.

传统上，数据库包含表、行、列和字段。 行和列包含数据，这些数据存储在单个字段中。 假设 Web 应用程序的数据库包含一个名为用户（表 11-1）的表。 该表包含三个列：ID、用户名和密码。它还包含三行数据，每行存储不同用户的凭据。 传统上，数据库包含表、行、列和字段。行和列包含数据，这些数据存储在单个字段中。假设一个 Web 应用程序的数据库包含一个名为“用户”（表 11-1）的表。该表包含三个列：ID、用户名和密码。它还包含三行数据，每行存储不同用户的凭据。

Table 11-1 : The Example Users Database Table

表 11-1：示例用户数据库表

ID	Username	Password
1	admin	t5dJ12rp$fMDEbSWz
2	vickie	password123
3	jennifer	letmein!

The SQL language helps you efficiently interact with the data stored in databases by using queries. For example, SQL SELECT statements can be used to retrieve data from the database. The following query will return the entire Users table from the database:

SQL 语言通过使用查询使您有效地与存储在数据库中的数据进行交互。例如，SQL SELECT 语句可用于从数据库检索数据。以下查询将从数据库返回完整的用户表：

SELECT * FROM Users;

This query would return all usernames in the Users table:

这个查询会在用户表中返回所有的用户名：

SELECT Username FROM Users;

Finally, this query would return all users with the username admin :

最后，此查询将返回所有用户名为 admin 的用户：

SELECT * FROM Users WHERE Username='admin';

There are many more ways to construct a SQL query that interacts with a database. You can learn more about SQL syntax from W3Schools at https://www.w3schools.com/sql/default.asp .

构建与数据库交互的 SQL 查询的方法还有很多。您可以从 W3Schools 了解有关 SQL 语法的更多信息，网址为 https://www.w3schools.com/sql/default.asp。

Injecting Code into SQL Queries

A SQL injection attack occurs when an attacker is able to inject code into the SQL statements that the target web application uses to access its database, thereby executing whatever SQL code the attacker wishes. For example, let’s say that a website prompts its users for their username and password, then inserts these into a SQL query to log in the user. The following POST request parameters from the user will be used to populate a SQL query:

SQL 注入攻击发生在攻击者能够将代码注入到目标网络应用程序用于访问其数据库的 SQL 语句中，从而执行攻击者期望的任何 SQL 代码。例如，假设一个网站提示用户输入用户名和密码，然后将这些信息插入到一个 SQL 查询中以登录用户。以下来自用户的 POST 请求参数将被用于填充 SQL 查询:

POST /login
Host: example.com

(POST request body)
username=vickie&password=password123

This SQL query will find the ID of a user that matches the username and password provided in the POST request. The application will then log in to that user’s account:

这个 SQL 查询将在 POST 请求中提供的用户名和密码匹配的用户 ID。应用程序将登录该用户的帐户。

SELECT Id FROM Users
WHERE Username='vickie' AND Password='password123';

So what’s the problem here? Since users can’t predict the passwords of others, they should have no way of logging in as others, right? The issue is that attackers can insert characters that are special to the SQL language to mess with the logic of the query. For example, if an attacker submits the following POST request:

那么问题在哪里呢？由于用户无法预测其他人的密码，所以他们应该没有办法以其他人的身份登录，不是吗？问题在于攻击者可以插入对 SQL 语言来说特殊的字符来干扰查询的逻辑。例如，如果攻击者提交以下 POST 请求：

POST /login
Host: example.com

(POST request body)
username="admin';-- "&password=password123

the generated SQL query would become this:

生成的 SQL 查询将成为这个：

SELECT Id FROM Users
WHERE Username='admin';-- ' AND Password='password123';

The -- sequence denotes the start of a SQL comment, which doesn’t get interpreted as code, so by adding -- into the username part of the query, the attacker effectively comments out the rest of the SQL query. The query becomes this:

“--” 字符串代表 SQL 注释的开头，不被编译为代码，因此通过将“--”添加到查询语句的用户名部分，攻击者可以有效地注释掉 SQL 查询语句的其余部分。查询语句变成这样：

SELECT Id FROM Users WHERE Username='admin';

This query will return the admin user’s ID, regardless of the password provided by the attacker. By injecting special characters into the SQL query, the attacker bypassed authentication and can log in as the admin without knowing the correct password!

这个查询将返回管理员用户的 ID，无论攻击者提供的密码是什么。通过向 SQL 查询中注入特殊字符，攻击者绕过了身份验证，并且可以在不知道正确密码的情况下作为管理员登录！

Authentication bypass is not the only thing attackers can achieve with SQL injection. Attackers might also be able to retrieve data they shouldn’t be allowed to access. Let’s say a website allows users to access a list of their emails by providing the server a username and an access key to prove their identity:

攻击者通过 SQL 注入不只能够绕过身份验证。攻击者可能还能够获取他们不应该访问的数据。例如，一个网站允许用户通过提供用户名和访问密钥来获取他们的电子邮件列表：

GET /emails?username=vickie&accesskey=ZB6w0YLjzvAVmp6zvr
Host: example.com

This GET request might generate a query to the database with the following SQL statement:

可能会生成以下 SQL 语句的数据库查询的 GET 请求：

SELECT Title, Body FROM Emails
WHERE Username='vickie' AND AccessKey='ZB6w0YLjzvAVmp6zvr';

In this case, attackers can use the SQL query to read data from other tables that they should not be able to read. For instance, imagine they sent the following HTTP request to the server:

在此情况下，攻击者可以利用 SQL 查询从其他不应读取的表中读取数据。例如，想象他们向服务器发送以下 HTTP 请求：

GET /emails?username=vickie&accesskey="ZB6w0YLjzvAVmp6zvr' 
1 UNION SELECT Username, Password FROM Users;-- "
Host: example.com

The server would turn the original SQL query into this one:

服务器将原始的 SQL 查询转换为以下内容：

1 SELECT Title, Body FROM Emails
WHERE Username='vickie' AND AccessKey='ZB6w0YLjzvAVmp6zvr'
2 UNION 3SELECT Username, Password FROM Users;4-- ;

The SQL UNION 2 operator combines the results of two different SELECT statements. Therefore, this query combines the results of the first SELECT statement 1 , which returns a user’s emails, and the second SELECT statement 3 , which, as described earlier, returns all usernames and passwords from the Users table. Now the attacker can read all users’ usernames and passwords in the HTTP response! (Note that many SQL injection payloads would comment out whatever comes after the injection point 4 , to prevent the rest of the query from messing up the syntax or logic of the query.)

SQL UNION 2 操作符将两个不同 SELECT 语句的结果合并起来。因此，该查询将第一个 SELECT 语句 1 的结果（返回用户的电子邮件）和第三个 SELECT 语句 3 的结果（如前所述，从 Users 表返回所有用户名和密码）合并在一起。现在攻击者可以在 HTTP 响应中读取所有用户的用户名和密码！（请注意，许多 SQL 注入有效负载会注释掉注入点 4 后面的任何内容，以防止查询的语法或逻辑混乱。）

SQL injection isn’t limited to SELECT statements, either. Attackers can also inject code into statements like UPDATE (used to update a record), DELETE (used to delete existing records), and INSERT (used to create new entries in a table). For example, let’s say that this is the HTTP POST request used to update a user’s password on the target website:

SQL 注入不仅限于 SELECT 语句。攻击者还可以将代码注入到 UPDATE（用于更新记录），DELETE（用于删除现有记录）和 INSERT（用于在表中创建新条目）等语句中。例如，假设这是用于更新目标网站上用户密码的 HTTP POST 请求：

POST /change_password
Host: example.com

(POST request body)
new_password=password12345

The website would form an UPDATE query with your new password and the ID of the currently logged-in user. This query will update the row in the Users table whose ID field is equal to 2, and set its password to password12345 :

该网站将通过新密码和当前登录用户的 ID 形成一个 UPDATE 查询。此查询将更新用户表中 ID 字段等于 2 的行，并将其密码设置为 password12345。

UPDATE Users
    SET Password='password12345'
WHERE Id = 2;

In this case, attackers can control the SET clause of the statement, which is used to specify which rows should be updated in a table. The attacker can construct a POST request like this one:

在这种情况下，攻击者可以控制语句中的 SET 子句，该子句用于指定应在表中更新哪些行。攻击者可以构造像这样的 POST 请求：

POST /change_password
Host: example.com

(POST request body)
new_password="password12345';--"

This request generates the following SQL query:

此请求生成以下 SQL 查询：

UPDATE Users
SET Password='password12345';-- WHERE Id = 2;

The WHERE clause, which specifies the criteria of the rows that should be updated, is commented out in this query. The database would update all rows in the table, and change all of the passwords in the Users table to password12345 . The attacker can now log in as anyone by using that password.

WHERE 子句在此查询中被注释了，它指定了应该更新的行的标准。数据库将更新表中所有行，并将 Users 表中所有密码更改为 password12345。攻击者现在可以使用该密码登录任何账户。

Using Second-Order SQL Injections

So far, the SQL injections we’ve discussed are all first-order SQL injections. First-order SQL injections happen when applications use user-submitted input directly in a SQL query. On the other hand, second-order SQL injections happen when user input gets stored into a database, then retrieved and used unsafely in a SQL query. Even if applications handle input properly when it’s submitted by the user, these vulnerabilities can occur if the application mistakenly treats the data as safe when it’s retrieved from the database.

到目前为止，我们已经讨论的 SQL 注入都是一级 SQL 注入。当应用程序直接在 SQL 查询中使用用户提交的输入时，就会发生一级 SQL 注入。另一方面，当用户输入被存储到数据库中，然后不安全地在 SQL 查询中检索和使用时，就会发生二级 SQL 注入。即使应用程序在用户提交输入时正确地处理输入，如果应用程序在从数据库中检索到数据时错误地将数据视为安全，则可能会出现这些漏洞。

For example, consider a web application that allows users to create an account by specifying a username and a password. Let’s say that a malicious user submits the following request:

例如，考虑一个允许用户通过指定用户名和密码创建帐户的 Web 应用程序。假设有一个恶意用户提交了以下请求：

POST /signup
Host: example.com

(POST request body)
username="vickie' UNION SELECT Username, Password FROM Users;-- 
"&password=password123

This request submits the username vickie' UNION SELECT Username, Password FROM Users;-- and the password password123 to the /signup endpoint. The username POST request parameter contains a SQL injection payload that would SELECT all usernames and passwords and concatenate them to the results of the database query.

这个请求提交了用户名 vickie' UNION SELECT Username, Password FROM Users;-- 和密码 password123 到 /signup 端点。用户名 POST 请求参数包含了一个 SQL 注入负载，该负载将选择所有用户名和密码，并将它们连接到数据库查询的结果中。

The application properly handles the user input when it’s submitted, using the protection techniques I’ll discuss in the next section. And the string vickie' UNION SELECT Username, Password FROM Users;-- is stored into the application’s database as the attacker’s username.

应用程序在用户提交时正确处理其输入，并使用我将在下一节中讨论的保护技术。并且字符串 vickie' UNION SELECT Username, Password FROM Users;--作为攻击者的用户名存储在应用程序的数据库中。 该应用程序能够正确处理用户提交的输入，并采用我在下一节中讨论的保护技术。攻击者使用的字符串“vickie' UNION SELECT Username, Password FROM Users;--”被存储于应用程序数据库中作为攻击者的用户名。

Later, the malicious user accesses their email with the following GET request:

以后，恶意用户使用以下 GET 请求访问他们的电子邮件：

GET /emails
Host: example.com

In this case, let’s say that if the user doesn’t provide a username and an access key, the application will retrieve the username of the currently logged-in user from the database and use it to populate a SQL query:

在这种情况下，假设用户没有提供用户名和访问密钥，则应用程序将从数据库中检索当前已登录用户的用户名并使用它来填充 SQL 查询。

SELECT Title, Body FROM Emails
WHERE Username='USERNAME'

But the attacker’s username, which contains SQL code, will turn the SQL query into the following one:

但攻击者的用户名中包含 SQL 代码，将会把 SQL 查询转变为以下形式：

SELECT Title, Body FROM Emails
WHERE Username='vickie'
UNION SELECT Username, Password FROM Users;--

This will return all usernames and passwords as email titles and bodies in the HTTP response!

这将在 HTTP 响应中返回所有用户名和密码作为电子邮件标题和正文！