How to prevent sql injection in Python

Preface

Everyone should know that the number one web vulnerability is now SQL. No matter which language is used for web back-end development, as long as a relational database is used , you may encounter SQL injection attack problems. So how does SQL injection appear during Python web development, and how to solve this problem?

Of course, I don’t want to discuss how other languages ​​avoid sql injection. There are various methods for preventing injection in PHP (blogger’s note: it is said to be the most awesome language in the world) on the Internet, including Python. The methods are actually similar, so I will give you an example here.

Cause

The most common cause of vulnerabilities is string splicing. Of course, sql injection is not just a case of splicing. There are also many types such as wide byte injection, special character escaping, etc. Here we will talk about the most common string splicing, which is also the most common mistake for junior programmers.

First we define a class to handle 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 has been seen in many of my previous scripts, involving Python I will write all the scripts that operate mysql database into this class, so is there any problem with this class?
The answer is: Yes!

This class is defective and can easily cause SQL injection. Let’s talk about why SQL injection occurs.

In order to verify the authenticity of the problem, write a method here to call the method in the above class. If an error occurs, an exception will be 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. One of the most common select query statements also uses the simplest string concatenation to form a sql statement. It is obvious that the incoming The parameter articleurl is controllable. If you want to perform injection testing, you only need to add a single quote after the value of articleurl to perform sql injection testing. Needless to say, there must be an injection vulnerability. Run the script and see 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")

The error message is echoed, a very familiar error. The test parameters I passed in here are

t.tips'

Let’s talk about another situation that leads to 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

There is no direct way in this method Using string concatenation, instead of using %s to replace the parameters to be passed in, does it look very much like precompiled sql? Can this way of writing prevent sql injection? After testing it, you will know that the echo is 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")

is the same as the above test result, so this method is not possible, and this method does not work. It is not precompiled sql statement, so what can be done to prevent sql injection?

Solution

Two solutions

1> Encoding and escaping the input parameters

2> Use the method that comes with Python’s MySQLdb module

The first solution is actually found in many PHP anti-injection methods, and performs special character manipulation on special characters. Escape or filter.

The second option is to use internal methods, similar to PDO in PHP. Here you 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 execute passes in two parameters when executing, the first one is the parameterized sql statement, The second one is the corresponding actual parameter value. The passed-in parameter value will be processed accordingly inside the function to prevent SQL injection. The actual method used is as follows

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

This can prevent sql injection. After passing in a list, the MySQLdb module will serialize the list into a tuple and then perform the escape operation.

For more related articles on methods to prevent sql injection in Python, please pay attention to the PHP Chinese website!