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 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?

@moylop260 is this your real example, or is table coming from somewhere else in your real code? Because in this example, table is coming from a constant in your code, so there's no security issue. — Dan Getz
– Dan Getz, Commented May 19, 2016 at 17:08

Dan Getz · Accepted Answer · 2016-05-19 18:30:48Z

8

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:

sql = 'SELECT name FROM user'
sql = 'SELECT name FROM ' + 'user'
sql = 'SELECT name FROM %s' % ['user']
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 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)

answered May 19, 2016 at 18:30

Dan Getz

9,2337 gold badges38 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vinícius Queiroz Over a year ago

This is nice to prevent SQL Injection. However, beware of opening your application to other vulnerabilities when doing so. For example, Broken Access Control (owasp.org/Top10/A01_2021-Broken_Access_Control) was the number one vulnerability on OWASP 2021, and BOLA (owasp.org/API-Security/editions/2023/en/…) is the number one in 2023. If a user should not have access to a specific database, then other control mechanisms should be implemented as well.

Scott H · Accepted Answer · 2017-01-12 17:53:48Z

Using psycopg2 query parameters in the execute function is safest, and it is easy to use when the parameters are being used as literals.

cursor.mogrify("select * from foo where bar = %s", ('example',))
# yields "select * from foo where bar = 'example'"

(Note that cursor.mogrify() acts like execute, but just shows the formatted SQL without actually executing it)

However, it is a little trickier to do when you want the parameter to be a table, schema, or other identifier. You could use AsIs to wrap your parameter, but that still leaves the door open to SQL injection.

from psycopg2.extensions import AsIs

cur.mogrify('select %s from foo;', (AsIs('* from dual; drop table students; --'),))
# yields 'select * from dual; drop table students; -- from foo;'

It looks like new development of psycopg2 (>=2.7) will have an Identifier class that you can wrap parameters in and hopefully be safe. If it's not released yet, or if you don't have it, here's a way to create your own class. I'll give some snippets below, but you can also see my gist.

import re
import psycopg2.extensions

class NotSqlIdentifierError(Exception):
    pass

valid_pattern = r'^[a-zA-Z_][a-zA-Z0-9_\$]*$'

class QuotedIdentifier(object):
    def __init__(self, obj_str):
        self.obj_str = obj_str

    def getquoted(self):
        if re.match(valid_pattern, self.obj_str):
            return self.obj_str
        else:
            raise NotSqlIdentifierError(repr(self.obj_str))

psycopg2.extensions.register_adapter(QuotedIdentifier, lambda x: x)

If you have a psycopg2 cursor instance already, you can test/use it this way:

# Test that a valid identifier formats into string
cursor.mogrify('select %s from foo;', (QuotedIdentifier('bar'),))
# returns 'select bar from foo;'

# Test formatting both an identifier and a literal
cursor.mogrify(
    'select * from foo where %s = %s;', 
    (
        QuotedIdentifier('bar'),
        'example'
    )
)
# returns "select * from foo where bar = 'example';"

# Test that a non-valid identifier fails with exception
cursor.mogrify('select %s from foo;', (QuotedIdentifier('* from dummy; drop table students; --'),))
"""Returns following:
---------------------------------------------------------------------------
NotSqlIdentifierError                     Traceback (most recent call last)
<ipython-input-14-d6a960dc458a> in <module>()
----> 1 cur.mogrify('select %s from foo;', (QuotedIdentifier('* from dummy; drop table students; --'),))
<ipython-input-12-0a1327cbaf78> in getquoted(self)
     18             return self.obj_str
     19         else:
---> 20             raise NotSqlIdentifierError(repr(self.obj_str))
     21 
     22 psycopg2.extensions.register_adapter(QuotedIdentifier, lambda x: x)
NotSqlIdentifierError: '* from dummy; drop table students; --'
"""

For more info on the mechanics of custom classes to wrap SQL parameters, see this section in the docs.

Ryabchenko Alexander · Accepted Answer · 2020-06-02 16:03:55Z

0

Here is nice article about injections and python code https://realpython.com/prevent-python-sql-injection/

you can transform

tables = ["user", "group", "partner", "product"]
for table in tables:    
    cursor.execute("SELECT name FROM %s LIMIT 1" % (table,))

to

from psycopg2 import sql

tables = ["user", "group", "partner", "product"]
for table_name in tables:        
    stmt = sql.SQL("SELECT name FROM {table_name} LIMIT 1").format(
        table_name = sql.Identifier(table_name),
    )
    cursor.execute(stmt)

answered Jun 2, 2020 at 16:03

Ryabchenko Alexander

12.7k9 gold badges74 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to avoid SQL injection with "SELECT * FROM {table_name}"?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related