Most SQL injection bugs we find during audits are not exotic. They are a single line of code where a variable was glued into a query string. This guide takes the diagnosis-then-fix approach: you will see real vulnerable code in Node.js and PHP, the exact payload that breaks it, and the corrected version using parameterized queries, ORM query builders, validation and hardened database permissions. It ends with a short testing routine you can run against your own endpoints before you ship.

Short answer: how do you prevent SQL injection?

To prevent SQL injection, never build SQL by concatenating user input. Instead, layer these defenses:

1. Use prepared statements with bound parameters (parameterized queries) for every query that touches user input. This is the primary fix.
2. Use an ORM or query builder correctly, and pass bindings even in raw queries.
3. Validate and allowlist anything that cannot be parameterized (column names, sort direction, table names, LIMIT values).
4. Apply least privilege to the database user your application connects with.
5. Suppress database errors in responses and log them server side instead.
6. Test before shipping with payload probes, automated scans on staging, and regression tests.

Parameterization alone stops the vast majority of attacks. The other layers exist because one forgotten raw query should not equal a full database dump.

Diagnosis: what SQL injection actually does to your query

A SQL injection happens when the database cannot tell the difference between your instructions and the user’s data. When input is concatenated into the query text, a quote character or a keyword becomes part of the command. This reference is the one worth keeping handy.

Attackers usually chase one of four outcomes:

• Authentication bypass: turning a WHERE clause into something always true.
• Data exfiltration: UNION SELECT to append rows from other tables.
• Blind extraction: boolean or time based probing when no output is visible (for example a SLEEP or pg_sleep delay).
• Write or destroy: UPDATE, DELETE, or stacked queries when privileges and drivers allow it.

Vulnerable sample 1: Node.js with mysql2 string concatenation

// VULNERABLE: input is concatenated into the SQL text
app.get('/api/users', async (req, res) => {
  const email = req.query.email;
  const sql = "SELECT id, email, role FROM users WHERE email = '" + email + "'";
  const [rows] = await pool.query(sql);
  res.json(rows);
});

What breaks it:

Request	Resulting SQL	Impact
?email=' OR '1'='1	... WHERE email = '' OR '1'='1'	Returns the entire users table
?email=' UNION SELECT 1,password_hash,3 FROM users--	Appends a second result set	Password hash dump
?email=' AND SLEEP(4)--	Query pauses before responding	Confirms blind injection point

Vulnerable sample 2: PHP with an unquoted numeric parameter

<?php
// VULNERABLE: no quotes at all, so no quote character is even needed
$id = $_GET['id'];
$result = mysqli_query($conn, "SELECT * FROM orders WHERE id = $id");
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['reference'];
}

Unquoted numeric fields are the easiest targets because the attacker does not need to escape a string. ?id=1 OR 1=1 returns every order. ?id=1 UNION SELECT ... pulls arbitrary columns. And a widespread myth deserves killing here: addslashes(), mysql_real_escape_string() and str_replace(“‘”, “””) are not a defense. They fail on numeric contexts, on multi byte charset tricks, and on identifier contexts.

Vulnerable sample 3: the dynamic ORDER BY that everyone forgets

// VULNERABLE: placeholders cannot be used for identifiers,
// so developers often fall back to concatenation
const sort = req.query.sort;   // "created_at"
const dir  = req.query.dir;    // "desc"
const sql = `SELECT * FROM invoices ORDER BY ${sort} ${dir}`;

This is the most common leftover hole in otherwise parameterized codebases. The fix is an allowlist, shown further down.

Fix 1: prepared statements and parameterized queries

With a prepared statement, the SQL text is sent to the database first, with placeholders. Values are sent separately and are always treated as data, never as syntax. Even if a value contains ' OR 1=1--, the database looks for a literal email address containing that text.

Node.js, MySQL (mysql2)

// FIXED: server side prepared statement with a bound parameter
app.get('/api/users', async (req, res) => {
  const { email } = req.query;
  const [rows] = await pool.execute(
    'SELECT id, email, role FROM users WHERE email = ? LIMIT 50',
    [email]
  );
  res.json(rows);
});

Use execute() rather than query() when you want true prepared statements in mysql2. Also make sure multipleStatements stays disabled in your pool configuration, which is the default.

Node.js, PostgreSQL (pg)

// FIXED: numbered placeholders
const { rows } = await client.query(
  'SELECT id, email FROM users WHERE email = $1 AND status = $2',
  [email, 'active']
);

PHP, PDO (recommended)

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false, // real prepared statements
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT reference, total FROM orders WHERE id = :id AND user_id = :uid');
$stmt->execute([':id' => $_GET['id'], ':uid' => $_SESSION['user_id']]);
$orders = $stmt->fetchAll();

Setting ATTR_EMULATE_PREPARES to false matters. With emulation on, PDO builds the final query string itself, which is still safe in normal cases but removes the strict separation you want, and it hides type errors.

PHP, mysqli

<?php
$stmt = $conn->prepare('SELECT * FROM orders WHERE id = ? AND user_id = ?');
$stmt->bind_param('ii', $id, $userId); // i = integer, s = string
$stmt->execute();
$result = $stmt->get_result();

Parameterizing an IN () list

You cannot bind an array to one placeholder in most drivers. Generate the placeholders from the array length, never from the values:

// Node.js
const ids = req.body.ids.map(Number).filter(Number.isInteger).slice(0, 100);
const marks = ids.map(() => '?').join(',');
const [rows] = await pool.execute(
  `SELECT id, name FROM products WHERE id IN (${marks})`, ids
);

<?php
// PHP
$ids   = array_map('intval', (array) $_POST['ids']);
$marks = implode(',', array_fill(0, count($ids), '?'));
$stmt  = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($marks)");
$stmt->execute($ids);

Fixing the dynamic ORDER BY with an allowlist

Identifiers and keywords cannot be bound. Map user input to values you control:

// FIXED: allowlist mapping, user input never reaches the SQL text
const SORTABLE = { created_at: 'created_at', total: 'total', ref: 'reference' };
const DIRS = { asc: 'ASC', desc: 'DESC' };

const column = SORTABLE[req.query.sort] || 'created_at';
const dir    = DIRS[String(req.query.dir).toLowerCase()] || 'DESC';
const limit  = Math.min(parseInt(req.query.limit, 10) || 20, 100);

const [rows] = await pool.execute(
  `SELECT id, reference, total FROM invoices WHERE user_id = ? ORDER BY ${column} ${dir} LIMIT ${limit}`,
  [userId]
);

The interpolated parts are now constants from a fixed map or an integer clamped by your code. That is the only acceptable pattern for identifier interpolation.

Fix 2: ORMs and query builders (and where they still leak)

ORMs parameterize by default, which is why they remove entire classes of bugs. They do not make you immune, because every one of them offers a raw escape hatch.

Safe by default

// Prisma
const user = await prisma.user.findFirst({ where: { email } });

// Knex
const rows = await knex('users').where({ email }).select('id', 'email');

// Sequelize
const users = await User.findAll({ where: { email } });

<?php
// Laravel Eloquent / query builder
$users = User::where('email', $request->input('email'))->get();

// Doctrine DQL
$q = $em->createQuery('SELECT u FROM App\\Entity\\User u WHERE u.email = :email')
         ->setParameter('email', $email);

The dangerous escape hatches

Tool	Unsafe pattern	Safe pattern
Prisma	$queryRawUnsafe(`... '${email}'`)	$queryRaw`SELECT id FROM users WHERE email = ${email}` (tagged template binds values)
Knex	.whereRaw(`email = '${email}'`)	.whereRaw('email = ?', [email])
Sequelize	sequelize.query('... ' + email)	sequelize.query('... = :email', { replacements: { email } })
Laravel	DB::select("... where email = '$email'")	DB::select('... where email = ?', [$email])
Doctrine	$conn->executeQuery("... = '$id'")	$conn->executeQuery('... = ?', [$id])
WordPress	$wpdb->get_results("... ID = $id")	$wpdb->prepare('... ID = %d', $id)

Rule of thumb for code review: any method name containing “raw”, “unsafe”, “unprepared” or “literal” needs a second pair of eyes. There is more on it in Cheat Sheet: Preventing SQL Injection.

Fix 3: input validation as a second wall

Validation is not a replacement for parameterization. It is what catches the mistake you did not notice, and it also cuts down abuse and garbage data. Validate type, format, length and range, and reject rather than clean whenever you can.

// Node.js with Zod
import { z } from 'zod';

const SearchQuery = z.object({
  email: z.string().email().max(254),
  page:  z.coerce.number().int().min(1).max(500).default(1),
  sort:  z.enum(['created_at', 'total', 'ref']).default('created_at'),
  dir:   z.enum(['asc', 'desc']).default('desc'),
});

app.get('/api/users', async (req, res) => {
  const parsed = SearchQuery.safeParse(req.query);
  if (!parsed.success) return res.status(400).json({ error: 'Invalid parameters' });
  // parsed.data is now typed and bounded
});

<?php
// PHP native validation
$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT, [
    'options' => ['min_range' => 1]
]);
if ($id === false || $id === null) {
    http_response_code(400);
    exit('Invalid id');
}

$email = filter_input(INPUT_GET, 'email', FILTER_VALIDATE_EMAIL);
if (!$email) { http_response_code(400); exit('Invalid email'); }

Two things to remember: validate on the server (client side checks are cosmetic), and be careful with second order injection. A value stored safely today can still be injected tomorrow if some background job or admin report concatenates it into a new query. Parameterize reads, not just writes.

Fix 4: least privilege database users

Least privilege does not stop injection, it caps the damage. If your web app connects as a superuser, one hole means table drops, file reads and sometimes command execution. If it connects with narrow grants, the same hole is a limited data leak.

MySQL / MariaDB

-- Application user: data only, no schema changes, no FILE, no SUPER
CREATE USER 'app_web'@'10.0.1.%' IDENTIFIED BY 'long-random-secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_web'@'10.0.1.%';

-- Separate user for migrations, used only by CI/CD
CREATE USER 'app_migrate'@'10.0.2.%' IDENTIFIED BY 'another-secret';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX, REFERENCES
  ON shop.* TO 'app_migrate'@'10.0.2.%';

FLUSH PRIVILEGES;

PostgreSQL

CREATE ROLE app_web LOGIN PASSWORD 'long-random-secret';
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT CONNECT ON DATABASE shop TO app_web;
GRANT USAGE ON SCHEMA public TO app_web;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_web;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_web;

-- Keep future tables consistent
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_web;

Additional hardening that pays off:

• Read only role for reporting, analytics and dashboards.
• No DDL for the runtime user. Migrations run through a separate credential in your pipeline.
• Deny multi statement execution in the driver so stacked queries fail even if a hole exists.
• Restrict host and network so the database is not reachable from the public internet.
• Rotate credentials and keep them in a secret manager, not in the repository.

Fix 5: stop leaking database errors

Verbose errors turn a slow blind injection into a fast one. Attackers use messages like Unknown column 'x' in 'where clause' to map your schema in minutes.

// Node.js: generic response, detailed server log
app.use((err, req, res, next) => {
  logger.error({ err, path: req.path, reqId: req.id });
  res.status(500).json({ error: 'Internal error', reference: req.id });
});

<?php
// PHP production settings
ini_set('display_errors', '0');
ini_set('log_errors', '1');
error_reporting(E_ALL);
// then catch PDOException and return a generic 500 page

Also log suspicious patterns. Repeated UNION SELECT, SLEEP(, information_schema or comment sequences in query strings are worth an alert. A WAF or your CDN’s managed rules can add an extra filter, but treat it as speed bumps, not as the fix.

Defense comparison at a glance

Defense	Stops injection?	Notes
Prepared statements with bound parameters	Yes	Primary control, works for values only
ORM / query builder used normally	Yes	Raw methods reopen the risk
Allowlist for identifiers and keywords	Yes	Only correct way to handle ORDER BY, table names
Input validation (type, format, range)	Partially	Strong second layer, not sufficient alone
Stored procedures	Partially	Only if they do not build dynamic SQL internally
Manual escaping / addslashes	No	Fails on numeric and identifier contexts
Blacklisting words like “UNION”	No	Trivially bypassed with encoding and case tricks
Least privilege DB user	No	Limits blast radius, mandatory anyway
WAF managed rules	No	Buys time, generates useful alerts

The pre-ship testing routine (about 20 minutes)

Run this on your own staging environment before every release that touches database code. Never scan systems you do not own or have written permission to test.

Step 1: grep for concatenation

# Node.js: template literals and plus signs near query calls
grep -rn "query(\`" src/ | grep '\${'
grep -rn "whereRaw\|queryRawUnsafe\|sequelize.query" src/

# PHP: variables inside query strings
grep -rn "mysqli_query\|->query(" app/ | grep '\$'
grep -rn "DB::select\|DB::statement\|executeQuery" app/

Every hit gets one of three verdicts: already parameterized, converted to bindings, or converted to an allowlist.

Step 2: probe each parameter by hand

Take the endpoint list from your router file and send these payloads into every input, including headers, cookies, JSON bodies and hidden fields:

Payload	Looking for
'	500 error or SQL syntax message
1 OR 1=1	More rows than expected
' AND '1'='2 then ' AND '1'='1	Different responses = boolean based injection
' AND SLEEP(4)-- / ' AND pg_sleep(4)--	Response time jumps = time based injection
%27 and %2527	Decoding layers that bypass filters
sort=id;-- in ORDER BY params	Identifier interpolation

A properly parameterized endpoint returns a normal 200 with zero results, or a clean 400 validation error. It never returns a 500 with driver text.

Step 3: run an automated scan on staging

# sqlmap against a single endpoint, staging only
sqlmap -u "https://staging.example.com/api/[email protected]" \
       --batch --level=2 --risk=1 --dbms=mysql

# Authenticated flows: save a raw request from your browser devtools
sqlmap -r request.txt --batch --level=3

Pair it with the free scanner of your choice (OWASP ZAP baseline scan works well in CI) and run it against a seeded database, never production. It is done convincingly by a design studio worth a look.

Step 4: add regression tests

// Jest example: the payload must be treated as literal data
it('treats injection payload as a plain value', async () => {
  const res = await request(app).get('/api/users').query({ email: "' OR '1'='1" });
  expect(res.status).toBe(400);      // or 200 with an empty array
  expect(res.body).not.toHaveProperty('0.password_hash');
});

One test per historically risky endpoint is enough to stop the bug from coming back after a refactor.

Step 5: wire it into CI

1. Static analysis in the pipeline (Semgrep rules for raw SQL, PHPStan, ESLint security plugins).
2. Dependency audit on every build, since driver and ORM bugs happen too.
3. Fail the build on new findings rather than filing a ticket nobody reads.
4. Keep an inventory of raw queries with a required review owner.

Step 6: verify the blast radius

Connect with the production application credentials in a maintenance window and confirm that DROP TABLE, CREATE TABLE and cross database reads all fail. If they succeed, your grants are too wide.

Quick audit checklist

• Every query with user input uses bound parameters.
• No string concatenation or template interpolation of values in SQL.
• Identifiers, sort direction and LIMIT come from an allowlist or a clamped integer.
• Raw ORM methods are inventoried and reviewed.
• Server side validation on type, format, length and range.
• Stored data is re-parameterized when reused (second order).
• Application DB user has no DDL, no FILE, no superuser rights.
• Multi statement execution disabled in the driver.
• Database errors never reach the browser.
• Payload probes, a staging scan and regression tests run before release.

FAQ

Can SQL injection be prevented completely?

Yes, at the code level. If every query separates SQL text from data using prepared statements, and everything that cannot be parameterized comes from an allowlist, the injection vector disappears. The residual risk is human: one raw query added in a hurry. That is why validation, least privilege and automated testing exist.

Which method prevents SQL injection best?

Parameterized queries, also called prepared statements. They are the only defense that removes the root cause instead of filtering symptoms. Everything else is complementary.

What are the best practices for preventing SQL injection?

Parameterize all queries, use an ORM correctly, allowlist identifiers, validate input server side, run the application with a least privilege database account, hide database errors, and test endpoints with injection payloads before each release.

How do prepared statements prevent SQL injection?

The database receives the query structure first and compiles the execution plan with placeholders in place. Parameters are then sent as typed data. Since the plan is already fixed, quotes or keywords inside a parameter cannot change the query structure. They are only compared as content.

Does using an ORM mean I am safe?

Mostly, but not automatically. ORM query builders bind values by default, however every ORM offers raw query methods. Injection in modern codebases almost always lives in those raw calls or in dynamic ORDER BY handling.

Is escaping user input enough?

No. Escaping breaks down in unquoted numeric contexts, with certain multi byte charsets, and anywhere an identifier is being built. Treat manual escaping as a legacy pattern to remove, not a defense to rely on.

Do stored procedures prevent SQL injection?

Only if they use parameters internally. A stored procedure that concatenates its arguments into an EXEC or PREPARE statement is exactly as vulnerable as the equivalent application code.

Does a WAF prevent SQL injection?

A WAF blocks common payloads and gives you alerting, which is useful. It is bypassable through encoding, comment insertion and case variations, so it should sit in front of secure code, never instead of it. The reasoning is set out in this piece.

How can injection attacks in general be prevented?

The same principle applies to command injection, LDAP injection, NoSQL injection and template injection: keep untrusted data out of the interpreter’s syntax. Use parameterized APIs, allowlist what cannot be parameterized, encode on output, and give each component the minimum privileges it needs.

Need a second pair of eyes on your codebase?

At Pixelseed we audit web applications and APIs, remove raw SQL from legacy code, harden database permissions, and set up the CI checks that keep those fixes in place. If you want your endpoints reviewed before your next release, get in touch with our team.