使用psycopg2/Python DB-API和PostgreSQL的参数化查询

Question

如何使psycopg2将参数化查询传递给PostgreSQL？我不想编写自己的转义机制或适配器，而且在Web浏览器中阅读psycopg2源代码和示例很困难。

如果需要切换到PyGreSQL或其他Python PG适配器，那对我来说也没问题。我只是想要简单的参数化。

- jeffcook2150

---

你想要什么样的参数化？提供伪代码示例会很有帮助。 - whatnick

顺便提一下，你可能想了解一下SQLAlchemy，入门成本在某些方面可能会更高一些，但它确实是一个非常好的ORM。 - Bryan McLemore

将来参考，答案在文档的第一页中：http://initd.org/psycopg/docs/usage.html - piro

文档中的示例非常简单。没有展示如何针对动态值执行更复杂的查询，例如更新操作。类似于：set height=5, weight=70。 - Mutai Mwiti

如果你提出一个具体的问题要解决，Stack Overflow 将变得更加有用。例如查询与更新表等问题。由于答案往往比较通用，即使标题吸引了我进入这里，也不能帮助我解决遇到的问题。 - Bill Gale

Answer 1

psycopg2 遵循 PEP-249 中规定的 DB-API 2.0 规则。这意味着您可以从您的 cursor 对象调用 execute 方法并使用 pyformat 绑定样式，它会为您进行转义。例如，以下内容应该是安全的（并且有效）：

cursor.execute("SELECT * FROM students WHERE last_name = %(lname)s", 
               {"lname": "Robert'); DROP TABLE students;--"})

编辑：tekHedd的评论 正确指出SELECT和DROP TABLE使用了不同的表名，所以我已经修复了它。

- Hank Gay

---

不需要，因为查询已经进行了参数化处理。 - Nathan Hinchey

好消息是，由于表格名为“student”，而不是“students”，即使代码不安全，也会失败。对于黑客们的教训是：测试你们的漏洞利用！ - tekHedd

Python的字符串format()方法是一个好的使用方式吗？ - Arsen

当SQL语句中注入了“DROP table”，为什么这个答案会被接受呢？ :) - Andrius

@Andrius，让我介绍你认识一下Bobby Tables。此外，使用参数化语句的整个目的是防止注入攻击（与手动字符串串联不同）。 - Hank Gay

@HankGay 我已经看过了 :) - Andrius

Answer 2

来自 psycopg 文档

(http://initd.org/psycopg/docs/usage.html)

警告：千万不要使用 Python 字符串拼接 (+) 或字符串参数插值 (%) 将变量传递给 SQL 查询字符串，甚至在枪口下也不行。

在 SQL 命令中正确传递变量的方式是使用 execute() 方法的第二个参数：

SQL = "INSERT INTO authors (name) VALUES (%s);" # 注意：不要加引号

data = ("O'Reilly", )

cur.execute(SQL, data) # 注意：不要使用 % 操作符

- Fábio Dias

Answer 3

这里有几个例子可能会对你有所帮助。

cursor.execute('SELECT * from table where id = %(some_id)d', {'some_id': 1234})

或者您可以根据字段名称和值的字典动态构建查询：

query = 'INSERT INTO some_table (%s) VALUES (%s)'
cursor.execute(query, (my_dict.keys(), my_dict.values()))

注意：这些字段必须在您的代码中定义，而不是用户输入，否则您将容易受到SQL注入攻击。

- adam

---

除非你知道自己在做什么，否则不应该将输入内容直接连接到 SQL 查询中，因为这会导致 SQL 注入攻击。 - jb.

由于建议存在 SQL 注入漏洞的风险，您的发言遭到了负评。 - Randy Syring

只有在键没有良好定义和正确识别符的情况下，才可能存在 SQL 注入问题。而值仍然得到了适当的参数化处理。 - Uyghur Lives Matter

换句话说，通常情况下，这会使您容易受到 SQL 注入攻击。 - rspeer

应该使用 psycopg2.extensions.quote_ident：fields = ','.join(quote_ident(key) for key in my_dict)，或者更好的方法是：使用 psycopg2.sql。 - Ali Akbar

Answer 4

我喜欢这个官方文档：

https://www.psycopg.org/psycopg3/docs/basic/params.html

- Szabolcs Szepesi

---

虽然该链接可能回答了问题，但最好在此处包含答案的基本部分并提供参考链接。 如果链接页面发生更改，则仅链接的答案可能会失效。-【来自评论】 - Emi OB