java - 消除查询中的 SQL 注入(inject)漏洞

Question

我需要从一个表中获取数据，该表中有多个过滤器并限制来自java脚本数据表请求的行

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON 
site_dn = mrbts AND siteid = child_site_id 

在我的代码中，我有一个实现，可以在执行准备好的语句之前在查询中附加过滤器。这里的过滤器是List<String[]> filters具有列名称为 (UPPER(mrbts) like UPPER('%6105%')) 的过滤器值... 6105 是过滤器字符串，mrbts 是列名称

private String createFilterWhereClause(List<String[]> filters) {
    StringBuilder sb = new StringBuilder();
        Iterator<String[]> filterParmItr = filters.iterator();
            while (filterParmItr.hasNext()) {
                String[] filterParm = filterParmItr.next();
               sb.append("(")
                        .append(filterParm[ScFilterCriteria.FILTER_PARM_VAL])
                        .append(")");
                if (filterParmItr.hasNext()) {
                    sb.append(" and ");
                }

            }
        return sb.toString();
}

在执行过程中，它形成如下的sql查询并在准备好的语句中执行。

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND
siteid = child_site_id   where UPPER(mrbts) like UPPER('%4105%') and 
((UPPER(technology) like UPPER('%LTE%')))

它存在 SQL 注入(inject)漏洞。为了解决这个问题，我尝试通过使用准备好的语句集字符串来保护它，如下所示，

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND
siteid = child_site_id  where ?

使用准备好的语句，

PreparedStatement ps = null;
Connection connection = null;
ps = connection.prepareStatement(sql);
String filters = createFilterWhereClause(filterClause);
ps.setString(1, filters );

在设置字符串后用单引号形成的sql查询中存在问题，

SELECT coalesce(parent_id,siteid) as siteid, address, state, status, plan,
remarks, FROM archive  LEFT OUTER JOIN site_mappings ON site_dn = mrbts AND 
siteid = child_site_id  where '((UPPER(mrbts) like UPPER(\'%6105%\')))';

如何在设置字符串期间删除单引号和/或任何其他方法来执行此操作？你能帮帮我吗？

Answer 1

静态 SQL 语句的代码如下所示:

String query = "SELECT coalesce(parent_id,siteid) AS siteid, address, state, status, "
    + "plan, remarks "
    + "FROM archive "
    + "LEFT OUTER JOIN site_mappings ON site_dn = mrbts "
    + "AND siteid = child_site_id "
    + "WHERE UPPER(mrbts) LIKE UPPER(?) "
    + "AND UPPER(technology) LIKE UPPER(?)";

// UPPER probably is not needed; there was one spurious comma after "remarks"

String mrbts = "4105";
String technology = "LTE";

try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
    preparedStatement.setString(1, "%" + mrbts + "%");
    preparedStatement.setString(2, "%" + technology + "%");
    try (resultSet = preparedStatement.executeQuery()) {
        while (resultSet.next()) {
            ...
        } 
        return list; // Or such
    }
}

对于动态数量的条件:

StringBuilder sb = new StringBuilder();
List<Object> params = new LinkedList<>();
...
sb.append(" AND mrbts LIKE ? ");
params.add(mrbts);
...
int column = 1;
for (Object param : params) {
    preparedStatement.setObject(column, param);
    ++column;
}