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

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

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

有什么可行的解决办法?


当前回答

只是为了完整起见,因为我没有看到其他人提出这个建议:

在实现上述任何复杂的建议之前,请考虑SQL注入是否确实是您的场景中的一个问题。

在许多情况下,提供给In(…)的值是一个id列表,这些id以某种方式生成,您可以确保不可能进行注入…(例如,之前select some_id from some_table where some_condition.)

如果是这种情况,您可能只是连接这个值,而不为它使用服务或准备好的语句,或将它们用于此查询的其他参数。

query="select f1,f2 from t1 where f3=? and f2 in (" + sListOfIds + ");";

其他回答

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

示例:假设您有多个参数要传递到' 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”,以确保查询中不可能出现这样的单词。希望你得到了答案。

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

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

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

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并在视图中进行转换是最有效的解决方案。

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);

对于某些情况,regexp可能会有所帮助。 下面是我在Oracle上查看的一个例子,它是有效的。

select * from my_table where REGEXP_LIKE (search_column, 'value1|value2')

但它也有一些缺点:

它应用的任何列都应该转换为varchar/char,至少是隐式转换。 使用特殊字符时要小心。 它会降低性能——在我的例子中,in版本使用索引和范围扫描,而REGEXP版本执行全扫描。