skip to content

A PHP product listing prepares ORDER BY ? and binds the user's chosen column name: why are results unsorted, with no PDO error?

level: middleimportance: should knowfreq 36%

answer

  1. markers stand for values only
  2. the bound name becomes a constant
  3. PDO::quote() makes a string literal
  4. match() to fixed SQL fragments
  5. Pdo\Pgsql::escapeIdentifier() since 8.4

basics

~20 s

A placeholder always carries a value, never an identifier, so ORDER BY ? bound to 'price' sorts by the constant string 'price', the same for every row. Map the user's choice to a fixed column and direction in PHP instead.

solid answer

~50 s

PDO markers represent a **complete data value** only: not a column, table, keyword or `ASC`/`DESC`. Binding `'price'` to `ORDER BY ?` therefore asks the database to sort by the **string value** `'price'`, which is identical for every row, so the rows come back in no requested order. On MySQL there is no error, because ordering by a constant expression is legal; under emulation the SQL literally reads `ORDER BY 'price'`. `PDO::quote()` does not help: it produces a quoted string literal, which is the same constant. The fix is to map the request to **fixed SQL fragments** in PHP, for example with `match ($sort) { 'price_asc' => 'price ASC', ... default => 'name ASC' }`, and concatenate only those. For PostgreSQL, PHP 8.4 added `Pdo\Pgsql::escapeIdentifier()`, but choosing from a fixed list is still the simpler design.

code

php · 13 lines
php
<?php
declare(strict_types=1);

/** @var PDO $pdo */
$orderBy = match ($_GET['sort'] ?? '') {
    'price_asc'  => 'price ASC, id ASC',
    'price_desc' => 'price DESC, id ASC',
    'newest'     => 'created_at DESC, id DESC',
    default      => 'name ASC, id ASC',
};

$stmt = $pdo->prepare("SELECT id, name, price FROM products WHERE price <= :max ORDER BY $orderBy LIMIT 20");
$stmt->execute(['max' => (float) ($_GET['max_price'] ?? 1000)]);

go deeper

for a junior

Know that placeholders only ever replace values, so column names, table names and ASC/DESC cannot be bound.

for a middle

Explain why ORDER BY ? sorts by a constant, why no error appears, and why PDO::quote() makes the same mistake.

for a senior

Implement sort and column choices as fixed fragments chosen with match, and review existing code for markers in identifier positions.

for a principal

Decide how dynamic SQL fragments are introduced and reviewed across teams, so identifier choices always come from code rather than requests.

## What a marker can stand for The PDO manual states it directly: parameter markers can represent a **complete data literal only**. Neither part of a literal, nor a keyword, nor an identifier can be bound. That rules out: - column names in `SELECT`, `WHERE` or `ORDER BY`; - table names in `FROM` or `JOIN`; - the sort direction `ASC` or `DESC`; - any other SQL keyword or fragment. ## Why ORDER BY ? fails silently In the product listing, a developer writes: ```php $stmt = $pdo->prepare('SELECT id, name, price FROM products ORDER BY ? LIMIT 20'); $stmt->execute([$_GET['sort']]); // 'price' ``` What the database receives depends on the emulation setting, but the meaning is the same: | Mode | What the database sees | Effect | |---|---|---| | emulated (MySQL default) | `ORDER BY 'price'` | sort by a string constant | | native | `ORDER BY` a parameter whose value is `'price'` | sort by a value that is the same for every row | Sorting every row by the same constant leaves the order unspecified, so the page shows products in whatever order the database happens to produce. On MySQL nothing is wrong syntactically, so there is **no exception**, and the bug is only noticed when someone checks the sort. Binding a table name, `FROM ?`, is different: a value cannot stand where a table name is required, so that query fails outright. ## Why PDO::quote() is not the fix `PDO::quote()` wraps a string in quotes and escapes it for use as a **string literal**. For `'price'` it returns `'price'` with quotes, which is exactly the constant from the emulated case. Identifier quoting uses different characters (backticks in MySQL, double quotes in PostgreSQL), and the base `PDO` class has no method for it. PHP 8.4 added `Pdo\Pgsql::escapeIdentifier()` for PostgreSQL connections only. ## The PHP mechanics of a fixed mapping Why an allowlist is the sound design is covered under SQL injection. In PHP the implementation is short: 1. Define the sort options the UI offers, as keys such as `price_asc`, `price_desc`, `newest`. 2. Map each key to a **literal SQL fragment** written by you, using `match`, with a `default` arm for anything else. 3. Concatenate only the fragment into the SQL; keep binding every value as usual. `match` fits well because it compares strictly, has no fall-through, and forces a `default` decision; without one, an unexpected value throws `UnhandledMatchError` rather than reaching the SQL. ## Other positions that look bindable but are not - **Column lists**: `SELECT ? FROM products` returns the bound string as a constant column, not the column's data. - **Direction alone**: `ORDER BY price ?` is a syntax error; the direction is part of the fixed fragment. - **`IN` lists**: need one marker per value, as a separate technique. - **`LIMIT` and `OFFSET`**: *are* values and can be bound, as integers. ## Direction as data, SQL as code A common refinement is to accept `sort` and `dir` separately. Keep each as data until the last moment: map `sort` to a column name from a fixed list, map `dir` to exactly `'ASC'` or `'DESC'` with `match`, and only then assemble the fragment. Add a unique tie-breaker such as `id` to every order, so pages do not repeat or skip rows when many products share a price. ## Reviewing for this bug Search the codebase for markers after `ORDER BY`, `GROUP BY`, `FROM` and in select lists. Each one is either a silent logic bug, as here, or an error waiting for its first test. Each should become a fixed mapping.

  • What does SELECT ? AS col FROM products return when 'price' is bound?
    Every row gets the string `'price'` in `col`, because the marker is a value and the query selects that value as a constant expression. It does not read the `price` column. Column choices must be mapped to fixed SQL in PHP, like sort orders.
  • When would Pdo\Pgsql::escapeIdentifier() be the right tool?
    When identifiers are genuinely dynamic and cannot be enumerated in advance, such as a PostgreSQL admin tool listing user-created tables. It quotes and escapes a name as an identifier. It exists only on `Pdo\Pgsql`, since PHP 8.4; for a product listing with a known set of sort options, a fixed mapping is simpler and easier to review.

saying these in an interview costs you the question

  • ORDER BY ? works if the bound value is a real column name
  • PDO::quote() turns a column name into a safe identifier
  • On MySQL, binding the sort column throws a PDOException you would notice
  • ASC or DESC can be bound as a separate parameter
  • The base PDO class has an identifier-quoting method for every driver