SQL Injection
How attackers turn input fields into database commands — and how parameterized queries stop them completely
Skills taught: websecurity.sql-injection, websecurity.parameterized-queries, websecurity.orm-security, websecurity.error-disclosure
How SQL injection works
SQL injection (SQLi) happens when user input is concatenated into a SQL query without sanitisation. The attacker's input becomes part of the query's logic — not just a value, but a command. It has been in every OWASP Top 10 since the list began (now under A03 — Injection). A single SQLi vulnerability can result in full database read access, data modification, authentication bypass, and sometimes OS command execution.
// VULNERABLE: user input concatenated directly into SQL
app.get('/user', async (req, res) => {
const { username } = req.query;
// If username = 'alice', the query is:
// SELECT * FROM users WHERE username = 'alice'
// Safe-looking, right? Now see what an attacker does:
const query = `SELECT * FROM users WHERE username = '${username}'`;
const user = await db.query(query);
res.json(user);
});
// ATTACK 1 — Authentication bypass
// username = ' OR '1'='1
// Query becomes: SELECT * FROM users WHERE username = '' OR '1'='1'
// '1'='1' is always true → returns ALL users
// ATTACK 2 — Dump entire table
// username = '; SELECT * FROM users; --
// Query becomes: SELECT * FROM users WHERE username = '';
// SELECT * FROM users; --'
// Drops first query, runs second → all user records
// ATTACK 3 — Drop a table
// username = '; DROP TABLE users; --
// This is a 'classic' but usually fails on modern DBs with limited user perms
// ATTACK 4 — Union attack (exfiltrate other tables)
// username = ' UNION SELECT username, password, email FROM admin_users --
// Appends results from admin_users to the original query
The fix — parameterized queries
// SAFE: parameterized query with pg (node-postgres)
import { Pool } from 'pg';
const db = new Pool();
app.get('/user', async (req, res) => {
const { username } = req.query;
// $1 is a placeholder — the value is passed separately
// The database driver escapes it automatically
// Even if username = "' OR '1'='1", it is treated as a LITERAL STRING
const result = await db.query(
'SELECT id, username, email FROM users WHERE username = $1',
[username] // Value passed separately, never interpolated into SQL
);
res.json(result.rows[0] ?? null);
});
// The database sees:
// SQL: SELECT id, username, email FROM users WHERE username = $1
// Parameters: ["' OR '1'='1"]
// It looks for a user with the literal name "' OR '1'='1" — finds none
// The injection payload is never parsed as SQL
// Other database drivers:
// MySQL (mysql2): db.query('SELECT * FROM users WHERE id = ?', [id])
// SQLite (better-sqlite3): db.prepare('SELECT * FROM users WHERE id = ?').get(id)
// Prisma: prisma.user.findUnique({ where: { id } }) // always safe
ORMs — parameterized by default
// Prisma — all queries are parameterized automatically
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();
// SAFE — Prisma generates parameterized SQL
const user = await prisma.user.findUnique({
where: { username: req.query.username },
select: { id: true, username: true, email: true },
});
// DANGEROUS EVEN WITH PRISMA — raw SQL query
// If you bypass the ORM, you bypass the protection
const result = await prisma.$queryRaw(
`SELECT * FROM users WHERE username = '${req.query.username}'` // ← VULNERABLE
);
// Safe raw query with Prisma (use Prisma.sql template literal):
import { Prisma } from '@prisma/client';
const result = await prisma.$queryRaw(
Prisma.sql`SELECT * FROM users WHERE username = ${req.query.username}`
// Prisma.sql is a tagged template that parameterizes automatically
);
// RULE: Never concatenate user input into SQL, even inside an ORM's raw query
Defence in depth for SQL injection
LAYER 1 — Parameterized queries (primary defence, mandatory)
Never concatenate user input into SQL. Always use ? or $1 placeholders.
LAYER 2 — Least privilege database user
Your app's DB user should only have SELECT/INSERT/UPDATE/DELETE
It should NOT have DROP TABLE, CREATE USER, FILE (reads OS files)
Limits damage if injection occurs
LAYER 3 — Input validation
If an ID should be a number: verify it's a number before the query
parseInt(id) === NaN → reject the request
But don't rely on this as your primary defence — validate AND parameterize
LAYER 4 — Error handling — don't leak schema
NEVER return raw database errors to users:
'ERROR: column "usernaem" does not exist at character 42' tells attacker
your schema, column names, and DB type
Return generic error messages; log the full error server-side only
LAYER 5 — WAF (Web Application Firewall)
Cloudflare and AWS WAF can detect and block common SQLi patterns
Not a replacement for parameterized queries — a last line of defence
Warning: Input escaping is NOT a sufficient defence. You may have seen advice to 'escape user input' (replacing quotes with escaped quotes). This approach is fragile — different databases have different escaping rules, escaping can be bypassed in multi-byte character encodings, and it only works for certain injection contexts (string values, not identifiers). Parameterized queries separate the SQL command from the data at the protocol level — they are the only reliable defence.
Frequently Asked Questions
Can NoSQL databases (MongoDB) be injected?
Yes — 'NoSQL injection' is different from SQL injection but real. MongoDB queries are JavaScript objects: if you take user input and directly use it as a query filter, an attacker can inject query operators. Example: { password: { $gt: '' } } — the $gt operator matches all non-empty passwords, bypassing login. Defence: use Mongoose (which sanitizes input) or manually validate that query values are strings/numbers, not objects.