将参数传递给方法时，将标记查询增强为sqlInjection [英] Fortify flagging query as sqlInjection when passing in parameters to a method

问题描述

我们在数据库层中有一个如下所示的方法:

We have a method in our database layer which looks like this:

public List<String> getNamesFromId(List<Long> idsList){
   StringBuilder query = new StringBuilder();
   query.append("Select first_name from person where id in (");

    for (int pos = 0; pos < idsList.size(); pos++) {
        query.append("?");
        query.append(",");
    }
    query.deleteCharAt(query.length() - 1).append(")");

    try {
        conn = establishConnection();           
        pstmt = conn.prepareStatement(query.toString());
        for (int i = 0; i < selections.size(); i++) {
            pstmt.setLong(i + 1, idsList.get(i));
        }
        rs = pstmt.executeQuery();
    } catch (SQLException e) {
        //
    }

    try {
       List<String> namesList = new ArrayList<String>();

        while (rs.next()) {


                namesList.add(rs.getString("FIRST_NAME"));


        }
    } catch (SQLException e) {
        //
    }
    // close the Connection object
    try {
        rs.close();
        pstmt.close();
        conn.close();
    } catch (SQLException e) {
        //
    }

在我们的强化扫描过程中，将其标记为SQL Injection 调用一个SQL查询，该查询使用可能来自不受信任来源的输入来构建.此调用可能使攻击者修改该语句的含义或执行任意SQL命令."

During our fortify scan it is flagging this as SQL Injection saying "invokes a SQL query built using input potentially coming from an untrusted source. This call could allow an attacker to modify the statement's meaning or to execute arbitrary SQL commands."

这是因为这是一个面向公众的方法，并且我们正在传递准备好的语句的IN部分的参数?如果是这样，我们如何才能做得更好?还是从设防上发出了虚假的警报?

Is this because this is a public facing method and we are passing in the parameters for the IN part of the prepared statement? If so how can we do it better? Or is it a false alarm from fortify?

推荐答案

这是一个错误的警报，您正在按照正确的方式进行操作.

This is a false alarm, you're doing it the right way.

有一些框架可以帮助您解决这个问题(例如Spring的NamedParameterJdbcTemplate，但它们基本上是在后台做同样的事情.

There are frameworks that can help you with this (e.g. Spring's NamedParameterJdbcTemplate, but they basically do the same thing under the hood.

静态分析器可能正在通过连接字符串来捕获正在构建查询的事实，或者某种程度上涉及到输入的大小，并将其标记为危险(仅在此处猜测).

The static analyzer is probably catching the fact you're building your query by concatenating strings, or that the size of input is somehow involved, and flagging it as a danger (only guessing here).

另一方面，与SQL注入无关的潜在问题是，您只能使用特定数量(取决于数据库)的那些参数-AFAIK在Oracle中限制为1000，而在Teradata中限制为2000，确定别人.如果需要在该IN子句中放置许多值，则需要使用其他方法，例如使用临时表或小批量执行查询并将结果合并到Java中.

On a side note, a potential problem that however has nothing to do with SQL injection is that you can only use certain number (DB-dependent) of those parameters - AFAIK the limit's 1000 in Oracle, about 2000 in Teradata, not sure about others. If you need to put many values in that IN clause, you'll need to use a different approach like using a temporary table or performing the query in smaller batches and merging the results in Java.

这篇关于将参数传递给方法时，将标记查询增强为sqlInjection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！