tool for detecting non-parametrized sql in java jdbc code

Question

I'm looking to inspect SQL statements in Java/jdbc code to ensure that the SQL to be executed is of acceptable quality. Neither PMD not Findbugs appears to have JDBC or sql rules. I could use p6spy to log the SQL and look at that way, but this is manual.

I'm wondering if the strategy of of using PMD/Findbugs/etc to create a rule that any string passed to PreparedStatement where there is an "=" or "in" has only parametrized vars on the compare side.

Has anyone done this? Or done this by other means?

Answer 1

This is a tricky problem. Comparison operators like = and IN() are some cases, but there's also: != <> < <= > >= LIKE.

How do you spot cases of interpolating application variables as literals in expressions?

String sql = "SELECT *, " + someJavaVar + " AS constant_column FROM mytable";

You could search for SQL containing string delimiters, but SQL injection doesn't come only from interpolating string literals.

How would you spot cases of interpolating application variables as things other than data values?

String sql = "SELECT * FROM mytable ORDER BY " + columnname;

I don't know any automatic way to detect SQL injection flaws. Code review is a more effective way to spot them. In every SQL statement that contains interpolated application variables, you have to confirm that the application variables are "safe" and that your app has explicitly validated them or transformed them so they don't contain dangerous payload.

Answer 2

Do you have the ability to completely test the application with a debugger connected to it?

Set a breakpoint in your JDBC driver's implementation of Connection.createStatement(), and run the app... (or if using a driver for which you don't have source code, write a fake driver that just delegates calls to the real one, and log all instances of createStatement())