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

URL: https://systemdesignsimple.com/what-is-sql-injection-and-how-to-prevent-it/
Published: 2026-11-06
Category: Security
Publisher: System Design Simple (https://systemdesignsimple.com)

## Short answer

SQL injection happens when an app pastes user input into the text of an SQL query, so the database parses part of that input as SQL. Use placeholders and pass values through the binding API, so their contents cannot become SQL syntax. Map table names and sort order to fixed tokens, and give the app a database account with only the permissions its queries need.

## Key facts

- **Cause:** User input concatenated into SQL text
- **Primary fix:** Placeholders with bound parameters
- **Table names, ORDER BY:** Map to fixed tokens from an allow-list
- **Escaping:** Strongly discouraged by OWASP as a substitute
- **Damage limit:** Least-privilege database account
- **Our lab:** String-built SQL: 3 rows for each crafted input. Prepared: 0

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](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html), and MITRE records the bug class as [CWE-89](https://cwe.mitre.org/data/definitions/89.html).

On this page: [mechanism](#what-is-sql-injection-at-the-level-of-the-parser) · [PHP fix](#how-do-you-prevent-sql-injection-in-php) · [ORDER BY](#can-you-use-a-parameter-for-a-table-name-or-order-by) · [escaping](#does-escaping-input-prevent-sql-injection) · [ORMs](#are-orms-and-stored-procedures-safe-from-sql-injection) · [privilege](#why-use-a-least-privilege-database-account) · [lab](#what-our-lab-showed) · [checklist](#fix-checklist) · [testing](#test-your-own-app)

## 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](https://www.php.net/manual/en/pdo.prepare.php) 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.

```php
// 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`.

```php
// 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](/php-security-common-vulnerabilities/).

The same pattern exists elsewhere: `$1` in node-postgres (see [Node.js security](/nodejs-security-common-vulnerabilities/)) and `PreparedStatement` in Java. Python placeholder syntax depends on the driver; check its `paramstyle` ([PEP 249](https://peps.python.org/pep-0249/#paramstyle)).

## 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.

```php
// 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.

```php
$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:

```php
// 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();
```

```php
// 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](https://laravel.com/docs/12.x/queries#raw-methods)). Stored procedures need their own review: bind call arguments and parameterize values in any internal dynamic SQL.

```php
// 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:

```sql
-- 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.

[Image: Terminal output of our sql-injection-lab lab run: node run.mjs | Our sql-injection-lab run. The full text output is below.]

```text
# 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](https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison)). 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.

```sh
# 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:

```php
// 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](https://psalm.dev/docs/security_analysis/) (`vendor/bin/psalm --taint-analysis`) follows request data into SQL calls in PHP, and [Bandit's B608 check](https://bandit.readthedocs.io/en/latest/plugins/b608_hardcoded_sql_expressions.html) flags string-built SQL in Python (more in [Python security](/python-security-common-vulnerabilities/)). Run them against your own repositories.

## Further reading

- [OWASP SQL Injection Prevention Cheat Sheet](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html): the defense order this article follows, with examples for Java, .NET and others.
- [PHP manual: prepared statements](https://www.php.net/manual/en/pdo.prepared-statements.php): how PDO binds, with named and positional placeholders.
- [SQLite: sqlite3_prepare](https://www.sqlite.org/c3ref/prepare.html): what preparation compiles and when SQLite recompiles.
- [PHP manual: PDO::prepare](https://www.php.net/manual/en/pdo.prepare.php): the emulation switch and how each mode treats placeholders.
- [SQLite: datatypes and type affinity](https://www.sqlite.org/datatype3.html): why the bound text `1` matched an INTEGER column in our lab.
- [Laravel: raw methods](https://laravel.com/docs/12.x/queries#raw-methods): which raw helpers accept a bindings array.
- [CWE-89](https://cwe.mitre.org/data/definitions/89.html): 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.

## Frequently asked questions

**Do prepared statements fully prevent SQL injection?**
They prevent it for every value you bind. They cannot bind table names, column names or keywords like ASC, so those parts need an allow-list, and any SQL still built by string concatenation elsewhere stays vulnerable.

**Is escaping quotes enough to prevent SQL injection?**
No. Numeric inputs need no quotes at all, and escaping rules depend on the database and its settings. OWASP strongly discourages escaping as a substitute for bound parameters.

**Are ORMs safe from SQL injection?**
Their query builders bind values for you. Their raw SQL helpers are only safe when you pass the values as bindings; an interpolated string in whereRaw is as exposed as hand-built SQL.

## Sources

- [OWASP Cheat Sheet Series: SQL Injection Prevention](https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html)
- [MITRE CWE-89: Improper Neutralization of Special Elements used in an SQL Command](https://cwe.mitre.org/data/definitions/89.html)
- [PHP manual: Prepared statements and stored procedures (PDO)](https://www.php.net/manual/en/pdo.prepared-statements.php)
- [PHP manual: PDO::prepare](https://www.php.net/manual/en/pdo.prepare.php)
- [SQLite: Compiling an SQL statement (sqlite3_prepare)](https://www.sqlite.org/c3ref/prepare.html)
- [SQLite: Datatypes and type affinity](https://www.sqlite.org/datatype3.html)
- [Laravel: Database query builder, raw methods](https://laravel.com/docs/12.x/queries#raw-methods)