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

There many ways to protect your database against SQL injection. I will list them one by one.

1. Using character-escaping functions for user-supplied input provided by your DBMS as following; Also make sure that character set must be set at the server level and mysql_real_escape_string() function does not escape % and _ wildcards.

   $input = mysqli_real_escape_string($db_connection, $_POST['username']);
   

2. Also using the ESCAPE keyword in your SQL query can protect you as following;

   SELECT * FROM table WHERE column LIKE '%%' ESCAPE '!'

3. Using a Web application firewall such as https://www.cloudflare.com/waf/ This will operate in front of your web servers and will monitor the traffic which goes in and out of your web server. So every request to the WAF is inspected against the rule engine and the threat intelligence such as SQL injection, XSS, Parameter tampering, and so on. So Any Suspicious requests can be blocked, challenged, or logged instantly.

4. Avoid administrative privileges such as DB root access. As for searching, your account only needs to have read access to those tables and columns that are required.

5. Using Stored procedures along with special EXECUTE privilege.

6. Using Parametrized queries by binding parameters so that inputs are quoted and the supplied input will not cause the change of the intent.

7. Input validation and sanitization.

8. Even you use SQL statement construction libraries such as jOOQ or JPA and other ORMs that relieve you from creating hand-coded SQL statements it will not protect you from writing vulnerable code. if you developing in Java. You can use short-lived credentials such as Spring Cloud Vault.

9. You can Log everything to prevent damage scope in case an attack occurs.

10. Block usage of the UNION operator. it is a very common Union-Based SQL Injection attack. For Example:

    GET yourapp.com/store.php?store=-1 UNION SELECT 1,pass,cc FROM users WHERE uname='test' HTTP/1.1 Host: yourapp.com

11. Block usage of the DROP operator.

    SELECT * FROM users; DROP users--

Answer 2

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_input
String query = "SELECT col1, col2, col3 FROM table1 WHERE ?";

// Create database connection
Connection conn = source.getConnection();

// Prepare a statement for the query
PreparedStatement stmt = conn.prepareStatement(query);

// set the placeholder with the actual user_input
stmt.setString(1, user_input);

// execute the query
ResultSet result = stmt.executeQuery(query);

IS IT ENOUGH? NO!

Even after using PreparedStatement or createQuery (Similar method for JPA) or anything, there still is a chance that the attacker can pass. So that brings us to this...

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.

Answer 3

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.

Answer 4

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.