Xupeng's blog

圆外之大,心向往之

MySQLdb 参数处理的坑

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

我见过至少这么几种:

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

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

1
2
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’))

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 传参时犯的最多的错误,大多数人遇到第一种错和掉进后两个坑之后,转而采用了下面的方式:

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)

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

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

1
2
3
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 是内部接口,并不推荐从外部使用。

然后就有了这样的方式:

1
2
3
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 传参的方式:

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

可以把 MySQLdb 处理参数的过程简化描述为:

  1. 对参数 (id_list,) 做 escape 得到 ((‘1’, ‘2’, ‘3’),)
  2. 用 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 的参数和其他参数一样,是一个整体,就要不要对属于参数一部分的 () 念念不忘了……

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

  1. 如果参数列表只有一个元素,比如 cursor.execute('SELECT col1, col2 FROM table1 WHERE id IN %s', ([1],)),生成的 SQL 是 SELECT col1, col2 FROM table1 WHERE id IN ('1',),是语法错误的
  2. 对列表内元素做 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.connectconv 参数。

Comments