PyMySQL 如何防止用户遭受 sql 注入攻击? [英] How do PyMySQL prevent user from sql injection attack?

问题描述

很抱歉在这里提问，但我找不到很多关于 pymysql 安全指南的参考资料，关于我们如何防止 sql 注入，当我做 PHP 开发时，我知道使用 mysql 准备语句(或称为参数化查询或 stmt)，但我在 pymysql 中找不到关于此的参考

Sorry for ask here but I cannot found much reference about pymysql's security guide about how do we prevent sql injection, When I do PHP develope I know use mysql preparedstatement(or called Parameterized Query or stmt),but I cannot found reference about this in pymysql

简单代码使用pymysql之类的

simple code use pymysql like

sqls="select id from tables where name=%s"
attack="jason' and 1=1"
cursor.execute(sqls,attack)

我怎么知道这会不会阻止sql注入攻击?如果阻止成功，pymysql如何阻止?cursor.execute是否已经默认使用preparedstatement?

How do I know this will prevent sql injection attack or not?if prevent succeed,how do pymysql prevent?Is cursor.execute already use preparedstatement by default?

推荐答案

Python 驱动程序不使用真正的查询参数.在 python 中，在将 SQL 发送到数据库服务器之前，参数(在您的示例中为变量 attack)被插入到 SQL 字符串中.

Python drivers do not use real query parameters. In python, the argument (the variable attack in your example) is interpolated into the SQL string before sending the SQL to the database server.

这与使用查询参数不同.在真正的参数化查询中，SQL 字符串将在参数占位符不变的情况下发送到数据库服务器.

This is not the same as using a query parameter. In a real parameterized query, the SQL string is sent to the database server with the parameter placeholder intact.

但是 Python 驱动程序在插入时确实会正确地转义参数，从而防止 SQL 注入.

But the Python driver does do properly escape the argument as they interpolate, which protects against SQL injection.

打开查询日志就可以证明了:

I can prove it when I turn on the query log:

mysql> SET GLOBAL general_log=ON;

并在我运行 Python 脚本时跟踪日志:

And tail the log while I run the Python script:

$ tail -f /usr/local/var/mysql/bkarwin.log
...
180802  8:50:47    14 Connect   root@localhost on test
           14 Query SET @@session.autocommit = OFF
           14 Query select id from tables where name='jason\' and 1=1'
           14 Quit  

您可以看到查询已将值插入其中，并且嵌入的引号字符前面有一个反斜杠，这可以防止它成为 SQL 注入向量.

You can see that the query has had the value interpolated into it, and the embedded quote character is preceded by a backslash, which prevents it from becoming an SQL injection vector.

我实际上是在测试 MySQL 的连接器/Python，但 pymysql 做同样的事情.

I'm actually testing MySQL's Connector/Python, but pymysql does the same thing.

我不同意 Python 连接器的这个设计决定，以避免使用真实的查询参数(即真实的参数通过将 SQL 查询发送到带有参数占位符的数据库，并分别发送这些参数的值来工作).风险在于程序员会认为将参数插入查询字符串的任何字符串与您让驱动程序执行时的操作相同.

I disagree with this design decision for the Python connectors to avoid using real query parameters (i.e. real parameters work by sending the SQL query to the database with parameter placeholders, and sending the values for those parameters separately). The risk is that programmers will think that any string interpolation of parameters into the query string will work the same as it does when you let the driver do it.

SQL 注入漏洞示例:

Example of SQL injection vulnerability:

attack="jason' and '1'='1"
sqls="select id from tables where name='%s'" % attack
cursor.execute(sqls)

日志显示这导致了 SQL 注入:

The log shows this has resulted in SQL injection:

180802  8:59:30    16 Connect   root@localhost on test
           16 Query SET @@session.autocommit = OFF
           16 Query select id from tables where name='jason' and '1'='1'
           16 Quit  

这篇关于PyMySQL 如何防止用户遭受 sql 注入攻击?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！