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...