Psycopg2 cursor.execute () with SQL query parameter causes syntax error

Question

When specifying a parameter to execute () in psycopg2 in Python, for example:

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

I get this error:

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

What am I doing wrong? Psycopg2 seems to add single quotes to the request, and these single quotes cause a syntax error.

If I do not use the parameter, it works correctly:

 cursor.execute('SELECT * FROM my_table') 

Answer 1

I believe parameterized statements like this are intended to be used with values, not table names (or SQL keywords, etc.). So you are mostly out of luck.

However, do not worry, because this mechanism is designed to prevent SQL injection, and you usually know which table you want to access while writing code, so there is little chance that someone could enter malicious code. Just go ahead and write a table in a row.

If for some (possibly perverted) reason you keep the table name parametric as follows:

• If the table name comes from your program (for example, a dictionary or a class attribute), do the usual string replacement.
• If the table name comes from the outside world (think "user input"): either do not do this, or completely trust the user, and use the previous approach.

For instance:

 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 by another '% s' at this time to allow later processing by psycopg2 #2 : This is the line that will be correctly quoted by psycopg2 and placed instead of this third "% s" in the original line