UltraBlog.vim

Vim的Wordpress客户端插件 ...

↑我也要推荐

MySQLdb 参数处理的坑

发布时间:2013-10-04 23:08:27, 关注:+6655, 赞美:+16, 不爽:+2

本文标签: mysqldb

原始出处: Xupeng's blog

前几天又有同事掉进了给 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 的代码,一直想当然地以为 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 处理参数的过程简化描述为:

  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 的参数和其他参数一样,是一个整体,就要不要对属于参数一部分的 () 念念不忘了……

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

如果你觉得本站对你有帮助,欢迎向本站赞助 :P

使用支付宝捐赠

Copyright© Python4cn(news, jobs) simple-is-better.com, 技术驱动:powered by web.py 空间主机:Webfaction

版权申明:文章转载已注明出处,如有疑问请来信咨询。本站为 python 语言推广公益网站,与 python 官方没有任何关系。

联系/投搞/留言: en.simple.is.better@gmail.com 向本站捐赠