SQL Injection: Beyond the Basics

The attack gets subtler

The previous lesson showed the classic attack: inject SQL, see the results in the response. But not all injection is that direct.

Blind SQL injection

Sometimes the attacker cannot see query results. The endpoint returns a generic success/failure message, not database rows. The attacker can still extract data by asking the database yes/no questions.

Boolean-based blind injection: The attacker sends a condition and observes whether the response changes.

?q=' AND (SELECT length(password_hash) FROM users WHERE email='[email protected]') > 50 --

If the response includes results, the password hash is longer than 50 characters. If it is empty, the hash is 50 or shorter. By binary searching (> 30? > 40? > 55?), the attacker determines the exact length. Then they extract each character the same way.

This is slow (one request per bit of information), but it is automated with tools. The attacker does not need to do this by hand.

Time-based blind injection: If even the boolean signal is not available, the attacker uses timing. SQLite does not have a SLEEP function, but other databases do:

-- PostgreSQL
?q=' AND (SELECT CASE WHEN (1=1) THEN pg_sleep(5) ELSE pg_sleep(0) END) --

If the response takes 5 seconds, the condition is true. If it returns immediately, the condition is false. Same binary search, just using time instead of content.

The fix is the same: parameterized queries. Blind injection requires the attacker to inject SQL code. If the input is parameterized, there is no injection point.

Second-order injection

Second-order injection is sneakier. The attacker’s input is stored safely in the database, then later used unsafely in a different query.

Imagine a user signs up with the name admin'--. The signup route uses parameterized queries, so the name is stored safely:

// Safe storage
db.prepare("INSERT INTO users (id, name, ...) VALUES (?, ?, ...)").run(id, name);

Later, a different route reads the name and uses it in a query with concatenation:

// Unsafe use of previously stored data
const user = db.prepare("SELECT * FROM users WHERE id = ?").get(userId);
const logs = db.prepare(`SELECT * FROM audit_log WHERE actor = '${user.name}'`).all(); // VULNERABLE

The stored name admin'-- breaks out of the string literal in the second query. The injection happens on the second use, not the first.

The fix: Use parameterized queries everywhere, not just where input first enters the system. Treat database-sourced values as untrusted (remember the trust boundary from lesson 1).

LIKE injection

In the previous lesson, we fixed SQL injection by parameterizing the LIKE query:

db.prepare("SELECT * FROM notes WHERE user_id = ? AND title LIKE ?").all(user.id, `%${query}%`);

This prevents SQL injection. But the user can still manipulate the LIKE pattern. The % and _ characters are LIKE wildcards:

• % matches any sequence of characters
• _ matches exactly one character

If the user searches for %, they get all notes. If they search for _____ (five underscores), they get all notes with a five-character title. This is not SQL injection (no code is executed), but it leaks information the developer may not intend to expose.

The fix: Escape LIKE wildcards in the user’s input:

function escapeLike(input: string): string {
  return input.replace(/[%_\\]/g, "\\$&");
}

const notes = db
  .prepare("SELECT * FROM notes WHERE user_id = ? AND title LIKE ? ESCAPE '\\'")
  .all(user.id, `%${escapeLike(query)}%`);

The ESCAPE '\\' clause tells SQLite to treat \ as an escape character. \% means a literal %, not a wildcard.

Summary

The first three have the same fix. The last one needs an additional step on top of parameterization.

Exercises

Exercise 1: Add the escapeLike function to your search route. Search for % and verify it no longer returns all notes.

Exercise 2: Think about where second-order injection could happen in your app. Are there any routes that read a value from the database and use it in a subsequent query with concatenation?