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

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

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

有什么可行的解决办法?


当前回答

Sormula支持SQL IN操作符,它允许你提供一个java.util.Collection对象作为参数。它使用?对于集合中的每个元素。参见示例4(示例中的SQL是一个注释,用于澄清Sormula创建但不使用的内容)。

其他回答

你可以使用PreparedStatement的setArray()方法

PreparedStatement statement = connection.prepareStatement("Select * from emp where field in (?)");
statement.setArray(1, Arrays.asList(1,2,3,4,5));
ResultSet rs = statement.executeQuery();

PreparedStatement没有提供任何处理SQL IN子句的好方法。根据http://www.javaranch.com/journal/200510/Journal200510.jsp#a2“你不能替代那些意味着成为SQL语句一部分的东西。这是必要的,因为如果SQL本身可以更改,驱动程序就不能预编译语句。它还具有防止SQL注入攻击的良好副作用。”我最终使用了以下方法:

String query = "SELECT my_column FROM my_table where search_column IN ($searchColumns)";
query = query.replace("$searchColumns", "'A', 'B', 'C'");
Statement stmt = connection.createStatement();
boolean hasResults = stmt.execute(query);
do {
    if (hasResults)
        return stmt.getResultSet();

    hasResults = stmt.getMoreResults();

} while (hasResults || stmt.getUpdateCount() != -1);

in()操作符的局限性是万恶之源。

它适用于不重要的情况,您可以将其扩展为“自动生成准备好的语句”,但它总是有其局限性。

如果您正在创建具有可变数量参数的语句,那么每次调用都会产生SQL解析开销 在许多平台上,in()操作符的参数数量是有限的 在所有平台上,总SQL文本大小是有限的,因此不可能为in参数发送2000个占位符 向下发送1000-10k的绑定变量是不可能的,因为JDBC驱动程序有其局限性

在某些情况下,in()方法已经足够好了,但还不能防火箭:)

最可靠的解决方案是在一个单独的调用中传递任意数量的参数(例如,通过传递一组参数),然后用一个视图(或任何其他方式)在SQL中表示它们,并在where条件中使用。

一个蛮力的变种在这里http://tkyte.blogspot.hu/2006/06/varying-in-lists.html

然而,如果你能使用PL/SQL,这些混乱就会变得非常整洁。

function getCustomers(in_customerIdList clob) return sys_refcursor is 
begin
    aux_in_list.parse(in_customerIdList);
    open res for
        select * 
        from   customer c,
               in_list v
        where  c.customer_id=v.token;
    return res;
end;

然后你可以在参数中传递任意数量的逗号分隔的客户id,并且:

将得到没有解析延迟,因为SQL选择是稳定的 没有流水线函数的复杂性——它只是一个查询 SQL使用一个简单的连接,而不是一个IN操作符,这是相当快的 毕竟,不使用任何普通的select或DML访问数据库是一个很好的经验法则,因为它是Oracle,它提供了比MySQL或类似的简单数据库引擎多得多的东西。PL/SQL允许您以一种有效的方式从应用程序域模型中隐藏存储模型。

这里的技巧是:

我们需要一个接受长字符串的调用,并存储在db会话可以访问它的地方(例如简单的包变量,或dbms_session.set_context) 然后我们需要一个视图,它可以将这些数据解析为行 然后你有一个包含你要查询的id的视图,所以你所需要的只是一个简单的连接到被查询的表。

视图如下所示:

create or replace view in_list
as
select
    trim( substr (txt,
          instr (txt, ',', 1, level  ) + 1,
          instr (txt, ',', 1, level+1)
             - instr (txt, ',', 1, level) -1 ) ) as token
    from (select ','||aux_in_list.getpayload||',' txt from dual)
connect by level <= length(aux_in_list.getpayload)-length(replace(aux_in_list.getpayload,',',''))+1

aux_in_list的地方。Getpayload引用原始的输入字符串。


一个可能的方法是传递pl/sql数组(仅由Oracle支持),但是你不能在纯sql中使用它们,因此总是需要一个转换步骤。这种转换不能在SQL中完成,因此,传递一个带有字符串中所有参数的clob并在视图中进行转换是最有效的解决方案。

使用嵌套查询是一种令人不快的变通方法,但肯定是可行的。创建一个包含列的临时表MYVALUES。将值列表插入到MYVALUES表中。然后执行

select my_column from my_table where search_column in ( SELECT value FROM MYVALUES )

很丑,但如果你的价值列表非常大,这是一个可行的选择。

如果您的数据库没有缓存准备好的语句,这种技术还有一个额外的好处,那就是可能会从优化器获得更好的查询计划(检查一个页面是否有多个值,表只能检查一次,而不是每个值检查一次,等等),这样可以节省开销。您的“insert”将需要批处理,并且可能需要调整MYVALUES表以使锁定或其他高开销保护最小化。

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