Skip to content
yutils

How SQL Injection Actually Works

Classic UNION-based vs blind vs time-based injection, why string concatenation always loses, how prepared statements actually work at the wire level, why ORMs don't fully save you, and second-order injection through stored data.

~9 min read

An OWASP Top 10 staple since 1998. Yet 25+ years later it still shows up in new systems. The reason — string concatenation feels too natural. This guide covers the three types (UNION / blind / time-based), exactly how prepared statements stop it at the wire level, and why even ORMs aren't bulletproof.

Classic Injection — UNION-based

// Vulnerable
const sql = "SELECT * FROM users WHERE id = " + userInput;
db.query(sql);

input: 1
SQL: SELECT * FROM users WHERE id = 1   ← OK

input: 1 OR 1=1
SQL: SELECT * FROM users WHERE id = 1 OR 1=1   ← every user!

input: 1 UNION SELECT username, password FROM admin
SQL: SELECT * FROM users WHERE id = 1 UNION SELECT username, password FROM admin
     ← admin data returned as if it were user data

input: 1; DROP TABLE users; --
SQL: SELECT * FROM users WHERE id = 1; DROP TABLE users; --
     ← DB obliterated (with multi-statement enabled)

Blind Injection — Possible Without Errors

Modern apps hide SQL errors from users.
Still exploitable — extract data one bit at a time via true/false responses.

// Vulnerable
const sql = "SELECT * FROM users WHERE id = " + userInput;
const found = db.query(sql).length > 0;
return found ? "exists" : "not found";

Attack:
input: 1 AND substring((SELECT password FROM admin), 1, 1) = 'a'
→ "exists" returned ⇒ admin password's first char is 'a'

Automation:
  for char in [a, b, c, ..., z, 0-9]:
    if response == "exists": first char = char
  → next char, next ... full password extracted in minutes

Time-based Blind — Identical Responses, Only Timing

Even when true/false responses look identical (both "OK"),
attack via response time, one bit at a time.

input: 1; IF (SELECT password FROM admin WHERE id=1) LIKE 'a%' WAITFOR DELAY '0:0:5'

→ Response takes 5s → first char is 'a'
→ Instant response → not 'a'

Automatable. Works even when every endpoint returns 200 OK.

Second-Order Injection — Through Stored Data

Malicious input is only stored at first (not executed). Later, another
query that uses it interpolates it → fires.

Step 1: Register
  username = "admin' --"
  → Stored as-is in DB (simple INSERT, assume escaped)

Step 2: User edits profile (their name flows back into SQL)
  sql = "UPDATE users SET active=1 WHERE name='" + name + "'"
  →     "UPDATE users SET active=1 WHERE name='admin' --'"
                                                    ↑↑↑↑↑
                                                    comment, affects everyone

→ First escape may have been fine, but if there's no re-escape on reuse, it explodes.

Prepared Statements — How They Defend at the Wire Level

// Safe
const sql = "SELECT * FROM users WHERE id = ?";
db.query(sql, [userInput]);

→ At the wire protocol, this splits into 2 messages:

1. Prepare:
   Server <- "PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?'"
   Server -> "Statement ID: 42, expects 1 parameter"

2. Execute:
   Server <- "EXECUTE 42 WITH params: [user_input_as_bytes]"
   Server -> result rows

Key points:
- The SQL string in step 1 has no user input — parsed in advance
- Step 2 sends parameters in a separate binary slot
- The DB NEVER parses those bytes as SQL → they're values

→ Whatever the user input (1 OR 1=1, ', DROP TABLE, ...), it's treated
   as a value. SQL injection is structurally impossible.

ORMs Aren't Fully Safe Either

// Sequelize (Node.js) — safe
User.findOne({where: {id: userInput}});  // ✓ parameterized

// Sequelize — dangerous
const sql = "SELECT * FROM users WHERE id = " + userInput;
sequelize.query(sql);  // ❌ raw query, direct injection risk

// Active Record (Rails) — safe
User.where(name: params[:name])  // ✓

// Active Record — dangerous
User.where("name = '#{params[:name]}'")  // ❌ string interp
User.find_by_sql("SELECT * WHERE id = #{params[:id]}")  // ❌

→ ORM raw/native SQL options are escape hatches — manual check required.

JS template literals (`SELECT * WHERE id = ${id}`) are the same trap.

NoSQL Injection — Same Idea, Different Syntax

MongoDB:
  db.users.find({username: req.body.username, password: req.body.password});

Attack:
  POST {username: "admin", password: {"$ne": null}}
  → password = "not null" matches any non-null value → login bypassed

Fixes:
- Validate input types (string only, reject objects)
- Typed queries via ORM/ODM (Mongoose schemas)
- Libraries like mongo-sanitize

Related Tools

  • SQL Formatter — inspect SQL structure (how the parser interprets it)

Common Pitfalls

  • Hand-rolled escape — quote escaping alone doesn't stop UNION, comments, multi-statement. Only prepared statements are a structural fix.
  • "It's an integer, no escape needed" — if the input is actually a string, it explodes. Always parameterize.
  • Raw queries with an ORM — see above. ORM trust doesn't transit to every query.
  • Ignoring second-order — only escaping at first touch. Anywhere user data flows into SQL — parameterize.
  • Over-privileged DB user — if the app user has DROP TABLE rights, SQL injection is catastrophic. Principle of least privilege (separate read vs write users).

Wrap-up

SQL injection has been known since 1998 yet appears in new systems every year — string concatenation is just too natural. The defense is singular: every user value through a prepared-statement parameter.

Practical: stick to your ORM's default API (avoid raw query), don't build SQL with template literals, minimize DB user privileges, never leak SQL error messages to users (info leak).

Related tools

← Back to guides