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

考虑下面的SQL语句:

SELECT my_column FROM my_table where search_column IN (?)

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

有什么可行的解决办法?


当前回答

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

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

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

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

其他回答

尝试使用instr函数?

select my_column from my_table where  instr(?, ','||search_column||',') > 0

then

ps.setString(1, ",A,B,C,"); 

不可否认,这是一个肮脏的黑客,但它确实减少了sql注入的机会。在甲骨文工作。

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

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

创建带有几个参数(例如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++;
}

下面是我在自己的应用程序中解决这个问题的方法。理想情况下,您应该使用StringBuilder而不是使用+来表示字符串。

    String inParenthesis = "(?";
    for(int i = 1;i < myList.size();i++) {
      inParenthesis += ", ?";
    }
    inParenthesis += ")";

    try(PreparedStatement statement = SQLite.connection.prepareStatement(
        String.format("UPDATE table SET value='WINNER' WHERE startTime=? AND name=? AND traderIdx=? AND someValue IN %s", inParenthesis))) {
      int x = 1;
      statement.setLong(x++, race.startTime);
      statement.setString(x++, race.name);
      statement.setInt(x++, traderIdx);

      for(String str : race.betFair.winners) {
        statement.setString(x++, str);
      }

      int effected = statement.executeUpdate();
    }

如果您决定以后更改查询,使用上面的x这样的变量而不是具体的数字会有很大帮助。

下面是一个完整的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;

    }

}