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

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

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

有什么可行的解决办法?


当前回答

下面是一个完整的Java解决方案来为你创建准备好的语句:

/*usage:

Util u = new Util(500); //500 items per bracket. 
String sqlBefore  = "select * from myTable where (";
List<Integer> values = new ArrayList<Integer>(Arrays.asList(1,2,4,5)); 
string sqlAfter = ") and foo = 'bar'"; 

PreparedStatement ps = u.prepareStatements(sqlBefore, values, sqlAfter, connection, "someId");
*/



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Util {

    private int numValuesInClause;

    public Util(int numValuesInClause) {
        super();
        this.numValuesInClause = numValuesInClause;
    }

    public int getNumValuesInClause() {
        return numValuesInClause;
    }

    public void setNumValuesInClause(int numValuesInClause) {
        this.numValuesInClause = numValuesInClause;
    }

    /** Split a given list into a list of lists for the given size of numValuesInClause*/
    public List<List<Integer>> splitList(
            List<Integer> values) {


        List<List<Integer>> newList = new ArrayList<List<Integer>>(); 
        while (values.size() > numValuesInClause) {
            List<Integer> sublist = values.subList(0,numValuesInClause);
            List<Integer> values2 = values.subList(numValuesInClause, values.size());   
            values = values2; 

            newList.add( sublist);
        }
        newList.add(values);

        return newList;
    }

    /**
     * Generates a series of split out in clause statements. 
     * @param sqlBefore ""select * from dual where ("
     * @param values [1,2,3,4,5,6,7,8,9,10]
     * @param "sqlAfter ) and id = 5"
     * @return "select * from dual where (id in (1,2,3) or id in (4,5,6) or id in (7,8,9) or id in (10)"
     */
    public String genInClauseSql(String sqlBefore, List<Integer> values,
            String sqlAfter, String identifier) 
    {
        List<List<Integer>> newLists = splitList(values);
        String stmt = sqlBefore;

        /* now generate the in clause for each list */
        int j = 0; /* keep track of list:newLists index */
        for (List<Integer> list : newLists) {
            stmt = stmt + identifier +" in (";
            StringBuilder innerBuilder = new StringBuilder();

            for (int i = 0; i < list.size(); i++) {
                innerBuilder.append("?,");
            }



            String inClause = innerBuilder.deleteCharAt(
                    innerBuilder.length() - 1).toString();

            stmt = stmt + inClause;
            stmt = stmt + ")";


            if (++j < newLists.size()) {
                stmt = stmt + " OR ";
            }

        }

        stmt = stmt + sqlAfter;
        return stmt;
    }

    /**
     * Method to convert your SQL and a list of ID into a safe prepared
     * statements
     * 
     * @throws SQLException
     */
    public PreparedStatement prepareStatements(String sqlBefore,
            ArrayList<Integer> values, String sqlAfter, Connection c, String identifier)
            throws SQLException {

        /* First split our potentially big list into lots of lists */
        String stmt = genInClauseSql(sqlBefore, values, sqlAfter, identifier);
        PreparedStatement ps = c.prepareStatement(stmt);

        int i = 1;
        for (int val : values)
        {

            ps.setInt(i++, val);

        }
        return ps;

    }

}

其他回答

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

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

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...
    }
}

or

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...
    }
}

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

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

在实现上述任何复杂的建议之前,请考虑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 + ");";