如何在 Python 中的 SQL 语句中使用变量？

问题描述：

我有以下 Python 代码：

cursor.execute("INSERT INTO table VALUES var1, var2, var3,")

其中var1是整数，var2和var3是字符串。

我怎样才能写出变量名称而不让 Python 将它们作为查询文本的一部分？

解决方案 1：

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

请注意，参数以元组形式传递。(a, b, c)如果传递单个参数，则元组需要以逗号结尾(a,)。

数据库 API 会对变量进行正确的转义和引用。请注意不要使用字符串格式化运算符 ( %)，因为

1. 它不进行任何转义或引用。

2. 它容易受到不受控制的字符串格式攻击，例如SQL 注入。

解决方案 2：

Python DB-API 的不同实现可以使用不同的占位符，因此您需要找出您正在使用哪一个 - 它可能是（例如使用 MySQLdb）：

cursor.execute("INSERT INTO table VALUES (%s, %s, %s)", (var1, var2, var3))

或者（例如使用 Python 标准库中的 sqlite3）：

cursor.execute("INSERT INTO table VALUES (?, ?, ?)", (var1, var2, var3))

或者其他（在VALUES您拥有(:1, :2, :3)、 或“命名样式”(:fee, :fie, :fo)或(%(fee)s, %(fie)s, %(fo)s)将 dict 而不是 map 作为 的第二个参数传递给之后execute）。检查您正在使用的 DB API 模块中的字符串常量，并在http://www.python.org/dev/peps/pep-0249/paramstyle上查找 paramstyle以查看所有参数传递样式！

解决方案 3：

方法有很多。在实际代码中不要使用最明显的方法 ( %sw​​ith %)，它容易受到攻击。

这里从 sqlite3 的 pydoc复制粘贴：

... 小心使用 Python 的字符串操作来组装查询，因为它们容易受到SQL 注入攻击。例如，攻击者可以简单地关闭单引号并注入 OR TRUE 来选择所有行：

# Never do this -- insecure!
symbol = input()

sql = "SELECT * FROM stocks WHERE symbol = '%s'" % symbol
print(sql)

cur.execute(sql)

如果你需要更多示例：

# Multiple values single statement/execution
c.execute('SELECT * FROM stocks WHERE symbol=? OR symbol=?', ('RHAT', 'MSO'))
print c.fetchall()
c.execute('SELECT * FROM stocks WHERE symbol IN (?, ?)', ('RHAT', 'MSO'))
print c.fetchall()
# This also works, though ones above are better as a habit as it's inline with syntax of executemany().. but your choice.
c.execute('SELECT * FROM stocks WHERE symbol=? OR symbol=?', 'RHAT', 'MSO')
print c.fetchall()
# Insert a single item
c.execute('INSERT INTO stocks VALUES (?,?,?,?,?)', ('2006-03-28', 'BUY', 'IBM', 1000, 45.00))

解决方案 4：

http://www.amk.ca/python/writing/DB-API.html

当您简单地将变量值附加到语句中时，请务必小心：想象一下用户命名自己';DROP TABLE Users;'——这就是为什么您需要使用 SQL 转义的原因，当您cursor.execute以得体的方式使用时，Python 会为您提供此功能。URL 中的示例为：

cursor.execute("insert into Attendees values (?, ?, ?)", (name, seminar, paid))

解决方案 5：

对于缺乏经验的 Python 用户来说，提供单一值的语法可能会令人困惑。

给定查询

INSERT INTO mytable (fruit) VALUES (%s)

一般来说*，传递给的值cursor.execute必须包装在有序序列中，例如元组或列表，即使值本身是单例，所以我们必须提供一个单元素元组，如下所示：(value,)。

cursor.execute("""INSERT INTO mytable (fruit) VALUES (%s)""", ('apple',))

传递单个字符串

cursor.execute("""INSERT INTO mytable (fruit) VALUES (%s)""", ('apple'))

将导致错误，具体错误因 DB-API 连接器而异，例如

• psycopg2：

TypeError：字符串格式化期间并非所有参数都已转换

• sqlite3

sqlite3.ProgrammingError: 提供的绑定数量不正确。当前语句使用了 1 个，但提供了 5 个

• mysql.连接器

mysql.connector.errors.ProgrammingError: 1064 (42000): 您的 SQL 语法有错误；

• pymysql 连接器处理单个字符串参数时不会出错。但是，最好将字符串包装在元组中，即使它是一个单个的，因为

• 如果你更换连接器包，则无需更改代码

• 你保持一致的心理模型，即查询参数是一系列对象而不是单个对象。

解决方案 6：

这是一个非常老的问题，但这里还有更多细节。

不同的 DBMS 驱动程序实现不同的参数样式。您可以使用以下代码片段找出具体哪一种：

import sqlite3
print(sqlite3.paramstyle)           #   'qmark'
import pyodbc
print(pyodbc.paramstyle)            #   'qmark'
import mysql.connector
print(mysql.connector.paramstyle)   #   'pyformat'
import psycopg2
print(psycopg2.paramstyle)          #   'pyformat'
import oracledb
print(oracledb.paramstyle)          #   'named'

我已将我自己的测试结果纳入其中。

您可以从以下位置获取参数样式列表：

https://peps.python.org/pep-0249/#paramstyle

以下是摘录：

之后，可以创建两个变量，一个用于 SQL，一个用于参数：

sql = 'INSERT INTO table VALUES ?,?,?'
data = (var1, var2, var3)
cursor.execute(sql, data)

这里，我使用了qmark样式。这data是一个值的元组，但它本来可以是一个列表。重点是，即使只有一个值，您也需要创建一个元组或列表。

如果您的 DBMS 使用pyformat或named样式，则可以使用如下内容：

sql = 'INSERT INTO table VALUES %(first)s,%(second)s,%(third)s' #   pyformat
sql = 'INSERT INTO table VALUES :first,:second,:third'          #   named
data = {'first': var1, 'second': var2, 'third': var3}
cursor.execute(sql, data)

这里您使用一个字典，其中的键与 SQL 中使用的任意名称匹配。