Interview frequency: …
Short answer
Use parameterized queries (placeholders such as ? or :name) and pass the values separately, and never concatenate user input into the SQL text.
Explanation
When parameters are passed, the library sends the values separately from the query text, so they cannot be interpreted as SQL commands. Manual escaping is unreliable. Note that placeholders work for values only, not for table or column names; if you need a dynamic column name (such as for sorting), choose it from an allowlist. Also apply permissions at the application level.
Code example
import sqlite3
def find_user(conn: sqlite3.Connection, email: str):
# Error: f"SELECT * FROM users WHERE email = '{email}'"
return conn.execute("SELECT id, name FROM users WHERE email = ?", (email,)).fetchone()
ALLOWED_SORT = {"name", "created_at"}
def list_users(conn, sort: str):
if sort not in ALLOWED_SORT:
raise ValueError("invalid sort column")
return conn.execute(f"SELECT id, name FROM users ORDER BY {sort}").fetchall()Common mistakes
- Passing a single value as (email) instead of (email,), so it is not a tuple.
- Using a placeholder for a column or table name and expecting it to work.