【MySQL 每日一技】使用 Python 操作 MySQL 数据库时应对特殊字符和空值处理
【MySQL 每日一技】使用 Python 操作 MySQL 数据库时应对特殊字符和空值处理
1 问题
你希望使用 Python 1发送一条以字符串形式构造的 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
使用一对双引号引起来2:
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
3格式限定符作为 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)
你可能需要先参考【MySQL 每日一技】使用 Python 连接、选择、断开数据库 ↩︎
这种情况下需确保数据库
ANSI_QUOTES
SQL 模式处于未生效状态。 ↩︎如果要在值中使用
%
字面量,则需要在 SQL 语句中使用%%
。 ↩︎
更多推荐
所有评论(0)