2

I'm building an application and I have some very, very advanced users who can come up with endless filters they want. They know SQL so in the end gave them some of the filters and said in the bottom of the screen you can have your custom filter, where you can write your own filter in SQL. And they where pleased. So my question is what checks do I have to perform to be sure that they can't ruin the database. There is no confidencial information and no passwords since these are performed with single sign on.

The surrounding query where they can inject SQL looks like this:

UPDATE table
SET col_a = val_b
WHERE {potentially some other filters}
AND {injected SQL};

I currently don't allow ;, but what else should don't allow? I'm using a postgres database and python using psycopg2.

  • 3
    From what language/framework/library are queries being made? Make sure it doesn't allow multiple queries in a single call. – multithr3at3d Nov 11 '19 at 22:30
  • 1
    You have to whitelist, not blacklist. The SQL language can change, and even if you think of every possible bad character or operator now, there will be something new in the future. Think of a list of the operators that you want to allow, and block everything else. – Martin Fürholz Nov 12 '19 at 5:15
  • @multithr3at3d if I don't allow ; how is it then posible to put multiple queries inside the statement? – Peter Mølgaard Pallesen Nov 12 '19 at 9:44
  • @PeterMølgaardPallesen another query without ; can be added by using subqueries. – BenjaminH Nov 12 '19 at 12:43
  • @PeterMølgaardPallesen I don't know, there's probably some other way. That's why blacklisting is no good – multithr3at3d Nov 12 '19 at 23:00
7

Restricted user accounts

Sanitizing such a query securely may be very, very hard and you should expect that you might have failed in some way which allows the users to execute arbitrary sql.

A possible mitigation for this would be severely restricted user accounts - you need to ensure that the connection under which these queries are executed is run under a user account which can access only the specific tables which you expect to be used, and can modify only the specific table which is expected to be updated. It may be that this needs to be a different database user account (and a different connection) than what the rest of the system runs on.

0

The heavy-lift answer is a creating a domain-specific language with ANTLR or its ilk and transforming that to known-good parameterized SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.