Python中使用%s占位符生成sql与literal转义防止sql注入攻击原理浅析
问题背景
在后端服务中经常需要通过传入参数动态生成sql查询mysql，如查询用户信息、资产信息等，一条常见的sql如下：
SELECT vip, coin FROM user_asset WHERE uid='u123456'
该条sql查询用户"u123456"的的vip身份与游戏币数量，其中具体的uid取值就应该是传入的动态参数，不同用户生成的对应sql自然是不同的。
python中拼接动态sql的多种方式
在python中,对于这条动态sql的拼接至少存在以下四种方案
- %s占位符形式
sql = "SELECT vip, coin FROM user_asset WHERE uid='%s' " % uid
cursor.execute(sql)
- format形式
sql = "SELECT vip, coin FROM user_asset WHERE uid='{}' ".format(uid)
cursor.execute(sql)
- f string形式
sql = f"SELECT vip, coin FROM user_asset WHERE uid='{uid}' "
cursor.execute(sql)
- MySQLdb定义的 %s占位符形式
sql = "SELECT vip, coin FROM user_asset WHERE uid=%s "
cursor.execute(sql, (uid, ))
其中1,2,3三种方式均是通过python本身的占位符语法先动态生成完整sql，而后直接提交到db执行，我们将其归为第一类，后面均以第1种方式作为代表进行分析，第4种方法则归为第二类。
存在sql注入风险的第一类方法
第一类方法其实十分危险，是需要我们极力避免的错误方式，因为它存在确切的sql注入风险。具体分析来看，uid作为一个字符串类型，要想生成sql中带引号的参数，需要额外再在占位符两侧添加引号才行，否则将生成错误的sql，如下例:
In [4]: "SELECT vip, coin FROM user_asset WHERE uid='%s' " % uid
Out[4]: "SELECT vip, coin FROM user_asset WHERE uid='u123456' " # 加引号输出为合法sql
In [5]: "SELECT vip, coin FROM user_asset WHERE uid=%s " % uid
Out[5]: 'SELECT vip, coin FROM user_asset WHERE uid=u123456 # 不加引号输出为非法sql
问题在于uid的来源并不一定是可信的，如果uid参数是由客户端直接传过来、或者其他不可信的恶意来源传递，服务端直接取用该参数拼接sql的话，就可能直接被sql注入攻击，比如客户端传递恶意的uid本身带有引号的情况，则可以生成包括以下sql在内的各种恶意sql:
In [46]: uid="' or 1 or ''='"
In [47]: "SELECT vip, coin FROM user_asset WHERE uid='%s' " % uid
Out[47]: "SELECT vip, coin FROM user_asset WHERE uid='' or vip or '___'='' " # 匹配所有VIP
In [48]: uid="' or coin>100 or '___'='"
In [49]: "SELECT vip, coin FROM user_asset WHERE uid='%s' " % uid
Out[49]: "SELECT vip, coin FROM user_asset WHERE uid='' or coin>100 or '___'='' " # 匹配所有游戏币>100的用户
In [62]: uid = "'; delete FROM test_user_asset WHERE ''='"
In [63]: "SELECT vip, coin FROM user_asset WHERE uid='%s' " % uid
Out[63]: "SELECT vip, coin FROM user_asset WHERE uid=''; delete FROM test_user_asset WHERE ''='' " # 极端恶意！删除全表记录
由此可见，通过使用python占位符直接拼装sql执行，是十分危险的行为。
防止注入的安全方式
事实上，在各类语言中拼装sql的标准写法应该都是采用第4种方式，即传入包含占位符的sql与参数列表，由库内部处理最终sql的拼装，其内部会对参数进行保护性转义之后再拼入sql之中。
那MySQLdb内部具体是如何处理参数转义拼接的呢？有没有办法可以得到最终拼装完成的sql在日志中输出方便调试呢？
cursor.execute内部的参数转义机制
先看第一个问题，通过查看源码可以在MySQLdb的cursors.py 中找到execute函数定义，其中有如下代码：
def execute(self, query, args=None):
"""Execute a query.
query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.
Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.
Returns integer represents rows affected, if any
"""
while self.nextset():
pass
db = self._get_db()
if isinstance(query, unicode):
query = query.encode(db.encoding)
if args is not None:
if isinstance(args, dict):
nargs = {}
for key, item in args.items():
if isinstance(key, unicode):
key = key.encode(db.encoding)
nargs[key] = db.literal(item)
args = nargs
else:
args = tuple(map(db.literal, args))
try:
query = query % args
except TypeError as m:
raise ProgrammingError(str(m))
assert isinstance(query, (bytes, bytearray))
res = self._query(query)
return res
可以看到，如果传入args为tuple，则将通过args = tuple(map(db.literal, args))将其每个参数通过db.literal进行转义，最终还是通过 query = query % args 生成字符串，由于所有参数都已经经过转义了，所以能避免之前的注入问题。
那么能不能得到execute内部最终生成的这个query sql呢，很遗憾我们发现query是个函数内的局部变量，所以外部是无法直接获取其值的。当然如果一定要获取最终生成的sql也不是没办法，可以在代码中模拟这一literal操作拼接sql，而后输出。
接下来探究一下db.literal是个什么函数，外部能否直接调用它。
Connection.literal函数
经过一通查找，发现literal函数定义在connections.py文件中：
def literal(self, o):
"""If o is a single object, returns an SQL literal as a string.
If o is a non-string sequence, the items of the sequence are
converted and returned as a sequence.
Non-standard. For internal use; do not use this in your
applications.
"""
if isinstance(o, unicode):
s = self.string_literal(o.encode(self.encoding))
elif isinstance(o, bytearray):
s = self._bytes_literal(o)
elif isinstance(o, bytes):
if PY2:
s = self.string_literal(o)
else:
s = self._bytes_literal(o)
elif isinstance(o, (tuple, list)):
s = self._tuple_literal(o)
else:
s = self.escape(o, self.encoders)
if isinstance(s, unicode):
s = s.encode(self.encoding)
assert isinstance(s, bytes)
return s
可以看到，db.literal其实就是根据传入参数的类型，再调用不同类型的literal方法对其进行转义，而且db.literal本身是个实例方法，这意味着至少需要一个Connection 实例才可以引用到这一个方法。
使用literal生成防sql注入的最终sql
通过初始化一个Connection示例，便可以调用其literal方式进行参数转义了，以下示例代码演示了通过literal对参数转义生成最终防注入风险的安全sql：
#!/usr/bin/python3
import MySQLdb
conn = MySQLdb.connect(host="127.0.0.1", port=3306, user="test", password="test123", db="test")
curosr = conn.cursor()
sql0 = "SELECT vip, coin FROM user_asset WHERE uid='%s' " # str类型直接占位替换需要加上引号
sql1 = "SELECT vip, coin FROM user_asset WHERE uid=%s " # 占位符%s会通过库内部literal处理转义, 直接使用即可
uid = "u123456"
print('\nuid=%s' % uid)
args = (uid, )
print("0:", sql0 % args) # 直接占位符替换
print("1:", (sql1.encode() % tuple(map(conn.literal, args))).decode()) # 通过literal处理后占位符替换, 生成为bytes类型, decode为str类型后输出
uid = "' or 1 or ''='"
print('\nuid=%s' % uid)
args = (uid, )
print("0:", sql0 % args) # 直接占位符替换
print("1:", (sql1.encode() % tuple(map(conn.literal, args))).decode()) # 通过literal处理后占位符替换, 生成为bytes类型, decode为str类型后输出
uid = "'; delete FROM test_user_asset WHERE ''='"
print('\nuid=%s' % uid)
args = (uid, )
print("0:", sql0 % args) # 直接占位符替换
print("1:", (sql1.encode() % tuple(map(conn.literal, args))).decode()) # 通过literal处理后占位符替换, 生成为bytes类型, decode为str类型后输出
输出结果：
uid=u123456
0: SELECT vip, coin FROM user_asset WHERE uid='u123456'
1: SELECT vip, coin FROM user_asset WHERE uid='u123456'
uid=' or 1 or ''='
0: SELECT vip, coin FROM user_asset WHERE uid='' or 1 or ''=''
1: SELECT vip, coin FROM user_asset WHERE uid='\' or 1 or \'\'=\''
uid='; delete FROM test_user_asset WHERE ''='
0: SELECT vip, coin FROM user_asset WHERE uid=''; delete FROM test_user_asset WHERE ''=''
1: SELECT vip, coin FROM user_asset WHERE uid='\'; delete FROM test_user_asset WHERE \'\'=\''
可以看到，uid内部添加的单引号'都会被'转义后才拼入sql之中。
需要注意的是，Connection.literal函数注释已明确说明该函数是Non-standard. For internal use; do not use this in your applications.，所以该函数的直接调用应仅限于调试用途，不可用于线上业务逻辑，同时由于必须现在实例化一个Connection对象才可调用其literal方法，要注意连接的正常关闭，防止泄漏。
转载请注明出处，原文地址: https://www.cnblogs.com/AcAc-t/p/python_sql_placeholder_prevent_injection.html
