How to prevent sql injection using Python

Preface

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

Cause

The most common cause of the vulnerability is stringsplicing. Of course, sql injection is not just a case of splicing, there are also things like wide bytes There are many types of injection, special charactersescaping, 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:
    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 a 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, here is a method 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 selectquery statements also uses the simplest string splicing to form a sql statement. It is obvious that the parameter testUrl passed in is controllable. If you want to perform injection testing, you only need to add a single quote 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 echoed, 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(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 does not use string splicing directly, but uses %s to replace the parameters to be passed in. See 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> Encoding and escaping 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. 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 a parameterized sql statement, and the second It is the corresponding actual parameter value. The function will process the incoming parameter value 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 passing in a list, the MySQLdb module will internally serialize the list into a tuple, and then escape operate.

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