Second-Order SQL Injection

Description

Second-order SQL injection (also called stored SQL injection or persistent SQL injection) differs from first-order injection in a critical way: the malicious payload is not executed when it is first submitted — it is stored in the database and executed later when it is retrieved and used in a subsequent query. This means that input sanitisation applied at the point of insertion is completely irrelevant if the retrieved value is used unsafely in a subsequent query.

CWE-89 — Improper Neutralisation of Special Elements used in an SQL Command covers all SQL injection variants including second-order. OWASP A03:2021 — Injection classifies it under the same root cause: trusting data that should not be trusted. In this case, the application trusts data from its own database, assuming that because it was once sanitised during insertion it is safe to use without parameterisation during retrieval.

This vulnerability is particularly dangerous because standard black-box testing tools and automated scanners, which probe insertion points for immediate feedback, frequently miss it entirely. The attack surface is also non-obvious: the value that triggers the vulnerability may be stored in a completely different application function than the one that triggers the exploitation.

How It Works

A classic second-order injection scenario involves a user registration and profile update flow:

Step 1 — Malicious value is stored safely.

The registration form escapes the username properly:

username = request.form['username']  # Input: admin'--
# After escaping: admin\'--
cursor.execute("INSERT INTO users (username) VALUES (%s)", (username,))
# Stored in DB: admin'--   (the backslash is not stored; parameterisation is correct here)

The input is stored correctly in the database as the string admin'--.

Step 2 — The stored value is retrieved and used unsafely.

Later, a password change function retrieves the stored username and concatenates it into a new query without re-parameterising:

# Retrieve the username from the database (trusted internal source, developer thinks)
user = db.query(f"SELECT * FROM users WHERE id = {session['user_id']}")
stored_username = user['username']  # Returns: admin'--

# Use the retrieved username in a new query — WITHOUT parameterisation
cursor.execute(f"UPDATE passwords SET hash='{new_hash}' WHERE username='{stored_username}'")
# Becomes: UPDATE passwords SET hash='...' WHERE username='admin'--'
# The -- comments out the closing quote and any subsequent conditions
# This updates the password for the 'admin' user, not the current user

The attacker has just reset the admin account's password without knowing it. They can then log in as admin.

A more sophisticated payload achieves data exfiltration:

-- Stored username payload:
' UNION SELECT username||':'||password FROM users--

-- Triggered query becomes:
SELECT email FROM users WHERE username='' UNION SELECT username||':'||password FROM users--'

Impact

• Authentication bypass — by manipulating how usernames or emails are used in authentication queries, an attacker can authenticate as a different user.
• Privilege escalation — modifying records belonging to administrative accounts (e.g., resetting admin passwords or changing admin email addresses).
• Data exfiltration — UNION-based extraction through queries that use stored attacker-controlled values.
• Data modification — updating or deleting records belonging to other users.
• Broader database compromise — with sufficient access, the same stacked query techniques available in first-order injection (reading information_schema, enabling xp_cmdshell) apply equally.

Detection

1. Trace data flows from storage to query construction — during code review, identify every database column whose value is retrieved and later used in a dynamically constructed query. Flag any that use string concatenation rather than parameterisation.
2. Register accounts with SQL injection payloads in user-supplied fields — use payloads like admin'--, ' OR '1'='1, and test' UNION SELECT 1-- as usernames, display names, and email prefixes.
3. Trigger all application functions that use registered data — after registering with payloads, exercise every function that might retrieve and use those values: password change, username display, email update, account search, activity logging.
4. Monitor for SQL errors — enable verbose error logging in a test environment. Second-order triggers that produce malformed queries will surface as database errors in functions exercised after registration.
5. Perform differential comparison — compare application behaviour when using a payload username versus a clean control username for every post-registration function. Behavioural differences indicate injection.

Remediation

Parameterise every query unconditionally. The root cause is the incorrect assumption that database-sourced values are safe to concatenate. Every dynamically constructed query must use parameterised statements regardless of where the input originated:

# Vulnerable — trusts data from the database
cursor.execute(f"UPDATE passwords SET hash='{new_hash}' WHERE username='{stored_username}'")

# Secure — parameterise even database-sourced values
cursor.execute("UPDATE passwords SET hash=%s WHERE username=%s", (new_hash, stored_username))

Treat all data as untrusted at the point of use. Security controls applied at input time do not provide protection at query construction time. Enforce parameterisation as an invariant at the ORM or data access layer, not as an input validation rule.

Implement database-level stored procedures with static SQL. Stored procedures that use only static SQL internally prevent injection regardless of the values passed as parameters.