Defense & Mitigation
The art of writing unbreakable queries
Rolling Thunder Security · Module 05 · Lab 08
The Defender's Toolkit
Now that you understand how SQL injection works, it is time to learn how to stop it. There is one technique that is overwhelmingly the most effective defense, and several others that provide valuable layers of protection.
A strong security posture uses defense in depth: multiple overlapping layers so that if one fails, others still protect you.
Primary Defense
Parameterized Queries
Primary Defense
Input Validation (Allowlists)
Secondary Defense
Stored Procedures
Secondary Defense
ORM Frameworks
Defense in Depth
Web Application Firewalls
Defense in Depth
Least Privilege Accounts
Defense in Depth
Proper Error Handling
Defense in Depth
Escaping User Input
Lab 1 of 3
Vulnerable vs. Parameterized
Unsolved
Scenario: A login form sends a username and password to the server. On the left, the code uses string concatenation to build the query. On the right, it uses parameterized queries (prepared statements). Type the same input into both panels and see the difference.
Goal: Find an injection that succeeds on the LEFT (vulnerable) panel but is completely neutralized on the RIGHT (parameterized) panel.
Goal: Find an injection that succeeds on the LEFT (vulnerable) panel but is completely neutralized on the RIGHT (parameterized) panel.
Vulnerable (String Concatenation)
Waiting for input...
Parameterized (Prepared Statement)
Waiting for input...
Try the classic tautology bypass in the password field:
On the left (vulnerable), this breaks out of the string and adds OR '1'='1' which is always true. On the right (parameterized), the entire input including the quotes is treated as a literal password value. Same input, completely different outcomes.
' OR '1'='1
On the left (vulnerable), this breaks out of the string and adds OR '1'='1' which is always true. On the right (parameterized), the entire input including the quotes is treated as a literal password value. Same input, completely different outcomes.
Lab 2 of 3
Blocklist Bypass Challenge
Unsolved
Scenario: A developer decided to "secure" their application by adding a blocklist filter that strips out dangerous SQL keywords. Your mission: bypass every filter rule to prove that blocklists are NOT a real defense.
After bypassing the blocklist, you will see the same attacks fail completely against a parameterized query.
After bypassing the blocklist, you will see the same attacks fail completely against a parameterized query.
The filter removes these keywords (case-insensitive):
UNION
SELECT
DROP
--
'
OR
Bypasses Found
0
Target
4
Bypass techniques to try:
1. Case variation:
2. Inline comments:
3. Double encoding:
4. Alternate operators:
After finding bypasses, click "Test Against Parameterized" to see that ALL of these are blocked by prepared statements.
1. Case variation:
uNiOn SeLeCt (some filters are case-sensitive)
2. Inline comments:
UN/**/ION SEL/**/ECT (SQL ignores /* */ comments)
3. Double encoding:
%27 or %2527 for the single quote
4. Alternate operators:
|| instead of OR, or 1 IN (1)
After finding bypasses, click "Test Against Parameterized" to see that ALL of these are blocked by prepared statements.
Lab 3 of 3
Defense Architect
Unsolved
Scenario: You are the developer now. Below are three vulnerable code snippets. Your job is to rewrite each one to be secure. When you are done, click "Run Tests" to see if your code withstands injection attacks.
Rules: Use parameterized queries (? placeholders), input validation (allowlists), and proper error handling. Do NOT use blocklists or escaping alone.
Rules: Use parameterized queries (? placeholders), input validation (allowlists), and proper error handling. Do NOT use blocklists or escaping alone.
Challenges Fixed
0
Target
3
Challenge 1: Parameterize the Login Query
login.py
Python
Challenge 2: Validate and Parameterize the Search
search.php
PHP
Challenge 3: Fix the Error Handling
api.py
Python
Challenge 1: Replace string concatenation with parameterized query:
Challenge 2: Two fixes needed: (1) parameterize the search term, and (2) use an allowlist for the sort column:
Challenge 3: Three fixes: (1) parameterize the query, (2) never expose the SQL query or error details to the user, and (3) log the real error server-side:
Return a generic error message like
query = "SELECT * FROM users WHERE username=? AND password=?"
result = db.execute(query, (username, password))
Challenge 2: Two fixes needed: (1) parameterize the search term, and (2) use an allowlist for the sort column:
allowed_columns = ["name", "price", "category"]
if sort_column not in allowed_columns: sort_column = "name"
$stmt = $conn->prepare("SELECT * FROM products WHERE name LIKE ? ORDER BY " . sort_column)
Challenge 3: Three fixes: (1) parameterize the query, (2) never expose the SQL query or error details to the user, and (3) log the real error server-side:
query = "SELECT * FROM users WHERE id=?"
Return a generic error message like
{"error": "An error occurred"}