Tool to detect non-parameterized sql in java jdbc code
I am looking to validate SQL queries in Java / jdbc code to ensure that the SQL to be executed is of acceptable quality. Neither PMD nor Findbugs seem to have JDBC or SQL rules. I could use p6spy to log the SQL and have a look at this, but this is a tutorial.
I am wondering if the strategy is to use PMD / Findbugs / etc to create a rule that any string passed to PreparedStatement where there is "=" or "in" has only parameterized vars on the comparison side.
Has anyone done this? Or do it in other ways?
source to share
This is a difficult problem. Comparison operators such as =
and IN()
, are some cases, but also: != <> < <= > >= LIKE
.
How do you define cases where application variables are interpolated as literals in expressions?
String sql = "SELECT *, " + someJavaVar + " AS constant_column FROM mytable";
You can search for SQL containing string delimiters, but SQL injection comes from more than string literal interpolation.
How would you treat application variable interpolation cases as things other than data values?
String sql = "SELECT * FROM mytable ORDER BY " + columnname;
I don't know of an automatic way to detect SQL injection errors. Code review is a more efficient way to identify them. In every SQL statement containing application variables, you must confirm that the application variables are "safe" and that your application has explicitly checked them or modified them so that they do not contain dangerous payloads.
source to share
Do you have the ability to fully test your application with a debugger attached to it?
Set a breakpoint in the JDBC driver implementation for Connection.createStatement () and run the app ... (or if you're using a driver for which you don't have source code, write a fake driver that just delegates calls to the real one, and register all instances of createStatement ( ))
source to share