The Complete SQL Injection Prevention Guide: Principles, Attacks, and Defense in Depth

The conclusion: structure goes through the compiler, data goes through parameters

SQL injection is not caused by "a quote that wasn't filtered"; it happens when query structure and user data are concatenated into one string. Let the code own the structure and let data travel only through parameters, and injection has nowhere to stand. What follows runs from "why it works" to "why parameterization works" to "which patterns only look safe".

Why the attack works: input → sink → impact

Input Sink Impact
' OR '1'='1' -- A WHERE clause in login or lookup queries The condition is always true; auth bypass or unauthorised reads
' UNION SELECT ... A SELECT result set Rows from other tables appended to the response
A function that raises an error, or a malformed type Error message reflected to the client Table names, column names, and SQL fragments leak in the error
Boolean or timing payloads Differences in response body or latency Read arbitrary data one bit at a time
' OR 1=1 -- stored first A second concatenation in admin tools, jobs, or reports Harmless as stored text, detonated when the backend runs it

The principle is one sentence: the database treats input as part of SQL syntax, so the attacker can rewrite the statement structure, not merely one value.

Sinks that get overlooked

Developers watch the login form; the real entry points are places that "look numeric or fixed":

  • order by, limit, filter field names — values can be parameterized, but column names cannot;
  • report builders, exports, and audit search;
  • LIKE patterns;
  • second queries triggered by cron jobs, queue consumers, and admin panels;
  • tenant_id taken from a request header in multi-tenant systems;
  • dynamic SQL inside database functions and stored procedures.

Why parameterization breaks the chain

The point is not "escaping more thoroughly" but that data never enters parsing:

  1. The application sends the statement with placeholders first; the database parses it into a syntax tree, where a placeholder is a value token in the grammar.
  2. The parameters arrive afterwards as separate messages; the database only type-checks and binds them, never lexing or parsing them again.
  3. So ' OR 1=1 -- reaches the database as an ordinary string — quotes, spaces, and comment markers cannot change an already-fixed syntax tree.

Escaping always keeps data inside the same string, so its safety depends on the escape rules covering every exception of the parser. Parameterization moves data out of the grammar, removing the precondition for injection. One patches holes; the other makes the hole impossible.

// ❌ Structure and data in one string
db.query(`SELECT * FROM users WHERE email = '${email}'`)

// ✅ Structure fixed, data separate
db.query('SELECT * FROM users WHERE email = $1', [email])

Parameterization also fixes two adjacent problems: the database type-checks values against placeholders, removing the guesswork; and prepared statements reuse the parsed result, cutting repeated parsing.

Three "parameterized-looking" mistakes

ORM raw escape hatches

ORMs are safe by default but always ship an escape hatch, and the hatch usually swallows a string:

// ❌ Interpolation inside literal / raw / whereRaw bypasses parameterization
User.findAll({ where: sequelize.literal(`role = '${role}'`) })
db.query(`SELECT * FROM users WHERE name LIKE '%${term}%'`)

Sort fields are subtler because they are not in the WHERE clause:

// ❌ The field name comes from the user; the ORM splices it into SQL
User.findAll({ order: [[req.query.sort, 'DESC']] })

// ✅ Allowlist mapping with a safe fallback
const SORT = { name: 'name', created: 'created_at' }
const col = SORT[req.query.sort] ?? 'created_at'
User.findAll({ order: [[col, 'DESC']] })

Second-order concatenation: query one is safe, query two is not

Parameterization protects only the statement it appears in. If code reads a value and then concatenates it into another SQL string — report generation, audit search, batch updates, dynamic SQL in procedures — query two's risk has nothing to do with query one.

const rows = await db.query('SELECT name FROM tags WHERE id = $1', [id])  // ✅ Safe

await db.query(`UPDATE reports SET filter = '${rows[0].name}'`)           // ❌ From the database, still untrusted

The test is not "where did the value come from" but "did it end up inside SQL text". Historical rows, third-party responses, and admin-entered content can all carry a payload from an earlier injection.

Wide characters: escaping breaks under certain charsets

This is the classic counterexample to the escaping approach. In GBK, addslashes inserts a backslash 0x5c before a quote, but 0xbf 0x5c is a valid double-byte GBK character. An attacker submits %bf%27; after escaping it becomes %bf%5c%27, the parser consumes bf 5c as one character, the backslash disappears, and the quote is exposed again.

The conditions are precise:

  • The connection charset is a double-byte encoding such as GBK, GB2312, or SJIS;
  • The defence relies on an escape function (addslashes, a hand-written replace);
  • Client and server charset declarations disagree (for example, a missing SET NAMES).

The fix is not a different escape function but dropping the escape-first design: parameterize, or set the charset explicitly and pair it with allowlisting. LIKE has a cousin bug — a literal % or _ in user input must be escaped, not left to shape the match.

What to do where you cannot parameterize

Column names, table names, and sort directions do not accept placeholders, so use an allowlist map: treat user input as a dictionary key, fall back to a default when missing, and never splice the input itself.

// ✅ Allowlist plus default; input never enters SQL text
const SORT_FIELDS = { name: 'name', created: 'created_at', price: 'price' }
const sortField = SORT_FIELDS[req.query.sort] || 'created_at'
const orderDir = req.query.order === 'desc' ? 'DESC' : 'ASC'

db.query(`SELECT * FROM products ORDER BY ${sortField} ${orderDir} LIMIT $1`, [limit])

Whether LIMIT can be parameterized depends on the database and driver — verify in your project. Where it cannot, coerce to an integer and clamp it to a sane range before it reaches SQL.

Least privilege: it caps the damage after a breach

Parameterization lowers the chance of injection; least privilege lowers the loss once it happens. Neither replaces the other:

Account Privileges Purpose
Application SELECT / INSERT / UPDATE on business tables only Serving requests
Migration DDL (create, alter, index) Deployments and migrations, stored separately
Reporting Read-only, a read replica when possible Analytics and exports
Admin Full Manual operations, never in application config

Three more rules: disable detailed error output in production; do not expose the database to the public internet; audit access to sensitive tables.

A fixed example: searchable list

// ❌ Concatenation opens sort, filter, and pagination at once
app.get('/api/products', (req, res) => {
  const sql = `SELECT * FROM products WHERE name LIKE '%${req.query.q}%'
               ORDER BY ${req.query.sort} LIMIT ${req.query.limit}`
  db.query(sql, (err, rows) => res.json(rows))
})

// ✅ Filter as a parameter, sort via allowlist, pagination coerced to a number
app.get('/api/products', async (req, res) => {
  const q = String(req.query.q ?? '')
  const sortField = SORT_FIELDS[req.query.sort] || 'created_at'
  const limit = Math.min(Math.max(parseInt(req.query.limit, 10) || 20, 1), 100)

  const rows = await db.query(
    'SELECT * FROM products WHERE name LIKE $1 ORDER BY ' + sortField + ' DESC LIMIT $2',
    [`%${q}%`, limit]
  )
  res.json(rows)
})

Note that sortField is still concatenated on purpose: column names cannot be parameterized, so its safety comes from being a constant out of the allowlist, never from user input. If a literal % in q must match, escape it inside the parameter value.

Pre-launch checks

  • [ ] Every SQL string is a code constant; user input appears only in parameter positions
  • [ ] ORM raw / literal / whereRaw / orderBy all pass through an allowlist
  • [ ] No code path concatenates one query's results into another SQL statement
  • [ ] Column names, table names, and sort directions come only from mapping tables
  • [ ] The application account has no DDL rights; reporting uses a read-only account
  • [ ] Production never echoes SQL errors; exceptions surface as generic messages

Reproducible results

Run the same query against an in-memory SQLite database and the difference between concatenation and parameterization is immediate:

import sqlite3
db = sqlite3.connect(':memory:')
db.execute("CREATE TABLE users (name TEXT, role TEXT)")
db.execute("INSERT INTO users VALUES ('alice', 'user')")

q = "' OR 1=1 --"
db.execute(f"SELECT * FROM users WHERE name = '{q}'")   # Injection: the condition is always true; every user returns
db.execute("SELECT * FROM users WHERE name = ?", (q,))  # Parameterized: q stays a string; nothing returns

The concatenated query makes WHERE always true and returns every user; the parameterized one treats q as a string and returns nothing. What changed is statement structure, not a value. To inspect structure before debugging, use the SQL formatter; syntax lives in the SQL reference.

Frequently Asked Questions

Why do parameterized queries prevent SQL injection?

Prepared statements separate the SQL structure from the data entirely: the statement template is pre-compiled on the server, and user input is bound only as a parameter value, which the database engine treats as data rather than executable code. So even if a user types ' OR '1'='1, it is only a string literal and can never alter the statement structure. This is the first and most effective defense, and it holds for virtually every mainstream database.

Is using an ORM always safe?

Not necessarily. An ORM's query builder is safe when used correctly (chained, type-safe APIs), but it breaks in three cases: concatenating raw SQL (raw/query methods) directly with user input; interpolating user-controlled table names, column names, or sort fields into ORDER BY or LIMIT — positions that do not accept placeholders; and skipping input validation because the ORM is assumed to filter. An ORM is just a tool; you still have to uphold the parameterization principle yourself.

Why is blacklist filtering (blocking keywords and quotes) insufficient?

Because injection keeps morphing: encoding bypasses (URL encoding, double encoding, Unicode variants), case and comment obfuscation (/**/, #, --), database-dialect quirks (MySQL's /*!...*/), and keyword-free attacks (numeric injection, stacked queries). Maintaining a blacklist that can never catch up with attacker mutations is essentially 'using regex against a Turing-complete parser' — you lose. The defense should switch to allowlisting: only accept values matching the expected format, reject everything else.

What should I do first if a live system is suspected of being SQL-injected?

Handle in order: ① confirm whether data was read or altered (compare backups, inspect key tables and audit logs); ② if confirmed, isolate first (take the service offline or cut database access), preserve live logs and request samples for forensics — do not rush to clear logs; ③ fix the code — convert every user input to parameterized queries, rotate secrets and credentials for affected fields; ④ notify affected users and evaluate compliance disclosure obligations; ⑤ retrospect: was the DB account on least privilege, are logs retained long enough, and why did monitoring alerts not fire. Stop the bleeding, fix the root, then review — in that order.

← Back to Blog