文章目录

• • 1 问题
  • 2. 解决方案
  • 3. 讨论

1 问题

你希望使用 Python ¹发送一条以字符串形式构造的 SQL 插入语句至 MySQL 服务端，但是具体的字段取值可能包含如引号以及反斜杠在内的特殊字符，或者 NULL 等特殊值。又或者，你构造得到的 SQL 语句是通过外部输入构造的，而你希望避免 SQL 注入的发生。

2. 解决方案

针对上述问题，你可以使用 Python 连接 MySQL 驱动的 API 中提供的占位符机制或引用函数（本文着重介绍前者），从而使得数据的插入更加安全。

3. 讨论

针对本文一开始提到的问题，下面先给出一个案例。例如：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De'Mont','1973-01-12','blue','eggroll',4);

上述案例存在的问题是，对于字段 name 的取值 'De'Mont' ，在一对单引号之间存在一个单引号。如果想让上述 SQL 语句有效，可以通过下列任意一个方式实现字符逃逸：

• 在引起问题的单引号之前加上一个单引号：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','blue','eggroll',4);

• 在引起问题的单引号之前加上一个反斜杠：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12','blue','eggroll',4);

• 将 De'Mont 使用一对双引号引起来²：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES("De'Mont",'1973-01-12','blue','eggroll',4);

实际上，如果你是先手动构造一个字符串形式的 SQL 语句，然后将其通过 Python 发送给 MySQL 服务端，那么你可以在构造时就通过类似上述的方式手动进行字符逃逸。然而问题是，如果上述语句中 name 字段的取值存放在变量中，你可能并不知道变量代表的取值究竟是否需要做字符逃逸的处理。

更糟糕的是，数据库还有可能存放例如图片或音频等二进制数据，这些二进制数据可能代表任意字符。更有甚者，插入的取值压根就是空值。

除此之外，在互联网时代，正确处理该问题的需求更加迫切，因为用于构造 SQL 语句的字段取值很多都是来自于用户的表单输入，这不仅会导致可能的取值包含需要特殊处理的字符，同时还可能因有恶意的用户输入而引起 SQL 注入。

因此，你必须能够使用一种通用的方式来处理各种情况下的输入。

另外，虽然 SQL 语句中的 NULL 并非是一个特殊字符，但在使用 Python 将字符串形式的 SQL 语句发送给 MySQL 服务端时，也需要特别注意。在 SQL 语句中， NULL 表示没有值。例如：在上述案例中，如果你不知道 De'Mont 最喜欢的颜色，你就可以将 color 字段设置为 NULL ，但是需要注意的是，下列的 SQL 语句是不正确的：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','NULL','eggroll',4);

下面的 SQL 语句才是正确的：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12',NULL,'eggroll',4);

同样的道理，如果 color 的具体取值来自于一个 Python 中的变量，那么你就需要首先知道该变量代表的值是否为空值，来确定构造 SQL 语句时是否需要在其两端加上引号。

实际上，对于上述处理特殊字符和空值的问题，你可以使用 Python 连接 MySQL 驱动的 API 中提供的占位符机制。具体地，在 SQL 语句中使用占位符，然后将字段取值和占位符进行一一对应绑定。这时， API 会为你完成所有需要做的工作：对特殊字符进行逃逸处理或将特殊的值映射为 NULL 。

Python 连接 MySQL 的 API 使用 %s ³格式限定符作为 SQL 语句中的占位符。具体使用占位符的是通过调用 execute() 方法时传入两个参数来实现的：

• 字符串形式的 SQL 语句，其中包含了格式限定符 %s ；
• 由各字段取值组成的序列，各取值和占位符一一对应。

cursor = conn.cursor()
cursor.execute('''
               INSERT INTO profile (name,birth,color,foods,cats)
               VALUES(%s,%s,%s,%s,%s)
               ''', ("De'Mont", "1973-01-12", None, "eggroll", 4))
cursor.close()
conn.commit()

在上述案例中还可以看出，如果需要表示取值为 NULL ，那么在 Python 中 None 和占位符进行绑定。

最终，由 execute() 方法发送给 MySQL 服务端的 SQL 语句为如下形式：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12',NULL,'eggroll',4)

---

1. 你可能需要先参考【MySQL 每日一技】使用 Python 连接、选择、断开数据库 ↩︎

2. 这种情况下需确保数据库 ANSI_QUOTES SQL 模式处于未生效状态。 ↩︎

3. 如果要在值中使用 % 字面量，则需要在 SQL 语句中使用 %% 。 ↩︎