Lesson 53 of 61

5 mins read

Python SQL Injection Defense: Threat Models & Prevention

In Plain English

SQL Injection (SQLi) is a critical security vulnerability where untrusted user input is directly concatenated into a raw SQL query string. Attackers craft malicious input fragments (e.g. `' OR '1'='1`) to break out of data literals, alter the abstract syntax tree of the query, bypass authentication, exfiltrate confidential databases, or drop entire tables. Parameterization is the non-negotiable primary defense.

The Mental Model

"SQL injection is like handing an order slip to a cook that says "1 Burger; AND ALSO burn down the kitchen": if the cook executes instructions written on the food order blindly, catastrophe ensues."

Deep Dive: How It Works

AST Mutation: String concatenation mixes untrusted user data with SQL code instructions, altering the query parse tree.

Classic Tautology: `admin' OR '1'='1` transforms `WHERE user = 'input'` into `WHERE user = 'admin' OR TRUE`, bypassing authentication.

Stacked Queries & Data Exfiltration: Attackers terminate the query with a semicolon and run `DROP TABLE users;` or `UNION SELECT password_hash FROM admin_users;`.

Syntax Blueprint

-- VULNERABLE (DO NOT USE):
"SELECT * FROM users WHERE email = '" + userInput + "'";

-- SECURE (PARAMETERIZED):
"SELECT * FROM users WHERE email = ?", [userInput];

Separates SQL code structure from user data values completely.

Core Rules to Remember

Zero Direct Concatenation: Never use string interpolation (`+`, `${var}`, `f"{var}"`) to insert user input into SQL.

Defense in Depth: Combine prepared statements with least-privilege database user roles.

Input Validation: Validate data types, lengths, and formats before passing to query parameters.

Common Beginner Traps & How to Fix Them

Mistake:Relying on manual regex escaping or replacing single quotes instead of using prepared statements.

Why it happens: Attackers bypass naive quote replacement using unicode tricks, nested comments, or hex encodings.

How to fix: Always use parameterized prepared statements provided by your DB driver or ORM.

Pro Tip: Even ORMs like Prisma or Drizzle can be vulnerable if you use their raw query escape hatches (`prisma.$queryRawUnsafe`) without bound parameters.

Live Interactive Example

Hit Run Code to see it live

Evaluating Safe Literal Matching vs Injection Prevention

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Safe literal lookup matching the alice user record without dynamic code interpolation.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Safe Query Lookup

Query the `secure_users` table to select all users with `role = 'admin'`. Ensure you match the literal text string safely.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.