SQL Injection: How It Works and How to Prevent It

SQL injection happens when user input is pasted into a SQL string, letting an attacker change the query itself. It's old, well understood and still found in production code.

The vulnerability

// email comes from a login form
const sql = `SELECT * FROM users WHERE email = '${email}' AND password_hash = '${hash}'`;

Enter ' OR '1'='1' -- as the email, and the query becomes:

SELECT * FROM users WHERE email = '' OR '1'='1' --' AND password_hash = '...'

The condition is always true and the rest is commented out. Worse payloads can read other tables or modify data.

The fix: parameterised queries

// node-postgres
const { rows } = await pool.query('SELECT * FROM users WHERE email = $1', [email]);

// mysql2
const [rows] = await db.execute('SELECT * FROM users WHERE email = ?', [email]);

cur.execute("SELECT * FROM users WHERE email = %s", (email,))

The query structure and the data travel separately, so input can never become SQL code. ORMs and query builders (Prisma, Drizzle, SQLAlchemy, Django ORM) do this for you, as long as you avoid their raw-string escape hatches.

Dynamic identifiers: allow-list them

Placeholders work for values, not for column names or sort directions. Map user choices to known-safe SQL:

const SORTS = { newest: 'created_at DESC', price_low: 'price ASC', price_high: 'price DESC' };
const orderBy = SORTS[req.query.sort] ?? SORTS.newest;

await pool.query(`SELECT * FROM products WHERE category = $1 ORDER BY ${orderBy} LIMIT 20`, [category]);

Defence in depth

• Run the app with a database user that has only the permissions it needs (no DROP, no access to unrelated schemas).
• Validate input types and formats at the API boundary.
• Don't show database error messages to users.
• Watch for the same class of bug elsewhere: NoSQL query objects built from raw JSON, shell commands, LDAP filters and LLM prompts that mix instructions with untrusted text.