SQL Injection Audit Challenge Week 1

Source: Dev.to

Introduction

Master SQL Injection Detection: 15 Real‑World Exercises for AppSec Engineers

The $1.5 Billion Wake‑Call

In 2015, hackers exploited a SQL injection vulnerability in the UK telecommunications company TalkTalk, stealing personal data of 157,000 customers—including names, addresses, dates of birth, and 15,600 bank account numbers. The company paid £400,000 in fines and lost an estimated £60 million in direct costs and customer compensation¹. The attacker? A 17‑year‑old who used one of the oldest and most preventable vulnerabilities in web security.

According to the OWASP Top 10 2025 (released Nov 6 2025), injection attacks rank as A05:2025 – Injection, affecting 100 % of applications tested for some form of injection². While SQL injection has decreased in frequency due to better frameworks and awareness, the impact remains catastrophically high:

• Low frequency, high impact – 14 000+ CVEs in the injection category²
• Average impact: complete data compromise
• Testing coverage: 100 % of applications tested for injection vulnerabilities²
• 37 CWEs in the injection category – the greatest number of CVEs for any OWASP Top 10 2025 category²

Translation: SQL injection is less common than it used to be, but when it appears, it’s devastating. OWASP explicitly characterizes SQL injection as “low frequency/high impact”—meaning fewer applications are vulnerable, but those that are face complete compromise.

If you’re interviewing for Security Engineering or Application Security roles at companies like GitLab, Stripe, Coinbase, Trail of Bits, or NCC Group, you’ll face SQL‑injection questions that require you to:

• Identify vulnerable code in unfamiliar frameworks
• Craft working exploit payloads demonstrating impact
• Provide secure fixes using parameterized queries
• Explain why validation alone fails as a defense

These skills separate candidates who have only read about SQL injection from those who can actually find and fix it in production code.

I created these 15 exercises while building my AppSec engineering skills, drawing from:

• PortSwigger Web Security Academy SQL Injection labs³
• OWASP SQL Injection Prevention Cheat Sheet⁴
• Secure by Design (Manning, 2017) – Chapters 1‑3
• API Security in Action (Manning, 2020) – Chapter 2

Each exercise presents real‑world vulnerable code you might encounter during security reviews or penetration tests. All exercises include:

• ✅ Vulnerability identification
• ✅ Exploit payload construction
• ✅ Secure parameterized‑query fixes
• ✅ Explanations of why defenses fail

⭐ Want more exercises like this? Star my repository: AppSec‑Exercises on GitHub for LeetCode‑style secure coding challenges.

Exercise 1 – WHERE Clause Injection

Difficulty: Beginner
Vulnerability Type: WHERE Clause Injection

import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')

    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    query = "SELECT id, username FROM users WHERE username = '" + username + "' AND password = '" + password + "'"

    cursor.execute(query)
    user = cursor.fetchone()
    conn.close()

    if user:
        return jsonify({"message": "Login successful", "user_id": user[0]})
    else:
        return jsonify({"message": "Invalid credentials"}), 401

Questions

1. Is this vulnerable to SQL injection? (Yes/No)
2. Craft a payload for the username field that bypasses authentication without knowing any passwords.
3. Provide the secure fix using SQLite parameterized queries.

💡 Solution at the end of this post.

Exercise 2 – Mixed Parameterization Anti‑Pattern

Difficulty: Beginner
Vulnerability Type: Mixed Parameterization Anti‑Pattern

import psycopg2
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/search', methods=['GET'])
def search_products():
    search_term = request.args.get('q', '')
    category = request.args.get('category', 'all')

    # Attempted escaping
    search_term = search_term.replace("'", "''")

    conn = psycopg2.connect(database="store", user="app", password="secret")
    cursor = conn.cursor()

    if category == 'all':
        query = "SELECT * FROM products WHERE name LIKE '%" + search_term + "%'"
        cursor.execute(query)
    else:
        query = "SELECT * FROM products WHERE name LIKE %s AND category = %s"
        cursor.execute(query, ('%' + search_term + '%', category))

    results = cursor.fetchall()
    conn.close()

    return jsonify({"products": results})

Questions

1. Is the category == 'all' branch vulnerable? (Yes/No)
2. Why does the string escaping (replace("'", "''")) fail to prevent SQL injection?
3. Provide the correct parameterized‑query fix for the vulnerable branch.

💡 Solution at the end of this post.

Exercise 3 – LIKE Clause Injection

Difficulty: Intermediate
Vulnerability Type: LIKE Clause Injection

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2),
    category TEXT
);

CREATE TABLE admin_notes (
    id INTEGER PRIMARY KEY,
    product_id INTEGER,
    note TEXT,
    created_by TEXT
);

import sqlite3
from flask import Flask, request, jsonify

app = Flask(__name__)

@app.route('/search', methods=['GET'])
def search_products():
    search_term = request.args.get('q', '')

    conn = sqlite3.connect('shop.db')
    cursor = conn.cursor()

    query = f"SELECT id, name, price FROM products WHERE name LIKE '%{search_term}%'"

    cursor.execute(query)
    results = cursor.fetchall()
    conn.close()

    return jsonify({"products": results})

Questions

1. Is this vulnerable? The search term is wrapped in % wildcards—does that prevent injection?
2. Craft a payload that extracts sensitive data from the admin_notes table.
3. Provide the secure fix with proper wildcard handling.

💡 Solution at the end of this post.

Exercise 4 – Second‑Order Injection

Difficulty: Intermediate
Vulnerability Type: Second‑Order Injection

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE NOT NULL,
    email TEXT,
    password_hash TEXT
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    author TEXT,
    content TEXT,
    created_at DATETIME
);

import psycopg2
from flask import Flask, request, jsonify

app = Flask(__name__)

# Endpoint 1: Update profile (SAFE)
@app.route('/update-profile', methods=['POST'])
def update_profile():
    user_id = request.form.get('user_id')
    bio = request.form.get('bio')

    conn = psycopg2.connect(host='localhost', user='app', password='pass', database='social')
    cursor = conn.cursor()

    query = "UPDATE users SET bio = %s WHERE id = %s"
    cursor.execute(query, (bio, user_id))
    conn.commit()
    conn.close()

    return jsonify({"message": "Profile updated successfully"})

# Endpoint 2: Get user posts (VULNERABLE)
@app.route('/get-user-posts', methods=['GET'])
def get_user_posts():
    user_id = request.args.get('user_id')

    conn = psycopg2.connect(host='localhost', user='app', password='pass', database='social')
    cursor = conn.cursor()

    cursor.execute("SELECT username FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()

    if not user:
        return jsonify({"error": "User not found"}), 404

    username = user[0]

    query = "SELECT post_id, content, created_at FROM posts WHERE author = '" + username + "' ORDER BY created_at DESC"
    cursor.execute(query)
    posts = cursor.fetchall()

    conn.close()

    return jsonify({"username": username, "posts": posts})

Questions

1. Which endpoint is vulnerable? (update-profile, get-user-posts, or both?)
2. Explain the attack flow: Which endpoint stores malicious data and which triggers the injection?
3. Craft a malicious username that would extract password hashes when viewing posts.
4. Provide the secure fix for the vulnerable endpoint.

💡 Solution at the end of this post.

Exercise 5 – ORDER BY Clause Injection

Difficulty: Intermediate
Vulnerability Type: ORDER BY Clause Injection

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10,2),
    category TEXT,
    stock_quantity INTEGER
);

(Further code and questions continue in the original challenge set.)

Solutions

Exercise 1 – WHERE Clause Injection

1. Vulnerable? Yes.

2. Payload (username):

' OR '1'='1

When submitted, the query becomes:

SELECT id, username FROM users WHERE username = '' OR '1'='1' AND password = ''

The condition '1'='1' is always true, bypassing authentication.

3. Secure fix (SQLite parameterized query):

query = "SELECT id, username FROM users WHERE username = ? AND password = ?"
cursor.execute(query, (username, password))

Exercise 2 – Mixed Parameterization Anti‑Pattern

1. Vulnerable? Yes.

2. Why escaping fails:
Replacing a single quote with two single quotes only protects against simple literal injection. It does not prevent attackers from breaking out of the string context when the surrounding query is built via concatenation, especially when other characters (e.g., %, _) are interpreted by the database.

3. Parameterized fix:

if category == 'all':
    query = "SELECT * FROM products WHERE name LIKE %s"
    cursor.execute(query, ('%' + search_term + '%',))
else:
    query = "SELECT * FROM products WHERE name LIKE %s AND category = %s"
    cursor.execute(query, ('%' + search_term + '%', category))

Exercise 3 – LIKE Clause Injection

1. Vulnerable? Yes. Wrapping the input in % does not neutralize injection; the input is still interpolated directly into the SQL string.

2. Payload to extract admin_notes:

' UNION SELECT id, note, created_by FROM admin_notes --

Resulting query:

SELECT id, name, price FROM products WHERE name LIKE '%' UNION SELECT id, note, created_by FROM admin_notes --'%'

3. Secure fix:

query = "SELECT id, name, price FROM products WHERE name LIKE ?"
param = f"%{search_term}%"
cursor.execute(query, (param,))

Exercise 4 – Second‑Order Injection

1. Vulnerable endpoint: get-user-posts.

2. Attack flow:

• Step 1 – Store malicious data: An attacker (or another compromised component) injects a crafted username value into the users table via any operation that writes to that column (e.g., registration, profile update).
• Step 2 – Trigger injection: When get-user-posts reads the username and concatenates it into a query, the malicious payload executes, allowing data exfiltration.

3. Malicious username payload:

' UNION SELECT password_hash, NULL, NULL FROM users WHERE username='admin' --

When the vulnerable query runs, it becomes:

SELECT post_id, content, created_at FROM posts WHERE author = '' UNION SELECT password_hash, NULL, NULL FROM users WHERE username='admin' --' ORDER BY created_at DESC

4. Secure fix (parameterized query):

query = "SELECT post_id, content, created_at FROM posts WHERE author = %s ORDER BY created_at DESC"
cursor.execute(query, (username,))

Exercise 5 – ORDER BY Clause Injection

(Solution omitted for brevity; apply the same pattern: validate/whitelist column names and use parameterized queries or safe mapping.)

---

Footnotes

1. TalkTalk breach details – https://example.com/talktalk-breach ↩

2. OWASP Top 10 2025 – https://owasp.org/Top10/2025/A05-Injection/ ↩ ↩² ↩³ ↩⁴

3. PortSwigger Web Security Academy – https://portswigger.net/web-security/sql-injection ↩

4. OWASP SQL Injection Prevention Cheat Sheet – https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html ↩