How to avoid SQL injection with "SELECT * FROM {table_name}"?
Question: In Python using Psycopg2 with the following code:

    
    
    import psycopg2
    
    import getpass
    
    conn = psycopg2.connect("dbname=mydb user=%s" % getpass.getuser())
    cursor = conn.cursor()
    
    tables = ["user", "group", "partner", "product"]
    for table in tables:
    
        # with sql injection
        cursor.execute("SELECT name FROM %s LIMIT 1" % (table,))
        print "table", table, "result", len(cursor.fetchone())
    
        # without sql injection
        cursor.execute("SELECT name FROM %s LIMIT 1", (table,))
        print "table", table, "result", len(cursor.fetchone())
    

The output was:

    
    
    table res_partner result 1
    Traceback (most recent call last):
      File "my_psycopg2_example.py", line 16, in <module>
        cursor.execute("SELECT name FROM %s LIMIT 1", (table,))
    psycopg2.ProgrammingError: syntax error at or near "'res_partner'"
    LINE 1: SELECT name FROM 'res_partner' LIMIT 1
    

With SQL injection it works fine.

But we don't want to create a security issue.

We read [this documentation](http://initd.org/psycopg/docs/usage.html#passing-
parameters-to-sql-queries) and in it found the following comment:

> Only variable values should be bound via this method: it shouldn’t be used
> to set table or field names. For these elements, ordinary string formatting
> should be used before running `execute()`.

But if we use "ordinary string formatting", we'll have SQL injection too.

What's a good way to manage this special case, and avoid SQL injection?


Answer: I think you're confusing the definition of SQL injection. SQL injection is an
_attack_ on your software where someone causes your SQL query to do something
you didn't want it to. String interpolation is not SQL injection. String
interpolation _can sometimes_ enable SQL injection, but not always. To see
that string interpolation isn't always unsafe, think about which of the
following is safest:

  1. `sql = 'SELECT name FROM user'`
  2. `sql = 'SELECT name FROM ' + 'user'`
  3. `sql = 'SELECT name FROM %s' % ['user']`
  4. `sql = 'SELECT name FROM {}'.format('user')`

Each of these lines of code does the exact same thing, so none of them can be
more or less safe than the others. In your exact example, there's no danger of
SQL injection, because you're just building a hardcoded SQL query string.

On the other hand, if your `table` value came from a user, then there could be
security issues:

  * What if they pass the name of a table that exists, but you didn't want them to query?
    
        table = 'secrets'
    sql = 'SELECT name FROM %s LIMIT 1' % table
    

results in:

    
        SELECT name FROM secrets LIMIT 1
    

  * What if they pass [something](https://xkcd.com/327/) that is not actually a table name?
    
        table = 'product; DROP TABLE user; --'
    sql = 'SELECT name FROM %s LIMIT 1' % table
    

results in:

    
        SELECT name FROM product;
    DROP TABLE user;
    -- LIMIT 1
    

You could prevent this by checking if the table name is allowed:

    
    
    if table.lower() not in ["user", "group", "partner", "product"]:
        raise Something('Bad table name: %r' % table)
    

