SQL injection
See how untrusted input rewrites database queries, then fix it with parameterised statements.
Learning objectives
- Recognise string-built SQL queries as injection risks
- Rewrite queries with parameter placeholders
- Protect dynamic identifiers such as column names with allowlists
The vulnerability
SQL injection happens when user input becomes part of the SQL command itself, instead of being treated as data. The database then cannot tell the attacker’s text from the developer’s query.
// VULNERABLE: user input is concatenated into the SQL string
app.get('/users', async (req, res) => {
const { name } = req.query;
const rows = await db.all(
`SELECT id, name, email FROM users WHERE name = '${name}'`
);
res.json(rows);
});
If name is ' OR '1'='1, the query becomes:
SELECT id, name, email FROM users WHERE name = '' OR '1'='1'
That condition is always true, so every user is returned. Worse payloads can read other tables, change data or drop them, depending on the database and driver.
The fix: parameterised queries
Send the SQL structure and the values separately. The database treats the values only as data, never as SQL.
// SAFE: placeholder (?) plus a separate array of values
const rows = await db.all(
'SELECT id, name, email FROM users WHERE name = ?',
[name]
);
Placeholder syntax varies by driver. PostgreSQL uses $1, $2, and so on. Most ORMs and query builders generate parameterised queries for you, but raw SQL inside them still needs the same care.
What parameters cannot protect
Placeholders work for values. They do not work for identifiers such as table names, column names, and sort direction. Use an allowlist for those:
const SORTABLE_COLUMNS = { name: 'name', created: 'created_at' };
const DIRECTIONS = { asc: 'ASC', desc: 'DESC' };
const column = SORTABLE_COLUMNS[req.query.sort] ?? 'name';
const direction = DIRECTIONS[req.query.dir] ?? 'ASC';
const rows = await db.all(
`SELECT id, name, email FROM users ORDER BY ${column} ${direction}`
);
Because the values come from a fixed map, nothing user-controlled reaches the SQL text.
Defence in depth
- Least privilege. The application’s database account should not own tables or be able to drop them.
- Limit error detail. Do not show raw database errors to users. They reveal structure that helps attackers.
- Do not rely on a WAF. A web application firewall can reduce noise but is not a fix for the code.
Lab: find and fix it
Only run this against a local app and database that you own.
- Create a local SQLite database with a
userstable. - Build the vulnerable route above and send the payload
' OR '1'='1to it. - Confirm that every row is returned.
- Replace the query with the parameterised version.
- Send the same payload again and confirm it is now treated as a plain string that matches nothing.
Key takeaways
- Never build SQL by concatenating or interpolating user input.
- Use placeholders for values and allowlists for identifiers and keywords.
- Keep the database account’s privileges as small as the app needs.