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

    }

}

其他回答

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

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

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

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

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

这适用于我(伪docode):

public class SqlHelper
{
    public static final ArrayList<String>platformList = new ArrayList<>(Arrays.asList("iOS","Android","Windows","Mac"));

    public static final String testQuery = "select * from devices where platform_nm in (:PLATFORM_NAME)";
}

指定绑定:

public class Test extends NamedParameterJdbcDaoSupport
public List<SampleModelClass> runQuery()
{
    //define rowMapper to insert in object of SampleClass
    final Map<String,Object> map = new HashMap<>();
    map.put("PLATFORM_LIST",DeviceDataSyncQueryConstants.platformList);
    return getNamedParameterJdbcTemplate().query(SqlHelper.testQuery, map, rowMapper)
}

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

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

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

我的解决方案是:

create or replace type split_tbl as table of varchar(32767);
/

create or replace function split
(
  p_list varchar2,
  p_del varchar2 := ','
) return split_tbl pipelined
is
  l_idx    pls_integer;
  l_list    varchar2(32767) := p_list;
  l_value    varchar2(32767);
begin
  loop
    l_idx := instr(l_list,p_del);
    if l_idx > 0 then
      pipe row(substr(l_list,1,l_idx-1));
      l_list := substr(l_list,l_idx+length(p_del));
    else
      pipe row(l_list);
      exit;
    end if;
  end loop;
  return;
end split;
/

现在你可以使用一个变量来获取表中的一些值:

select * from table(split('one,two,three'))
  one
  two
  three

select * from TABLE1 where COL1 in (select * from table(split('value1,value2')))
  value1 AAA
  value2 BBB

因此,预处理语句可以是:

  "select * from TABLE where COL in (select * from table(split(?)))"

问候,

哈维尔·伊瓦内斯