18.2 SQL Injection & Parameterized Queries — The Complete SQL Course

SQL injection is what happens when user input becomes part of the SQL program instead of staying a value. The fix is not clever escaping; the fix is parameterized queries: SQL text and user values travel separately.

You saw the placeholder shape in 18.1. This lesson shows the attack live, because it is easier to respect after you watch one string turn "find one account" into "return everything."

The Vulnerable Shape

The bug starts in application code that builds SQL by concatenating strings:

const sql =
  `SELECT session_id, customer_email, plan_name, active, internal_note ` +
  `FROM app_sessions ` +
  `WHERE customer_email = '${email}' AND active = true;`;

That looks ordinary until email is not an email address. Here is the same bug inside PostgreSQL so the playground can execute it for real. The function below is intentionally unsafe:

CREATE TABLE app_sessions (
  session_id integer PRIMARY KEY,
  customer_email text NOT NULL,
  plan_name text NOT NULL,
  active boolean NOT NULL,
  internal_note text NOT NULL
);
 
INSERT INTO app_sessions (session_id, customer_email, plan_name, active, internal_note)
VALUES
  (1, 'ada@example.com', 'team', true, 'renewal call booked'),
  (2, 'ken@example.com', 'starter', true, 'trial discount approved'),
  (3, 'priya@example.com', 'enterprise', false, 'security review failed'),
  (4, 'tomas@example.com', 'team', true, 'billing card expiring');
 
CREATE FUNCTION vulnerable_session_lookup(search_text text)
RETURNS TABLE (
  session_id integer,
  customer_email text,
  plan_name text,
  active boolean,
  internal_note text
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT session_id, customer_email, plan_name, active, internal_note FROM app_sessions WHERE customer_email = '''
    || search_text
    || ''' AND active = true';
END;
$$;
 
SELECT v.session_id,
       v.customer_email,
       v.plan_name,
       v.active,
       v.internal_note
FROM vulnerable_session_lookup('ada@example.com') v
ORDER BY v.session_id;

CREATE TABLE
INSERT 0 4
CREATE FUNCTION
 session_id | customer_email  | plan_name | active |    internal_note
------------+-----------------+-----------+--------+---------------------
          1 | ada@example.com | team      | t      | renewal call booked
(1 row)

The normal input returns one row. Now use the classic attack string: ' OR 1=1 --.

SELECT 'SELECT session_id, customer_email, plan_name, active, internal_note FROM app_sessions WHERE customer_email = '''
       || ''' OR 1=1 --'
       || ''' AND active = true' AS vulnerable_sql;
 
SELECT v.session_id,
       v.customer_email,
       v.plan_name,
       v.active,
       v.internal_note
FROM vulnerable_session_lookup(''' OR 1=1 --') v
ORDER BY v.session_id;

                                                                vulnerable_sql
----------------------------------------------------------------------------------------------------------------------------------------------
 SELECT session_id, customer_email, plan_name, active, internal_note FROM app_sessions WHERE customer_email = '' OR 1=1 --' AND active = true
(1 row)
 session_id |  customer_email   | plan_name  | active |      internal_note
------------+-------------------+------------+--------+-------------------------
          1 | ada@example.com   | team       | t      | renewal call booked
          2 | ken@example.com   | starter    | t      | trial discount approved
          3 | priya@example.com | enterprise | f      | security review failed
          4 | tomas@example.com | team       | t      | billing card expiring
(4 rows)

The row-count footer is the alarm: one lookup became four rows, including the inactive account the original filter was supposed to hide.

Escaping Is Not The Defense

Escaping means trying to rewrite dangerous characters before concatenating a string. You will hear advice like "replace every ' with ''." Do not build your security model on that.

Escaping is easy to apply in the wrong context, forget on one code path, or use for a value when the bug is actually an identifier, sort direction, raw filter fragment, or whole expression. It also asks every developer to remember the correct rule every time. That is not a defense; that is a recurring incident waiting for one missed branch.

The practical rule is simpler: user input is never SQL text. It is a value.

Parameterized Queries

A parameterized query sends SQL with placeholders plus a separate list of values. A prepared statement is the same idea with the SQL shape prepared and reused by the database or driver. The important property is parse-time separation: the database parses the SQL program before values are bound.

Python with psycopg:

cur.execute(
    """
    SELECT session_id,
           customer_email,
           plan_name,
           active,
           internal_note
    FROM app_sessions
    WHERE customer_email = %s
      AND active = true;
    """,
    (email,),
)
rows = cur.fetchall()

JavaScript with pg:

const result = await client.query(
  `
  SELECT session_id,
         customer_email,
         plan_name,
         active,
         internal_note
  FROM app_sessions
  WHERE customer_email = $1
    AND active = true;
  `,
  [email],
);

Do not put quotes around the placeholder. %s and $1 are not string interpolation markers; they are driver placeholders. If email contains ' OR 1=1 --, the database receives that whole thing as one text value. It looks for an email address literally equal to those characters, not a new WHERE clause.

The same habit works for writes:

cur.execute(
    """
    INSERT INTO login_events (customer_email, ip_address)
    VALUES (%s, %s);
    """,
    (email, ip_address),
)

The values are still untrusted. They are just untrusted values now, not executable SQL.

ORMs Do Not Make You Immune

An ORM maps database rows to language objects and usually parameterizes ordinary filters for you. That is helpful, but it is not immunity. ORMs still have escape hatches for raw SQL, and raw fragments can reintroduce the same bug:

// unsafe raw fragment
await db.query(`
  SELECT session_id, customer_email, plan_name
  FROM app_sessions
  WHERE customer_email = '${email}';
`);

Dynamic ORDER BY is another common trap because a column name and sort direction are not value placeholders:

// unsafe: user controls SQL structure
await db.query(`
  SELECT session_id, customer_email, plan_name
  FROM app_sessions
  ORDER BY ${sortColumn} ${sortDirection};
`);

The fix is a whitelist, not a parameter:

const allowedSorts = {
  email: "customer_email",
  plan: "plan_name",
  recent: "session_id",
};
 
const allowedDirections = {
  asc: "ASC",
  desc: "DESC",
};
 
const sortSql = allowedSorts[sortKey] ?? allowedSorts.recent;
const directionSql = allowedDirections[directionKey] ?? allowedDirections.asc;
 
await db.query(`
  SELECT session_id, customer_email, plan_name
  FROM app_sessions
  ORDER BY ${sortSql} ${directionSql};
`);

The only strings that become SQL are strings your code chose from tiny approved maps. User input chooses keys in those maps; it does not become SQL text.

Least Privilege Limits The Blast

Least privilege means the application connects as a database user that can do only what the app needs. If a public search endpoint only needs to read public profile rows, its database user should not be able to read billing notes, run admin reports, or change schema.

Least privilege is the second layer, not the first. Parameterized queries prevent the input from becoming SQL. Least privilege limits the damage if a bug still slips through. Section 19.1 teaches roles and permissions properly; for now, keep the security stack in order:

parameterized queries first  ->  least-privilege users second
prevent the injection        ->  limit the blast radius

Try the attack and the safe lookup below. The vulnerable function returns too much; the ordinary equality query treats the attack as text and returns zero rows.

sql — playgroundlive

CREATE TABLE app_sessions (
session_id integer PRIMARY KEY,
customer_email text NOT NULL,
plan_name text NOT NULL,
active boolean NOT NULL,
internal_note text NOT NULL
);

INSERT INTO app_sessions (session_id, customer_email, plan_name, active, internal_note)
VALUES
(1, 'ada@example.com', 'team', true, 'renewal call booked'),
(2, 'ken@example.com', 'starter', true, 'trial discount approved'),
(3, 'priya@example.com', 'enterprise', false, 'security review failed'),
(4, 'tomas@example.com', 'team', true, 'billing card expiring');

CREATE FUNCTION vulnerable_session_lookup(search_text text)
RETURNS TABLE (
session_id integer,
customer_email text,
plan_name text,
active boolean,
internal_note text
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY EXECUTE
  'SELECT session_id, customer_email, plan_name, active, internal_note FROM app_sessions WHERE customer_email = '''
  || search_text
  || ''' AND active = true';
END;
$$;

SELECT v.session_id,
     v.customer_email,
     v.plan_name,
     v.active,
     v.internal_note
FROM vulnerable_session_lookup(''' OR 1=1 --') v
ORDER BY v.session_id;

-- try it: the safe shape treats the attack as one text value
SELECT s.session_id,
     s.customer_email,
     s.plan_name,
     s.active,
     s.internal_note
FROM app_sessions s
WHERE s.customer_email = ''' OR 1=1 --'
AND s.active = true
ORDER BY s.session_id;

⌘/Ctrl + Enter to run

SQL injection is a boundary bug: code forgot where SQL ends and data begins. Next lesson looks at ORMs more broadly: what they help with, where they hide bad SQL, and why N+1 query loops still show up in mature codebases.

Checkpoint

Answer all three to mark this lesson complete