SQL Injection: From Discovery to Remediation

The lab app is a small storefront: a login form and a product-search endpoint, backed by MySQL, with a Node/Express and a C#/.NET variant so I can show the fix in both stacks. The vulnerable query concatenates user input straight into SQL — the single most common root cause I see in real assessments:

// login handler (Node/Express) — DO NOT SHIP THIS
const q = "SELECT id, role FROM users " +
          "WHERE username = '" + req.body.user + "' " +
          "AND password = '" + req.body.pass + "'";
const rows = await db.query(q);
if (rows.length) grantSession(rows[0]);

The user's input becomes part of the query's structure, not just its data. That confusion between code and data is the whole vulnerability.

I map every input that reaches the database — form fields, URL parameters, headers, cookies — and probe each with a single quote. A raw database error is the first tell:

username:  admin'
→ 500: You have an error in your SQL syntax near "'' AND password = ''"

That error confirms the input breaks out of the string literal. I fingerprint the engine (error dialect, version functions) and count result columns with an incremental ORDER BY until it fails — that tells me how many columns a UNION needs to match.

search?q=phone' ORDER BY 1--   → ok
search?q=phone' ORDER BY 5--   → ok
search?q=phone' ORDER BY 6--   → error   ⇒ 5 columns

Once input reaches the query as structure, the classic techniques follow. In an assessment I confirm each only to the depth needed to prove business impact — the goal is a clear demonstration, not collecting trophies.

Authentication bypass is the textbook case: a tautology in the login field makes the WHERE clause always-true and comments out the password check, so the app grants a session for an account whose password is never evaluated.

username:  admin' --
→ WHERE username = 'admin' -- ' AND password = '...'
→ logs in as admin · password never checked

Data extraction is the next escalation. Where results are reflected back, a UNION pulls rows from other tables — credentials, tokens, PII — into the page. Where nothing is reflected, the database still leaks one bit at a time through boolean or timing differences (blindinjection). In practice I demonstrate that sensitive data is reachable, note the ceiling of impact — up to remote code execution against an over-privileged database account — and stop there. The proof matters; the dump doesn't.

One concatenated query put the entire users table — including password hashes — in reach, and let an attacker log in as any account without a password. Against a database account with excess privileges, stacked queries and engine features (e.g. xp_cmdshell on misconfigured MSSQL, file writes on MySQL) can escalate from data theft to code execution. In CIA terms: confidentiality and integrity are gone, and availability is one DROP away. This is why SQL injection has sat in the OWASP Top 10 (now under A03: Injection) for over two decades.

The fix is not escaping or blacklisting quotes — it's never letting input become code. Parameterized queries (prepared statements) send the SQL structure and the data on separate channels, so input is always treated as a value:

const rows = await db.query(
  "SELECT id, role FROM users WHERE username = ? AND password_hash = ?",
  [req.body.user, hash(req.body.pass)]
);

using var cmd = new SqlCommand(
  "SELECT id, role FROM users WHERE username = @user AND password_hash = @hash", conn);
cmd.Parameters.AddWithValue("@user", model.User);
cmd.Parameters.AddWithValue("@hash", Hash(model.Pass));

Parameterization is the control that closes it. The rest is defense-in-depth:

• Least privilege — the app's DB account gets only the rights it needs — no DROP, no xp_cmdshell, no FILE.
• Input validation — allowlist expected shapes (numeric IDs, enum values) and reject the rest — a second layer, never the only one.
• Safe ORMs / stored procedures — used correctly they parameterize by default; string-built dynamic SQL inside them re-opens the hole.
• Hashing — passwords stored with bcrypt/argon2, so an exfiltrated table isn't instantly usable.
• WAF — catches known payloads at the edge — useful as a speed bump, not a substitute for the code fix.

Prevention closes the bug; detection tells me when someone tries. On the blue-team side I watch for the fingerprints these techniques leave:

• Spikes in database syntax errors in application logs — probing is noisy.
• Requests containing UNION SELECT, ORDER BY ladders, SLEEP/WAITFOR/BENCHMARK, or comment sequences (--, #, /*).
• Response-time outliers on a single parameter — the signature of time-blind injection.
• Suricata/IDS rules on the SQLi payload patterns, plus SIEM correlation of failed-login bursts against one account.

After the parameterized rewrite I replay every payload from the exploitation phase. The tautology now searches for a user literally named admin' --, the UNION is treated as a search string, and the blind conditions never execute. Same inputs, no bypass, no leak — the fix holds under the original attack.