PYTHON / DATABASES WITH PYTHON
Querying, parameter binding, and SQL injection
Run SELECT queries from Python with qmark and named placeholders, and see exactly why bound parameters make SQL injection impossible.
What you will learn
- Pass values as a tuple or dict of parameters instead of formatting them into SQL
- Use ? for positional binds and :name for named binds in the same query
- Build one ? per item for IN clauses; a list cannot fill a single placeholder
- Validate table and column names against an allowlist, since binds cannot name them
Understanding Querying, parameter binding, and SQL injection
A query goes to the database in two separate pieces: the SQL text, which the engine compiles into a statement, and the parameter values, which are attached to that already-compiled statement afterwards. When you write "WHERE role = ?" and pass ("user",), the compiler has already decided that the position after the equals sign is one value slot before it ever sees your string. Nothing you put in that string can add a clause, close a quote, or start a comment, because the parsing stage is over.
String formatting destroys that separation. An f-string produces one blob of text where your data and your syntax are indistinguishable, so the value "user' OR '1'='1" becomes part of the WHERE expression and the row filter disappears. This is the whole mechanism of SQL injection: not a magic exploit, just data that was allowed to become code. Escaping quotes by hand is a losing game because you have to be right about every quoting rule of every backend, while binding is right by construction.
Binding works for values only, never for identifiers or SQL structure. "SELECT ? FROM t" with ("a",) does not select column a, it selects the constant string 'a' for every row, and you cannot bind a table name, a column name, an ORDER BY direction, or an operator. When those genuinely need to vary, check the requested name against a fixed set you control and then interpolate the approved literal, so the untrusted string never reaches the SQL text.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, role TEXT)")
con.executemany(
"INSERT INTO users (name, role) VALUES (?, ?)",
[("ana", "admin"), ("bo", "user"), ("cy", "user")],
)
cur = con.execute("SELECT id, name FROM users WHERE role = ?", ("user",))
print(cur.fetchall())
attacker = "user' OR '1'='1"
cur = con.execute("SELECT id, name FROM users WHERE role = ?", (attacker,))
print(cur.fetchall())
cur = con.execute(
"SELECT name FROM users WHERE role = :role AND name LIKE :pattern",
{"role": "user", "pattern": "b%"},
)
print(cur.fetchone())
unsafe = f"SELECT id, name FROM users WHERE role = '{attacker}'"
print(unsafe)
print(con.execute(unsafe).fetchall())
con.close()Parameters are values bound to an already-compiled statement, so user input can never change what the SQL means.
Worked examples
IN with a variable number of values
Shows that an IN list needs one placeholder per value, generated from the length of the Python list.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE fruit (name TEXT, qty INTEGER)")
con.executemany(
"INSERT INTO fruit VALUES (?, ?)",
[("apple", 3), ("pear", 7), ("fig", 1)],
)
wanted = ["apple", "fig"]
placeholders = ", ".join("?" * len(wanted))
sql = f"SELECT name, qty FROM fruit WHERE name IN ({placeholders})"
print(sql)
print(con.execute(sql, wanted).fetchall())
try:
con.execute("SELECT name FROM fruit WHERE name IN (?)", (wanted,))
except sqlite3.InterfaceError:
print("InterfaceError: a list cannot be bound to one placeholder")
con.close()Example explained
Line 1", ".join("?" * len(wanted)) builds the SQL fragment ?, ? from the count only, so no user data is interpolated.
Line 2The f-string is safe here because the only thing inserted is question marks the program generated itself.
Line 3wanted is passed as the parameter sequence, so its two strings fill the two slots in order.
Line 4Binding the whole list to a single ? fails: each placeholder takes one scalar value, not a collection.
Placeholders cannot name columns
Demonstrates that a bound parameter in the select list becomes a constant, and how to handle a dynamic column safely.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (a TEXT, b TEXT)")
con.execute("INSERT INTO t VALUES ('x', 'y')")
print(con.execute("SELECT ? FROM t", ("a",)).fetchall())
allowed = {"a", "b"}
requested = "a"
if requested not in allowed:
raise ValueError(f"unknown column: {requested}")
print(con.execute(f'SELECT "{requested}" FROM t').fetchall())
con.close()Example explained
Line 1SELECT ? returns the literal string 'a' once per row, proving the bind is a value and not an identifier.
Line 2The membership test against allowed means only strings the program already knows can reach the SQL text.
Line 3Interpolating after the check is safe because requested is now one of two hardcoded names, not user data.
Line 4Double quotes around the name are SQL's identifier quoting, which keeps unusual but approved names working.
LIKE wildcards survive binding
Shows that binding stops injection but not wildcard abuse inside a LIKE pattern, and how ESCAPE fixes it.
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE doc (path TEXT)")
con.executemany(
"INSERT INTO doc VALUES (?)",
[("notes.txt",), ("a%b.txt",), ("secret.txt",)],
)
term = "%"
print(con.execute("SELECT path FROM doc WHERE path LIKE ?", (f"%{term}%",)).fetchall())
safe = term.replace("!", "!!").replace("%", "!%").replace("_", "!_")
print(con.execute(
"SELECT path FROM doc WHERE path LIKE ? ESCAPE '!'",
(f"%{safe}%",),
).fetchall())
con.close()Example explained
Line 1The first query binds the pattern correctly, yet the user's % is a wildcard inside LIKE and matches every row.
Line 2The replace chain escapes ! first, then % and _, so the escape character itself is never ambiguous.
Line 3ESCAPE '!' tells SQLite that !% means a literal percent sign rather than 'any characters'.
Line 4Only the row whose path actually contains % comes back, which is what the user asked for.
Important notes
Placeholder style is per driver: sqlite3 accepts ? and :name, while mysql-connector uses %s and psycopg uses %s too, so a query string is not portable even when the binding idea is.
Binding protects the SQL text, not authorization. A correctly bound query can still return rows the current user should not see if you forgot the WHERE clause that scopes them.
Common mistakes
Writing execute("SELECT * FROM users WHERE name = ?", ("ana")) without the trailing comma: the parentheses are not a tuple, so the string is treated as a sequence of characters and you get ProgrammingError about the wrong number of bindings.
Quoting the placeholder as WHERE name = '?': the quotes make it a literal question mark, so the statement uses zero parameters and execute raises ProgrammingError for the one value you supplied.
Sanitizing input by stripping or doubling quotes and then formatting it into the SQL: numeric contexts need no quotes at all, so an id like 1 OR 1=1 still leaks every row.
Try it yourself
Change, predict, then run
Create an in-memory table of employees with name and salary, then write one function that returns employees whose name matches a search term using a bound LIKE pattern, and call it with the term "%" to confirm your ESCAPE handling stops it from returning everyone.
Open the Python workspaceCheck your understanding
Why can a bound parameter containing "' OR 1=1 --" not change which rows a query returns?
- The database compiles the SQL text before the value exists, so the value can only fill a value slot and is never parsed as SQL
- The driver escapes quotes and comment markers in the value before pasting it into the SQL string
- SQLite refuses any parameter whose text contains SQL keywords such as OR
- Placeholders force parameters to be strings, and a string can never affect a WHERE clause
Show answer
Compilation happens on the SQL text alone, so by the time the value is attached, the statement's structure is already fixed and the value is just data in one slot. The escaping answer describes a different, weaker technique: it still produces a single string where data and syntax are mixed, and it depends on getting every quoting rule right, which is exactly what binding avoids.