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.

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.

Prepared Statements & Input Sanitisation

1. Prepared Statements

Java offers PreparedStatement to execute parameterised queries. Queries built with Prepared Statements are less prone to exploits.

Example:

The Query we need to make:

String query = "SELECT col1, col2, col3 FROM table1 WHERE " + user_input;

Using PreparedStatement with parameterised values:

// write the query with "?" placeholder for the user_inputString query = "SELECT col1, col2, col3 FROM table1 WHERE ?";// Create database connectionConnection conn = source.getConnection();// Prepare a statement for the queryPreparedStatement stmt = conn.prepareStatement(query);// set the placeholder with the actual user_inputstmt.setString(1, user_input);// execute the queryResultSet result = stmt.executeQuery(query);

IS IT ENOUGH? NO!

Edit as @phil 's pointed out, using PreparedStatement does stop the illegal values from execution. But still I highly recommend sanitising the inputs as the user may input "String" or random special characters when you were expecting "int".

2. Sanitisation

Let's say, we have two sets of columns for both tables and user can input the column name and the value as well.

Instead of unfiltered input like this:String query = "SELECT col1, col2, col3 FROM table1 WHERE ?";Use some filters. Filters can be anything. May be some string functions or string comparison or input variable type check or anything.

Case1:Let's say the user can filter using the column "col1" and it is an "Integer" or "Numeric" type, we can filter the input to see if there are any special characters in it using Regex:

^[0-9]*$

Case2:Check if the input column name is valid.

private static final Set<String> valid_column_names = Collections.unmodifiableSet(Stream.of("col1", "col2", "col3").collect(Collectors.toCollection(HashSet::new)));boolean is_valid = false;if (valid_column_names.contains(user_column_input)) {  is_valid = true;}if(!is_valid){  throw new IllegalArgumentException("Invalid Column input");}String query = "SELECT col1, col2, col3 FROM table1 WHERE ?";// prepare statements and execute

Final Notes:

So, after all these preventive measures, is your dynamically generated query safe? A lot safer but you can't assure that. There are a lot of problems that makes your db prone to Injection.