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.