Why does psycopg2 cursor.execute() with SQL query parameter cause syntax error?

Question

When specifying a parameter to execute() in psycopg2 in Python, like this:

cursor.execute('SELECT * FROM %s', ("my_table", ))

I'm getting this error:

psycopg2.ProgrammingError: syntax error at or near "'my_table'"
LINE 1: SELECT * FROM 'my_table'

What am I doing wrong? It looks like psycopg2 is adding single quotes to the query, and those single quotes are causing the syntax error.

If I don't use a parameter, it works correctly:

cursor.execute('SELECT * FROM my_table')

Defamation

Answer 1

I believe that parametrized statements like this are meant to be used with values and not table names (or SQL keywords, etc.). So you're basically out of luck with this.

However, do not worry, as this mechanism is meant to prevent SQL injection, and you normally know what table you want to access at code-writing time, so there is little chance somebody may inject malicious code. Just go ahead and write the table in the string.

If, for some (possibly perverse) reason you keep the table name parametric like that:

1. If the table name comes from your program (e.g. a dictionary, or class attribute), then do the usual string substitution.
2. If the table name comes from the external world (think "user input"): either don't do that, or trust the user completely and apply the previous approach 1.

For example:

cursor.execute(
    'SELECT * FROM %s where %s = %s'
    % ("my_table", "colum_name", "%s"), #1
    ("'some;perverse'string;--drop table foobar")) #2

#1: Let the third %s be replaced with another '%s' at this time, to allow later processing by psycopg2 #2: This is the string that will be properly quoted by psycopg2 and placed instead of that third '%s' in the original string

Grodin

Very nice detailed answer, just what I needed. Thanks! – Defamation

Answer 2

There is functionality within Psycopg2 that supports this use case via SQL String Composition. This approach provides a way to "generate SQL dynamically, in a convenient and safe way."

In the use case in the original answer:

cursor.execute(
    sql.SQL("SELECT * FROM {}").format(
        sql.Identifier("my_table")))

Using SQL String Composition is a much safer approach than string parameter interpolation via % discussed in Irfy's answer. As noted in the 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.

Alameda

Answer 3

cur.execute(
    """
    SELECT * FROM %s;
    """,
    {"my_table",}
)
conn.commit()

Papillose