MySQLdb 参数处理的坑

前几天又有同事掉进了给 SQL 的 IN 条件传参的坑，就像 SELECT col1, col2 FROM table1 WHERE id IN (1, 2, 3) 这类 SQL，如果是一个可变的列表作为 IN 的参数，那这个参数应该怎么传呢？

我见过至少这么几种：

id_list = [1, 2, 3]
cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN (%s)', id_list)

这种方式是语法错误的，原因是 MySQLdb 做字符串格式化时占位符和参数个数不匹配。

id_list = [1, 2, 3]
cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN (%s)', (id_list,))

这种方式语法是正确的，但语义是错误的，因为生成的 SQL 是 SELECT col1, col2 FROM table1 WHERE id IN ((‘1’, ‘2’, ‘3’))

id_list = [1, 2, 3]
id_list = ','.join([str(i) for i in id_list])
cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN (%s)', id_list)

这种方式语义也是错误的，因为生成的 SQL 是 SELECT col1, col2 FROM table1 WHERE id IN (‘1,2,3’)

这三种是第一次使用 MySQLdb 给 IN 传参时犯的最多的错误，大多数人遇到第一种错和掉进后两个坑之后，转而采用了下面的方式：

id_list = [1, 2, 3]
id_list = ','.join([str(i) for i in id_list])
cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN (%s)' % id_list)

这个方式对于可信的参数(比如自己生成的列表：range(1, 10, 2))来说可以用，但由于参数未经 escape，对于从用户端接受的不可信参数来说，存在 SQL 注入的风险。

严防 SQL 注入的问题时刻都不能松懈，于是就有了这样的改进版本：


id_list = [1, 2, 3]
id_list = ','.join([str(cursor.connection.literal(i)) for i in id_list])
cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN (%s)' % id_list)

这个方式控制了 SQL 注入问题的滋生，但由于 cursor.connection.literal 是内部接口，并不推荐从外部使用。

然后就有了这样的方式：

id_list = [1, 2, 3]
arg_list = ','.join(['%s'] * len(id_list))
cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN (%s)' % arg_list, id_list)

这个方式是先生成与参数个数相同的 %s 占位，拼出 ‘SELECT col1, col2 FROM table1 WHERE id IN (%s,%s,%s)’ 这样的 SQL，然后使用安全的方式来传参。

就是想传一个参数而已，怎么会这么麻烦呢？触令丧惨！

更正：以下划线内容为未经充分测试的错误结论，仅做记录：

一直以为 MySQLdb 是不支持给 IN 传参的，直到这次又有同事掉坑我才读了 MySQLdb escape 部分的代码，然后发现，MySQLdb 是在很多类型的 Python object 和 SQL 支持的类型之间做自动转换的，比如 MySQLdb 会对 list 和 tuple 内的元素逐个进行 escape，生成一个 tuple，因此这才是正确的给 IN 传参的方式： ``` id_list = [1, 2, 3] cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN %s', (id_list,)) ```
可以把 MySQLdb 处理参数的过程简化描述为：

对参数 (id_list,) 做 escape 得到 ((‘1’, ‘2’, ‘3’),)

用 escape 过的参数对 SQL 进行格式化：‘SELECT col1, col2 FROM table1 WHERE id IN %s’ % ((‘1’, ‘2’, ‘3’),)，得到完整 SQL：‘SELECT col1, col2 FROM table1 WHERE id IN (‘1’, ‘2’, ‘3’)

~~整理一下口诀：IN 的参数和其他参数一样，是一个整体，就要不要对属于参数一部分的 () 念念不忘了……~~

总结一下评论中对这个方法提出的问题：

如果参数列表只有一个元素，比如 cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN %s', ([1],))，生成的 SQL 是 SELECT col1, col2 FROM table1 WHERE id IN ('1',)，是语法错误的
对列表内元素做 esacpe 时增加的引号会被留下，如果列表元素是字符串，结果会是错误的，比如 cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN %s', (["1", "2"],)) 生成的 SQL 是 SELECT col1, col2 FROM table1 WHERE id IN ("'1'", "'2'")，而对于数字参数恰好能正确工作的原因是，在执行 SQL 时如果列定义是 int 而传参为字符串，MySQL 会做隐式类型转换（ Type Conversion in Expression Evaluation）。

MySQLdb 支持对各种类型的 Python object 进行转换和 escape，感兴趣的同学可以看看 MySQLdb.converters 和 _mysql.c 中 *_escape* 系列的函数，另外 MySQLdb 也支持自定义转换规则，参见 MySQLdb.connect 的 conv 参数。