Chapter 16 · Security, Reliability, and Governance
SQL Injection and Parameterized Queries
SQL injection is not caused by dangerous characters alone. It occurs when untrusted data is allowed to become SQL syntax. The primary defense is to keep statement structure fixed and send values through the database driver’s parameter channel.
Learning outcomes
Keep untrusted values outside SQL structure
Explain injection as a boundary failure between data and SQL syntax.
Use bound parameters with Python’s SQLite driver.
Handle dynamic sorting, identifiers, and variable-length lists safely.
Recognize ineffective defenses such as manual escaping and stored-procedure assumptions.
Combine parameterization with validation, least privilege, and security testing.
How structure changes
An application often intends one predicate but accidentally allows input to terminate a string literal and add new SQL tokens. The database receives only the final statement; it cannot know which characters came from the developer and which came from the attacker.
A safe driver sends the SQL template and parameter values separately, so the parser never treats a value as statement structure.
The vulnerable pattern
def find_customer_vulnerable(connection, email): sql = "SELECT customer_id, email FROM customer WHERE email = '" + email + "'" return connection.execute(sql).fetchall()# Input: ' OR 1=1 --# Final SQL:# SELECT customer_id, email FROM customer WHERE email = '' OR 1=1 --' Escaping a few characters is not a durable parser boundary. Encoding rules, dialect features, numeric contexts, second-order data, and future refactoring make manual escaping fragile.
Bound parameters
def find_customer(connection, email): sql = """ SELECT customer_id, email, region FROM customer WHERE email = ? """ return connection.execute(sql, (email,)).fetchall()# The driver transmits the SQL template and value separately.# A malicious-looking value remains one TEXT value.def orders_between(connection, minimum_cents, maximum_cents, status): sql = """ SELECT order_id, customer_id, status, total_cents FROM sales_order WHERE status = :status AND total_cents BETWEEN :minimum AND :maximum ORDER BY total_cents DESC, order_id """ return connection.execute(sql, { "status": status, "minimum": minimum_cents, "maximum": maximum_cents, }).fetchall()Parameters bind values, not SQL grammar
A placeholder cannot represent a table name, column name, keyword, or sort direction. When structure must vary, map an application-level option to a fixed allow-list.
SORT_EXPRESSIONS = { "newest": "ordered_at DESC, order_id DESC", "oldest": "ordered_at ASC, order_id ASC", "largest": "total_cents DESC, order_id DESC",}def list_orders(connection, sort_key, status): order_by = SORT_EXPRESSIONS.get(sort_key) if order_by is None: raise ValueError("unsupported sort option") sql = f""" SELECT order_id, ordered_at, status, total_cents FROM sales_order WHERE status = ? ORDER BY {order_by} """ return connection.execute(sql, (status,)).fetchall()The interpolation is safe because order_by comes only from developer-controlled constants. User input chooses a key; it never becomes SQL text.
Variable-length IN lists
def customers_by_ids(connection, customer_ids): ids = list(customer_ids) if not ids: return [] if len(ids) > 100: raise ValueError("too many identifiers") placeholders = ", ".join("?" for _ in ids) sql = f""" SELECT customer_id, email, region FROM customer WHERE customer_id IN ({placeholders}) ORDER BY customer_id """ return connection.execute(sql, ids).fetchall()LIKE is still parameterized
Parameterization prevents injection, but wildcard characters still retain their SQL meaning. Decide whether the feature is a pattern search or a literal substring search.
-- Build the wildcard pattern in SQL while binding the value.SELECT customer_id, emailFROM customerWHERE email LIKE '%' || :literal_text || '%' ESCAPE '!';-- Before binding, application code can escape:-- ! as !!-- % as !%-- _ as !_Stored routines are not automatically safe
A routine that concatenates arguments into dynamic SQL can still be injectable. Safe routines bind values and allow-list structural choices. Also consider the routine’s execution privileges: a security-definer routine can amplify the impact of a defect.
CREATE FUNCTION reporting.orders_for_customer(p_customer_id bigint)RETURNS TABLE(order_id bigint, status text, total_cents integer)LANGUAGE sqlSECURITY INVOKERAS $$ SELECT o.order_id, o.status, o.total_cents FROM commerce.sales_order AS o WHERE o.customer_id = p_customer_id ORDER BY o.order_id$$;Defense layers
| Control | Primary purpose | What it does not replace |
|---|---|---|
| Parameterized query | Separates values from SQL syntax | Authorization and business validation |
| Allow-list validation | Constrains dynamic structure and semantic choices | Parameter binding for ordinary values |
| Least-privilege account | Limits blast radius after a defect | Secure query construction |
| Schema constraints | Rejects invalid persisted state | Authentication or statement authorization |
| Security tests and monitoring | Detects regressions and attempted abuse | Preventive controls |
Injection review
- Can a placeholder safely replace a column name?
- Why is escaping input weaker than parameter binding?
- Is a stored procedure automatically immune to injection?
- What should happen when an API requests an unsupported sort key?
Review the answers
No: placeholders represent values, not identifiers or keywords. Escaping depends on context and dialect, while binding creates a parser-level separation. A routine can concatenate unsafe dynamic SQL and remain vulnerable. Reject the option; do not fall back to interpolating it.
Test the boundary
import sqlite3connection = sqlite3.connect(":memory:")connection.execute("CREATE TABLE account(id INTEGER PRIMARY KEY, email TEXT UNIQUE)")connection.executemany( "INSERT INTO account(email) VALUES (?)", [("ada@example.com",), ("grace@example.com",)],)probe = "' OR 1=1 --"rows = connection.execute( "SELECT id, email FROM account WHERE email = ?", (probe,),).fetchall()assert rows == []assert connection.execute("SELECT COUNT(*) FROM account").fetchone()[0] == 2Summary and references
- Never build SQL by concatenating untrusted values.
- Bind every ordinary value through the driver or routine interface.
- Allow-list structural variation such as identifiers and sort directions.
- Validate semantic constraints such as length, range, and allowed choices.
- Use least privilege so one query defect does not become total database compromise.