Change this code to not construct SQL queries directly from user-controlled data SonarIssue

[neha_menezes]

I am getting the Change this code to not construct SQL queries directly from user-controlled data issue for files , where the query is formed by appending some constants based on if condition.
Any suggestions on how to resolve the sonar issue.?
Sonar Version 9.9.1

public List<WeeklyData> findData( Double Min,
            Double Max, Filter coverageType) {

StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append(FileConstants.WhereConst);
queryBuilder.append(FileConstants.Const);
IntlEUtil.appendThresholdQuery(Min, Max, coverageType, queryBuilder);
return jdbcTemplate.query(queryBuilder.toString(), rowMapper);
}



public static void appendThresholdQuery(Double l1Min, Double l1Max, Filter coverageType,
            StringBuilder queryBuilder) {
        
        queryBuilder.append(IntlConstants.AND);
        if (IntlConstants.GREEN.equals(coverageType)) {
            queryBuilder.append(IntlConstants.valuConst)
            .append(l1Min);
        }
        }else if (IntlConstants.RED.equals(coverageType)) {
            queryBuilder.append(IntlConstants.Val2Const)
        
            .append(IntlConstants.SPACE)
            .append(l1Max);
        }else {
            queryBuilder.append(" 1=1 ");
        }
    }

[Colin]

Hey there

@neha_menezes

Sonar Version 0.0.1304

I’m not sure what version this really is. Can you double-check the footer of your SonarQube instance?

[neha_menezes]

Sonar version is Version 9.9.1

[Loris_Sierra]

Hi @neha_menezes, Welcome to this forum !!

This message happens when you directly add data from a request to the querybuild: For example: Get Parameters, our Route Parameters, or Post Parameters, you name it.

If you concatenate data coming from a Request to the query builder, this is vulnerable to SQL injections.

Instead, you need to do a prepared statement: Instead of concatenating the data, you add a ? string, and then use the function Number 2 here (Not number 1, the one you are currently using, it is unsafe):

(documented here: JdbcTemplate (Spring Framework 6.1.12 API))

Starting from the 3RD argument: You add the initial object(s). So you need to make sure the object(s) is/are passed to the findData function. The point is: Do not concatenate request data into SQL queries, use “prepared statements” instead.

Look at this example:

For More information

Go to this demo Sonarqube instance.
Click on “How can I fix it?”:

And then on “Spring”:

Scroll at the very end, to this explanation:

[neha_menezes]

Update: On further analysis , found that there are a series of issues shown in SonarQube , starting with ‘Source: a user can craft an HTTP request with malicious content’ for RequestBody where i am accepting Filter dto object . is there any way to resolve this while getting the request from api since the code has multiple append stmts based on if conditions.

[Loris_Sierra]

Hello Neha,

Your message does not contain enough data for me to help you. Please give me screenshots, like this:

and

[Loris_Sierra]

@neha_menezes

Just in case you did not know: When you receive a message from SonarQube you can learn about what we recommend you do to fix the message.

You need to click on the message:

Example in code:

Example in “issues” page

[neha_menezes]

[Loris_Sierra]

Hi again @neha_menezes,

can you show me the full window with numbers ?

Please click on the numbers and take a screenshots of the code next to it. When you click on a number, it goes to the code and I need to see all numbers

Also I need to see all the numbers, normally there’s “SINK” written in the last one, like that:

I also need to understand the code of InboundLtService.java

Basically, to really understand, I need to understand your code, to know where the problem is

[neha_menezes]

[Loris_Sierra]

Hey @neha_menezes I think I found the problem, can you show me the code of functions

IntlE2ESqlUtil.appendFilterValuesWithOutStore

and

IntlE2ESqlUtil.appendFilterValuesOfChannel

(The ones used here:

If I know what attribute of filterCriteria is used, I can try to help craft a solution

[neha_menezes]

@Loris_Sierra

appendFilterValuesWithOutStore

[Loris_Sierra]

Hi @neha_menezes, I expected to see the line where there’s a queryBuilder.append with a filterCriteria attribute, so we need to dig further and look at appendFilterCriteria or appendStringEqualFilterCriteria.

To avoid too many screenshots and taking too long here is what I suggest:

Follow the queryBuilder variable everytime it goes in a function with filterCriteria. And then, everytime there is

queryBuilder.append(filterCriteria.A);,
queryBuilder.append(filterCriteria.B);,
queryBuilder.append(filterCriteria.C);,
etc.

You replace it with queryBuilder.append("?");

And then in DCInboundLTRepositoryImpl.java,

replace

return jdbcTemplate.query(queryBuilder.toString(), rowMapper);

with

return jdbcTemplate.query(queryBuilder.toString(), rowMapper, filterCriteria.A, filterCriteria.B, filterCriteria.C);

Is it ok ?

I will not be available next week but try this solution and it should work

[Loris_Sierra]

Hi @neha_menezes ,

Did you manage to solve this problem?