Preventing SQL Injection in PHP and Node.js (with Real Code Examples)

SQL injection has been at or near the top of the OWASP Top 10 since the list was first published in 2003. Twenty-three years later, it still accounts for a large share of breached websites — not because the defence is complicated, but because legacy code keeps getting written with the same broken patterns. This guide shows you the attack, the correct fix, and how to audit your own code.

What SQL injection is

Put simply: an attacker makes your application execute SQL they wrote, by sneaking it in through user input. The root cause is a category error — the application treats user-supplied data as if it were part of the SQL code.

The classic example

// NEVER DO THIS
$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";
$result = mysqli_query($conn, $query);

An attacker submits as username: admin' --. The query becomes:

SELECT * FROM users WHERE username = 'admin' --'

The -- is a SQL comment, so everything after it is ignored. The query now returns the admin user without checking the password. Game over.

The three classes of SQL injection

In-band / classic. Error messages or UNION SELECT reveal data directly in the response the attacker sees. Easiest to exploit, easiest to notice.

Blind. The attacker infers data from true/false responses or response timing. Payloads like ' OR SLEEP(5) -- make the database pause for five seconds if the injection works. No data is visibly returned; the attacker extracts information one bit at a time.

Out-of-band. The attacker makes the database itself exfiltrate data — typically via DNS requests to a domain they control. Rare on well-configured servers.

Why "escaping" alone fails

A common mistake is to "sanitise" user input with mysqli_real_escape_string or similar. This is not enough.

// STILL VULNERABLE in some contexts
$id = mysqli_real_escape_string($conn, $_POST['id']);
$query = "SELECT * FROM posts WHERE id = $id";

Escaping only helps within string contexts ('$var'). In integer contexts like above, the attacker can inject without any quotes: 1 OR 1=1. No single-quote to escape, defence bypassed.

The correct mental model is not "escape the input" but "separate the code from the data" using parameterised queries. The database receives the query template and the values via two separate channels. The values are never parsed as SQL.

PHP — the correct fixes

PDO with prepared statements (preferred)

$pdo = new PDO('mysql:host=localhost;dbname=app;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES   => false,        // important
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :u AND status = :s');
$stmt->execute(['u' => $username, 's' => 'active']);
$user = $stmt->fetch();

Why this is safe: with EMULATE_PREPARES disabled, PDO sends the template and values to MySQL via the binary protocol on separate packets. The values are bound as typed parameters — MySQL never parses them as SQL.

mysqli prepared statements

$stmt = $mysqli->prepare('SELECT * FROM users WHERE username = ?');
$stmt->bind_param('s', $username);
$stmt->execute();
$result = $stmt->get_result();

The bind_param type letters matter: s for string, i for integer, d for double, b for blob. Get them right or you reintroduce bugs.

Laravel Eloquent / Query Builder

$user = User::where('username', $username)->first();
$users = DB::select('SELECT * FROM users WHERE username = ?', [$username]);

Eloquent and DB::select with ? placeholders are safe. What is NOT safe:

// Never do this — raw string interpolation
DB::raw("SELECT * FROM users WHERE username = '$username'");

DB::raw turns off the safety. If you must use raw SQL, use parameter binding:

DB::select('SELECT * FROM users WHERE username = ?', [$username]);   // safe

Cases that still need manual care

Even with PDO, some SQL features cannot be parameterised:

ORDER BY with user-supplied column name. You cannot bind ORDER BY ?. The parameter system is for values only, not identifiers. Use an allowlist:

$allowedSort = ['created_at', 'username', 'email'];
$sort = in_array($_GET['sort'] ?? '', $allowedSort) ? $_GET['sort'] : 'created_at';
$stmt = $pdo->prepare("SELECT * FROM users ORDER BY $sort");

LIMIT with user input. Some MySQL versions refuse to bind LIMIT ?. Cast to integer explicitly:

$limit = max(1, min(100, (int)($_GET['limit'] ?? 25)));
$stmt = $pdo->prepare("SELECT * FROM posts LIMIT $limit");

Dynamic table names. Same issue — use allowlist.

Code review checklist — PHP

• No string concatenation inside ->query() or mysqli_query()
• PDO::ATTR_EMULATE_PREPARES => false in all PDO connections
• ORDER BY / LIMIT / table names validated against allowlist or cast to int
• No extract($_POST) (different vuln class, common antipattern)
• Framework raw-SQL helpers (DB::raw, $wpdb->query) reviewed case-by-case
• WordPress: use $wpdb->prepare() before any $wpdb->query() on user input

Node.js — the correct fixes

mysql2 promise API

import mysql from 'mysql2/promise';

const conn = await mysql.createConnection({ host, user, password, database });
const [rows] = await conn.execute(
  'SELECT * FROM users WHERE username = ? AND status = ?',
  [username, 'active']
);

Use execute() (prepared statement) not query() (escapes client-side but not a prepared statement). execute() is safer and usually faster for repeated queries.

pg (PostgreSQL)

import { Pool } from 'pg';
const pool = new Pool();
const result = await pool.query(
  'SELECT * FROM users WHERE username = $1',
  [username]
);

PostgreSQL always uses proper bind parameters as long as you pass the values array. Simple and safe.

Prisma (ORM)

const user = await prisma.user.findFirst({
  where: { username: input }
});

Prisma always parameterises. What is NOT safe:

// Do not do this — parameters are NOT bound
const user = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE username = '${input}'`
);

$queryRawUnsafe is explicitly unsafe — audit every usage. The safe version is:

await prisma.$queryRaw`SELECT * FROM users WHERE username = ${input}`;

Tagged template literal syntax — Prisma automatically parameterises ${input}.

Knex

// Safe — query builder parameterises
await knex('users').where('username', username);

// Safe — ? placeholders with bindings
await knex.raw('SELECT * FROM users WHERE username = ?', [username]);

// UNSAFE — template-literal string building
await knex.raw(`SELECT * FROM users WHERE username = '${username}'`);

Code review checklist — Node.js

• No template-literal string building for SQL
• execute() used over query() in mysql2 where possible
• Input validation with zod / joi / ajv before queries run
• No $queryRawUnsafe in Prisma except with static strings
• DB credentials in env vars (not committed to git)

Beyond parameterisation — defence in depth

Parameterisation stops the attack at the SQL layer. Additional layers help when something else goes wrong.

Least-privilege database users

Your web app's DB user should not have DROP, CREATE, GRANT, or ALTER privileges. Create two database users:

• Migration user: elevated privileges, used only during migrate runs
• Runtime user: SELECT, INSERT, UPDATE, DELETE on specific tables only

If SQL injection happens, the damage is bounded to data — the attacker can't drop tables or create backdoor admin accounts.

Input validation at the boundary

Validate types, lengths, and character classes before the query runs. Not as a substitute for parameterisation, but as an additional layer that rejects obviously bad input early:

// Laravel
$request->validate([
    'username' => 'required|string|alpha_dash|max:50',
    'email'    => 'required|email|max:255',
]);

// Node.js with zod
import { z } from 'zod';
const schema = z.object({
  username: z.string().regex(/^[a-zA-Z0-9_]{3,50}$/),
  email:    z.string().email().max(255),
});
const data = schema.parse(req.body);   // throws on invalid

Web Application Firewall (WAF)

Server-side firewall that matches request patterns against known attack signatures. Our cPanel plans include ModSecurity with the OWASP Core Rule Set enabled by default — flags and blocks common SQL injection patterns at the web server layer.

This is a second line of defence, not a substitute for correct code. A determined attacker can craft payloads that bypass WAF rules; the point is to buy time and stop opportunistic attacks.

Error handling

Never show raw database errors to users. They leak table names, column names, and SQL version — all useful to the attacker.

PHP production: display_errors = 0, log to file.

Laravel: APP_DEBUG=false.

Express: error-handling middleware that returns a generic 500 with a correlation ID for logs.

Monitoring

• Enable the slow-query log on MySQL / PostgreSQL. A sudden spike in slow queries often indicates blind SQL injection probing.
• Review web server logs weekly for suspicious patterns — UNION, OR 1=1, long query strings with SQL keywords.
• On VPS: fail2ban rules on the web server log to auto-block IPs firing obviously malicious payloads.

Quick-test: am I vulnerable?

Manual tests (on a copy / staging, never production):

1. In any text input, submit ' OR '1'='1 — does login or search bypass authentication?
2. Submit admin'-- in a login field — does the query still run?
3. In any numeric field, submit 1 OR 1=1 — does it return all records?

If any of the above works on your app, you have SQL injection. Fix immediately.

Automated tools (for dev / staging environments only, never production, never on sites you don't own):

• sqlmap (sqlmap.org) — the canonical tool; runs dozens of injection patterns automatically
• OWASP ZAP — active scan mode
• Burp Suite Community — manual testing with repeater

Static analysis for your codebase:

• PHP: phpstan with phpstan-strict-rules, or psalm with --security-analysis
• Node.js: eslint-plugin-security, semgrep with the javascript.lang.security ruleset

Real-world case studies

• 2011 Sony Pictures — 10 million records, plain-text passwords. SQL injection root cause.
• 2015 TalkTalk (UK) — £77 million fine after SQL injection exposed 157,000 customer records.
• 2020 Cit0day — 23,000 breached sites aggregated, many from SQL injection.

Common thread: legacy code paths nobody had audited in years. Rotation on code you inherit is as important as writing new code correctly.

Pre-commit / pre-merge checklist

Print and stick to your monitor:

• No string concatenation inside SQL statements anywhere
• Every user input flows through a parameterised query
• Runtime DB user has the minimum privileges it needs
• Inputs validated for type, length, pattern before reaching the DB
• APP_DEBUG / display_errors off in production
• Errors logged, not shown to users
• Slow-query log enabled, reviewed weekly
• ModSecurity OWASP CRS enabled (shared hosting) or fail2ban (VPS)
• Static analysis in CI (phpstan / eslint-security)
• Annual penetration test, or after every major feature release

Frequently asked questions

Does using an ORM mean I'm safe from SQL injection?

For standard CRUD operations — yes, ORMs like Eloquent, Prisma, and Sequelize parameterise everything. But ORMs also expose raw-SQL escape hatches (DB::raw, $queryRawUnsafe) — any use of those needs audit. "My ORM protects me" is a starting point, not an end point.

Is `mysqli_real_escape_string` enough to prevent SQL injection?

No. It only escapes strings inside quoted contexts. Integer context injection bypasses it. Character-set mismatch can also bypass it. Parameterised queries are the correct defence.

Does my hosting plan's WAF protect me?

The WAF stops many opportunistic attackers by matching known patterns. A determined attacker with knowledge of your stack can bypass it. The WAF is defence-in-depth, not primary defence.

What's the difference between SQL injection and XSS?

SQL injection — attacker makes your database execute their SQL. XSS — attacker makes a victim's browser execute their JavaScript. Different layer, different defence (output encoding for XSS, parameterisation for SQLi). Both are OWASP Top 10.

How often should I audit for SQL injection?

Every pull request (via automated static analysis) and once per quarter manually on high-risk areas (auth, search, admin panels). After every major framework upgrade, run a sweep — new features can introduce new query patterns.

Can SQL injection happen in NoSQL databases like MongoDB?

Yes — the equivalent is NoSQL injection. Same root cause (user input treated as query language), different syntax. MongoDB drivers have parameterised query equivalents; use them.

Questions on auditing your own code? [email protected]. Our senior support team can review specific query patterns for injection risk as a standard support request.