Hibernate：当集合元素需要用`like`运算符检查时如何防止SQL注入？

问题描述

我有一个 p>

如果我的集合元素将根据 = 运算符进行比较，那么我使用准备好的语句，如下所示：

  sbQry.append（（in：：collection）中的列）; 
 Query query = session.createSQLQuery（sbQry.toString（））; 
 query.setParameterList（collection，myCollection）; 
  

任何人都可以请建议我如何在这种情况下阻止SQL注入。

任何帮助将不胜感激。

解决方案

为防止SQL注入，您应该使用绑定参数，而不是字符串插值。

  StringBuilder sbQry = new StringBuilder（）; 
列表< String> params = new ArrayList<>（）; 
 sbQry.append（select * from tableName）; 
 if（！myCollection.isEmpty（））{
 sbQry.append（where）; 
 int size = myCollection.size; 
列表< String> terms = new ArrayList<>（）; 
 for（int i = 0; i< size; i ++）{
 final String module = myCollection.get（i）; 
 terms.add（column =？or column like？）; 
 params.add（module）; 
 params.add（J_+ module.replaceAll（ - ，％））; 
} 
 sbQry.append（String.join（or，terms））; 
} 
 
查询q = sess.createQuery（sbQry）; 
 int size = params.size（）; 
 for（int i = 0; i< size; i ++）{
 q.setString（i，params [i]）; 
} 
  

我没有测试过上面的代码，但它应该给你一个总体思路。

使用绑定参数而不是字符串连接是防止SQL注入的安全方法，它还使代码更易于编写并更易于阅读。

同样使用ArrayList作为条件，并且 String.join（），意味着您不必大惊小怪 1 = 1 或用于该系列条目开头或结尾的特殊条件代码。

I am having a query something like this.

StringBuilder sbQry = new StringBuilder();
sbQry.append("select * from tableName where 1=1");
if(!myCollection.isEmpty()){
    sbQry.append("  and (");
        for (int i = 0; i < myCollection.size(); i++) {
            final String module = myCollection.get(i);
            sbQry.append("column = '" + module
                    + "' or column like 'J_'||'"
                    + module.replaceAll("-", "%") + "'");
            if (!(i == (myCollection.size() - 1))) {
                sbQry.append(" or ");
            }
        }
        sbQry.append(") ");
 }

Here this query sbQry is vulnerable to SQLInjection because of myCollection is coming from the external source.

In case my collection elements would be comparing based on = operator then I use prepared statement, something like:

sbQry.append(column in (:collection));
Query query = session.createSQLQuery(sbQry.toString());
query.setParameterList("collection",myCollection);

Could anyone please suggest me how can I prevent the SQL injection in this case.

Any help will be appreciated.

解决方案

To protect against SQL injection, you should use bound parameters, not string interpolation.

StringBuilder sbQry = new StringBuilder();
List<String> params = new ArrayList<>();
sbQry.append("select * from tableName");
if(!myCollection.isEmpty()){
    sbQry.append(" where ");
    int size = myCollection.size;
    List<String> terms = new ArrayList<>();
    for (int i = 0; i < size; i++) {
        final String module = myCollection.get(i);
        terms.add("column = ? or column like ?");
        params.add(module);
        params.add("J_" + module.replaceAll("-", "%"));
    }
    sbQry.append(String.join(" or ", terms));
}

Query q = sess.createQuery(sbQry);
int size = params.size();
for (int i = 0; i < size; i++) {
    q.setString(i, params[i]);
}

I have not tested the above code, but it should give you the general idea.

Using bound parameters instead of string concatenation is a safe way to protect against SQL injection, and it also makes code easier to write and easier to read.

Also using ArrayList for the terms, and String.join(), means you don't have to fuss with 1=1 or special conditional code for the beginning or end of the series of terms.

这篇关于Hibernate：当集合元素需要用`like`运算符检查时如何防止SQL注入？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！