SQL_INJECTION_JDBC Findbugs when loading from file

Question

I have an anonymous procedure that I am getting with a StatementLoader and passing it to a CallableStatement. However FindBugs identifies this as being vulnerable to SQL Injection (SQL_INJECTION_JDBC). If I create a static method that returns the procedure as a string it is fine.

Is there a way I can use the statement loader without the sql injection bug popping up?

Example:

StatementLoader stmt = StatementLoader.getLoader(MyClass.class, connection);    
try (final CallableStatement callable = connection.prepareCall(stmt.load("mySqlCode"))) {...

Answer 1

Yes, if your programming merely loads an SQL statement from an external source and executes it, it is a bona-fide SQL injection opportunity. The SQL statement should be a constant within the program, using parameter-substitution to provide values when executing it. The bug-catcher is correct – you should rewrite this code at once.

Answer 2

I'd like to clarify exactly what I meant by "parameters," since the OP's latest comment used the word, "sanitized."

SQL permits the use of "parameters" in an SQL statement – indicated by question-marks that are not literal-strings – such as the following:

SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = ?

Each and every time the statement is executed, a value for each parameter must be supplied, usually as an array of values. (Of course, most actual database APIs are more friendly, letting you conveniently "name" your parameters.) So, the parameters are never part of the SQL string.

The SQL statement is parsed ("prepared") only once, and the database engine knows that parameter-values must then be supplied each and every time the statement is executed. The query-engine will take and use those parameter-values directly; it will not re-evaluate the SQL. (Which, by the way, is noticeably more efficient!)

The binary values that you provide are used as-is. You don't even have to convert them to character form. Specifically, you don't "sanitize" them! If you provide a value such as FOO%20BAR to some character-type column in an INSERT statement, you'll find a 9-character literal value being inserted into your database: F-O-O-%-2-0-B-A-R. "Those are the 9 characters that you gave me, so that's what I did."

(Database systems, and interfaces to the same, vary in their handling of data-types such as "date.")