PHP
PHP prepared statements vs SQL concatenation: secure PDO queries
Build safer PHP database queries with PDO prepared statements, bound values and allowlisted sort columns. Understand what parameter binding does and does not protect.
A search form should supply a value to a database query, not decide the structure of the SQL. That distinction is central to preventing SQL injection in PHP applications.
Prepared statements are an important baseline, but they do not replace validation or authorisation. This guide separates those responsibilities using a small project-list example.
Without prepared statements: input becomes SQL text
An unsafe implementation builds its SQL by appending a submitted value directly into a query string. An apostrophe in an ordinary name can already expose the problem: the database may interpret part of that value as SQL syntax.
Do not try to solve this by removing apostrophes, blocking selected words or applying HTML escaping. Those techniques either damage legitimate input or address a different output context.
The useful boundary is between the statement’s structure and its data values.
With prepared statements: bind the values
Assume $pdo is an established PDO connection, $accountId comes from the authenticated server-side context, and $status has passed application validation:
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$statement = $pdo->prepare(
'SELECT id, name, status FROM projects
WHERE account_id = :account_id AND status = :status'
);
$statement->execute([
'account_id' => $accountId,
'status' => $status,
]);
$projects = $statement->fetchAll(PDO::FETCH_ASSOC);
The placeholders represent complete data values. Read the PHP manual on prepared statements and PDO::prepare for driver behavior and limitations.
Use the connection configuration appropriate to your database driver, including its character encoding. Do not assume every PDO driver supports every attribute or identical native/emulated preparation behavior.
Placeholders do not replace column names
A sort field changes SQL structure. A placeholder cannot stand in for an arbitrary table name, column name or SQL keyword.
Map a public sort choice to a fixed expression controlled by your application:
$allowedSorts = [
'newest' => 'created_at DESC, id DESC',
'name' => 'name ASC, id ASC',
];
$orderBy = $allowedSorts[$sortChoice] ?? $allowedSorts['newest'];
$statement = $pdo->prepare(
'SELECT id, name FROM projects
WHERE account_id = :account_id ORDER BY '.$orderBy
);
$statement->execute(['account_id' => $accountId]);
Here, the appended expression comes only from the fixed allowlist. Never append the raw request value instead. Set result limits or pagination appropriate to the page; a safe query can still request too much data.
Keep validation and authorisation separate
| Responsibility | Example |
|---|---|
| Validation | Status must be one of the allowed project states |
| Parameter binding | The status is passed as a data value |
| Authorisation | The query is restricted to the authenticated account |
| Output encoding | The project name is escaped for its display context |
A perfectly bound query can still expose another customer’s records if it lacks the required account scope. Likewise, safe database access does not make unescaped HTML output safe.
Understand search semantics
In a LIKE search, binding a value does not make % and _ literal characters. Decide whether your search intentionally supports those wildcards. If not, implement escaping appropriate to the database and query syntax, and test it.
For an IN clause, build one placeholder per value rather than passing a comma-separated list as one parameter. Bound values represent individual values, not arbitrary SQL fragments.
Test the boundary, not just the happy path
Use a test database and include names containing apostrophes, Unicode characters, empty input and a value resembling SQL syntax. Assert the intended records are returned and that another account’s records remain inaccessible.
Test sort choices outside the allowlist. Confirm they fall back predictably or produce a validation error. Inspect database failures through your logging system rather than displaying connection details to visitors.
This is a query-pattern guide, not a complete authentication system. For the failure path around database operations, read production PHP error handling. If queries are secure but numerous, see the related Laravel N+1 guide.