What is SQL injection and how to prevent it: bound parameters, tested in a PHP lab

SQL injection is a weakness where an application builds an SQL query by pasting user input into the query text, so the database reads part of that input as SQL instead of as a value. Prevent it by keeping the SQL template fixed and supplying data values through the binding API.

This guide is for protecting applications you own or maintain.

This guide applies OWASP's recommendations to PHP: bind email and id values, constrain sorting choices in application code, and restrict database permissions. The source is the OWASP SQL Injection Prevention Cheat Sheet, and MITRE records the bug class as CWE-89.

On this page: mechanism · PHP fix · ORDER BY · escaping · ORMs · privilege · lab · checklist · testing

Prerequisites

You need to know what an SQL SELECT ... WHERE does and how PHP reads $_GET. PDO (PHP Data Objects) is PHP's built-in database layer, one API for MySQL, PostgreSQL, SQLite and others. An ORM (object-relational mapper) such as Laravel's Eloquent writes SQL for you from method calls.

What is SQL injection, at the level of the parser?

The database decides the structure of a query (which tables, which conditions, how many SELECTs) from the SQL text it parses. When you concatenate input into that text, the input takes part in parsing. A quote in the input closes your string literal, and whatever follows is read as SQL operators and clauses.

A placeholder changes what the parser sees. In SQLite, preparation compiles the SQL template into bytecode, with a parameter slot where ? stood. Binding supplies values to parameter slots. SQLite may recompile the statement during execution, but the bound strings remain values and cannot introduce SQL operators or clauses. Binding says nothing about type or range, so validate those separately.

PDO handles bound values in one of two modes. With native preparation, the template goes to the database and the values follow separately. With emulation, PDO itself quotes each value and builds the final SQL string on the client before sending it. The PDO::prepare page documents the PDO::ATTR_EMULATE_PREPARES switch.

How do you prevent SQL injection in PHP?

Unsafe: the email from the request becomes part of the SQL text.

// search.php (unsafe)
$email = $_GET['email'];
$rows = $pdo->query("SELECT id, email FROM users WHERE email = '$email'")->fetchAll();

Fixed: the query has a placeholder, and the value goes through execute.

// search.php (fixed)
$pdo = new PDO('mysql:host=localhost;dbname=app;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,   // native prepares
]);
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = ?');
$stmt->execute([$_GET['email']]);
$rows = $stmt->fetchAll();

The MySQL driver emulates by default, which means PDO quotes values and builds the SQL string on the client. Setting the charset in the DSN keeps that quoting consistent with the connection. Where the driver supports native preparation, use it: with emulation off, as above, the template and the values travel separately. For the wider PHP list this fix belongs to, see PHP security: common vulnerabilities.

The same pattern exists elsewhere: $1 in node-postgres (see Node.js security) and PreparedStatement in Java. Python placeholder syntax depends on the driver; check its paramstyle (PEP 249).

Can you use a parameter for a table name or ORDER BY?

No. A placeholder stands for a value, and table names, column names and keywords such as ASC are structure the database needs before any value is bound. This is where string-built SQL tends to survive after the rest of a codebase uses parameters.

// unsafe: the sort column comes straight from the request
$sql = "SELECT id, email FROM users ORDER BY " . $_GET['sort'];

Map the request to a small set of tokens your code owns, and reject anything else. In this example only strings written in your own source reach the SQL text.

$columns = ['email' => 'email', 'newest' => 'created_at'];
$dirs    = ['asc' => 'ASC', 'desc' => 'DESC'];
$col = $columns[$_GET['sort'] ?? 'email'] ?? null;
$dir = $dirs[$_GET['dir'] ?? 'asc'] ?? null;
if ($col === null || $dir === null) { http_response_code(400); exit; }
$rows = $pdo->query("SELECT id, email FROM users ORDER BY $col $dir")->fetchAll();

Does escaping input prevent SQL injection?

OWASP marks manual escaping as strongly discouraged. Escaping rules depend on the database and its settings (the cheat sheet notes that MySQL's NO_BACKSLASH_ESCAPES mode turns backslash escaping off), and escaping only helps inside a quoted string. A numeric parameter has no quotes to escape:

// unsafe even with escaping: "1 OR 1=1" contains no quote
$id = addslashes($_GET['id']);
$notes = $pdo->query("SELECT body FROM notes WHERE owner = $id")->fetchAll();

// fixed
$stmt = $pdo->prepare('SELECT body FROM notes WHERE owner = ?');
$stmt->execute([$_GET['id']]);

Checking that an id is digits only still helps, because it rejects bad requests early, but treat it as a second layer on top of binding.

Are ORMs and stored procedures safe from SQL injection?

Laravel's query builder binds values, and whereRaw accepts bindings separately (Laravel raw methods). Stored procedures need their own review: bind call arguments and parameterize values in any internal dynamic SQL.

// Laravel, unsafe: interpolated raw SQL
User::whereRaw("email = '$email'")->get();

// Laravel, fixed: bindings passed separately
User::whereRaw('email = ?', [$email])->get();

Why use a least-privilege database account?

Least privilege limits what an injection bug can reach, though the bug itself remains. Grant each runtime account only the operations and tables its queries require. For the read-only email search above, that is one grant:

-- app-user.sql
GRANT SELECT ON app.users TO 'app'@'localhost';

The host part ('localhost' here) has to match where your app connects from, so adjust it to your deployment.

What our lab showed

We ran labs/security/sql-injection-lab/sql-injection-lab.mjs on this Mac on 2026-11-06: PHP 8.5.2, an in-memory SQLite database with 3 throwaway users and 3 notes, each input sent once to a string-built query and once to a PDO prepared statement. The script counts rows returned and does not print the field values.

# node labs/security/sql-injection-lab/sql-injection-lab.mjs, 2026-11-06 10:13 UTC
sql-injection-lab PHP 8.5.2 · 2026-11-06 · string-built SQL vs PDO prepared statements

test                       input                                                  unsafe   prepared
search by email (rows)     a@example.test                                         1        1
search by email (rows)     x' OR '1'='1                                           3        0
search by email (rows)     x' UNION SELECT 0, role, pw_hash, 0 FROM users --      3        0
notes of user id (rows)    1                                                      1        1
notes of user id (rows)    1 OR 1=1                                               3        0

The lab uses SELECT id, email, role, 0; its four result columns let this UNION payload run, so expect different behavior from the two-column example earlier. It returned 3 rows from a search that matched no email, and those rows carried the role and password-hash columns in the slots the search had reserved for its own fields. The numeric case returned all 3 notes without a single quote character, the escaping gap from above.

In the prepared column, the bound 1 found user 1's note because owner has INTEGER affinity, so SQLite converts the text '1' to the number 1 before comparing (SQLite comparison rules). The bound 1 OR 1=1 is not a well-formed number, so it stays text, equals no integer, and returns 0 rows.

Binding fixed every case here. The risk left over is in whatever SQL your code still assembles from strings, so spend review time there. The lab checks query behavior only and does not measure performance.

Fix checklist

Where SQL meets input	Fix
Values in WHERE, INSERT, UPDATE	Placeholder with a bound parameter
PDO with MySQL	charset in the DSN; ATTR_EMULATE_PREPARES => false for native prepares
Table, column, ORDER BY, ASC/DESC	Map to fixed tokens from an allow-list, reject the rest
ORM raw helpers	Pass values as bindings
Stored procedures	Use static SQL with parameters, or bind values inside dynamic SQL; map structural choices to fixed tokens
Database account	Only the operations and tables the app's queries need
Errors	Log them; do not show SQL errors to users

Where each defense stops:

Defense	Covers	Leaves open
Bound parameters	Every value you bind	Identifiers, keywords, any string-built SQL left elsewhere
Allow-list mapping	Sort columns, directions, table choices	Values (bind those)
Input validation	Rejects malformed requests early	Valid-looking input inside string-built SQL
Least privilege	Limits what a missed bug reaches	The bug itself

Test your own app

Search your code for SQL built from variables. These searches catch some obvious cases; queries assembled through variables and helper functions still need a manual read.

# query strings that interpolate or concatenate variables
grep -rnE "(query|exec|prepare|whereRaw|selectRaw|DB::raw)\(\s*\"[^\"]*\\\$" --include=*.php .
grep -rnE "(SELECT|INSERT|UPDATE|DELETE)[^;]*['\"]\s*\.\s*\\\$" --include=*.php .
grep -rnE "ORDER BY[^;]*\\\$" --include=*.php .

Write a unit test that sends a quote and an always-true condition through your own data layer and expects no extra rows:

// tests/UserSearchTest.php
public function testSearchTreatsInputAsData(): void
{
    $this->assertCount(0, $this->repo->findByEmail("x' OR '1'='1"));
    $this->assertCount(0, $this->repo->notesOf('1 OR 1=1'));
    $this->assertCount(1, $this->repo->findByEmail('a@example.test'));
}

Add a static checker to CI. Psalm's security analysis (vendor/bin/psalm --taint-analysis) follows request data into SQL calls in PHP, and Bandit's B608 check flags string-built SQL in Python (more in Python security). Run them against your own repositories.

Further reading

• OWASP SQL Injection Prevention Cheat Sheet: the defense order this article follows, with examples for Java, .NET and others.
• PHP manual: prepared statements: how PDO binds, with named and positional placeholders.
• SQLite: sqlite3_prepare: what preparation compiles and when SQLite recompiles.
• PHP manual: PDO::prepare: the emulation switch and how each mode treats placeholders.
• SQLite: datatypes and type affinity: why the bound text 1 matched an INTEGER column in our lab.
• Laravel: raw methods: which raw helpers accept a bindings array.
• CWE-89: the official record of the weakness and its variants.

This guide covers causes and fixes only, based on the OWASP cheat sheet and our own offline lab on throwaway data.