How to write raw SQL for anti-SQL injection using the SQLAlchemy library

The Python camp has many open source libraries that operate the database (after installing PIP, you can see the list of available libraries with "Pip search MySQL"), where the most used is undoubtedly mysqldb, a library that is easy to get started with. The low-level features provide flexibility for developers, as well as a test of the DB operation code written by many novices, because it only supports raw SQL, which is prone to SQL injection attacks.

In view of this, many libraries provide ORM interface capabilities, with Oo thought, the tables in the database are mapped to Python classes, the objects of the class represent a row of records in the data table, all the DB operations are implemented through object method calls, which are automatically converted to SQL statements at the bottom, and during the conversion process, Typically, parameter bind is used to ensure that the generated parameterized SQL does not present a risk of being injected .

SQLAlchemy is such an ORM-capable DB Operation Python Library, which also enables developers to execute raw SQL and implement the params binding through the text object they provide, thus protecting the risk of SQL injection.

Note the Prepare/bind_param interfaces supported by the DB Operations library (such as PDO or mysqli) in 1:php are also recommended by the industry to prevent SQL injection, whereas escape_string can only make simple substitutions for special characters such as single/double quotes. It is not guaranteed to defend against all dangerous characters.

Note 2:sqlalchemy's official documentation is more, its architectural details can be referred to SQLAlchemy at Architecture of Open Source applications This article, I believe there is no small help for beginners.

The following code example shows how to use SQLAlchemy's parameters bind capability to write raw SQL to prevent SQL injection.

premise Assumptions

Suppose we implement a simple SQLAlchemy wrapper class (dbutil.py) with the following code:

 
#!/bin/env python
#-*- encoding: utf-8 -*-

from sqlalchemy import create_engine

class DbWrapper(object):
    _db_inst = None
    _db_driver_cfg = {
        ‘dbtype‘ : ‘mysql‘,
    }

    @classmethod
    def get_db_inst(cls, dbtype = ‘mysql‘, user = ‘‘, password = ‘‘, host = ‘127.0.0.1‘, port = 3306, dbname = ‘‘, encoding = ‘utf-8‘):
        if cls._db_inst is None:
            stmt = ‘%s://%s:%[email protected]%s:%s/%s‘ % (cls._db_driver_cfg[‘dbtype‘], user, password, host, port, dbname)
            cls._db_inst = create_engine(stmt, encoding = encoding)
        return cls._db_inst

The code above is simple enough to get an instance of a class that can manipulate db by calling Dbutil.get_db_inst () and passing in the DB configuration when you need to manipulate the db.

Note: DB instances are best created once and saved after the process is started and the DB instance can be created and the entire process is available to access the database once the necessary initialization work is done. This is because the SQLAlchemy library is supported by the connection pool and is enabled by default, and in most cases a DB instance is sufficient to handle the concurrent access requirements of the entire process to db.

Insert Example

Here's an example of insert SQL to illustrate how to write raw SQL without SQL injection risk with sqlalchemy.text (assuming you've already created a _db_inst instance).

 
#!/bin/env python
#-*- encoding: utf-8 -*-

import time
from sqlalchemy import text

def insert_into_xxx_tbl(user_id, user_name, nickname):
    insert_params_dict = {
        ‘user_id‘: user_id,
        ‘user_name‘: user_name,
        ‘nickname‘: nickname,
        ‘db_insert_time‘: int(time.time()),
        ‘db_update_time‘: int(time.time()),
    }

    ## use sqlalchemy bindparams to prevent sql injection
    pre_sql = ‘insert into xxx_tbl (user_id, user_name, nickname, db_insert_time, db_update_time) values(:user_id, :user_name, :nickname, :db_insert_time, :db_update_time)‘
    bind_sql = text(pre_sql)
    resproxy = _db_inst.connect().execute(bind_sql, insert_params_dict)
    ## return lastid as event_id
    event_id = resproxy.lastrowid
    return event_id

Select Example

The use of Select is similar to insert: Using Python dict to construct the kv pairs of the Select SQL where condition, using text () for parameter binding to SQL, invoking execute () to pass in the bound SQL and the real arguments.

 
#!/bin/env python
#-*- encoding: utf-8 -*-

import time
from sqlalchemy import text

def select_from_xxx_tbl(event_id):
    select_params_dict = {
        ‘event_id‘: event_id,
    }

    ## use sqlalchemy bindparams to prevent sql injection
    pre_sql = ‘select user_id, user_name, nickname from xxx_tbl where event_id = :event_id‘
    bind_sql = text(pre_sql)
    resproxy = _db_inst.connect().execute(bind_sql, select_params_dict)
    rows = resproxy.fetchall()
    ret = rows[0]
    ## return (user_id, user_name, nickname)
    return ret

Resources

1. SQLAlchemy doc:using Textual SQL
2. Architectural Documentation:sqlalchemy at Architecture of Open Source applications
3. Stackoverflow:how can I prevent sql-injection in PHP?

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

How to write raw SQL for anti-SQL injection using the SQLAlchemy library