Parse string literals from Java source files to find potential SQL-injections

Question

In our Java-project I need to find potential SQL-injections in the java source files.

We use our own Java-SQL frontend class and all relevant SQL-queries are in the following form:

String someText = "potential SQL injection";
SQL.selectInto(
    "SELECT C.LANGUAGE,  " +
    someText +
    "FROM   COMPANY C " +
    "WHERE  C.COMPANY_NR = :companyNr " +
    "INTO   :languageUid ",
    new NVPair("companyNr", companyNr),
    new NVPair("languageUid", languageUid)
);

The problem is, that the variable "someText" could potentially contain another SQL-string which might cause an SQL-injection.

I am looking for a way (regular expression, java parser, etc...) to find all java-string literals with SQL in it, which have some kind of string concatenation which might contain a SQL-snippet, so that I could inspect this instances manually and rewrite them if necessary.

I assume that SQL-stmts are only used with the SQL-helper class like:

SQL.select(...)
SQL.insert(...)

Rather than trying to block cases of injections, it might be easier to limit what `someText` can actually contain. It looks like you would only want it to have other column names from the `COMPANY` table or function calls on them, but are there other things you would want to allow? — jswolf19, Mar 21 '11 at 14:30
he can't because he is using his own sql library (which by itself is a bad idea but that is not the question here) — Stefan Ernst, Mar 21 '11 at 14:35

score 1 · Answer 1 · edited May 23 '17 at 12:26

Maybe this question helps.

A quick and simple approach could be to check whether the supplied string contains any SELECT, INSERT, UPDATE or DELETE (case insensitive). Without those subqueries should not work and if the user would supply anything else except the correct syntax for selected fields the query should fail to compile.

Additionally, the user that executes the statements (most likely some system user) should not have priviliges to alter the database structure, i.e. no DROP, ALTER XYZ etc. statements at all.

score 1 · Answer 2 · edited May 23 '17 at 12:04

If someText is always list of columns, then you can perform some validation on it. (Even if it differs from just a list of columns, you can perform some validation). At a minimum, you can split it on commas and make sure each element in the resulting array is a valid column name. That would prevent SQL injection in that space.

Why haven't you mentioned the possibility in companyNr and languageUid? Are you doing something behind the scenes there?

If not, have a look at Java - escape string to prevent SQL injection because I think you'll want to prevent it there as well.

score 1 · Answer 3 · answered Mar 21 '11 at 14:43

If there aren't an obscene number of columns in the table(s) you're selecting from then you could use someText to select specific columns from the result set after returning the info from all columns.

Rather than look for SQL injection though, it probably makes more sense to allow a white list of allowable characters for someText that includes letters, numbers, spaces, commas, underscores, etc. that are the only legitimate portions of your column names. The white list approach should be easier to implement the hunting for sql injection.

Parse string literals from Java source files to find potential SQL-injections

3 Answers3