Introduction to Python's method to prevent sql injection

Preface

The number one web vulnerability is none other than SQL. No matter which language is used for web back-end development, as long as a relational database is used, SQL injection attacks may be encountered. 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 on the Internet. Python’s method is actually similar. Here I will give an example.

Cause

The most common cause of vulnerabilities is string splicing. Of course, sql injection is not just a case of splicing, but also wide byte injection and special character escaping. There are many kinds, but here we will talk about the most common string concatenation, which is also the most common mistake for junior programmers.

First we define a class to handle mysql operations

class Database:
    hostname = '127.0.0.1'
    user = 'root'
    password = 'root'
    db = 'pythontab'
    charset = 'utf8'
    def __init__(self):
        self.connection = MySQLdb.connect(self.hostname, 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()

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(testUrl):
    mysql = Database()
    try:
        querySql = "SELECT * FROM `article` WHERE url='" + testUrl + "'"
        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 parameter testUrl passed in is controllable. If you want to perform injection testing, just You need to add single quotes after the value of testUrl to perform sql injection testing. Needless to say, there must be an injection vulnerability. Run the script and see what the result is.

(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 displayed, 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(testUrl):
    mysql = Database()
    try:
        querySql = ("SELECT * FROM `article` WHERE url='%s'" % testUrl)
        chanels = mysql.query(querySql)
        return chanels
    except Exception, e:
        print e

this method It does not use string splicing directly, but uses %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? You will know after testing it. The response 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 is not precompiled sql statement, so what can be done to prevent sql injection?

Solution

Two solutions

1> Encode and escape the incoming 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 is to escape or filter special characters.

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:
    hostname = '127.0.0.1'
    user = 'root'
    password = 'root'
    db = 'pythontab'
    charset = 'utf8'
    def __init__(self):
        self.connection = MySQLdb.connect(self.hostname, 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, function The incoming parameter value will be processed internally 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.

The above is the detailed content of Introduction to Python's method to prevent sql injection. For more information, please follow other related articles on the PHP Chinese website!