PreparedStatement IN 子句的替代方案? [英] PreparedStatement IN clause alternatives?

问题描述

将 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 use UNION ALL in place of those semicolons. --ed] Requires one prepared statement per size-of-IN-list. Stupidly slow, strictly worse than WHERE 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 子句的替代方案?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！