准备好的语句如何防止 SQL 注入攻击？

How can prepared statements protect from SQL injection attacks?

提问by Aan

How do prepared statementshelp us prevent SQL injectionattacks?

准备好的语句如何帮助我们防止SQL 注入攻击？

Wikipedia says:

维基百科说：

Prepared statements are resilient against SQL injection, because parameter values, which are transmitted later using a different protocol, need not be correctly escaped. If the original statement template is not derived from external input, SQL injection cannot occur.

准备好的语句对 SQL 注入具有弹性，因为稍后使用不同协议传输的参数值不需要正确转义。如果原始语句模板不是来自外部输入，则不会发生 SQL 注入。

I cannot see the reason very well. What would be a simple explanation in easy English and some examples?

我不能很好地看出原因。什么是简单的英语和一些例子的简单解释？

回答by Your Common Sense

The idea is very simple - the query and the data are sent to the database server separately.
That's all.

这个想法很简单-查询和数据被发送到数据库服务器分开。
就这样。

The root of the SQL injection problem is in the mixing of the code and the data.

SQL注入问题的根源在于代码和数据的混合。

In fact, our SQL query is a legitimate program. And we are creating such a program dynamically, adding some data on the fly. Thus, the data may interfere with the program codeand even alter it, as every SQL injection example shows it (all examples in PHP/Mysql):

事实上，我们的 SQL 查询是一个合法的程序。我们正在动态创建这样一个程序，动态添加一些数据。因此，数据可能会干扰程序代码甚至改变它，正如每个 SQL 注入示例所显示的那样（PHP/Mysql 中的所有示例）：

$expected_data = 1;
$query = "SELECT * FROM users where id=$expected_data";

will produce a regular query

将产生一个常规查询

SELECT * FROM users where id=1

while this code

而这段代码

$spoiled_data = "1; DROP TABLE users;"
$query        = "SELECT * FROM users where id=$spoiled_data";

will produce a malicious sequence

会产生恶意序列

SELECT * FROM users where id=1; DROP TABLE users;

It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program, and depending on the data passed, we will either have a regular output or a table usersdeleted.

它起作用是因为我们将数据直接添加到程序体中，它成为程序的一部分，因此数据可能会改变程序，并且根据传递的数据，我们将有一个常规输出或一个表users被删除。

While in case of prepared statements we don't alter our program, it remains intact
That's the point.

虽然在准备好的语句的情况下，我们不会改变我们的程序，但它保持不变，
这就是重点。

We are sending a programto the server first

我们首先向服务器发送一个程序

$db->prepare("SELECT * FROM users where id=?");

where the data is substituted by some variablecalled a parameter or a placeholder.

其中数据被一些称为参数或占位符的变量替换。

Note that exactly the same query is sent to the server, without any data in it! And then we're sending the data with the secondrequest, essentially separated from the query itself:

请注意，将完全相同的查询发送到服务器，其中没有任何数据！然后我们用第二个请求发送数据，基本上与查询本身分开：

$db->execute($data);

so it can't alter our program and do any harm.
Quite simple - isn't it?

所以它不能改变我们的程序并造成任何伤害。
很简单——不是吗？

The only thing I have to add that always omitted in the every manual:

我必须添加的唯一一件事总是在每本手册中被省略：

Prepared statements can protect only data literals, but cannot be used with any other query part.
So, once we have to add, say, a dynamical identifier- a field name, for example - prepared statements can't help us. I've explained the matter recently, so I won't repeat myself.

准备好的语句只能保护数据文字，但不能与任何其他查询部分一起使用。
所以，一旦我们不得不添加一个动态标识符——例如一个字段名——准备好的语句就无法帮助我们。我最近已经解释过了，所以我不再重复。

回答by Glenn

Here is SQL for setting up an example:

这是用于设置示例的 SQL：

CREATE TABLE employee(name varchar, paymentType varchar, amount bigint);

INSERT INTO employee VALUES('Aaron', 'salary', 100);
INSERT INTO employee VALUES('Aaron', 'bonus', 50);
INSERT INTO employee VALUES('Bob', 'salary', 50);
INSERT INTO employee VALUES('Bob', 'bonus', 0);

The Inject class is vulnerable to SQL injection. The query is dynamically pasted together with user input. The intent of the query was to show information about Bob. Either salary or bonus, based on user input. But the malicious user manipulates the input corrupting the query by tacking on the equivalent of an 'or true' to the where clause so that everything is returned, including the information about Aaron which was supposed to be hidden.

Inject 类容易受到 SQL 注入的攻击。查询与用户输入动态粘贴在一起。查询的目的是显示有关 Bob 的信息。工资或奖金，基于用户输入。但是恶意用户通过在 where 子句上附加一个“或真”的等价物来操纵破坏查询的输入，以便返回所有内容，包括本应隐藏的有关 Aaron 的信息。

import java.sql.*;

public class Inject {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=user&password=pwd";
        Connection conn = DriverManager.getConnection(url);

        Statement stmt = conn.createStatement();
        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='" + args[0] + "'";
        System.out.println(sql);
        ResultSet rs = stmt.executeQuery(sql);

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

Running this, the first case is with normal usage, and the second with the malicious injection:

运行这个，第一种情况是正常使用，第二种情况是恶意注入：

c:\temp>java Inject salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary'
salary 50

c:\temp>java Inject "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType='salary' OR 'a'!='b'
salary 100
bonus 50
salary 50
bonus 0

You should not build your SQL statements with string concatenation of user input. Not only is it vulnerable to injection, but it has caching implications on the server as well (the statement changes, so less likely to get a SQL statement cache hit whereas the bind example is always running the same statement).

您不应使用用户输入的字符串连接来构建 SQL 语句。它不仅容易受到注入攻击，而且对服务器也有缓存影响（语句发生变化，因此不太可能获得 SQL 语句缓存命中，而绑定示例始终运行相同的语句）。

Here is an example of Binding to avoid this kind of injection:

这是避免这种注入的绑定示例：

import java.sql.*;

public class Bind {

    public static void main(String[] args) throws SQLException {

        String url = "jdbc:postgresql://localhost/postgres?user=postgres&password=postgres";
        Connection conn = DriverManager.getConnection(url);

        String sql = "SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?";
        System.out.println(sql);

        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, args[0]);

        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            System.out.println(rs.getString("paymentType") + " " + rs.getLong("amount"));
        }
    }
}

Running this with the same input as the previous example shows the malicious code does not work because there is no paymentType matching that string:

使用与前一个示例相同的输入运行此代码显示恶意代码不起作用，因为没有与该字符串匹配的 paymentType：

c:\temp>java Bind salary
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?
salary 50

c:\temp>java Bind "salary' OR 'a'!='b"
SELECT paymentType, amount FROM employee WHERE name = 'bob' AND paymentType=?

回答by Jose

Basically, with prepared statements the data coming in from a potential hacker is treated as data - and there's no way it can be intermixed with your application SQL and/or be interpreted as SQL (which can happen when data passed in is placed directly into your application SQL).

基本上，使用准备好的语句，来自潜在黑客的数据被视为数据 - 并且它无法与您的应用程序 SQL 混合和/或被解释为 SQL（当传入的数据直接放入您的应用程序时，可能会发生这种情况）应用程序 SQL）。

This is because prepared statements "prepare" the SQL query first to find an efficient query plan, and send the actual values that presumably come in from a form later - at that time the query is actually executed.

这是因为准备好的语句首先“准备”SQL 查询以找到有效的查询计划，然后发送可能来自表单的实际值 - 那时查询实际执行。

More great info here:

更多精彩信息在这里：

Prepared statements and SQL Injection

准备好的语句和 SQL 注入

回答by N.Vegeta

I read through the answers and still felt the need to stress the key point which illuminates the essence of Prepared Statements. Consider two ways to query one's database where user input is involved:

我通读了答案，仍然觉得有必要强调阐明准备陈述本质的关键点。考虑两种查询涉及用户输入的数据库的方法：

Naive Approach

天真的方法

One concatenates user input with some partial SQL string to generate a SQL statement. In this case the user can embed malicious SQL commands, which will then be sent to the database for execution.

将用户输入与一些部分 SQL 字符串连接起来以生成 SQL 语句。在这种情况下，用户可以嵌入恶意 SQL 命令，然后将其发送到数据库执行。

String SQLString = "SELECT * FROM CUSTOMERS WHERE NAME='"+userInput+"'"

For example, malicious user input can lead to SQLStringbeing equal to "SELECT * FROM CUSTOMERS WHERE NAME='James';DROP TABLE CUSTOMERS;'

例如，恶意用户输入可能导致SQLString等于"SELECT * FROM CUSTOMERS WHERE NAME='James';DROP TABLE CUSTOMERS;'

Due to the malicious user, SQLStringcontains 2 statements, where the 2nd one ("DROP TABLE CUSTOMERS") will cause harm.

由于恶意用户，SQLString包含 2 条语句，其中第 2 条 ( "DROP TABLE CUSTOMERS") 会造成伤害。

Prepared Statements

准备好的报表

In this case, due to the separation of the query & data, the user input is never treated as a SQL statement, and thus is never executed. It is for this reason, that any malicious SQL code injected would cause no harm. So the "DROP TABLE CUSTOMERS"would never be executed in the case above.

在这种情况下，由于查询和数据分离，用户输入永远不会被视为 SQL 语句，因此永远不会被执行。正是因为这个原因，任何注入的恶意 SQL 代码都不会造成伤害。所以在"DROP TABLE CUSTOMERS"上面的情况下永远不会执行。

In a nutshell, with prepared statements malicious code introduced via user input will not be executed!

简而言之，通过用户输入引入的恶意代码不会被执行！

回答by wulfgarpro

When you create and send a prepared statement to the DBMS, it's stored as the SQL query for execution.

当您创建准备好的语句并将其发送到 DBMS 时，它会存储为 SQL 查询以供执行。

You later bind your data to the query such that the DBMS uses that data as the query parameters for execution (parameterization). The DBMS doesn't use the data you bind as a supplemental to the already compiled SQL query; it's simply the data.

稍后您将数据绑定到查询，以便 DBMS 使用该数据作为执行（参数化）的查询参数。DBMS 不会使用您绑定的数据作为已编译 SQL 查询的补充；这只是数据。

This means it's fundamentally impossible to perform SQL injection using prepared statements. The very nature of prepared statements and their relationship with the DBMS prevents this.

这意味着使用准备好的语句执行 SQL 注入从根本上是不可能的。准备好的语句的本质及其与 DBMS 的关系阻止了这一点。

回答by lloydom

In SQL Server, using a prepared statement is definitely injection-proof because the input parameters don't form the query. It means that the executed query is not a dynamic query. Example of an SQL injection vulnerable statement.

在SQL Server 中，使用准备好的语句绝对是防注入的，因为输入参数不构成查询。这意味着执行的查询不是动态查询。SQL 注入易受攻击语句的示例。

string sqlquery = "select * from table where username='" + inputusername +"' and password='" + pass + "'";

Now if the value in the inoutusername variable is something like a' or 1=1 --, this query now becomes:

现在，如果 inoutusername 变量中的值类似于 a' 或 1=1 --，则此查询现在变为：

select * from table where username='a' or 1=1 -- and password=asda

And the rest is commented after --, so it never gets executed and bypassed as using the prepared statement example as below.

其余的在 之后进行注释--，因此它永远不会像下面使用准备好的语句示例那样被执行和绕过。

Sqlcommand command = new sqlcommand("select * from table where username = @userinput and password=@pass");
command.Parameters.Add(new SqlParameter("@userinput", 100));
command.Parameters.Add(new SqlParameter("@pass", 100));
command.prepare();

So in effect you cannot send another parameter in, thus avoiding SQL injection...

所以实际上你不能发送另一个参数，从而避免 SQL 注入......

回答by Feisty Mango

The key phrase is need not be correctly escaped. That means that you don't to worry about people trying to throw in dashes, apostrophes, quotes, etc...

关键词是 need not be correctly escaped。这意味着您不必担心人们试图插入破折号、撇号、引号等...

It is all handled for you.

一切都为你处理。

回答by MeBigFatGuy

ResultSet rs = statement.executeQuery("select * from foo where value = " + httpRequest.getParameter("filter");

Let's assume you have that in a Servlet you right. If a malevolent person passed a bad value for 'filter' you might hack your database.

让我们假设您在 Servlet 中拥有它是正确的。如果一个恶意的人为“过滤器”传递了一个错误的值，你可能会入侵你的数据库。

回答by DanAllen

Root Cause #1 - The Delimiter Problem

根本原因 #1 - 分隔符问题

Sql injection is possible because we use quotation marks to delimit strings and also to be parts of strings, making it impossible to interpret them sometimes. If we had delimiters that could not be used in string data, sql injection never would have happened. Solving the delimiter problem eliminates the sql injection problem. Structure queries do that.

Sql 注入是可能的，因为我们使用引号来分隔字符串并作为字符串的一部分，有时无法解释它们。如果我们有无法在字符串数据中使用的分隔符，则永远不会发生 sql 注入。解决定界符问题就消除了sql注入问题。结构查询就是这样做的。

Root Cause #2 - Human Nature, People are Crafty and Some Crafty People Are MaliciousAnd All People Make Mistakes

根本原因#2 - 人性，人是狡猾的，有些狡猾的人是恶意的，所有人都会犯错

The other root cause of sql injection is human nature. People, including programmers, make mistakes. When you make a mistake on a structured query, it does not make your system vulnerable to sql injection. If you are not using structured queries, mistakes can generate sql injection vulnerability.

sql注入的另一个根本原因是人性。人们，包括程序员，都会犯错误。当您在结构化查询上出错时，它不会使您的系统容易受到 sql 注入的影响。如果您没有使用结构化查询，错误会产生 sql 注入漏洞。

How Structured Queries Resolve the Root Causes of SQL Injection

结构化查询如何解决 SQL 注入的根本原因

Structured Queries Solve The Delimiter Problem, by by putting sql commands in one statement and putting the data in a separate programming statement. Programming statements create the separation needed.

结构化查询通过将 sql 命令放在一个语句中并将数据放在单独的编程语句中来解决分隔符问题。编程语句创建所需的分离。

Structured queries help prevent human error from creating critical security holes.With regard to humans making mistakes, sql injection cannot happen when structure queries are used. There are ways of preventing sql injection that don't involve structured queries, but normal human error in that approaches usually leads to at least some exposure to sql injection. Structured Queries are fail safe from sql injection. You can make all the mistakes in the world, almost, with structured queries, same as any other programming, but none that you can make can be turned into a ssstem taken over by sql injection. That is why people like to say this is the right way to prevent sql injection.

结构化查询有助于防止人为错误造成严重的安全漏洞。对于人为错误，使用结构查询时不会发生sql注入。有一些方法可以防止不涉及结构化查询的 sql 注入，但是这种方法中的正常人为错误通常至少会导致一些 sql 注入。结构化查询可以避免 sql 注入。您几乎可以使用结构化查询犯世界上所有的错误，就像任何其他编程一样，但是您犯的任何错误都不能变成由 sql 注入接管的 ssstem。这就是为什么人们喜欢说这是防止sql注入的正确方法。

So, there you have it, the causes of sql injection and the nature structured queries that makes them impossible when they are used.

所以，你知道了，sql 注入的原因和使它们在使用时不可能的自然结构化查询。