Skip to content

SQL Injection Defense — Parameterized Queries

Defends against

SQL Injection (SQLi) — catalog search (q) on Marketplace and app sign-in (POST /api/auth/login).

Prerequisites

  • Local INSECURE-threat-intel-app stack (Docker setup).
  • You have already worked through the SQLi attack guide and can trigger the "Found SQL Injection" flag with ' OR '1'='1 and analyst'--.
  • Editor open on exercise-files/web-attacks-101/INSECURE-threat-intel-app.

Principle

Never build a SQL statement by concatenating user input. Send the query text and the data on separate channels: a query template with placeholders, plus an array of values. The database driver then treats every value as data — never as executable SQL — so a quote, --, or OR 1=1 is matched literally instead of changing the query's structure.

This is parameterization (also called prepared statements or bind variables). It is the only complete fix; input filtering, escaping, and WAFs are compensating controls, not replacements.

Step 1 — Locate the vulnerable code

Both sinks live in src/lib/lab/sqli.ts. They interpolate ${...} straight into the SQL string:

``10:31:exercise-files/web-attacks-101/INSECURE-threat-intel-app/src/lib/lab/sqli.ts /** INTENTIONALLY VULNERABLE — string concatenation SQLi for WA101 lab only. */ export async function unsafeProductSearch(query: string): Promise<LabProduct[]> { const q = query.trim(); // VULNERABLE:qis interpolated straight into the SQL via ${q} on the WHERE lines // below, so input like ' OR '1'='1 rewrites the query's logic. const sql =SELECT id, name, description, price::text AS price FROM dashboard.lab_products WHERE name ILIKE '%\({q}%' OR description ILIKE '%\)%'`; const result = await pool.query(sql); return result.rows as LabProduct[]; }

/* INTENTIONALLY VULNERABLE — login bypass via SQLi on lab-only accounts table query. / export async function unsafeLabLogin( username: string, password: string ): Promise<{ username: string } | null> { // VULNERABLE: username AND password are interpolated into the query (${username}, // ${password}), so analyst'-- comments out the password check entirely. const sql = SELECT username FROM dashboard.lab_profiles WHERE username = '${username}' AND password = '${password}' LIMIT 1; const result = await pool.query(sql); if (result.rowCount === 0) return null; return { username: result.rows[0].username as string }; } 

The rest of the app already does this correctly — for example `src/lib/db.ts` passes `[panelName]` as the second argument to `pool.query`. We are bringing these two functions up to the same standard.

## Step 2 — Parameterize the catalog search

The `pg` driver supports `$1`, `$2`, … placeholders. Move the user value out of the string and into the values array. The `%…%` wildcards belong to the **value**, not the SQL text:

```ts
export async function safeProductSearch(query: string): Promise<LabProduct[]> {
  const q = query.trim();
  const like = `%${q}%`;
  const sql = `SELECT id, name, description, price::text AS price
    FROM dashboard.lab_products
    WHERE name ILIKE $1
       OR description ILIKE $1`;
  const result = await pool.query(sql, [like]);
  return result.rows as LabProduct[];
}

Key points:

  • $1 is reused for both columns — one value, referenced twice.
  • The % characters are concatenated into the data, so they still work as wildcards, but a ' typed by the user is now just a character to search for.
  • No escaping needed — the driver handles it over the wire protocol.

Step 3 — Fix the login and stop storing plaintext passwords

The login query has the same flaw, but it also reveals a second problem: comparing a plaintext password column. Parameterize the lookup, then verify a password hash in application code rather than in SQL:

import { pool } from "../db";
import { timingSafeEqual } from "node:crypto";
// In a real app, verify against a slow hash (bcrypt/argon2), e.g. `import bcrypt from "bcrypt"`.

export async function safeLabLogin(
  username: string,
  password: string
): Promise<{ username: string } | null> {
  const result = await pool.query(
    `SELECT username, password_hash FROM dashboard.lab_profiles WHERE username = $1 LIMIT 1`,
    [username]
  );
  if (result.rowCount === 0) return null;

  const { username: foundUser, password_hash } = result.rows[0];
  // const ok = await bcrypt.compare(password, password_hash);
  const ok = verifyHash(password, password_hash); // constant-time compare
  return ok ? { username: foundUser as string } : null;
}

Why the password check moves out of SQL:

  • WHERE username = $1 AND password = $2 would still work and be injection-safe, but it forces an exact plaintext match in the database. Authentication logic should compare a salted hash with a constant-time function so the database never holds recoverable passwords.
  • Looking the user up by username only (then verifying the secret in code) also gives uniform failure behavior, which limits username-enumeration via timing.

Lab seed data

The seed (docker/init/03-lab-vulnerabilities.sql) stores a plaintext password column to make the attack demonstrable. A production-style fix also changes the schema to password_hash and seeds a hash. For the WA101 exercise, parameterizing the query is the core lesson; the hashing note shows where real apps go further.

Step 4 — Point the app at the safe functions

Update Marketplace (src/pages/marketplace.astro) to use safeProductSearch, and sign-in (src/pages/api/auth/login.ts) to use safeLabLogin:

// marketplace.astro
import { safeProductSearch } from '../lib/lab/sqli';
// ...
products = await safeProductSearch(q);

// api/auth/login.ts
import { safeLabLogin } from '../../../lib/lab/sqli';
// ...
const user = await safeLabLogin(username, password);

Step 5 — Verify the fix

Rebuild and retest:

npm run docker:down
npm run docker:up
Test Before After (fixed)
Search Threat Matching products Same matching products (functionality intact)
Search ' OR '1'='1 All products returned Zero/normal results — treated as a literal search string
Sign-in analyst'-- Dashboard redirect (login bypass) 302 to /login?error=1

The "Found SQL Injection" lab flag (detectSqliSearch / detectSqliLogin in src/lib/lab/flags.ts) should no longer fire, because the tautology no longer changes the query result. That flag flipping off is your fastest regression check.

In Burp Repeater, resend your saved GET /marketplace?q=' OR '1'='1 and POST /api/auth/login requests — confirm they now behave like ordinary invalid input.

Defense in depth

Parameterization fixes the bug. These layers reduce blast radius if a new sink slips in:

  • Least-privilege DB account. The app role should not own tables or hold DROP/CREATE. In the production topology this maps to the webapp_rw user, not projectx_dbadmin.
  • Allowlist non-data fields. Column and table names, ORDER BY keys, and ASC/DESC cannot be parameterized — map user choices through a fixed allowlist ({ name: "name", price: "price" }[sort]).
  • ORMs / query builders (Drizzle, Prisma, Knex) parameterize by default — but db.raw("... " + input) re-opens the hole. Treat any raw fragment as a review red flag.
  • Input validation as a secondary check (length, expected charset) for defense in depth and better UX — never as the primary control.
  • A WAF (e.g. ModSecurity + OWASP CRS) can catch generic payloads, but attackers bypass signatures; it buys time, it does not fix the code.

Verification checklist

  • [ ] No ${...} or string + building SQL anywhere in src/lib/.
  • [ ] Every pool.query call that includes user input passes a values array.
  • [ ] Tautology and comment payloads return normal results, not full tables / logins.
  • [ ] The "Found SQL Injection" flag no longer appears.