Java PreparedStatement 如何避免或防止 SQL 注入？

How does a PreparedStatement avoid or prevent SQL injection?

提问by Prabhu R

I know that PreparedStatements avoid/prevent SQL Injection. How does it do that? Will the final form query that is constructed using PreparedStatements will be a string or otherwise?

我知道 PreparedStatements 避免/防止 SQL 注入。它是如何做到的？使用 PreparedStatements 构造的最终表单查询是字符串还是其他形式？

采纳答案by tangens

The problem with SQL injection is, that a user input is used as part of the SQL statement. By using prepared statements you can force the user input to be handled as the content of a parameter (and not as a part of the SQL command).

SQL 注入的问题在于，用户输入被用作 SQL 语句的一部分。通过使用准备好的语句，您可以强制将用户输入作为参数的内容进行处理（而不是作为 SQL 命令的一部分）。

But if you don't use the user input as a parameter for your prepared statement but instead build your SQL command by joining strings together, you are still vulnerable to SQL injectionseven when using prepared statements.

但是，如果您不将用户输入用作准备好的语句的参数，而是通过将字符串连接在一起来构建 SQL 命令，那么即使使用准备好的语句，您仍然容易受到 SQL 注入的影响。

回答by Paul Tomblin

Consider two ways of doing the same thing:

考虑两种做同一件事的方法：

PreparedStatement stmt = conn.createStatement("INSERT INTO students VALUES('" + user + "')");
stmt.execute();

Or

或者

PreparedStatement stmt = conn.prepareStatement("INSERT INTO student VALUES(?)");
stmt.setString(1, user);
stmt.execute();

If "user" came from user input and the user input was

如果“用户”来自用户输入并且用户输入是

Robert'); DROP TABLE students; --

Then in the first instance, you'd be hosed. In the second, you'd be safe and Little Bobby Tables would be registered for your school.

那么在第一种情况下，你会被冲洗掉。第二，你会安全，Little Bobby Tables 会在你的学校注册。

回答by shahkalpesh

I guessit will be a string. But the input parameters will be sent to the database & appropriate cast/conversions will be applied prior to creating an actual SQL statement.

我想这将是一个字符串。但是输入参数将被发送到数据库，并且在创建实际 SQL 语句之前将应用适当的强制转换/转换。

To give you an example, it might try and see if the CAST/Conversion works.
If it works, it could create a final statement out of it.

举个例子，它可能会尝试看看 CAST/Conversion 是否有效。
如果它有效，它可以从中创建一个最终声明。

   SELECT * From MyTable WHERE param = CAST('10; DROP TABLE Other' AS varchar(30))

Try an example with a SQL statement accepting a numeric parameter.
Now, try passing a string variable (with numeric content that is acceptable as numeric parameter). Does it raise any error?

尝试使用接受数字参数的 SQL 语句的示例。
现在，尝试传递一个字符串变量（数字内容可以作为数字参数）。它会引发任何错误吗？

Now, try passing a string variable (with content that is not acceptable as numeric parameter). See what happens?

现在，尝试传递一个字符串变量（其内容不能作为数字参数）。走着瞧吧？

回答by Travis Heseman

The SQL used in a PreparedStatement is precompiled on the driver. From that point on, the parameters are sent to the driver as literal values and not executable portions of SQL; thus no SQL can be injected using a parameter. Another beneficial side effect of PreparedStatements (precompilation + sending only parameters) is improved performance when running the statement multiple times even with different values for the parameters (assuming that the driver supports PreparedStatements) as the driver does not have to perform SQL parsing and compilation each time the parameters change.

PreparedStatement 中使用的 SQL 是在驱动程序上预编译的。从那时起，参数将作为文字值而不是 SQL 的可执行部分发送给驱动程序；因此不能使用参数注入 SQL。PreparedStatements（预编译 + 仅发送参数）的另一个有益的副作用是，即使参数的值不同（假设驱动程序支持 PreparedStatements）多次运行语句时的性能也会提高，因为驱动程序不必每次都执行 SQL 解析和编译参数变化的时间。

回答by Guru R Handa

Prepared statement is more secure. It will convert a parameter to the specified type.

准备好的语句更安全。它将参数转换为指定的类型。

For example stmt.setString(1, user);will convert the userparameter to a String.

例如stmt.setString(1, user);将user参数转换为字符串。

Suppose that the parameter contains a SQL string containing an executable command: using a prepared statement will not allow that.

假设参数包含一个包含可执行命令的 SQL 字符串：使用准备好的语句将不允许这样做。

It adds metacharacter (a.k.a. auto conversion) to that.

它添加了元字符（又名自动转换）。

This makes it is more safe.

这使得它更安全。

回答by Mukesh Kumar

PreparedStatement:

准备语句：

1) Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.

1) SQL 语句的预编译和 DB 端缓存导致整体执行速度更快，并且可以批量重用相同的 SQL 语句。

2) Automatic prevention of SQL injection attacks by builtin escaping of quotes and other special characters. Note that this requires that you use any of the PreparedStatement setXxx() methods to set the value.

2) 通过引号和其他特殊字符的内置转义自动防止 SQL 注入攻击。请注意，这要求您使用任何 PreparedStatement setXxx() 方法来设置值。

回答by Jayesh

To understand how PreparedStatement prevents SQL Injection, we need to understand phases of SQL Query execution.

要了解 PreparedStatement 如何防止 SQL 注入，我们需要了解 SQL 查询执行的各个阶段。

1. Compilation Phase. 2. Execution Phase.

1. 编译阶段。2. 执行阶段。

Whenever SQL server engine receives a query, it has to pass through below phases,

每当 SQL 服务器引擎收到查询时，它必须通过以下阶段，

1. Parsing and Normalization Phase:In this phase, Query is checked for syntax and semantics. It checks whether references table and columns used in query exist or not. It also has many other tasks to do, but let's not go in detail.

2. Compilation Phase:In this phase, keywords used in query like select, from, where etc are converted into format understandable by machine. This is the phase where query is interpreted and corresponding action to be taken is decided. It also has many other tasks to do, but let's not go in detail.

3. Query Optimization Plan:In this phase, Decision Tree is created for finding the ways in which query can be executed. It finds out the number of ways in which query can be executed and the cost associated with each way of executing Query. It chooses the best plan for executing a query.

4. Cache:Best plan selected in Query optimization plan is stored in cache, so that whenever next time same query comes in, it doesn't have to pass through Phase 1, Phase 2 and Phase 3 again. When next time query come in, it will be checked directly in Cache and picked up from there to execute.

5. Execution Phase:In this phase, supplied query gets executed and data is returned to user as ResultSetobject.

1. 解析和规范化阶段：在此阶段，检查 Query 的语法和语义。它检查查询中使用的引用表和列是否存在。它还有许多其他任务要做，但我们不详细介绍。

2. 编译阶段：在此阶段，查询中使用的关键字如 select、from、where 等被转换为机器可以理解的格式。这是解释查询并决定要采取的相应操作的阶段。它还有许多其他任务要做，但我们不详细介绍。

3. 查询优化计划：在此阶段，创建决策树以查找可以执行查询的方式。它找出可以执行查询的方式的数量以及与执行 Query 的每种方式相关的成本。它选择执行查询的最佳计划。

4. 缓存：查询优化计划中选择的最佳计划存储在缓存中，以便下次相同的查询进入时，不必再次通过阶段1、阶段2和阶段3。下次查询进来时，会直接在Cache中进行检查，然后从那里取出执行。

5. 执行阶段：在此阶段，执行提供的查询并将数据作为ResultSet对象返回给用户。

Behaviour of PreparedStatement API on above steps

PreparedStatement API 在上述步骤中的行为

1. PreparedStatements are not complete SQL queries and contain placeholder(s), which at run time are replaced by actual user-provided data.

2. Whenever any PreparedStatment containing placeholders is passed in to SQL Server engine, It passes through below phases

   1. Parsing and Normalization Phase
   2. Compilation Phase
   3. Query Optimization Plan
   4. Cache (Compiled Query with placeholders are stored in Cache.)

1. PreparedStatements 不是完整的 SQL 查询并且包含占位符，在运行时由实际用户提供的数据替换。

2. 每当任何包含占位符的 PreparedStatment 传递到 SQL Server 引擎时，它都会通过以下阶段

   1. 解析和规范化阶段
   2. 编译阶段
   3. 查询优化计划
   4. 缓存（带有占位符的编译查询存储在缓存中。）

UPDATE user set username=? and password=? WHERE id=?

更新用户集用户名=？和密码=？哪里 id=?

1. Above query will get parsed, compiled with placeholders as special treatment, optimized and get Cached. Query at this stage is already compiled and converted in machine understandable format. So we can say that Query stored in cache is Pre-Compiled and only placeholders need to be replaced with user-provided data.

2. Now at run-time when user-provided data comes in, Pre-Compiled Query is picked up from Cache and placeholders are replaced with user-provided data.

3. 上面的查询将被解析，使用占位符作为特殊处理进行编译，优化并缓存。此阶段的查询已经编译并转换为机器可理解的格式。所以我们可以说缓存中存储的 Query 是预编译的，只需要用用户提供的数据替换占位符。

4. 现在在运行时，当用户提供的数据进来时，预编译查询从缓存中提取，占位符替换为用户提供的数据。

(Remember, after place holders are replaced with user data, final query is not compiled/interpreted again and SQL Server engine treats user data as pure data and not a SQL that needs to be parsed or compiled again; that is the beauty of PreparedStatement.)

（请记住，在占位符被用户数据替换后，最终查询不会再次编译/解释，SQL Server 引擎将用户数据视为纯数据，而不是需要再次解析或编译的 SQL；这就是 PreparedStatement 的美妙之处。 )

If the query doesn't have to go through compilation phase again, then whatever data replaced on the placeholders are treated as pure data and has no meaning to SQL Server engine and it directly executes the query.

如果查询不必再次经过编译阶段，那么占位符上替换的任何数据都被视为纯数据，对 SQL Server 引擎没有意义，它直接执行查询。

Note: It is the compilation phase after parsing phase, that understands/interprets the query structure and gives meaningful behavior to it. In case of PreparedStatement, query is compiled only once and cached compiled query is picked up all the time to replace user data and execute.

注意：解析阶段之后的编译阶段，理解/解释查询结构并为其提供有意义的行为。在 PreparedStatement 的情况下，查询只编译一次，缓存的编译查询一直被选中以替换用户数据并执行。

Due to one time compilation feature of PreparedStatement, it is free of SQL Injection attack.

由于 PreparedStatement 的一次性编译特性，它不受 SQL 注入攻击。

You can get detailed explanation with example here: http://javabypatel.blogspot.in/2015/09/how-prepared-statement-in-java-prevents-sql-injection.html

您可以在此处通过示例获得详细说明：http: //javabypatel.blogspot.in/2015/09/how-prepared-statement-in-java-prevents-sql-injection.html

回答by Vlad Mihalcea

As explained in this post, the PreparedStatementalone does not help you if you are still concatenating Strings.

正如这篇文章中所解释的，PreparedStatement如果您仍在连接字符串，则单独对您没有帮助。

For instance, one rogue attacker can still do the following:

例如，一个流氓攻击者仍然可以执行以下操作：

• call a sleep function so that all your database connections will be busy, therefore making your application unavailable
• extracting sensitive data from the DB
• bypassing the user authentication

• 调用 sleep 函数，以便您的所有数据库连接都处于忙碌状态，从而使您的应用程序不可用
• 从数据库中提取敏感数据
• 绕过用户认证

Not only SQL, but even JPQL or HQL can be compromised if you are not using bind parameters.

如果您不使用绑定参数，不仅 SQL，甚至 JPQL 或 HQL 都可能受到损害。

Bottom line, you should never use string concatenation when building SQL statements. Use a dedicated API for that purpose:

最重要的是，在构建 SQL 语句时，永远不要使用字符串连接。为此使用专用 API：

• JPA Criteria API
• jOOQ

• JPA 标准 API
• 约克

回答by Hyman

SQL injection: when user has the chance to input something that could be part of the sql statement

SQL 注入：当用户有机会输入一些可能是 sql 语句的一部分时

For example:

例如：

String query = “INSERT INTO students VALUES(‘” + user + “‘)”

字符串查询 = “INSERT INTO Students VALUES('” + user + “')”

when user input “Robert'); DROP TABLE students; –” as the input, it causes SQL injection

当用户输入“罗伯特”）；DROP TABLE 学生；–”作为输入，导致SQL注入

How prepared statement prevents this?

准备好的语句如何防止这种情况？

String query = “INSERT INTO students VALUES(‘” + “:name” + “‘)”

String query = “INSERT INTO Students VALUES('” + “:name” + “')”

parameters.addValue(“name”, user);

parameters.addValue(“名称”, 用户);

=> when user input again “Robert'); DROP TABLE students; –“, the input string is precompiled on the driver as literal values and I guess it may be casted like:

=> 当用户再次输入“罗伯特”); DROP TABLE 学生；–“，输入字符串在驱动程序上预编译为文字值，我想它可能会被转换为：

CAST(‘Robert'); DROP TABLE students; –‘ AS varchar(30))

CAST('罗伯特'); DROP TABLE 学生；–' AS varchar(30))

So at the end, the string will be literally inserted as the name to the table.

所以最后，字符串将作为名称插入到表中。

http://blog.linguiming.com/index.php/2018/01/10/why-prepared-statement-avoids-sql-injection/

http://blog.linguiming.com/index.php/2018/01/10/why-prepared-statement-avoids-sql-injection/