SQL Injection via field names, and table names.

About a year ago I had to implement a system where the table name could be configured for an application.  Why would you want to do that?  Lots of reasons really, like if you have a table generator via a web interface.  In this case, the system integrates with other systems, and users need a way to specify where the data would go in an existing database.  So I merrily went ahead to try and put the table name in a prepared statement... but ERROR!  Unfortunately the sqlite database does not do table names or column names via prepared statements (like postgresql does for example).  The documented solution by sqlite is to escape them correctly with the provided functions.  Unfortunately python does not expose these SQL escaping functions - since most people should be using prepared statements.

Here are some WONTFIX, closed, invalid bugs also mentioning table names, and column names not being a problem.  One of them is six years old.

(closed wontfix) http://bugs.python.org/issue11685
(closed invalid) https://code.djangoproject.com/ticket/1474
(closed invalid) https://code.djangoproject.com/ticket/14991

Python and Django don't really consider this a vulnerability in themselves, but a problem in users code.

However, I still think it is something people should still be aware of.  Especially if you are taking user input to generate your table names, or your field names.  For those people, it could be a problem unless you are really careful.

SQL Injection with Django (a case study)

I'm going to use the python framework Django as a case study of what to be careful of.  However this advice applies to all python, php, [insert your favourite language or framework] and other SQL using libraries.

This code is used to quote table names, and field names in Django via the mysql db backend.

    def quote_name(self, name):
        if name.startswith('"') and name.endswith('"'):
            return name # Quoting once is enough.
        return '"%s"' % name

Note how there is no database provided escaping of the input?  To protect against SQL injection it should be using prepared statements or an escaping function.

Because Django mostly does things like validate field names, and table names... this turns out to not be a problem in a lot of cases.  Other layers of security in Django protect it here.  This isn't an "OMG free Django p0wny!" bug, but a fairly niche bug if you do something wrong in your own code.

An example of how you can inject SQL because of quote_name is here, where the command takes a table name:

django-admin.py createcachetable 'myinjectedtable` (id INT) select id from users;create table `thecachetablename'

This SQL injection could be a problem if you are getting the cache table name from user input somewhere else, and not validating it first.

quote_name (as one of the places that does it wrong) is used in public 3rd party code, and internally within Django.  It is not a private method, since it does not begin with '_' or '__'.  There are places where a string can be supplied from outside of django (either through command line, or through other APIs used, or from a file).  It says that the db backends are responsible for quoting the field names (eg, the Q.__str__ method).

The Django book says:

"Thus, if you need to, say, dynamically construct a list of tables from a POST variable, you’ll need to escape that name in your code. Django provides a function, django.db.connection.ops.quote_name, which will escape the identifier according to the current database’s quoting scheme."

Another example where you can inject SQL...

MyObject.objects.extra(where=['"name"="SomeName";CREATE TABLE qwer (id int) select id from users'])

The Django documentation warns to be careful about this function.  However there is no documentation on the extra method about escaping field names, or table names.  Should probably add a warning there: https://docs.djangoproject.com/en/1.4/ref/models/querysets/#extra

You should be careful using input from untrusted places where you'll use it for table names, or field names.  Luckily this is not done very often by lots of people.

What can we do to fix table name SQL injections?

• Use prepared statements if db backend allows,
• ... or escape it correctly(as documented by each DB engine) when using prepared statements are not possible.
• Disable multiple statements in the db connect, so even if something gets through the damage is limited.
• validate the input where possible (for example, why would a table name need to include ;  or DROP DATABASE?)
  
• document dangerous calls to explain how to escape values correctly in your database abstraction.

Some more expensive to do fixes are:

• have automated SQL injection tests.
• use the database provided permission systems. So front end code only has permissions to do what is required, and admin code can only do what is required of it.