Skip to content
Journal

Guide //

How to fix SQL injection in Python, and prove it stays fixed

Spot SQL injection in Python code, fix it with parameterized queries in sqlite3, psycopg and SQLAlchemy, and write a regression test that fails if the bug ever comes back.

What SQL injection looks like in Python

SQL injection happens when text from a user becomes part of the SQL itself, instead of being passed as a value. The database can't tell your query from the attacker's additions, so input like ' OR '1'='1 changes what the query does.

In Python, it almost always comes from building the query string yourself: an f-string, % formatting, .format() or +.

def find_orders(db, customer):
    # Vulnerable: customer is pasted into the SQL text
    return db.execute(f"SELECT * FROM orders WHERE customer = '{customer}'").fetchall()

The fix: let the driver pass values separately

Every Python database driver supports placeholders. You write the SQL with a marker, and pass the values separately. The driver sends them as data, never as SQL, so no input can change the query's shape. The placeholder style depends on the driver: ? for sqlite3, %s for psycopg and most MySQL drivers.

# sqlite3
db.execute("SELECT * FROM orders WHERE customer = ?", (customer,))

# psycopg (PostgreSQL)
cur.execute("SELECT * FROM orders WHERE customer = %s", (customer,))

# SQLAlchemy
from sqlalchemy import text
session.execute(text("SELECT * FROM orders WHERE customer = :customer"), {"customer": customer})

Mistakes that look safe but are not

  • Escaping by hand. Replacing quotes yourself misses edge cases. Use placeholders.
  • Formatting before calling execute. execute("... = '%s'" % customer) is still string building: the % runs before the driver sees anything. The values must be the second argument.
  • Table and column names. Placeholders only work for values. If a user picks a column to sort by, check it against a fixed list of allowed names.
  • ORM raw queries. text(), .raw() and .extra() are only safe with bound parameters.

Prove it with a regression test

A fix you can't test is a fix that can quietly come back in the next refactor. The test that matters checks how input reaches the database: the user's value must arrive as a parameter, never inside the SQL text. It should fail on the vulnerable code above, and pass on the fixed version.

import unittest
from unittest import mock
from app import orders

class FindOrdersTest(unittest.TestCase):
    def test_customer_is_passed_as_a_parameter_not_sql(self):
        db = mock.MagicMock()
        attack = "x' OR '1'='1"
        orders.find_orders(db, attack)
        sql, *params = db.execute.call_args.args
        self.assertNotIn(attack, sql)          # never inside the query text
        self.assertIn(attack, str(params))     # always passed as a value

if __name__ == "__main__":
    unittest.main()

Find the rest of them

Searching by hand for execute(f" catches the obvious cases, but not queries built a few lines earlier, or in a helper. Taint-tracking scanners follow user input from where it enters (a request handler or function argument) to where it reaches the database.

Mallace runs that kind of scan on your GitHub repository for free. For each SQL injection it finds, it writes a test like the one above, runs it against your current code to prove the bug is real, writes the parameterized fix, and opens a pull request containing both. Nothing is merged automatically. Start free.

Let Mallace find and fix these for you.

Scanning is free and unlimited. Each fix comes as a pull request with the test that proved the bug.