为什么带有 SQL 查询参数的 psycopg2cursor.execute() 会导致语法错误？

Question

当在Python中的psycopg2中指定execute()的参数时，如下所示：

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

我收到此错误：

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

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

如果我不使用参数，它会正常工作：

cursor.execute('SELECT * FROM my_table')

Answer 1

我相信像这样的参数化语句应该与值一起使用，而不是与表名（或 SQL 关键字等）一起使用。所以你基本上不走运。

不过，不用担心，因为这种机制是为了防止 SQL 注入，而且您通常在编写代码时就知道要访问哪个表，因此有人注入恶意代码的可能性很小。只需继续将表格写入字符串即可。

如果出于某种（可能是反常的）原因，您将表名保持参数化，如下所示：

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 将正确引用并放置的字符串，而不是原始字符串中的第三个“%s”

Answer 2

Psycopg2 中的功能通过 SQL 字符串组合。这种方法提供了一种“以方便且安全的方式动态生成 SQL”的方法。

在原始答案的用例中：

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

使用 SQL 字符串组合是比 Irfy 答案中讨论的通过 % 进行字符串参数插值更安全的方法。正如 psycopg2 文档中所述:

警告永远、永远、永远不要使用 Python 字符串连接 (+) 或字符串参数插值 (%) 将变量传递给 SQL 查询字符串。即使是在枪口下也不行。

Answer 3

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