SQL Injection

The most dangerous bug in web development

SQL injection has been the #1 web vulnerability for over two decades. It lets an attacker run arbitrary SQL queries on your database. They can read all your data, modify it, delete it, or extract your entire database schema.

It happens when you build SQL queries by concatenating user input into the query string.

The vulnerable code

Look at the search route from the project setup:

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

The query variable comes from the URL: ?q=test. When the user searches for “test,” the SQL becomes:

SELECT * FROM notes WHERE user_id = 'user-1' AND title LIKE '%test%'

This works perfectly. But what if the user searches for something that is not a keyword?

Attack 1: Read other users’ data

Search for: ' OR '1'='1

The SQL becomes:

SELECT * FROM notes WHERE user_id = 'user-1' AND title LIKE '%' OR '1'='1%'

The OR '1'='1' is always true. The query returns every note in the database, including the admin’s secret note.

curl -b cookies.txt "http://localhost:3000/notes/search?q=%27%20OR%20%271%27%3D%271"

You should see all three notes, including the admin’s note.

Why it works

The database cannot distinguish between your SQL code and the attacker’s input. When you concatenate, everything is parsed as SQL. The attacker’s ' ends the string literal, and everything after is code. Code and data are mixed in the same string.

Attack 2: Extract the database schema

Search for: ' UNION SELECT sql, '', '', '', '', '' FROM sqlite_master --

This uses UNION SELECT to append the results of a different query — one that reads sqlite_master, which contains the SQL used to create every table. The -- comments out the remaining SQL. The attacker now knows your entire table structure.

The fix: parameterized queries

Parameterized queries separate code from data. You put ? placeholders where data goes and pass values separately:

// BEFORE (vulnerable)
const notes = db
  .prepare(`SELECT * FROM notes WHERE user_id = '${user.id}' AND title LIKE '%${query}%'`)
  .all();

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

The ? tells the database: “This is data, never interpret it as code.” Even if the attacker sends ' OR '1'='1, the database searches for that literal string instead of executing it as SQL.

Update the search route in src/routes/notes.ts with the safe version. Try the attack again — you should get an empty array.

The rule

Never build SQL queries by concatenating user input. Always use parameterized queries. This is the complete fix for SQL injection. If you never concatenate, you cannot have SQL injection.

Exercises

Exercise 1: Before fixing, try the UNION SELECT attack to extract the schema. What tables do you see?

Exercise 2: Apply the fix. Try all attacks again. All should fail.

Exercise 3: Review every db.prepare() in your codebase. The login route and note creation already use ? placeholders. The search route was the only vulnerable one.

Exercise 4: Search for %. The % is a LIKE wildcard — it returns all notes. This is not SQL injection (the value is parameterized), but it is a LIKE injection issue we cover next.