DanGordon DanGordon - 1 month ago 9
Java Question

Prevent this statement from SQL injection

I am trying to write a very simply query using the

PreparedStatement
class. I read here:Fail to convert to internal representation JDBC that you cannot parameterize column names, only values. Since my query is very simple, the only 'value' I can parameterize is
count (*)
.

This is the query:
SELECT COUNT (*) FROM EZ_DAY


If I try to parameterize it like this:
SELECT ? FROM EZ_DAY


I get an error:
Fail to convert to internal representation

when using the method
getInt()
on the
ResultSet
.

How can I use
PreparedStatement
and parameterize something in this query to prevent SQL injection? Also I know you can't parameterize column names, does that include table names? For example, can I do something like:

SELECT COUNT (*) FROM ?

?

Answer

That query cannot fall into SQL injection. The queries that fall in this category are those queries that you build by plain String concatenation. For example:

String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colX = " + stringParameter;
Statement stmt = con.createStatement(query);
ResultSet rs = stmt.executeQuery();

In your case, there's no parameter to inject, so there's no way to have a SQL injection attack for your specific case.

If you need to prevent from SQL injection attacks, use PreparedStatement and do not concatenate the query. Instead, pass the parameters through the interface, which will escape any invalid character for you:

String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colX = ?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setString(1, stringParameter);
ResultSet rs = pstmt.executeQuery();

In case you need to build a dynamic query, then you may fall back into concatenating strings, regardless if you use plain String concatenation or a StringBuilder:

//Common solution, still suffers from SQL injection
String query = "SELECT COUNT(*) FROM EZ_DAY WHERE 1 = 1 ";
if (stringParameter != null) {
    query = query + = "AND colX = " + stringParameter;
}

Instead, it is better to use a COALESCE or IFNULL function to the parameter to avoid such situations:

//Better solution
String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colx = COALESCE(?, colx)";

In the case above:

  • If the parameter has a different value than null, the query would be like this:

    String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colx = ?";
    
  • If the parameter has null value, then the query would be like this:

    String query = "SELECT COUNT(*) FROM EZ_DAY WHERE colx = colx";
    

In the last example, you're still able to use PreparedStatement and avoid SQL injection attacks.

Related: