问题描述
将 SQL IN
子句与 java.sql.PreparedStatement
的实例一起使用的最佳解决方法是什么,由于 SQL 注入攻击的安全性,不支持多个值问题:一个 ?
占位符代表一个值,而不是一个值列表.
What are the best workarounds for using a SQL IN
clause with instances of java.sql.PreparedStatement
, which is not supported for multiple values due to SQL injection attack security issues: One ?
placeholder represents one value, rather than a list of values.
考虑以下 SQL 语句:
Consider the following SQL statement:
SELECT my_column FROM my_table where search_column IN (?)
使用 preparedStatement.setString( 1, "'A', 'B', 'C'" );
本质上是对使用 原因的一种变通方法的非工作尝试?
放在首位.
Using preparedStatement.setString( 1, "'A', 'B', 'C'" );
is essentially a non-working attempt at a workaround of the reasons for using ?
in the first place.
有哪些可用的解决方法?
What workarounds are available?
推荐答案
对各种可用选项的分析,以及每个选项的优缺点 这里.
An analysis of the various options available, and the pros and cons of each is available here.
建议的选项是:
- 准备
SELECT my_column FROM my_table WHERE search_column = ?
,为每个值执行它并在客户端联合结果.只需要一个准备好的语句.缓慢而痛苦. - 准备
SELECT my_column FROM my_table WHERE search_column IN (?,?,?)
并执行它.每个 size-of-IN-list 需要一个准备好的语句.快速而明显. - 准备
SELECT my_column FROM my_table WHERE search_column = ?;SELECT my_column FROM my_table WHERE search_column = ?;...
并执行它.[或使用UNION ALL
代替那些分号.--ed] 每个 size-of-IN-list 需要一个准备好的语句.非常慢,比WHERE search_column IN (?,?,?)
更糟糕,所以我不知道博主为什么建议它. - 使用存储过程构建结果集.
- 准备N个不同大小的IN-list查询;比如说,有 2、10 和 50 个值.要搜索具有 6 个不同值的 IN 列表,请填充大小为 10 的查询,使其看起来像
SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6)
.任何体面的服务器都会在运行查询之前优化掉重复值.
- Prepare
SELECT my_column FROM my_table WHERE search_column = ?
, execute it for each value and UNION the results client-side. Requires only one prepared statement. Slow and painful. - Prepare
SELECT my_column FROM my_table WHERE search_column IN (?,?,?)
and execute it. Requires one prepared statement per size-of-IN-list. Fast and obvious. - Prepare
SELECT my_column FROM my_table WHERE search_column = ? ; SELECT my_column FROM my_table WHERE search_column = ? ; ...
and execute it. [Or useUNION ALL
in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse thanWHERE search_column IN (?,?,?)
, so I don't know why the blogger even suggested it. - Use a stored procedure to construct the result set.
- Prepare N different size-of-IN-list queries; say, with 2, 10, and 50 values. To search for an IN-list with 6 different values, populate the size-10 query so that it looks like
SELECT my_column FROM my_table WHERE search_column IN (1,2,3,4,5,6,6,6,6,6)
. Any decent server will optimize out the duplicate values before running the query.
这些选项都不理想.
如果您使用 JDBC4 和支持 x = ANY(y)
的服务器,最好的选择是使用 PreparedStatement.setArray
作为 此处描述
The best option if you are using JDBC4 and a server that supports x = ANY(y)
, is to use PreparedStatement.setArray
as described here
不过,似乎没有任何方法可以使 setArray
与 IN 列表一起工作.
There doesn't seem to be any way to make setArray
work with IN-lists, though.
有时 SQL 语句在运行时加载(例如,从属性文件)但需要可变数量的参数.在这种情况下,首先定义查询:
Sometimes SQL statements are loaded at runtime (e.g., from a properties file) but require a variable number of parameters. In such cases, first define the query:
query=SELECT * FROM table t WHERE t.column IN (?)
接下来,加载查询.然后在运行之前确定参数的数量.知道参数计数后,运行:
Next, load the query. Then determine the number of parameters prior to running it. Once the parameter count is known, run:
sql = any( sql, count );
例如:
/**
* Converts a SQL statement containing exactly one IN clause to an IN clause
* using multiple comma-delimited parameters.
*
* @param sql The SQL statement string with one IN clause.
* @param params The number of parameters the SQL statement requires.
* @return The SQL statement with (?) replaced with multiple parameter
* placeholders.
*/
public static String any(String sql, final int params) {
// Create a comma-delimited list based on the number of parameters.
final StringBuilder sb = new StringBuilder(
String.join(", ", Collections.nCopies(possibleValue.size(), "?")));
// For more than 1 parameter, replace the single parameter with
// multiple parameter placeholders.
if (sb.length() > 1) {
sql = sql.replace("(?)", "(" + sb + ")");
}
// Return the modified comma-delimited list of parameters.
return sql;
}
对于某些不支持通过 JDBC 4 规范传递数组的数据库,此方法可以方便地将慢速 = ?
转换为更快的 IN (?)
子句条件,然后可以通过调用 any
方法对其进行扩展.
For certain databases where passing an array via the JDBC 4 specification is unsupported, this method can facilitate transforming the slow = ?
into the faster IN (?)
clause condition, which can then be expanded by calling the any
method.
这篇关于PreparedStatement IN 子句的替代方案?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!