PreparedStatement IN 子句替代方案？

Question

将 SQL IN 子句与 java.sql.PreparedStatement 的实例一起使用的最佳解决方法是什么，由于 SQL 注入攻击安全问题，它不支持多个值：一个 ? 占位符代表一个值，而不是值列表。

考虑以下 SQL 语句：

 SELECT my_column FROM my_table where search_column IN (?)

使用 preparedStatement.setString( 1, "'A', 'B', 'C'" ); 本质上是对首先使用 ? 的原因的变通尝试。

有哪些解决方法？

原文由 Chris Mazzola 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

JavaRanch Journal 上的 Jeanne Boyarsky 在 JDBC 条目中的 Batching Select Statements 中 提供了对各种可用选项的分析以及每个选项的优缺点。

建议的选项是：

• 准备 SELECT my_column FROM my_table WHERE search_column = ? ，为每个值执行它并在客户端 UNION 结果。只需要一个准备好的语句。缓慢而痛苦。
• 准备 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 列表查询；比如，有 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) 。任何体面的服务器都会在运行查询之前优化掉重复的值。

这些选项都不是理想的。

如果您使用的是 JDBC4 和支持 x = ANY(y) 的服务器，那么最好的选择是使用 PreparedStatement.setArray 如 Boris 的 anwser 中所述。

不过，似乎没有任何方法可以使 setArray 使用 IN 列表。

---

有时 SQL 语句在运行时加载（例如，从属性文件）但需要可变数量的参数。在这种情况下，首先定义查询：

 query=SELECT * FROM table t WHERE t.column IN (?)

接下来，加载查询。然后在运行它之前确定参数的数量。知道参数计数后，运行：

 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 方法。

原文由 Dónal 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

PostgreSQL 的解决方案：

 final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table where search_column = ANY (?)"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

或者

final PreparedStatement statement = connection.prepareStatement(
        "SELECT my_column FROM my_table " +
        "where search_column IN (SELECT * FROM unnest(?))"
);
final String[] values = getValues();
statement.setArray(1, connection.createArrayOf("text", values));

try (ResultSet rs = statement.executeQuery()) {
    while(rs.next()) {
        // do some...
    }
}

原文由 Boris 发布，翻译遵循 CC BY-SA 4.0 许可协议