Detailed explanation of Python methods to prevent SQL injection

SQL injection is one of the more common network attack methods. It does not use the BUG of the operating system to achieve the attack, but targets the programmer's negligence during programming. Through SQL statements, it can log in without an account or even tamper with the database. The following article mainly introduces to you the methods to prevent SQL injection in Python. Friends in need can refer to it.

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 you use relational database, you may encounter SQL injection attacks. 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 the vulnerability is stringsplicing. Of course, sql injection does not There is only one case of splicing, but there are many other types such as wide byte injection, special characters 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 the operation of mysql

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 operationsmysql database I will write all scripts 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 selectquery statements also uses the simplest string splicing to form a sql statement. It is obvious that the parameter articleurl passed in 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 what the results are.

(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")

Back An error is reported, a very familiar error. The test parameter I passed in here is

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

In this method Instead of using string concatenation directly, %s is used instead of the parameters to be passed in. Doesn’t it look very much like precompiled sql? Can this way of writing prevent sql injection? After a test, you will know that the following echo

(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 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, which 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, two parameters are passed in when execute is executed. The first is the parameterized sql statement, and the second is the corresponding actual parameter value,FunctionThe incoming parameter value will be processed accordingly 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 a list is passed in, inside the MySQLdb module The list will be serialized into a tuple and then escaped.

【Related recommendations】

1. Python free video tutorial

2. Python basic introductory tutorial

3. Geek Academy Python Video Tutorial

The above is the detailed content of Detailed explanation of Python methods to prevent SQL injection. For more information, please follow other related articles on the PHP Chinese website!