阅读中文版本

SQL Injection via String Concatenation — Parameterize Queries

Eliminate SQL injection by replacing string concatenation with parameterized queries, validating input, and using an allowlist for identifiers.

curl -i "https://api.example.com/search?q=%27%20OR%201%3D1%20--%20"

grep -rn "'+.*\|${.*}" src/ | grep -i "select\|insert\|update\|delete"

Symptoms

This guide profiles the failure behind “SQL Injection via String Concatenation — Parameterize Queries”. It walks through the observable symptoms, a reproducible diagnosis path, the underlying root cause, the concrete fix, and how to verify the change and prevent a regression.

Observed symptoms include:

• An input such as ’ OR 1=1 – returns unexpected rows
• A username containing a quote breaks the query
• Logs show SQL containing user input verbatim

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Reproduce with an injection probe

curl -i "https://api.example.com/search?q=%27%20OR%201%3D1%20--%20"

Extra rows or a changed result order proves user input reaches the query unescaped.

Step 2: Find concatenation sites

grep -rn "'+.*\|${.*}" src/ | grep -i "select\|insert\|update\|delete"

Every query built by concatenation is a candidate; replace with bound parameters.

Likely contributors, in order of frequency:

• User input is concatenated into the SQL string
• Parameters are interpolated in a template literal instead of bound
• Dynamic identifiers (table/column names) come from user input without an allowlist

Root Cause

SQL text and data share one channel when input is concatenated, so the database cannot distinguish code from data. An attacker inserts SQL that becomes part of the statement, executing arbitrary clauses.

The Fix

Use parameterized queries (placeholders bound by the driver) for all values, and for identifiers that cannot be parameters, validate against a strict allowlist before interpolation.

Watch out: ORMs are not automatically safe; raw or literal fragments can still allow injection if values are concatenated.

// ❌ concatenation -> injection
// const sql = `SELECT * FROM users WHERE email = '${email}'`;

// ✅ parameterized query (values are bound, never parsed as SQL)
const { rows } = await db.query(
  'SELECT id, email FROM users WHERE email = $1',
  [email],
);

// dynamic identifier: allowlist, never interpolate raw input
const COLS = new Set(['created_at', 'name']);
const col = COLS.has(sort) ? sort : 'created_at';
const q = `SELECT * FROM users ORDER BY ${col} LIMIT $1`;

Verify and Prevent

Resend the injection probe and confirm it returns no extra rows; verify a legitimate query still works and logs show placeholders.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.