具有SQL查询参数的psycopg2 cursor.execute（）导致语法错误 [英] psycopg2 cursor.execute() with SQL query parameter causes syntax error

问题描述

在Python的psycopg2中指定要执行的参数时，如下所示：

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

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

我收到此错误：

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

我在做什么错？看起来psycopg2在查询中添加了单引号，而这些单引号导致了语法错误。

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

推荐答案

我相信这样的参数化语句应与 values 一起使用，而不是与表名一起使用（或SQL关键字等）。因此，您基本上对此感到不走运。

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.

如果出于某些（可能是错误的）原因，将表名保持参数化，则如下：

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

1. 如果表名来自您的程序（例如字典或类属性），请执行通常的字符串替换。


2. 如果表名来自外部世界（请考虑用户输入）：要么不这样做，要么完全信任用户并应用以前的方法1。

例如：

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

＃1 ：此时将用另一个'％s'替换第三个％s，以允许psycopg2
＃2 进行后续处理：这是正确的字符串由psycopg2引用并放置在原始strin中而不是第三个'％s' g

#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

这篇关于具有SQL查询参数的psycopg2 cursor.execute（）导致语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！