Prepared statements send the SQL template separately from data values. This prevents an input value from being interpreted as SQL syntax and also makes repeated queries easier to manage.
What It Does
A quote inside an input remains data, not SQL. Named parameters make multi-value statements readable.
Requirements and Compatibility
Use a supported PHP runtime and install any third-party tool or extension explicitly shown in the examples. Commands should be run from the project root unless stated otherwise.
Syntax, Inputs and Results
The examples separate configuration, the smallest working operation and a realistic follow-up task. Adapt file paths and dependency versions to your own project.
Example 1: Basic Use
A quote inside an input remains data, not SQL.
<?php
$db = new PDO('sqlite::memory:');
$db->exec('CREATE TABLE users (name TEXT)');
$db->prepare('INSERT INTO users (name) VALUES (?)')->execute(["Sam' OR 1=1"]);
$q = $db->prepare('SELECT name FROM users WHERE name = ?');
$q->execute(["Sam' OR 1=1"]);
var_dump($q->fetchColumn());Example 2: Another Case
Named parameters make multi-value statements readable.
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE stock (sku TEXT, qty INTEGER)');
$stmt = $pdo->prepare('INSERT INTO stock VALUES (:sku, :qty)');
$stmt->execute(['sku' => 'T-10', 'qty' => 4]);Practical Example
Sort column identifiers must be chosen from a known allowlist.
<?php
$allowed = ['name', 'price'];
$sort = 'price';
if (!in_array($sort, $allowed, true)) throw new InvalidArgumentException('Bad sort');
$sql = 'SELECT name, price FROM products ORDER BY ' . $sort;
echo $sql;For production, apply the compatibility and edge-case limits described below.
Important Behavior and Edge Cases
Placeholders represent VALUES, not table names, column names, or ORDER BY keywords; identifiers require explicit allowlists.
Comparison with Related Functions and Tools
| Subject | Key difference |
|---|---|
| Prepared statements vs SQL concatenation | Prepared statements bind data values; concatenation can turn unchecked input into SQL syntax. |
Common Beginner Mistakes
| Mistake | Correct approach |
|---|---|
| Interpolating user values into database SQL | Use named or positional placeholders and handle database errors. |
| Skipping the prerequisite of this specific workflow | Install or configure the named tool and confirm the example can actually run. |
| Ignoring edge conditions | Placeholders represent VALUES, not table names, column names, or ORDER BY keywords; identifiers require explicit allowlists. |
Key Takeaways
- Prepared statements send the SQL template separately from data values. This prevents an input value from being interpreted as SQL syntax and also makes repeated queries easier to manage.
- Placeholders represent VALUES, not table names, column names, or ORDER BY keywords; identifiers require explicit allowlists.
- Keep application configuration, source code and validation tests under version control.
Use these examples as a starting point, then validate the result with the actual PHP build, dependencies and inputs used by your application.