How do PyMySQL prevent user from sql injection attack?

Question

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

simple code use pymysql like

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

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?

Answer 1

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.

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.

But the Python driver does properly escape the argument as it interpolates, which protects against SQL injection.

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

mysql> SET GLOBAL general_log=ON;

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  

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.

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

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.

Example of SQL injection vulnerability:

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

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