SQLi second order

Why This Matters

Second-order SQL injection is one of the most frequently missed vulnerability classes in both manual code review and automated scanning, because the storage and execution phases occur in different functions, different files, and often different developer-authored components. Automated scanners that inject a payload and immediately observe the response will never detect it — the payload is stored without triggering any anomaly, and only fires when a different code path retrieves and uses it. Real-world examples have been found in CMS platforms, e-commerce frameworks, and forum software where username or profile fields were later used unsafely in administrative queries.

Core Concept

Second-order SQL injection (also called stored SQL injection) involves two distinct phases that are separated in time and often in code:

1. Storage phase: user input is stored in the database safely — it may pass through a parameterised INSERT, HTML encoding, or addslashes() — so no injection occurs at write time.
2. Execution phase: the stored value is later retrieved from the database and used in a second SQL query via unsafe string concatenation rather than parameterisation.

The violated invariant is subtle: a value that was safe to store is not necessarily safe to use as SQL input when retrieved. Developers who sanitise at the point of input often consider the data "clean" once it is in the database, and code that reads from the database frequently skips input validation under the assumption that database-sourced data is trusted.

The attacker precondition is: (1) there exists a storage endpoint (registration, profile update, comment submission) that stores user input; (2) there exists a separate execution endpoint or background process that reads that stored value and concatenates it into a SQL query. The attacker only needs write access to the storage endpoint — they do not need to interact directly with the execution endpoint.

A classic example: a user registers with the username admin'--. The INSERT uses a parameterised query, so the literal string admin'-- is stored correctly. Later, a password-change function retrieves the username from the session and builds: "UPDATE users SET password='" + newPassword + "' WHERE username='" + storedUsername + "'". The stored admin'-- closes the WHERE clause string and comments out the rest, causing the UPDATE to affect all rows or the admin row.

Technical Deep-Dive

-- The stored payload (username registered by attacker):
--   admin'--
-- Stored in DB exactly as: admin'--  (the parameterised insert escapes nothing
-- because it is binding a value, not interpolating text)

-- Vulnerable password-change query (PHP example):

<?php
// Storage phase — safe: uses prepared statement
$stmt = $pdo->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt->execute([$_POST['username'], hash('sha256', $_POST['password'])]);
// Username "admin'--" stored literally in database row.

// Execution phase — VULNERABLE: retrieves username from DB then concatenates
$username = $_SESSION['username'];  // Retrieved from DB: admin'--
$newHash  = hash('sha256', $_POST['new_password']);

// String interpolation — injection fires here:
$sql = "UPDATE users SET password = '$newHash'
        WHERE username = '$username'";
// Resulting query:
// UPDATE users SET password = '<hash>'
// WHERE username = 'admin'--'
// The -- comments out the trailing quote → syntax cleans up
// Effectively: UPDATE users SET password = '<hash>' WHERE username = 'admin'
// Attacker now controls the admin password.
$pdo->query($sql);   // DANGEROUS: uses query() not prepare()
?>

# Testing approach — no single-step scanner will catch this
# Step 1: Register attacker-controlled username with SQL metacharacters
curl -X POST https://target.example.com/register 
  -d "username=admin'--&password=P@ssw0rd1"

# Step 2: Log in as the registered user
curl -X POST https://target.example.com/login 
  -d "username=admin'--&password=P@ssw0rd1" -c cookies.txt

# Step 3: Trigger the execution-phase query (e.g. password change)
curl -X POST https://target.example.com/profile/change-password 
  -b cookies.txt -d "new_password=Hacked123"

# Step 4: Attempt login as admin with the new password
curl -X POST https://target.example.com/login 
  -d "username=admin&password=Hacked123"
# 200 OK with admin session → second-order injection confirmed

Security Assessment Methodology

1. Map all storage endpoints — Identify every feature that persists user-controlled input: registration, profile edit, comment/post creation, preference settings, address books.
2. Map all retrieval-and-use endpoints — Trace where stored values reappear in application logic. Focus on administrative functions, report generation, email sending, and inter-user features that read from stored fields.
3. Register payloads with SQL metacharacters — Create test accounts or content items with payloads like test'--, test' OR '1'='1, test'; DROP TABLE test-- in every string field.
4. Trigger all downstream operations — After storing payloads, exercise every application function that could read those stored values: password changes, account updates, admin viewing user profiles, search features that display user-supplied content.
5. Observe for anomalous behaviour — SQL errors, unexpected record modifications (verify via the application UI), or changed data confirm the injection.
6. Review source code if available — Search for patterns where a DB-retrieved value is used in string concatenation with SQL keywords: "WHERE " + dbValue, "SET password='" + sessionUser.
7. Document the two-step exploit chain in the report clearly, showing both the storage step and the triggering step.

Defensive Countermeasure — Parameterise every SQL query that uses data from any source — not just direct user input, but also values retrieved from the database, session objects, and configuration. The fix is not additional sanitisation at storage time but applying prepare()/bindParam() at every query construction site. Code review tooling (Semgrep rules, SAST tools like Checkmarx or Snyk Code) should flag any occurrence of string concatenation into SQL regardless of the variable's apparent origin. Treat database-sourced values with the same distrust as HTTP request values.

Common Assessment Errors

• Relying solely on automated scanners — All major DAST tools (OWASP ZAP, Burp Scanner) miss second-order injection because they inject and observe in a single request cycle. Manual multi-step testing is required.
• Forgetting to trigger the execution phase — Registering a malicious username and then not testing every downstream function that uses the username means the injection is stored but never confirmed as exploitable.
• Assuming prepared statements at storage time imply safety — The INSERT uses a prepared statement, so developers believe the data is safe. The vulnerability lives in the UPDATE/SELECT that reads it back out.
• Not testing admin-side consumption — Many second-order injections fire only when an administrator views a user list, exports a report, or processes a request. Testers who only act as regular users miss these paths.
• Missing numeric fields — Second-order injection is not limited to string fields. A stored integer (e.g., a quantity or ID) used in an unparameterised UPDATE can be equally dangerous.
• Underreporting severity — Because the attack requires two steps, testers sometimes rate it lower than first-order injection. Impact is identical; the two-step nature is a detection-avoidance advantage for the attacker.

NICE Framework Alignment

Further Reading

• Second Order SQL Injection Attacks — PortSwigger Web Security Research Blog
• The Art of Software Security Assessment — Dowd, McDonald & Schuh, Addison-Wesley
• OWASP Testing Guide v4.2: Testing for SQL Injection (OTG-INPVAL-005) — OWASP Foundation