SQL injection happens when user input is concatenated into a query. It is old, well understood and still found in production because string building is convenient.
Learning objectives
- Recognise injectable code in Node and SQL.
- Fix it with prepared statements and parameter binding.
- Validate input without treating validation as the defence.
- Apply least privilege to the database account.
1. The vulnerable query
app.get("/search", (req, res) => {
const name = req.query.name;
const rows = db
.prepare(`SELECT * FROM products WHERE name LIKE '%${name}%'`)
.all();
res.json(rows);
});
Request:
/search?name=' UNION SELECT id, email, password FROM users --
The database executes the attacker's query. Depending on the driver, ; DROP TABLE products -- is also possible.
Login bypass:
const query = `SELECT * FROM users WHERE email = '${email}' AND password = '${password}'`;
// email = "[email protected]' --"
The password check becomes a comment.
2. Prepared statements
The SQL text is fixed first; values are sent separately and never parsed as SQL.
const searchByName = db.prepare(
"SELECT id, name, price, category FROM products WHERE name LIKE ? ORDER BY name ASC",
);
app.get("/search", (req, res) => {
const name = String(req.query.name ?? "").trim();
const rows = searchByName.all(`%${name}%`);
res.json({ success: true, count: rows.length, data: rows });
});
const findByEmail = db.prepare("SELECT id, email, password_hash FROM users WHERE email = ?");
const user = findByEmail.get(email);
Named parameters are equally safe:
db.prepare("SELECT * FROM products WHERE category = @category AND price <= @max")
.all({ category, max: Number(maxPrice) });
3. What you cannot parameterise
Identifiers and SQL keywords cannot be bound:
// column name from a query string — dangerous
db.prepare(`SELECT * FROM products ORDER BY ${req.query.sort}`).all();
Use an allow-list:
const sortable = {
name: "name",
price: "price",
newest: "created_at DESC",
};
const orderBy = sortable[req.query.sort] ?? "name";
const rows = db.prepare(`SELECT * FROM products ORDER BY ${orderBy}`).all();
The interpolation is safe here because the value comes from your own map, never from the request.
4. ORMs help, but are not magic
Prisma, Sequelize and Mongoose parameterise normal queries. Raw escape hatches do not:
// Prisma — safe
await prisma.user.findMany({ where: { email } });
// Prisma raw — dangerous if interpolated
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);
// Prisma raw — safe
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;
Mongoose $where, mapReduce and unvalidated operators in filters ({ $ne: null } from req.query) have their own injection class — validate and whitelist query operators.
5. Defence in depth
- Prepared statements everywhere. This is the actual fix.
- Validate types, lengths and formats at the boundary. Reject early with
400. - Least privilege: the app's database user needs
SELECT/INSERT/UPDATE/DELETEon its tables — notDROP, not access to other schemas. Never connect as root. - Do not leak errors: a stack trace reveals table and column names.
- Log suspicious input and monitor for repeated failures.
Validation alone is not enough: a valid-looking string can still carry a payload.
6. A safe login
app.post("/login", async (req, res) => {
const email = String(req.body.email ?? "").trim().toLowerCase();
const password = String(req.body.password ?? "");
if (!email || !password) {
return res.status(400).json({ error: "Email and password are required" });
}
const user = findByEmail.get(email);
const ok = user && (await bcrypt.compare(password, user.password_hash));
if (!ok) {
return res.status(401).json({ error: "Invalid credentials" });
}
req.session.userId = user.id;
res.json({ success: true });
});
One generic error for both unknown email and wrong password — do not tell an attacker which accounts exist.
Common mistakes
- Escaping quotes manually instead of using parameters.
- Assuming an ORM makes raw SQL safe.
- Returning database errors to the client.
- Using the same privileged database account for migrations and the app.
- Trusting client-side validation.
Practice
Fix the vulnerable search and login in the assignment. Then try the original payloads against your fixed version.