Present SQL injection in Python (CWE-89)

What is a SQL injection?

A SQL injection (listed as CWE-89 by MITRE) is a vulnerability where inputs are not sanitized, and a user passes data that injects random SQL commands into the query.

Imagine that you have a SQL query built in Python like this

query = f"SELECT * FROM users WHERE id={user_id}"

If one user manages to set user_id to the value 1 ; DELETE FROM users ;, the query will be the following:

SELECT * FROM users WHERE id=1 ; DELETE FROM users;

As a result, the table users will be deleted.

How do SQL injections happen in Python?

SQL injections in Python occur by building queries by hand, using raw strings. When users build their queries manually, there is a high chance of introducing SQL injections.

This occurs when using the database modules directly. For example, if you are using the mysql module, use the following code, you may be vulnerable to a MySQL injection attack by not sanitizing or checking the customer_id value.

import mysql.connector

def get_user(customer_id):
  mydb = mysql.connector.connect(...)
  mycursor = mydb.cursor()
  mycursor.execute(f"SELECT * FROM customers WHERE id={customer_id}")
  ...

What Python modules are vulnerable to SQL injections?

This vulnerability exists with all database modules, either mysql, postgresql or generally, any module that interacts with a relational database.

How to avoid SQL injections in Python?

There are two ways to avoid SQL injections in Python:

Check all code that queries the database directly and make sure all data is sanitized
Use an Object Relational Mapper (ORM) that sanitizes the data for you.

We detail each one in the following sub-sections.

Eliminate SQL injections in your Python code

To prevent SQL injections in your Python code, you need to review each query and ensure the data is properly sanitized.

Instead of building a query manually, such as:

cursor.execute(f"SELECT * from users where id={user_id})

use the following query

cursor.execute(f"SELECT * from users where id=%s", (user_id, ))

In the latter code, cursor.execute sanitizes the data and prevents any SQL injection.

Use an Object Relational Mapper (ORM)

An ORM maps your data from the database to your language directly. It saves you from writing SQL queries manually and automatically prevent SQL injections.

The most popular ORM for Python is SQLAlchemy. It works well for all Python versions and is compatible with most databases.

Automatically detect and fix SQL injections in Python?

Codiga provides IDE plugins and integrations with GitHub, GitLab, or Bitbucket to detect unsafe deserialization for SQL-related Python modules. The Codiga static code analysis detects SQL injections directly in your IDE or code reviews.

There are multiple rules in the Codiga engine that checks for SQL injection, there is an example of a rule that detects SQL injections for MySQL.

Avoid SQL Injections for MySQL

To use this rule consistently, all you need to do is to install the integration in your IDE (for VS Code or JetBrains) or code management system and add a codiga.yml file at the root of your profile with the following content:

rulesets:
  - python-security

It will then check all your Python code against 100+ rules that detect unsafe and insecure code and suggests fixes for each of them.

Explore Code Analysis Rules for Python