Java: User provides the where-clause: how to prevent SQL injection?

Question

We are currently developing a feature, a part of which is to allow the user to define a where-clause for a simple select statement, so he can restrict the result he wants to get, via GUI.

The table name may differ, but else it's just a "select * from TABLE_NAME" (I know, it's a terrible idea, but the customer is the king). Since this offers unlimited possibilities for an SQL injection, I have been searching for viable approaches, to at least prevent the most widely used SQL injection techniques, for the last days and I could not find a lot of information. Most tips are based on the assumption, that the user only provides the parameters for the query, which can be solved through PreparedStatements. But in my case they are pretty much useless.

I am currently thinking of two approaches, which I probably will combine for the best effect, but I don't know, if there aren't much better ways to prevent the injections and how exactly to realize it (maybe there exist open source tools or frameworks for that). I think of analysing the text and

1. Define, what the user input must not contain, based on the widely used SQL injection techniques.
2. Define, what the user input should look like.

For 1. I think to create a utility class, which will contain the methods to check the different SQL injection cases. I could i.e. use regular expressions to recognize the patterns.

For 2. I think to use either regular expressions or the XText framework, to define a DSL, so the user input is only accepted, when it matches the defined rules. I also could extract the column names to check, if they actually exist in the current TABLE_NAME. But in this case it would force us to only allow certain kind of restrictions for the query (i.e. rownum<=100 would not work, or would need special handling).

I would be very grateful, if you can recommend me any better techniques, tools or approaches, since, as I mentioned, there isn't much information out there on this topic.

Thank you in advance!

Answer 1

You are building a sql injection app

Two things you can do:

1. Limit the types of queries your database user can do. For instance, don't grant update privileges.

2. Limit your where clauses to only match some simple criteria, e.g. a clauses must match t.col=value. Then compare all input against your rules using a simple parser

There are several injection techniques and it might be hard to get them all.

Answer 2

I would consider a graphical query builder UI instead of vanilla SQL where clause text field.

• For instance, if you do Java, it just might not be such a big job to create graphical representation (clever boxes with properties and whatnot) that are then mapped into critera objects for Hibernate Criteria queries .
• Who knows, your customer might be more grateful for a graphical query builder than a plain text field that requires help + expertise in SQL queries.