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

Sonarqube is giving me this error:

[BLOCKER] Change this code to not construct SQL queries directly from user-controlled data

Here is my code:

String countSQL;

countSQL = (String.format("SELECT count(*) as total FROM ltid_owner.enty %s",additionalWhereClauses));

jdbcTemplateTMI.queryForObject(countSQL, Integer.class);

In the above code additionalWhereClauses could be something like this shown below which I am building on the fly when the user clicks on the grid to perform filtering on different columns:

additionalWhereClauses = where UPPER(enty_num) like '003%'

Can you please let me know how to resolve this issue?

CodePudding user response：

Your code combines strings into SQL statements. If any of these strings contains user provided input, an attacker can sneak in code to trigger an SQL injection attack and possibly run arbitrary code on your computer (obligatory Bobby Tables reference).

Simple example:

String sql = "SELECT * FROM users WHERE name = '"   name   "' AND password = '"   password   "'";

If I enter ' OR 1=1 -- for the name (and "..." for the password, but that doesn't really matter anymore) the code becomes a valid SQL statement:

SELECT *
FROM users 
WHERE name = '' OR 1=1 -- ' AND password = '...'

but the user name / password check is completely disabled.

To avoid this, use prepared statements. They build the SQL command in a way that SQL injection is impossible.

Maybe this never happens in your code as you don't accept user input, but Sonar doesn't know this (and human reviewers won't either). I'd always use prepared statements. Just because your code only passed column headers from a frontend, doesn't mean an attacker cannot manually call your web service endpoints and pass whatever they want, it your code runs as an HTTP endpoint.