How to avoid "Security - A prepared statement is generated from a nonconstant String" FindBugs Warning

Question

I am working on a project that has a piece of code like the one below:

String sql = "SELECT MAX(" + columnName + ") FROM " + tableName;                
PreparedStatement ps = connection.prepareStatement(sql);

Is there any way that I can change this code so that FindBugs stop giving me a "Security - A prepared statement is generated from a nonconstant String" warning ?

Please assume that this code is safe regarding SQL INJECTION since I can control elsewhere in the code the possible values for "tableName" and "columnName" (they do not come come directly from user input).

Answer 1

Do not concatenate the sql String by +. You can use

String sql = String.format("SELECT MAX(%s) FROM %s ", columnName, tableName);

This is slower than concatenating a String so you should initialize this static then this is not a problem.

I think using a StringBuilder will also fix this warning.

Another way you can avoid this warning is to add @SuppressWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING") above that string (or the method/or the class).

You could also use a Filter File to define rules which should be excluded.

Answer 2

private static final String SQL = "SELECT MAX(?) FROM ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.preparedStatement.setInt(1,columnName);
ps.preparedStatement.setString(2,tableName);

if you are using prepared statement, then in parameter should be a final string and parameters should be added later using setInt, setString methods.

this will resolve the findbug warning.

Answer 3

Neither String.format nor StringBuilder (or StringBuffer) helped me.

Solution was "prepareStatement" isolation:

private PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {
    return conn.prepareStatement(sql);
}

Answer 4

Try using the following...

private static final String SQL = "SELECT MAX(%s) FROM %s";

And then using a String.format() call when you use it...

PreparedStatement ps = connection.prepareStatement(String.format(sql,columnName,tableName));

If that doesn't solve the problem, you can always ignore that check; turn it off in your FindBugs configuration.

If that doesn't work (or isn't an option), some IDEs (like IntelliJ) will also let you suprress warnings with either specially formatted comments or annotations.