pyMysql 的参数化查询是如何实现的？原理是什么？

[black11black]

如题，mysql 参数化查询用得很多了，早年就听说过 sql 注入，所以从来没自己拼接过命令串

但是最近想到一个问题是它是如何实现的，真正发送给 mysql 的命令是什么。百度谷歌搜了半天没啥有效信息。

拼接用起来比如这么用

cursor.execute('select * from user where user=%s and password=%s'%(name,password))

一个反应的想法是，是不是用 set 实现的，比如发送给 mysql 的命令为

set @name = '这里是用户输入的 name';
select * from users where name=@name;

但是这种命令也防止不了注入吧。set 的环节比如输入'or 1=1#这个字符串一样发生了转义，只不过送进去的是空字符串罢了。。

基础不牢，mysql 原理不太懂，到底咋回事？

经楼下带哥的确认，防注入主要是两层过滤，一是特殊字符转义，二是预编译，都是防止特殊字符进入解释的手段。

我之前一个怀疑是因为人为手写转义过滤总也许会产生某些不易察觉的漏洞，也许会有安全隐患。带哥确认了一下使用预编译的话 set 即使出现过滤疏忽也并不会导致特殊字符被解释，所以转义+prepare 可以说是 100%完全防护的。

单纯使用转义过滤后拼接字符串提交 query，比如楼下带哥提到的 pymysql 的实现，根据 stackoverflow 的带哥回答也是基本上没有任何问题。

至于为什么这些库的作者不使用预编译而是传递拼接 sql 语句，也许是认为没必要？或者是要节省网络传输时间损失？

[black11black]

或者比如使用预编译命令，`prepare sel from 'select * from users where name=%s';`这样
调用的时候还是需要先用 set 设置变量，这个步骤会被转义替换啊

[monsterxx03]

https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/cursors.py#L161 mogrify 这个函数你跟下去看就知道了, 其实就是发送前把参数做了个本地 escape, 发出去的是一条拼好的 sql.

prepare 不是简单的字符串替换, 可以理解成在 prepare 阶段, sql 被解析成了 MySQL 内部类似 AST 那样的语法树结构,它知道你占位的地方是 input, 最后执行的时候, 不管输入的是什么, 都会被当成 value 去匹配, 而不是和前面的语句拼起来.

[black11black]

@monsterxx03

感谢回复，请问这里的 escape 翻译成中文应该是什么，是指做了什么操作？

我的意思是如果使用 prepare 的话（不论 python 还是其他语言的库），似乎都要经过三个步骤，1、prepare 2、set 变量 3、调用 stmt，输入变量。我的疑问是如果这么做的话似乎在 set 的步骤同样会被转义（如果不进行字符串过滤的情况下），比如将文本内容替换成' or 1=1#之类的，虽然看起来这步替换似乎不会产生实际的危害，但是能保证吗？

还有一个问题就是使用 sqlalchemy 之类的 ORM 的时候，看原理似乎最终也是拼接成一整句 sql，是不是也同样无法原理上防范注入。具体防御力如何还全看转义字符串过滤？

[black11black]

stackoverflow 里看到一条对这个问题的解答

https://stackoverflow.com/questions/6501583/sqlalchemy-sql-injection

If you have any "special" characters (such as semicolons or apostrophes) in your data, they will be automatically quoted for you by the SQLEngine object, so you don't have to worry about quoting. This also means that unless you deliberately bypass SQLAlchemy's quoting mechanisms, SQL-injection attacks are basically impossible.

[per http://www.rmunn.com/sqlalchemy-tutorial/tutorial.html]

[monsterxx03]

escape 就是转义, 如果参数是 string, 具体到 pymysql 里就做了这个: https://github.com/PyMySQL/PyMySQL/blob/master/pymysql/converters.py#L72

我没看过 sqlalchemy 的实现, 但应该一样在 client 端转义的.

prepare 语句是可以防 sql 注入的: https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

这个和 set 转不转义没啥关系. 额, 这么想, 本来输入是一条 select, 通过注入拼接可能变成了一条 delete 语句, 但假如用的是 prepare, 提供给 prepare 的 sql 是你写的, 不会有用户输入, prepare 完它就是一条 select 语句, 后续你传入的 value 玩出花来 mysql 也只是把它当成一个文本参数, 不会再次执行 sql 的编译过程.

[msg7086]

如果是拼接的话可以通过转义解决。
Prepare 不是拼接是预编译。比如你用 prepare 连续插入了 5 行数据，那实际上你是发送了一个 SQL 语句和 5 个数据组，而不是 5 个 SQL 语句。