psycopg2 cursor.execute() with SQL query parameter

问题:

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')

回答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