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

Question

如何让psycopg2把参数化查询传递给PostgreSQL是最好的方法？我不想自己写转义机制或适配器，而且psycopg2的源代码和示例在浏览器里看起来很难懂。

如果我需要换成像PyGreSQL或者其他的Python PostgreSQL适配器，我也没问题。我只想要简单的参数化功能。

Answer 1

这里有一些你可能会觉得有用的例子

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注入攻击。

Answer 2

来自psycopg的文档

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

警告：绝对不要、绝对不要、绝对不要使用Python的字符串拼接（+）或者字符串参数插入（%）来把变量传给SQL查询字符串。就算是被逼也不行。

正确的做法是使用execute()方法的第二个参数来传递变量：

SQL = "INSERT INTO authors (name) VALUES (%s);" # 注意：这里没有引号

data = ("O'Reilly", )

cur.execute(SQL, data) # 注意：这里没有%运算符

Answer 3

psycopg2 遵循了数据库API 2.0的规则，这些规则在 PEP-249 中有详细说明。这意味着你可以从你的 cursor 对象调用 execute 方法，并使用 pyformat 这种绑定方式，它会自动处理一些安全问题。例如，下面的代码 应该 是安全的（并且可以正常工作）：

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

补充说明： tekHedd的评论 正确指出 SELECT 和 DROP TABLE 使用了不同的表名，所以我进行了修正。