How to Prevent SQL Injection in Python, and how to prevent pythonsql Injection

How to Prevent SQL Injection in Python, and how to prevent pythonsql Injection

Preface

We should all know that SQL is the first web vulnerability. No matter which language is used for web backend development, as long as a relational database is used, SQL injection attacks may occur. So how does SQL Injection occur during Python web development? How does one solve this problem?

Of course, I do not want to discuss how other languages avoid SQL injection. PHP is the best language in the world) there are various anti-injection methods. The Python method is actually similar. I will give an example here.

Cause

The most common cause of the vulnerability is String concatenation. Of course, SQL injection is not just concatenation, but also many types such as wide-byte injection and special character escaping, here we will talk about the most common String concatenation, which is also the easiest mistake for junior programmers.

First, we define a class to process mysql operations.

class Database: aurl = '127.0.0.1' user = 'root' password = 'root' db = 'testdb' charset = 'utf8' def __init__(self):  self.connection = MySQLdb.connect(self.aurl, self.user, self.password, self.db, charset=self.charset)  self.cursor = self.connection.cursor() def insert(self, query):  try:   self.cursor.execute(query)   self.connection.commit()  except Exception, e:   print e   self.connection.rollback() def query(self, query):  cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)  cursor.execute(query)  return cursor.fetchall() def __del__(self):  self.connection.close()

This code will be seen in many of my previous scripts. I will write all the scripts that involve Python operations on the mysql database into this class. Is there a problem with this class?
The answer is: yes!

This class is flawed and can easily cause SQL injection. The following describes why SQL injection is generated.

To verify the authenticity of the problem, write a method to call the method in the class above. If an error occurs, an exception is thrown directly.

def test_query(articleurl): mysql = Database() try:  querySql = "SELECT * FROM `article` WHERE url='" + articleurl + "'"  chanels = mysql.query(querySql)  return chanels except Exception, e:  print e

This method is very simple. The most common select query statement also uses the simplest String concatenation to form an SQL statement. It is obvious that the input parameter articleurl is controllable. To perform the injection test, you only need to add single quotation marks to the value of articleurl to perform SQL injection testing. In other words, there must be an injection vulnerability. Run the script again and read the results.

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''t.tips''' at line 1")

An error is reported during ECHO, which is very familiar. Here the test parameter I passed in is

t.tips'

Next, let's talk about a situation that causes injection. After slightly modifying the above method

def test_query(articleurl): mysql = Database() try:  querySql = ("SELECT * FROM `article` WHERE url='%s'" % articleurl)  chanels = mysql.query(querySql)  return chanels except Exception, e:  print e

This method does not directly concatenate strings, but uses % s instead of the parameters to be passed in. Does it look like a pre-compiled SQL statement? Can this method prevent SQL injection? After testing, the echo is shown as follows:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''t.tips''' at line 1")

This method is the same as the test result above, so this method is not feasible, and this method is not a pre-compiled SQL statement, so how can we prevent SQL injection?

Solution

Two solutions

1> encode and escape input parameters

2> use the built-in method of MySQLdb module of Python

The first solution is actually available in many PHP anti-injection methods, which escape or filter special characters.

The second solution is to use the internal method, similar to the PDO in PHP. Here we can simply modify the above database class.

Modified code

class Database: aurl = '127.0.0.1' user = 'root' password = 'root' db = 'testdb' charset = 'utf8' def __init__(self):  self.connection = MySQLdb.connect(self.aurl, self.user, self.password, self.db, charset=self.charset)  self.cursor = self.connection.cursor() def insert(self, query, params):  try:   self.cursor.execute(query, params)   self.connection.commit()  except Exception, e:   print e   self.connection.rollback() def query(self, query, params):  cursor = self.connection.cursor(MySQLdb.cursors.DictCursor)  cursor.execute(query, params)  return cursor.fetchall() def __del__(self):  self.connection.close()

Here, two parameters are input during execution of execute. The first is a parameterized SQL statement, and the second is the actual parameter value, the function internally processes the passed parameter values to prevent SQL injection. The actual usage is as follows:

preUpdateSql = "UPDATE `article` SET title=%s,date=%s,mainbody=%s WHERE id=%s"mysql.insert(preUpdateSql, [title, date, content, aid])

This prevents SQL injection. After a list is input, the MySQLdb module serializes the list into a tuples and then performs the escape operation.

Summary

In some of my previous scripts, the Code with the SQL injection vulnerability will be changed slowly. Well, all the above is the content of this article, I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message.