SQL injection - System Design Guide

Overview

SQL injection (SQLi) happens when untrusted input is concatenated into a SQL statement so that the input changes the query's structure. A login query built as "SELECT * FROM users WHERE email = '" + email + "'" lets an attacker enter ' OR '1'='1 and log in without a password, or read, modify, and delete data.

The fix is simple and absolute. Always use parameterized queries (prepared statements), where the query structure is sent separately from the values, so input is always treated as data. Defense in depth adds least-privilege database accounts, allow-list validation for identifiers such as sort columns, and monitoring.

A fill-in-the-blank form

A parameterized query is a printed form with boxes; whatever someone writes in the name box stays a name. String concatenation is letting the person rewrite the form's instructions.

When to use it

Any code that queries a database with user-influenced values.
Security reviews and threat modeling.
Interview discussions of API security (OWASP Top 10 injection).

Where it shows up in interviews

Secure data access

Recognize it when: user input reaches database queries.

Design a secure login API
Design a search endpoint with filters and sorting

Where it is used in real software

OWASP Top 10

Injection, including SQLi, has been one of the most critical web application risks for two decades.

Major breaches

The 2008 Heartland Payment Systems breach used SQL injection to steal over 100 million card numbers.

ORMs and query builders

Prisma, Hibernate, and SQLAlchemy parameterize values by default, but raw query escape hatches can reintroduce the risk.

Key terms

Parameterized query: SQL with placeholders ($1, ?) whose values are bound separately.
Prepared statement: Query compiled once and executed with bound parameters.
Second-order injection: Stored input later used unsafely in another query.
Blind SQLi: Attacker infers data from true/false or timing differences.
Least privilege: The app's DB user has only the permissions it needs.

How it works, step by step

1
Find every query built from input
Search for string concatenation or template literals in SQL.
2
Replace with parameters
Placeholders for all values, never string building.
3
Allow-list identifiers
Table names, columns, and sort directions cannot be parameters; map them from a fixed list.
4
Restrict database permissions
No DROP or admin rights for the application user.
5
Test and monitor
Static analysis, security tests, and alerts on SQL errors.

How injection changes a query

Input

Concatenated SQL

Database

STEP 1The attacker types the email: ' OR '1'='1' --

Vulnerable vs safe patterns

Common cases

Case	Vulnerable	Safe
Login lookup	WHERE email = '${email}'	WHERE email = $1 with [email]
Search	LIKE '%${q}%'	LIKE $1 with ['%' + q + '%']
Sort column	ORDER BY ${sort}	Map sort to an allow-listed column name
IN list	IN (${ids.join(',')})	= ANY($1) with an array parameter

NOWCase: Login lookup | Vulnerable: WHERE email = '${email}' | Safe: WHERE email = $1 with [email]

Values become parameters; identifiers come from allow-lists. Nothing from the user is spliced into SQL text.

Implementation

1// VULNERABLE: input becomes part of the SQL text2// db.query(`SELECT id FROM users WHERE email = '${email}'`);3 4// SAFE: values are sent separately from the query structure5async function findUser(email: string) {6  const { rows } = await db.query("SELECT id, name FROM users WHERE email = $1", [email]);7  return rows[0];8}9 10// Identifiers cannot be parameters: map from an allow-list11const SORTABLE = { newest: "created_at DESC", price: "price_cents ASC", name: "name ASC" } as const;12 13async function searchProducts(q: string, sort: string) {14  const orderBy = SORTABLE[sort as keyof typeof SORTABLE] ?? SORTABLE.newest;15  const { rows } = await db.query(16    `SELECT id, name, price_cents FROM products WHERE name ILIKE $1 ORDER BY ${orderBy} LIMIT 50`,17    [`%${q}%`],18  );19  return rows;20}

Complexity and performance

Fix effortLow per query

Mechanical replacement with parameters.

Impact if missedFull data breach

Read, modify, or delete any data the DB user can access.

Trade-offs

ORMs vs raw SQL

ORMs parameterize automatically but raw-query escape hatches and dynamic identifiers still need care; raw SQL is fine when always parameterized.

Escaping vs parameters

Manual escaping is error-prone and database-specific; parameters are the only reliable defense.

Variants and related techniques

NoSQL injection

Passing user-controlled objects into MongoDB queries (e.g. {"$ne": null}) can bypass filters.

Command and LDAP injection

The same class of bug in shell commands and directory queries.

Common mistakes

Relying on input validation alone.
Fix: Validation helps but parameterization is the actual defense.
Parameterizing values but concatenating ORDER BY or table names.
Fix: Use allow-lists for identifiers.
Returning raw database errors to clients.
Fix: Log details internally; return generic messages to avoid aiding attackers.

Interview questions

How do you prevent SQL injection?

Use parameterized queries or prepared statements for every value, allow-list any dynamic identifiers like sort columns, run the application with a least-privilege database user, avoid exposing SQL errors, and add static analysis and security testing.

Why do prepared statements prevent injection?

The query structure is parsed before values are supplied, and values are bound as data, so no input can change the SQL's syntax regardless of quotes or keywords it contains.

Practice problems

Problem	Difficulty	What it trains
Fix 5 vulnerable queries	Easy	Parameterization and allow-lists.
Design a safe dynamic filter API	Medium	Building queries from user filters safely.