Safe queries

What makes a query safe?

A query is safe from injection when the SQL text is fixed by you and every value that came from outside travels separately. The database driver sends the command and the values as two parts. The database works out the command's shape first and then slots the values in, so no value can change what the command does.

That gives you one rule that covers almost every case: the first argument to db.all, db.get, db.run or pool.query should be a string you could print and read without knowing anything about the visitor. If reading it requires knowing what the visitor typed, it is unsafe.

In the task, Spoke and Chain's booking route already followed that rule. The repair lookup and the hire search broke it in two different ways, which is common: a codebase is often safe in one place and glued in the next, depending on who wrote each route and when.

Which placeholder style does my database use?

The placeholder character belongs to the database driver, the package your code uses to talk to the database. Using the wrong one gives an error straight away, which is easy to notice and fix.

• SQLite with better-sqlite3 or sqlite3: ?, or named ones such as @bike and :bike.
• MySQL and MariaDB with mysql2: ?. Use execute for a true prepared statement; query fills the values in on the client side with careful escaping.
• PostgreSQL with pg: numbered $1, $2, in the order of the values list.
• Query builders and ORMs such as Knex, Prisma, Drizzle and Sequelize: placeholders are used for you when you call their normal methods.

How do you write a LIKE search safely?

The hire search wanted any bike whose name contains a word, so the SQL used LIKE '%word%'. It is tempting to keep the % signs in the SQL and put the placeholder inside the quotes, as in LIKE '%?%'. That does not work: a ? inside quotes is an ordinary question mark, so the driver ignores it.

The % signs are part of the value. Build the search text in JavaScript and pass the whole thing as one value, as you did in the task. A visitor can still type % or _ themselves, which act as wildcards and make a search match more than you expected. That is harmless for a list of hire bikes. For anything sensitive, escape those two characters in the value and add ESCAPE '\' to the SQL.

const sql = 'SELECT name, size, day_rate FROM hire_bikes WHERE name LIKE ?';
res.json(db.all(sql, ['%' + req.query.q + '%']));

Is it fine to keep SQL in a variable?

Yes, as long as the variable holds fixed text. Long queries often read better when they sit on their own line, and naming them can help. The lab follows sql back one step to see how it was made, and so should you when you review code.

The danger is that the variable makes the gluing easy to miss. In the starting code, line 13 built sql with ${req.query.q} and line 14 sent it. A reviewer reading only line 14 would see db.all(sql) and assume it was fine. The habit to build is to check the place the SQL string was created, every time.

What about sort orders and column names?

Placeholders work for values: names, numbers, dates, search words. They cannot fill in the names of tables or columns, or keywords such as ASC and DESC. When the shop lets customers sort hire bikes by name or by price, the column name has to go into the SQL text itself.

The safe approach is an allow-list. Your code keeps a short list of the columns customers may sort by and picks one of those strings, never the visitor's own text. If the visitor sends anything else, the code falls back to a default. The text that reaches the SQL is always one you wrote.

The lab is simpler here. It sees the + in a line like the one below and marks the query as glued, even though only your own fixed strings can reach it. A real reviewer would read the allow-list and accept it. That is one of the places where the lab's pattern rules are stricter than reality, so keep this example in your notes and out of the lab.

const columns = { name: 'name', price: 'day_rate' };
const sort = columns[req.query.sort] || 'name';
db.all('SELECT name, day_rate FROM hire_bikes ORDER BY ' + sort);

Do ORMs and query builders make this automatic?

For their normal methods, yes. A call such as prisma.booking.findMany({ where: { phone } }) sends phone as a value. Every one of these tools also has an escape hatch for raw SQL, and that is where injection comes back. Prisma's $queryRawUnsafe, Sequelize's sequelize.query with a glued string and Knex's knex.raw with + all behave like the code you fixed.

Several libraries offer tagged templates, written as `sqlSELECT ... WHERE id = ${id} . These look like the unsafe template strings, with one difference: the sql tag in front receives the values separately and turns each ${} into a placeholder. The tag is what makes them safe. Remove it and you have plain gluing again. The lab treats a sql` tag as parameterised for this reason.

How do you check your own queries?

Search the project for the names of your database calls, such as .query(, .all(, .get(, .run( and .raw(. For each one, find where its SQL text was made and confirm that it contains no visitor value. It takes a few minutes on a small site.

Then test by hand on your local copy. Type a single quote into each search box and form field, and add ' to ids in the address. A database error or an odd result means that path still reaches the SQL. On a properly converted site, a quote behaves like any other character, and a customer called O'Brien can book a bike.

What do AI tools get wrong about safe queries?

Asked to add a filter or a search to an existing route, assistants often keep the placeholders that were there and append the new condition with + or ${}, which is how the half-converted PUT route in Break it happens. Read the whole SQL string after every change, including the parts you did not ask about.

They also mix up placeholder styles, writing $1 for SQLite or ? for pg, and they sometimes write LIKE '%?%', which never matches what you expect. When an assistant reaches for $queryRawUnsafe or knex.raw to make something work, ask whether the normal query method can do the same job. It usually can.

Field notes

• Every value from a visitor goes in through a placeholder, never through joining strings.
• SQLite and MySQL use ?, while Postgres with pg uses $1, $2.
• For a LIKE search, add the % signs to the value, never inside the SQL.
• Column names and ASC or DESC cannot be placeholders, so pick them from a fixed list.

Easy to get wrong

One placeholder does not protect the rest of the string. Every value needs its own, in the same order as the list.

A ? inside quotes is a literal question mark. The wildcards belong in the value.

Placeholders cannot hold column names. An allow-list makes sure only text you wrote reaches the SQL.

Questions people ask

Are prepared statements slower?

Not in any way you would notice on a small site. Some databases reuse the plan for repeated queries, which can make them slightly faster.

Do I still need to validate input?

Yes, for a different reason. Validation keeps data sensible, such as a phone number that looks like one. Placeholders keep data from becoming SQL. Use both.

What about stored procedures?

They are safe only if they also use parameters inside. A procedure that builds SQL from its arguments with string joining has the same problem.

Does this apply to NoSQL databases like MongoDB?

A version of it does. Passing a visitor's JSON straight into a MongoDB filter can let them send operators such as $ne. Check that values are plain strings or numbers before using them.