SQL Injection: Prepared Statements and Safe Dynamic Queries

Understand how SQL injection happens, eliminate it with PDO prepared statements and bound parameters, safely handle dynamic table/column names that cannot be bound as parameters using allowlists, and apply the principle of least-privileged database users.

SQL injection is one of the oldest and still most damaging web vulnerabilities: an attacker supplies input that gets concatenated directly into a SQL string, changing the meaning of the query itself rather than just its data. A single unescaped $_GET['id'] can let an attacker read every row in every table, bypass a login form, or drop a table entirely. The fix is not "escape the input carefully" — it's to never build SQL by concatenating untrusted strings in the first place. PDO prepared statements separate the query structure from the data, so user input can never be interpreted as SQL syntax. This lesson also covers the trickier case of dynamic identifiers (table and column names), which can't be bound as parameters at all, and closes with defense-in-depth at the database-user level.

How SQL Injection Actually Works

SQL injection happens when untrusted input is concatenated directly into a SQL query string and then sent to the database as-is. The database has no way to distinguish "data the developer intended" from "SQL syntax the attacker snuck in" — it just sees one string and parses all of it as SQL. If a login check builds a query like "SELECT * FROM users WHERE username = '" . $user . "' AND password = '" . $pass . "'", an attacker who types ' OR '1'='1 into the username field turns the WHERE clause into something that's always true, and the query returns every user in the table — often the first one, an admin, is what the app then logs in as.

It gets worse than reading data. Depending on the database driver and query context, injection can be used to run entirely separate statements (; DROP TABLE users; --), extract data from unrelated tables via UNION SELECT, or blindly infer data one bit at a time through timing or boolean responses even when the app shows no error output at all ("blind" SQL injection). The root cause is always the same: untrusted data was allowed to influence the *structure* of the query, not just its *values*.

The fix is not string sanitization, escaping quotes, or blocklisting suspicious words — those approaches are fragile and attackers routinely find bypasses (alternate encodings, comment tricks, second-order injection). The reliable fix is to never build a query by string concatenation with variable data at all. PDO prepared statements send the query template and the data to the database as two separate messages; the database compiles the query structure first, and values are only ever treated as literal values, never as SQL syntax, no matter what characters they contain.

• SQL injection = untrusted input changes the *meaning* of a query, not just its data. • Classic symptom: string concatenation like "...WHERE id = " . $_GET['id']. • Can lead to data theft, auth bypass, data destruction, or blind data extraction. • Escaping/blocklisting is fragile; the real fix is prepared statements with bound parameters.

Prepared Statements with PDO

PDO (PHP Data Objects) prepares a statement by sending the SQL template — with placeholders like ? or :name in place of values — to the database driver first. The driver parses and compiles that template once. Then the actual values are bound and sent separately with execute(). Because the values arrive after the query structure is already fixed, there is no way for a value like ' OR '1'='1 to be reinterpreted as SQL; the database treats it as a literal string, full stop, even if it contains quotes, semicolons, or comment markers.

Named placeholders (:username) are generally preferred over positional (?) placeholders for anything beyond a couple of parameters, because they're self-documenting and order-independent — you can pass an associative array straight to execute(). Always set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so database errors surface as catchable exceptions instead of silent failures or warnings that are easy to miss in production.

Prepared statements protect *values* — things that go on the right-hand side of a comparison, in a WHERE clause, an INSERT's VALUES list, or an UPDATE's SET list. They do not protect identifiers: table names, column names, and ORDER BY/ASC/DESC direction cannot be bound as parameters, because a bound parameter is always treated as a quoted literal value, not as raw SQL syntax — ORDER BY :col would try to sort by the literal string "email", not the email column.

• $pdo->prepare($sql) compiles the query template with placeholders. • $stmt->execute([...]) sends values separately — values, never as SQL syntax. • Named placeholders (:name) are order-independent and self-documenting. • Always use PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION. • Placeholders bind *values* only — never table/column names or sort direction.

Dynamic Identifiers: The Allowlist Pattern

Sometimes an application legitimately needs a dynamic table name, column name, or sort column — for example, an API endpoint that lets the client choose which column to sort a results list by (?sort=price vs ?sort=created_at). Since identifiers can't be bound as parameters, the only safe approach is to never let raw user input reach the SQL string as an identifier at all. Instead, validate the requested value against a fixed allowlist of known-safe identifiers defined in your own code, and only ever interpolate the *allowlisted* value (never the raw user input) into the query string.

The allowlist should be a hardcoded array mapping accepted external names to the exact, trusted identifier to use — this also lets you decouple the public API name from the internal column name. If the requested value isn't in the allowlist, reject the request (throw, return a 400, or fall back to a safe default) rather than trying to sanitize it. Never attempt to "clean" a raw identifier with a regex or blocklist as your only defense — an allowlist of exact accepted values is the only approach that's actually safe, because there's a finite, known-in-advance set of columns your query can legally reference.

The same pattern applies to dynamic table names (e.g. a multi-tenant app routing to orders_2024, orders_2025, etc.) and to ASC/DESC sort direction, which should be checked against exactly ['ASC', 'DESC'] (case-normalized) rather than interpolated raw.

• Identifiers (table/column names, sort direction) cannot be bound as PDO parameters. • Define a hardcoded allowlist array of accepted identifiers in your own code. • Look up the requested value in the allowlist; reject anything not present. • Only interpolate the allowlisted (trusted) value into SQL — never the raw request input.

Least-Privileged Database Users

Prepared statements stop injection at the query layer, but defense-in-depth means assuming some other bug — a logic flaw, a misconfigured ORM, a vulnerable dependency — might still let an attacker run unintended SQL. The database-user account your web application connects with should have only the privileges it actually needs for normal operation, nothing more. A typical web app user needs SELECT, INSERT, UPDATE, and DELETE on its own application tables — it almost never needs DROP, CREATE, ALTER, GRANT, or access to unrelated schemas.

If that limited-privilege account is ever compromised through some other vulnerability, the blast radius is contained: an attacker can manipulate data in the tables the app already touches, but can't drop tables, create new admin accounts at the database-engine level, exfiltrate via LOAD_FILE, or grant themselves more access. Administrative tasks (schema migrations, backups, user management) should run under a separate, more privileged account that the running web application itself never uses — for example, migrations run via a deploy pipeline with a dedicated migration user, not the app's runtime credentials.

This is a standard example of the principle of least privilege: every component of a system should operate with the minimum set of permissions required to do its job, so that a compromise of any one component doesn't automatically become a compromise of everything.

• The app's runtime DB user should have only SELECT/INSERT/UPDATE/DELETE on its own tables. • It should not have DROP, CREATE, ALTER, GRANT, or FILE privileges. • Run migrations/admin tasks under a separate, more privileged account, never the app's runtime user. • Principle of least privilege limits the blast radius if the app is compromised some other way.

PDO Prepared Statement Shape

PDO::ATTR_EMULATE_PREPARES => false makes the database driver do real, server-side prepared statements rather than PHP faking it client-side. fetch(PDO::FETCH_ASSOC) returns one row as an associative array; use fetchAll() for every row. The placeholder never contains the value — the value is only ever passed to execute().

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false, // use real (native) prepared statements
]);

$stmt = $pdo->prepare('SELECT id, email FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

Code Examples

This is the canonical SQL injection shape: raw, untrusted input from $_GET is concatenated straight into the SQL string. Because id is never quoted or validated, an attacker can inject arbitrary SQL syntax — not just change the numeric value, but add clauses, UNION in other tables, or chain a second statement, depending on the database driver.

$sql = "SELECT * FROM users WHERE id = " . $id;

Common mistake: Never interpolate $_GET/$_POST/any request-derived value directly into a SQL string, even for values you 'know' should be numeric — validate the type, but still use a prepared statement.

The query template (SELECT ... WHERE id = :id) is sent to the database and compiled first, with :id as a placeholder. The actual value of $id is sent separately via execute(). Even if $id were the string 1 OR 1=1, the database would only ever try to compare the id column to that literal string value — it can never become part of the query's structure. Expected result: Returns exactly one row matching the numeric id, or false if none exists — never an unintended full table dump.

$stmt->prepare('SELECT id, email, created_at FROM users WHERE id = :id');

$sortKey from the request is looked up in a hardcoded allowlist array; if it isn't a recognized key, the function throws instead of guessing. The SQL string only ever interpolates $column, which came from the allowlist's own values, and $direction, which was forced into exactly 'ASC' or 'DESC'. The raw, attacker-controlled $sortKey/$direction strings never reach the SQL string themselves. The values (none here) would still go through placeholders; it's specifically the ORDER BY column/direction — which SQL syntax requires as bare identifiers — that needs the allowlist approach instead of parameter binding.

if (!array_key_exists($sortKey, SORTABLE_COLUMNS)) { throw new InvalidArgumentException(...); }

The webapp account is scoped to only the CRUD operations the running application performs, on only its own database. Schema changes are performed by a separate account used only during deploys/migrations, never stored in the running application's configuration. If the app's credentials leak (via a log, an environment misconfiguration, or another vulnerability), the attacker still can't drop tables, alter the schema, or grant themselves broader access.

GRANT SELECT, INSERT, UPDATE, DELETE ON sampleapp_db.* TO 'webapp'@'%';

<?php
// DANGEROUS — for illustration only. Never build SQL like this.
$id = $_GET['id']; // untrusted, attacker-controlled

$sql = "SELECT * FROM users WHERE id = " . $id;
$result = $pdo->query($sql);

// An attacker requesting ?id=1 OR 1=1 turns this into:
// SELECT * FROM users WHERE id = 1 OR 1=1
// which returns every row in the table, not just id = 1.

<?php
declare(strict_types=1);

$id = $_GET['id'] ?? null;

$stmt = $pdo->prepare('SELECT id, email, created_at FROM users WHERE id = :id');
$stmt->execute(['id' => $id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user === false) {
    http_response_code(404);
    exit('User not found.');
}

echo htmlspecialchars($user['email'], ENT_QUOTES, 'UTF-8');

<?php
declare(strict_types=1);

// Public API sort key => trusted internal column name.
const SORTABLE_COLUMNS = [
    'price'   => 'price_cents',
    'newest'  => 'created_at',
    'name'    => 'product_name',
];

function buildProductQuery(PDO $pdo, string $sortKey, string $direction): PDOStatement
{
    // Reject anything not in the allowlist instead of trying to sanitize it.
    if (!array_key_exists($sortKey, SORTABLE_COLUMNS)) {
        throw new InvalidArgumentException("Unsupported sort key: {$sortKey}");
    }
    $column = SORTABLE_COLUMNS[$sortKey]; // trusted, never the raw input

    $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC'; // allowlist of two values

    // Identifiers can't be bound as parameters, so we interpolate ONLY the
    // already-validated, trusted values — never the raw request input.
    $sql = "SELECT id, product_name, price_cents FROM products ORDER BY {$column} {$direction}";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();
    return $stmt;
}

// Usage: buildProductQuery($pdo, $_GET['sort'] ?? 'newest', $_GET['dir'] ?? 'asc');

-- Run by a DBA/admin account, NOT by the running web application.

CREATE USER 'webapp'@'%' IDENTIFIED BY 'a-long-random-generated-password';

-- Grant only what the running application actually needs day to day:
GRANT SELECT, INSERT, UPDATE, DELETE
  ON sampleapp_db.*
  TO 'webapp'@'%';

-- Explicitly NOT granted to 'webapp':
--   DROP, CREATE, ALTER   (schema changes go through a separate migration user)
--   GRANT OPTION          (webapp can never escalate its own or others' privileges)
--   FILE                  (no LOAD_FILE / INTO OUTFILE access to the filesystem)

FLUSH PRIVILEGES;

Concatenation vs. Prepared Statement

Prepared statements fix the query's structure before any user value is ever sent.

String concatenation (vulnerable):
  "SELECT * FROM users WHERE id = " . $id
                                       |
                     attacker input becomes SQL SYNTAX
                     "1 OR 1=1"  ->  WHERE id = 1 OR 1=1

Prepared statement (safe):
  prepare("SELECT * FROM users WHERE id = :id")   <- structure fixed first
  execute(['id' => $id])                          <- value sent separately
                                       |
                     attacker input can only ever be a LITERAL VALUE

Important Rules

Never concatenate request-derived data directly into a SQL string.

Use PDO prepared statements with bound parameters for every value in every query.

Set PDO::ATTR_ERRMODE to ERRMODE_EXCEPTION and disable emulated prepares.

For dynamic table/column names or sort direction, validate against a hardcoded allowlist and interpolate only the allowlisted, trusted value.

Give the application's runtime DB user only the CRUD privileges it needs — never DROP, ALTER, GRANT, or FILE.

Why This Matters in Production

SQL injection has appeared in the OWASP Top 10 for over two decades and has been responsible for some of the largest breaches in web history, from mass data leaks to full database wipes, because a single unguarded input field is often enough to compromise an entire application. The defense described here is layered on purpose: prepared statements stop injection at the query layer for the overwhelming majority of cases; allowlisting handles the one category (identifiers) prepared statements structurally can't cover; and least-privileged database users limit the damage if some other, unrelated bug ever lets an attacker run SQL they shouldn't. None of these three layers is optional — each covers a gap the others leave open.

$pdo->query("SELECT * FROM users WHERE email = '" . $_POST['email'] . "'");

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email'); $stmt->execute(['email' => $_POST['email']]);

String concatenation lets any character the attacker sends — including quotes and SQL keywords — change the query's meaning. Binding the value through execute() guarantees it is only ever treated as a literal string value.

$stmt = $pdo->prepare("SELECT * FROM products ORDER BY :col"); $stmt->execute(['col' => $sortColumn]);

$column = SORTABLE_COLUMNS[$sortKey] ?? throw new InvalidArgumentException('bad sort'); $sql = "SELECT * FROM products ORDER BY {$column}";

Binding an identifier as a parameter doesn't work the way developers expect — the driver will quote it as a string literal, so ORDER BY :col sorts by a constant, not by the named column. Identifiers must be validated against an allowlist and interpolated directly, never bound.

Why do PDO prepared statements prevent SQL injection? A. They automatically strip all quotes from user input before running the query. B. The query's structure is compiled before values are sent, so bound values can only ever be treated as literal data, never as SQL syntax. C. They run every query inside an implicit transaction that rolls back on suspicious input. D. They encrypt the SQL query text before sending it to the database.

B. The query's structure is compiled before values are sent, so bound values can only ever be treated as literal data, never as SQL syntax. — Prepared statements separate the query template from the data. Because the structure is already fixed when values are bound, those values can never be reinterpreted as part of the SQL syntax.

A dynamic column name for an ORDER BY clause comes from user input. What is the safe way to handle it? A. Bind it as a PDO parameter just like any other value. B. Escape it with addslashes() before interpolating it into the SQL string. C. Validate it against a hardcoded allowlist of accepted column names, then interpolate only the allowlisted value. D. Reject the request outright; dynamic sorting is never possible with PDO.

C. Validate it against a hardcoded allowlist of accepted column names, then interpolate only the allowlisted value. — Identifiers can't be bound as parameters — the driver would quote them as string literals. The only safe approach is an allowlist: validate the requested key, then use the trusted, known-safe value from your own code.

Why should a web application's runtime database user avoid having DROP or GRANT privileges? A. Those privileges make queries run noticeably slower. B. MySQL and PostgreSQL don't support those privileges for application accounts. C. To limit the blast radius if the app is compromised through some other vulnerability — a limited account can't destroy schema or escalate its own access. D. Prepared statements already make those privileges unnecessary to restrict.

C. To limit the blast radius if the app is compromised through some other vulnerability — a limited account can't destroy schema or escalate its own access. — Least privilege is defense-in-depth: if some other bug lets an attacker execute unintended SQL, a runtime account without DROP/ALTER/GRANT limits what damage they can do, even though prepared statements already stop typical injection.

Write a PHP function findUsersByRole(PDO $pdo, string $role, string $sortKey): array that returns all users with a given role, sorted by a caller-chosen column. $role is a value (safe to bind). $sortKey must be validated against an allowlist mapping 'name' => 'full_name' and 'joined' => 'created_at'; reject anything else.

function findUsersByRole(PDO $pdo, string $role, string $sortKey): array { const SORT = ['name' => 'full_name', 'joined' => 'created_at']; if (!isset(SORT[$sortKey])) throw new InvalidArgumentException('bad sort key'); $col = SORT[$sortKey]; $stmt = $pdo->prepare("SELECT * FROM users WHERE role = :role ORDER BY {$col}"); $stmt->execute(['role' => $role]); return $stmt->fetchAll(PDO::FETCH_ASSOC); }

Bind $role with a placeholder in the WHERE clause. Look up $sortKey in a hardcoded array; if missing, throw. Interpolate only the resolved column name into the ORDER BY clause.