带有SQL查询参数的psycopg2 cursor.execute（）会导致语法错误

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

在Python中使用psycopg2中的execute()指定参数时,如下所示:

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.

我究竟做错了什么?看起来psycopg2正在向查询添加单引号,这些单引号导致语法错误。

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

如果我不使用参数,它可以正常工作:

cursor.execute('SELECT * FROM my_table')

1 个解决方案

#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.

我相信像这样的参数化语句意味着与值而不是表名(或SQL关键字等)一起使用。所以你基本上没有运气了。

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.

但是,不要担心,因为此机制旨在防止SQL注入,并且您通常知道在代码编写时要访问哪个表,因此很可能有人可能会注入恶意代码。然后继续在字符串中写表。

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

如果,由于某些(可能是有悖常理的)原因,您将表名称保持参数化:

If the table name comes from your program (e.g. a dictionary, or class attribute), then do the usual string substitution.

如果表名来自您的程序(例如字典或类属性),则执行通常的字符串替换。

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.

如果表名来自外部世界(想想“用户输入”):要么不这样做,要么完全信任用户并应用之前的方法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

#1:此时让第三个%s替换为另一个'%s',以便稍后通过psycopg2#2进行处理:这是psycopg2正确引用并放置而不是第三个'%s'的字符串在原始字符串中

#1