在java.sql实例中使用SQL IN子句的最佳变通方法是什么?由于SQL注入攻击安全问题,不支持多值的PreparedStatement:一个?占位符表示一个值,而不是一个值列表。

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

使用preparedStatement。setString(1, "'A', 'B', 'C'");本质上是一种无用的尝试,试图解决使用原因?首先。

有什么可行的解决办法?


当前回答

在研究了不同论坛上的各种解决方案后,没有找到一个好的解决方案,我觉得下面的方法是最容易遵循和编码的:

示例:假设您有多个参数要传递到' in '子句中。只要在'IN'子句中放入一个虚拟字符串,例如,“PARAM”确实表示将在这个虚拟字符串中出现的参数列表。

    select * from TABLE_A where ATTR IN (PARAM);

您可以在Java代码中将所有参数收集到一个String变量中。可以这样做:

    String param1 = "X";
    String param2 = "Y";
    String param1 = param1.append(",").append(param2);

你可以将所有用逗号分隔的参数附加到一个String变量中,在我们的例子中是'param1'。

在将所有参数收集到单个字符串中之后,您可以将查询中的虚拟文本替换为参数String,即param1,在本例中为“PARAM”。以下是你需要做的:

    String query = query.replaceFirst("PARAM",param1); where we have the value of query as 

    query = "select * from TABLE_A where ATTR IN (PARAM)";

现在可以使用executeQuery()方法执行查询。只要确保在查询中没有“PARAM”这个词。您可以使用特殊字符和字母的组合来代替单词“PARAM”,以确保查询中不可能出现这样的单词。希望你得到了答案。

注意:虽然这不是一个准备好的查询,但它完成了我希望代码完成的工作。

其他回答

对于PreparedStatement中的IN子句,我们可以使用不同的替代方法。

Using Single Queries - slowest performance and resource intensive Using StoredProcedure - Fastest but database specific Creating dynamic query for PreparedStatement - Good Performance but doesn't get benefit of caching and PreparedStatement is recompiled every time. Use NULL in PreparedStatement queries - Optimal performance, works great when you know the limit of IN clause arguments. If there is no limit, then you can execute queries in batch. Sample code snippet is; int i = 1; for(; i <=ids.length; i++){ ps.setInt(i, ids[i-1]); } //set null for remaining ones for(; i<=PARAM_SIZE;i++){ ps.setNull(i, java.sql.Types.INTEGER); }

你可以在这里查看更多关于这些替代方法的细节。

我从来没有尝试过,但是.setArray()做什么你正在寻找?

更新:显然不是。setArray似乎只适用于来自以前查询中检索到的ARRAY列的java.sql.Array,或具有ARRAY列的子查询。

没有简单的办法。 如果目标是保持较高的语句缓存比(即不是每个参数都创建一条语句),您可以执行以下操作:

创建带有几个参数(例如10个)的语句: ... 一个在 (?,?,?,?,?,?,?,?,?,?) ... 绑定所有实际参数 setString(1、“foo”); setString(2,“酒吧”); 其余的绑定为NULL Types.VARCHAR setNull (3) ... Types.VARCHAR setNull (10)

NULL从不匹配任何东西,因此它会被SQL计划构建器优化。

当你将List传递给DAO函数时,逻辑很容易自动化:

while( i < param.size() ) {
  ps.setString(i+1,param.get(i));
  i++;
}

while( i < MAX_PARAMS ) {
  ps.setNull(i+1,Types.VARCHAR);
  i++;
}

在javaranche Journal上Jeanne Boyarsky的JDBC批处理选择语句条目中,有对各种可用选项及其优缺点的分析。

建议的方案是:

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的描述在鲍里斯的回答。

不过,似乎没有任何方法可以使setArray与IN-lists一起工作。


有时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方法进行扩展。

只是为了完整性:只要值集不是太大,您也可以简单地用字符串构造语句,如

... WHERE tab.col = ? OR tab.col = ? OR tab.col = ?

然后你可以把它传递给prepare(),然后在循环中使用setXXX()来设置所有的值。这看起来很讨厌,但许多“大型”商业系统通常都会这样做,直到达到特定于db的限制,例如Oracle中的语句为32 KB(我认为是)。

当然,您需要确保集合永远不会不合理地大,或者在这种情况下进行错误捕获。