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

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

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

有什么可行的解决办法?


当前回答

我的解决方案(JavaScript)

    var s1 = " SELECT "

 + "FROM   table t "

 + "  where t.field in ";

  var s3 = '(';

  for(var i =0;i<searchTerms.length;i++)
  {
    if(i+1 == searchTerms.length)
    {
     s3  = s3+'?)';
    }
    else
    {
        s3  = s3+'?, ' ;
    }
   }
    var query = s1+s3;

    var pstmt = connection.prepareStatement(query);

     for(var i =0;i<searchTerms.length;i++)
    {
        pstmt.setString(i+1, searchTerms[i]);
    }

SearchTerms是包含你的输入/键/字段等的数组

其他回答

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

遵循亚当的想法。让你的准备语句select my_column from my_table where search_column in (#) 创建一个字符串x,并根据你的值列表用一些“?,?,?”填充它 然后只需更改查询中的#为您的新字符串x填充

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

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

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

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

而不是使用

SELECT my_column FROM my_table where search_column IN (?)

使用Sql语句作为

select id, name from users where id in (?, ?, ?)

and

preparedStatement.setString( 1, 'A');
preparedStatement.setString( 2,'B');
preparedStatement.setString( 3, 'C');

或者使用存储过程,这将是最好的解决方案,因为sql语句将被编译并存储在数据库服务器中

Spring允许将java.util.Lists传递给NamedParameterJdbcTemplate,这将自动生成(?, ?, ?,…, ?),作为适当的参数数量。

对于Oracle,这篇博文讨论了Oracle .sql. array (Connection. array)的使用。createArrayOf不支持Oracle)。为此你必须修改你的SQL语句:

SELECT my_column FROM my_table where search_column IN (select COLUMN_VALUE from table(?))

oracle table函数将传递的数组转换为在in语句中可用的类似表的值。