A python approach to preventing SQL injection

SQL injection is one of the most common methods of network attack, it is not to exploit the bugs of the operating system to implement the attack, but to neglect the programmer's programming, to realize the login without account and even tamper with the database through SQL statements. The following article mainly gives you a description of how to prevent SQL injection in Python, the need for friends can refer to.

Objective

Everyone should know that the first web vulnerability is SQL, regardless of the language used for Web backend development, as long as the use of a relational database, you may encounter SQL injection attack problems. So how did SQL injection occur in Python web development, and how to solve this problem?

Of course, I do not want to discuss how other languages to avoid SQL injection, online about PHP (Bo Main note: is said to be the world's most cock language) anti-injection of various methods have, Python method is actually similar, here I say for example.

Cause

The most common cause of the vulnerability is string concatenation, of course, SQL injection is not just stitching a situation, there are many kinds of like wide-byte injection, special character escaping, and so on, here is the most common string splicing, which is the easiest for the novice programmer to make mistakes.

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 will be seen in many scripts before me, and I will write the script for the MySQL database in Python, so is there a problem with this class?
The answer is: Yes!

This class is flawed and can easily cause SQL injection, so let's say why SQL injection is generated.

To verify the authenticity of the problem, write a method to invoke the method in the above class, and throw an exception if an error occurs.

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, but also uses the simplest string concatenation composed of SQL statements, it is obvious that the parameters passed Articleurl controllable, in order to inject test, Only need to add a single quotation mark after the value of Articleurl to the SQL injection test, this is not much to say, there must be an injection vulnerability, script run over, see what results

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

echo error, very familiar errors, here I passed the test parameters are

T.tips '

The following is a case of causing the injection, the above method is slightly modified

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 use string concatenation directly, but instead uses%s instead of the parameters to pass in, does it look like a precompiled SQL? Can this kind of writing prevent SQL injection? Test it and know, echo the following

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

As with the test results above, so this method is not possible, and this method is not precompiled SQL statements, then what can be done to prevent SQL injection?

Solve

Two scenarios

1> code escape for passed in parameters

2> using Python's MySQLdb module's own approach

The first scenario is actually in many PHP anti-injection methods, to escape or filter out special characters.

The second option is to use an internal method, similar to the PDO inside PHP, where you can simply modify the database classes 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 execute executes the time passed two parameters, the first is a parameterized SQL statement, the second is the corresponding actual parameter value, the function will be passed the parameter value of the corresponding processing to prevent SQL injection, the actual use of the 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 SQL injection, and after passing in a list, the MySQLdb module serializes the list into a tuple and then the escape operation.

"Recommended"

1. Python Free video tutorial

2. Python Basics Getting Started tutorial

3. Geek College Python video tutorial