Week 3 • Day 5Week 3

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 write f"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, %s or $1 in 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!)

python
# 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 (?)

python
# 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:

powershell
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.