CODEWITHSUNDEEP
javasql-injectionowaspesapiveracode
Iris_geek
Iris_geek

Reputation: 393

Cannot mitigate SQL Injections using OWASP.ESAPI - Veracode

I am performing a veracode scan for my Java code and it throws following error in one of my DAO classes

Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection') CWE ID 89

However, I tried to mitigate this using the OWASP.ESAPI Library as I cannot use paramterized input to structure my query as I am using String builders. The following is the sample code I'm trying to work on 

private static String getPhoneDataSql(QuerySearchType type, PhoneQueryParams queryParams){      
    StringBuilder sql = new StringBuilder("select * from users where ");
    List<String> sqls = new ArrayList<String>();

    if(StringUtils.isNotBlank(queryParams.getUserId())){
        sqls.add("USER_ID like ".concat(type.format(encodeSQLInput(queryParams.getUserId()))));
    }
    if(StringUtils.isNotBlank(queryParams.getUserName())){
        sqls.add(UNAME_LIKE.concat(type.format(encodeSQLInput(queryParams.getUserName()))));
    }
    if(StringUtils.isNotBlank(queryParams.getCompany())){
        sqls.add("STR_COMPANY like ".concat(type.format(encodeSQLInput(queryParams.getCompany()))));
    }   

    boolean firstAnd = false;
    for(String s : sqls){
        sql.append(firstAnd ? STR_SQL_LIKE : "").append(s);
        firstAnd = true;
    }
    return sql.toString();
}

private static String encodeSQLInput(String sqlInput){  
    Codec ORACLE_CODEC = new OracleCodec();
    if(sqlInput == null) {
        return "NULL";
    }   
    sqlInput = ESAPI.encoder().encodeForSQL( ORACLE_CODEC, sqlInput);
    return sqlInput;
}

Can somebody help?

Upvotes: 0

Views: 2694

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562991

SQL injection scanners like Veracode typically complain if they find you are building SQL queries by string-concatenation with application variables. They can't tell if you have properly escaped the content in the application variable to make it safe.

It's better practice to avoid string-concatenation because it's so easy to make a mistake and allow an SQL injection vulnerability by accident.

Instead, use query parameters. Instead of concatenating your strings, leave a placeholder like ? in your SQL string, and bind your variable to the statement.

There are many examples in documentation: https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Upvotes: 1

Related Questions