lesson 233 Secure Python · advanced

Stop SQL injection

When a value is pasted into SQL text, the value can rewrite the query. Placeholders keep values out of the SQL entirely. Table and column names can't be placeholders, so they are chosen from an allow-list.

You'll be able to

Show how a pasted value turns WHERE email = '…' into a condition that matches every row
Pass every value through ? placeholders, including lists of values for IN (...)
Build ORDER BY and column choices from an allow-list, because placeholders only hold values

The idea

The Packaging & Databases module used ? placeholders so that a name like O'Brien couldn't break a query. The same mistake is also the most famous security hole on the web. If a program builds SQL by pasting in text from a user, the user decides part of the SQL. A quote ends the string early, and whatever follows is read as SQL: an extra OR condition, a UNION that reads another table, a statement that deletes data. This is SQL injection.

The example runs the attack against a real (in-memory) customers table. The lookup is meant to return one customer's details for the email typed into a "find my orders" form.

Run each example to see what it prints. Running examples doesn't count toward progress.

example-1.py · The attack, and the placeholder that stops it

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE customers (customer_id TEXT, email TEXT, city TEXT)")
conn.executemany("INSERT INTO customers VALUES (?, ?, ?)", [
    ("C00038", "nora_garcia@fastmail.com", "New York"),
    ("C00077", "jamal.walker62@icloud.com", "Houston"),
    ("C01338", "grace_walker29@yahoo.com", "Seattle"),
])

def find_unsafe(email):
    sql = f"SELECT customer_id, city FROM customers WHERE email = '{email}'"
    return conn.execute(sql).fetchall()

def find_safe(email):
    return conn.execute("SELECT customer_id, city FROM customers WHERE email = ?", (email,)).fetchall()

typed = "x' OR '1'='1"
print(find_unsafe("nora_garcia@fastmail.com"))
print(find_unsafe(typed))
print(find_safe(typed))

Placeholders hold values only. Names that are part of the query's structure (a table, a column, ASC/DESC) can't be placeholders; the database must know them before it can plan the query. Trying anyway fails:

example-2.py · A placeholder where a table name goes

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (sku TEXT, price REAL)")
table = "products"
print(conn.execute("SELECT COUNT(*) FROM ?", (table,)).fetchone())

So when a request chooses a column (a sort field, a filter field), map its text through an allow-list to SQL you wrote yourself, and refuse anything not in the map. And when a request supplies *several* values, as in WHERE order_id IN (...), generate one ? per value: the number of placeholders comes from your code, the values still travel separately.

example-3.py · Sorting and IN lists, safely

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE products (sku TEXT, name TEXT, price REAL, rating REAL)")
conn.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", [
    ("SPO-11681", "Ultra Bike Light", 7.49, 4.1),
    ("SPO-61044", "Travel Yoga Mat", 50.99, 4.6),
    ("HOM-22847", "Deluxe Vase", 31.0, 4.4),
])

SORT_COLUMNS = {"price": "price", "rating": "rating DESC", "name": "name"}

def products_sorted(sort):
    if sort not in SORT_COLUMNS:
        raise ValueError(f"cannot sort by {sort!r}")
    sql = "SELECT sku FROM products ORDER BY " + SORT_COLUMNS[sort]
    return [row[0] for row in conn.execute(sql)]

def prices_for(skus):
    marks = ", ".join("?" for _ in skus)
    sql = f"SELECT sku, price FROM products WHERE sku IN ({marks}) ORDER BY sku"
    return conn.execute(sql, skus).fetchall()

print(products_sorted("price"))
print(products_sorted("rating"))
print(prices_for(["SPO-61044", "HOM-22847"]))
try:
    products_sorted("price; DROP TABLE products")
except ValueError as err:
    print(err)

When to use it: every query that involves anything from outside, which is nearly every query. Mistakes to avoid: f-strings, + or % to put values into SQL; "escaping" quotes by hand (replace("'", "''") misses cases); a placeholder for a column or table name; and pasting a column name from the request after checking only that it "looks like a word".

Review

Pasting a value into SQL lets the value rewrite the query: SQL injection.
Every value goes through ?; for IN (...), build one ? per value and pass the values separately.
Columns, tables and sort directions can't be placeholders: choose them from an allow-list of your own SQL text.
Next: loading data can run code too. Safe serialization.

Practise this