Day 5:SQL Injection Defense & The Repository Pattern in Python
Battle-tested daily lesson note • 100% runnable code, interview Q&A, test suite & interactive quiz
Week 3 — Day 5: SQL Injection Defense & The Repository Pattern in Python
Mentor's Note (10+ YOE Staff AI Engineer POV): Why is SQL Injection (
' OR '1'='1) even more dangerous in the era of AI Agents than in traditional web apps? Because in traditional apps, only a malicious human user typed into a search box. In AI Systems, if you writef"SELECT * FROM docs WHERE category = '{llm_extracted_category}'"using an f-string, an attacker can hide an Indirect Prompt Injection inside an email or PDF (Category: ' UNION SELECT api_key FROM secrets --) so the LLM passes that string straight into your database!Golden Rule: NEVER use Python
f"..."or.format()to put values into SQL! Always use Parameterized Queries (?in SQLite,%sor$1in PostgreSQL) so the database driver treats the input strictly as a literal data value, never as executable SQL syntax!
Part 1: How a SQL Injection Attack Works Visually
1.1 The Vulnerable f-string Code (NEVER DO THIS!)
# VULNERABLE CODE:
user_input = "alice' OR '1'='1"
sql = f"SELECT * FROM api_keys WHERE owner = '{user_input}';"
# Resulting SQL executed by database:
# SELECT * FROM api_keys WHERE owner = 'alice' OR '1'='1';
# Since '1'='1' is ALWAYS TRUE, the database dumps EVERY user's secret API key!
1.2 The Bulletproof Parameterized Query (?)
# SAFE PARAMETERIZED CODE:
conn.execute("SELECT * FROM api_keys WHERE owner = ?;", (user_input,))
# The database compiles the SQL AST BEFORE inserting the parameter!
# It searches literally for a user named `alice' OR '1'='1'` -> Returns 0 rows!
Part 2: Hands-On Lab — Live SQL Injection Exploit vs Safe Repository Pattern (safe_repository.py)
Check out src/safe_repository.py (in Tab 2: Codes — click Run in Browser to execute) and run the demonstration and tests:
python week03_sql_databases_from_zero_to_pro/day05_python_db_security_and_sql_injection/src/safe_repository.py
python -m unittest discover -s week03_sql_databases_from_zero_to_pro/day05_python_db_security_and_sql_injection/tests -v
In Simple English: Blank Check vs Locked Form
Why does parameterized SQL (WHERE owner = ?) completely defeat SQL Injection?
- String Formatting (
f"..."): Like handing someone a blank Word document and letting them type both the answer AND new commands. If an attacker types' OR '1'='1, the database obeys it as code! - Parameterized Query (
?): Like locking a PDF form so only the text box is editable. Even if the attacker types' OR '1'='1, the database safely checks whether anyone's literal username is that weird string and finds zero matches!
Click Run in Browser in Tab 2 (Codes) to watch both the exploit and parameterized defense run live.