Method to prevent sql injection in Python

preface

As you probably know, sql is now the most common vulnerability in web. No matter what language you use for web back-end development, as long as you use a relational database, you may encounter the problem of sql injection attack. So how did sql injection occur during the development of Python web, and how did it solve this problem?

Of course, I don't want to talk about how other languages avoid sql injection here. There are various ways to prevent PHP injection on the Internet.

The cause of

The most common cause of vulnerability is string concatenation, of course, sql injection is not only concatenation 1 case, there are also like wide byte injection, special character escape and so on many kinds, here is the most common string concatenation, this is also the most common mistake for junior programmers.

First, let's 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()

I've seen this code in a lot of scripts that I've seen before, scripts that involve Python operating on mysql database that I've written into this class, so is there a problem with this class?
The answer is: yes!

This class is defective and can easily cause an sql injection, so here's why you can get an sql injection.

To verify the authenticity of the problem, write a method that calls the method in the class above, and throw an exception if there is an error.

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, also use the most simple string concatenation of sql statements, obviously the incoming articleurl controllable parameters, to inject test, only need in articleurl values followed by single quotes can be sql injection test, the not much said, there must be injected, the vulnerability of the scripts to run 1 time, look at what 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")

Echo error, very familiar error, the test parameter I passed in here is

t.tips'

Here is one more case that leads to injection, with a slight modification of 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

Instead of using string concatenation directly, this method USES %s instead of the parameter to be passed in. Does it look very much like the precompiled sql? Does this prevent sql injection? As you can see from test 1, echo is shown below

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

As shown in test result 1 above, this method does not work, and this method does not precompile sql statements, so what can be done to prevent the injection of sql?

To solve

Two kinds of schemes

1 > The passed parameter is encoded escape

2 > Use the method that comes with the MySQLdb module of Python

The first is actually found in many of PHP's anti-injection methods, where special characters are escaped or filtered.

The second option is to use an internal method, similar to PDO in PHP, where you can simply modify the database class above.

The 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, when execute is executed, two parameters are passed in, the first is the parameterized sql statement, and the second is the corresponding actual parameter value. The function will handle the parameter value passed in internally to prevent the injection of sql. The actual method 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 the injection of sql. Once a list is passed in, the MySQLdb module internally serializes the list into a tuple and then performs the escape operation.

conclusion

The code with sql injection vulnerability used in some of my previous scripts will be gradually corrected. Well, the above is the whole content of this article. I hope the content of this article can bring you some help in your study or work.