Passing table name as a parameter in psycopg2

Question

I have the following code, using pscyopg2:

sql = 'select %s from %s where utctime > %s and utctime < %s order by utctime asc;'
data = (dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql, data)

This outputs:

select 'waterTemp, airTemp, utctime' from 'ss2012_t02' where utctime > '2012-05-03T17:01:35+00:00'::timestamptz and utctime < '2012-05-01T17:01:35+00:00'::timestamptz order by utctime asc;

When I execute this, it falls over - this is understandable, as the quotes around the table name are illegal.

Is there a way to legally pass the table name as a parameter, or do I need to do a (explicitly warned against) string concatenation, ie:

voyage = 'ss2012_t02'
sql = 'select %s from ' + voyage + ' where utctime > %s and utctime < %s order by utctime asc;'

Cheers for any insights.

Sourpuss

Answer 1

According to the official documentation:

If you need to generate dynamically an SQL query (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module.

The sql module is new in psycopg2 version 2.7. It has the following syntax:

from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {table} values (%s, %s)")
        .format(table=sql.Identifier('my_table')),
    [10, 20])

More on: https://www.psycopg.org/docs/sql.html#module-usage

[Update 2017-03-24: AsIs should NOT be used to represent table or fields names, the new sql module should be used instead: https://mcmap.net/q/242966/-psycopg2-difference-between-asis-and-sql-module ]

Also, according to psycopg2 documentation:

Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint.

Apomict

This is the most up to date answer – Sapsago

Make sure .format(...) is called on the sql.SQL object and not on the inner string. I just lost one hour because I had the wrong parenthesis. That is, SQL('select * from {}'.format(...)) won't work, SQL('select * from {}').format(...) will. – People

Answer 2

Per this answer you can do it as so:

import psycopg2
from psycopg2.extensions import AsIs

#Create your connection and cursor...

cursor.execute("SELECT * FROM %(table)s", {"table": AsIs("my_awesome_table")})

Petrillo

it should be cursor.execute("SELECT * FROM %(table)s", {"table": AsIs("my_awesome_table")}) but can't edit because it is only 2 characters :) – Unloosen

Unfortunately AsIs defeats the purpose of parametrization. Do not use with user data. – Questionable

AsIs should not be used for this purpose: https://mcmap.net/q/242966/-psycopg2-difference-between-asis-and-sql-module – Queeniequeenly

Answer 3

The table name cannot be passed as a parameter, but everything else can. Thus, the table name should be hard coded in your app (Don't take inputs or use anything outside of the program as a name). The code you have should work for this.

On the slight chance that you have a legitimate reason to take an outside table name, make sure that you don't allow the user to directly input it. Perhaps an index could be passed to select a table, or the table name could be looked up in some other way. You are right to be wary of doing this, however. This works, because there are relatively few table names around. Find a way to validate the table name, and you should be fine.

It would be possible to do something like this, to see if the table name exists. This is a parameterised version. Just make sure that you do this and verify the output prior to running the SQL code. Part of the idea for this comes from this answer.

SELECT 1 FROM information_schema.tables WHERE table_schema = 'public' and table_name=%s LIMIT 1

Phthalocyanine

Yeah, the table name does need to be passed in externally, there's not really any way around that. I do have a list of 'valid' (ie secure) tables though, so I can do a check against that to make sure the passed parameter is acceptable, to prevent injection.. – Sourpuss

Actually, thinking about it, it would be a fairly easy SQL query. I've posted such a query, you can port it to your adapter to suit your purposes. – Phthalocyanine

"The table name cannot be passed as a parameter" - This is not true. You can use .format to send in without strings. – Whereby

@SamirAlajmovic The point is that using format is vulnerable to sql injection attacks whereas using true parameters with mogrify is not. – Abracadabra

https://mcmap.net/q/236961/-passing-table-name-as-a-parameter-in-psycopg2 This answer below is correct. – Abracadabra

Yes, of course @sage88, I agree. However it still stands that it is possible. A better phrasing would be: "While it's possible to pass in the table name as a parameter, it is NOT recommended to do so due to the possibility of SQL injections". – Whereby

Answer 4

This is a workaround I have used in the past

query = "INSERT INTO %s (col_1, col_2) VALUES (%%s, %%s)" % table_name
cur.execute(query, (col_1_var, col_2_var))

Hope it help :)

Jerome

Answer 5

This is a small addition to @Antoine Dusséaux's answer. If you want to pass two (unquoted) parameters in a SQL query, you can do it as follows: -

query = sql.SQL("select {field} from {table} where {pkey} = %s").format(
    field=sql.Identifier('my_name'),
    table=sql.Identifier('some_table'),
    pkey=sql.Identifier('id'))

As per the documentation,

Usually you should express the template of your query as an SQL instance with {}-style placeholders and use format() to merge the variable parts into them, all of which must be Composable subclasses. You can still have %s-style placeholders in your query and pass values to execute(): such value placeholders will be untouched by format()

Source: https://www.psycopg.org/docs/sql.html#module-usage

Also, please keep this in mind while writing queries.

Tempestuous

Answer 6

I have created a little utility for preprocessing of SQL statements with variable table (...) names:

from string import letters
NAMECHARS = frozenset(set(letters).union('.'))

def replace_names(sql, **kwargs):
    """
    Preprocess an SQL statement: securely replace table ... names
    before handing the result over to the database adapter,
    which will take care of the values.

    There will be no quoting of names, because this would make them
    case sensitive; instead it is ensured that no dangerous chars
    are contained.

    >>> replace_names('SELECT * FROM %(table)s WHERE val=%(val)s;',
    ...               table='fozzie')
    'SELECT * FROM fozzie WHERE val=%(val)s;'
    """
    for v in kwargs.values():
        check_name(v)
    dic = SmartDict(kwargs)
    return sql % dic

def check_name(tablename):
    """
    Check the given name for being syntactically valid,
    and usable without quoting
    """
    if not isinstance(tablename, basestring):
        raise TypeError('%r is not a string' % (tablename,))
    invalid = set(tablename).difference(NAMECHARS)
    if invalid:
        raise ValueError('Invalid chars: %s' % (tuple(invalid),))
    for s in tablename.split('.'):
        if not s:
            raise ValueError('Empty segment in %r' % tablename)

class SmartDict(dict):
    def __getitem__(self, key):
        try:
            return dict.__getitem__(self, key)
        except KeyError:
            check_name(key)
            return key.join(('%(', ')s'))

The SmartDict object returns %(key)s for every unknown key, preserving them for the value handling. The function could check for the absence of any quote characters, since all quoting now should be taken care of ...

Calendar

The check_name function could be extended, of course, e.g. to make it check the tablename against a whitelist. – Calendar

Answer 7

If you want to pass the table name as a parameter, you can use this wrapper:

class Literal(str):
    def __conform__(self, quote):
        return self

    @classmethod
    def mro(cls):
        return (object, )

    def getquoted(self):
        return str(self)

Usage: cursor.execute("CREATE TABLE %s ...", (Literal(name), ))

Chance

I think this does the same as the built in AsIs wrapper. Check this answer (https://mcmap.net/q/236961/-passing-table-name-as-a-parameter-in-psycopg2) – Mccrae

Do you have any documentation on python's __conform__? – Gnostic

@TD1, see peps.python.org/pep-0246. – Chance

Answer 8

You can just use the module format for the table name and then use the regular paramaterization for the execute:

xlist = (column, table)
sql = 'select {0} from {1} where utctime > %s and utctime < %s order by utctime asc;'.format(xlist)

Keep in mind if this is exposed to the end user, you will not be protected from SQL injection unless you write for it.

Howsoever

According to psycopg2 documentation: "Warning: Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint." The new SQL module has been created for this purpose: https://mcmap.net/q/236961/-passing-table-name-as-a-parameter-in-psycopg2 – Queeniequeenly

Answer 9

If you need to pass a qualified identifier, e.g. a schema name + a table name:

from psycopg2 import sql

cur.execute(
    sql.SQL(
        "INSERT INTO {table} VALUES (%s, %s)"
    ).format(
        table=sql.Identifier("my_schema", "my_table")
    ),
    [10, 20]
)

# INSERT INTO "my_schema"."my_table" VALUES (10, 20)

See: https://www.psycopg.org/docs/sql.html#psycopg2.sql.Identifier

Parclose

Answer 10

Surprised no one has mentioned doing this:

sql = 'select {} from {} where utctime > {} and utctime < {} order by utctime asc;'.format(dataItems, voyage, dateRangeLower, dateRangeUpper)
rows = cur.mogrify(sql)

format puts in the string without quotations.

Whereby

While technically as correct as many other answers here, you haven't mentioned SQL injection, which is the main concern. – Mccrae