How can I securely allow user defined SQL queries?

Question

I want to allow users to query a database with some fairly flexible criteria. I could just use the following:

String slqCmdTxt = "SELECT * FROM TheTable WHERE " + userExpression;

However, I know this is wide open to SQL injection. Using parameters is good, but I don't see a way to allow very flexible queries.

How can I allow flexible database queries without opening myself up to SQL injection?

---

More Details:

There are really two tables, a master and a secondary with attributes. One master record may have many attributes. We want to query on values in both tables. The results are processed into a report which will be more readable than a simple table view. Data is written by a C# program but current direction is to query the table from a component written in Java.

So I need a way to provide user inputs then safely build a query. For a limited set of inputs I've written code to build a query string with the inputs given and parameter values. I then go through and add the input values as parameters. This resulted in complex string catination which will be difficult to change/expand.

Now that I'm working with Java some searching has turned up SQL statement construction libraries like jOOQ...

Answer 1

You should probably create a UI, where the user can select a table from a drop down and then add filters. If you've ever used TOAD or DBVisualizer or even SQLDeveloper, they all have parts in the UI where you can select a table, and then without actually writing SQL the user can add filters and sorting from UI controls.

Then of course, in the code behind the UI you will validate the filter inputs and use them as parameters in prepared statements (depending on what language you are using).

For example, this is what DBVisualizer (which is written in Java) has in their UI when you are browsing database objects and click on a Table. Notice you can select any column from a drop down, then select an operator from another drop down (i.e. =, >, >=, <, <=, LIKE, IN, etc.), and then you can enter a user defined value for the filter value.

You could do something very similar in your UI.

It would help by the way if you include what language your application is going to be written in. If I had to guess, I'd say Java or C# based on your string declaration, but it would be good to know for sure.

Answer 2

Assuming a modern DBMS (eg. Sql Server or Oracle; I am unfamilliar with MySQL), then you can allow the user to write raw SQL as long as you ensure that the account they are logging in with has the proper restrictions applied to it.

In SQL Server you can limit what actions a user can take against any db object (SELECT, DELETE, UPDATE, EXECUTE). I believe this is true in Oracle.. and I think it may even extend to the column level, but I am unsure.