skip to content

In PHP, how do PDO::prepare() and execute() run a query with user input, and how do ? and :name placeholders differ?

level: juniorimportance: must knowfreq 76%

answer

  1. template first, values later
  2. list for ?, keyed array for :name
  3. never mix the two styles
  4. a marker is a whole value, unquoted
  5. HY093: Invalid parameter number

basics

~20 s

PDO::prepare() turns an SQL template with placeholders into a PDOStatement; execute() supplies the values, a list for ? markers or a keyed array for :name markers. The styles cannot be mixed, and a marker is one whole value.

solid answer

~40 s

`$pdo->prepare($sql)` parses an SQL **template** and returns a `PDOStatement`; `$stmt->execute($params)` sends the values separately from the SQL text. With **positional** markers, `WHERE category_id = ? AND price < ?`, you pass a list in marker order: `[3, 50]`. With **named** markers, `:cat` and `:max`, you pass a keyed array, `['cat' => 3, 'max' => 50]`, where the leading colon on keys is optional. One statement uses one style only, the number of values must match the markers, and a mismatch makes `execute()` fail, typically with SQLSTATE `HY093` ("Invalid parameter number"). A marker replaces a **complete value**: write `LIKE ?` and bind `'%lamp%'`, never `'%?%'` inside quotes, which is just text. The same statement can be executed again with new values.

code

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

/** @var PDO $pdo */
$stmt = $pdo->prepare(
    'SELECT id, name, price FROM products
     WHERE category_id = :cat AND price <= :max AND name LIKE :term'
);
$stmt->execute([
    'cat'  => (int) ($_GET['category'] ?? 0),
    'max'  => (float) ($_GET['max_price'] ?? 1000),
    'term' => '%' . ($_GET['q'] ?? '') . '%',
]);
$products = $stmt->fetchAll(PDO::FETCH_ASSOC);

go deeper

for a junior

Know prepare() then execute(), a list for ? and a keyed array for :name, and that a marker is never put inside quotes.

for a middle

Explain the matching rules, the HY093 errors, why LIKE patterns are bound whole, and what the 8.4 parsers changed about markers inside literals.

for a senior

Recognise the places binding cannot reach, identifiers and wildcards in values, and review code for markers used as partial literals.

for a principal

Make parameterised queries the only accepted path in the codebase and decide how exceptions, such as dynamic sort orders, are reviewed.

## Two calls instead of one string With PDO, a query that contains user input is split in two: 1. **`PDO::prepare(string $query, array $options = []): PDOStatement|false`** receives an SQL **template** in which each user-supplied value is replaced by a **parameter marker**. 2. **`PDOStatement::execute(?array $params = null): bool`** runs it, supplying the values. The values never become part of the SQL text you wrote. Why that defeats injection is the subject of the SQL-injection topic; this one is about using the PDO API correctly. ## Positional and named markers | | Positional `?` | Named `:name` | |---|---|---| | template | `WHERE category_id = ? AND price < ?` | `WHERE category_id = :cat AND price < :max` | | `execute()` argument | list in marker order: `[3, 50]` | keyed array: `['cat' => 3, 'max' => 50]` | | key format | `0, 1, 2, ...` with no gaps | name, colon optional: `'cat'` or `':cat'` | | `bindValue()` identifier | 1-based position: `1`, `2` | the name: `':cat'` | | reuse of one marker | not applicable | only with emulated prepares | Rules that apply to both styles: - **Do not mix them** in one statement; PDO reports `HY093` "mixed named and positional parameters". - **Counts must match.** Too few values, an extra key, or a list with a gap in its indexes makes `execute()` fail, typically with `HY093`, "Invalid parameter number". - **One style per driver is native**; PDO rewrites the other style for drivers that support only one, so both work everywhere. Named markers read better once a query has more than three or four values; positional markers are convenient when the values are generated, as in an `IN (...)` list. ## A marker is one complete value A marker replaces an entire value, never part of one and never SQL syntax: - `WHERE name LIKE '%?%'` contains no marker at all: the `?` sits inside a string literal and is sent as a literal question mark. Write `WHERE name LIKE ?` and bind `'%' . $term . '%'`. - `WHERE id IN (?)` with one marker takes one value, not a list. - Column names, table names, `ASC`/`DESC` and other keywords cannot be markers. PDO never treats a `?` or `:word` inside a string literal or comment as a marker. Since PHP 8.4 each driver supplies its own **SQL parser** for that scan, so driver-specific syntax such as MySQL's backslash-escaped quotes is recognised too; before 8.4 such quotes could make PDO misplace markers. Since PHP 7.4, `??` in a template is sent as a single `?`, which matters for PostgreSQL operators that are spelled with a question mark. ## Reusing a statement A `PDOStatement` can be executed many times. In the product-search service, a statement that loads one product by id can be prepared once and executed for each id in a batch, which lets the driver reuse what it learned about the query. Each `execute()` replaces the previous values. ## Mistakes seen in code review 1. Quoting the marker, `WHERE sku = '?'`, which compares with a literal question mark. 2. Building the SQL with `"... WHERE name = '$name'"` and then calling `prepare()` on it: preparing a string that already contains the value protects nothing. 3. Passing an associative array to a `?` statement, or a list to a `:name` statement. 4. Reusing a named marker twice with native prepares, which fails with a parameter error. 5. Treating a `LIKE` search term as literal text, forgetting that `%` and `_` inside it are still wildcards. ## What execute() returns and throws Under the default exception error mode, a failing `execute()` throws `PDOException`; on success it returns `true`, and the rows are read with the fetch methods. Values passed through the `execute()` array are all sent as strings (`PDO::PARAM_STR`), which is usually fine and occasionally not, as with `LIMIT`; typed binding with `bindValue()` handles those cases.

  • Can the same :name marker appear twice in one statement?
    Only when prepares are emulated. The PDO manual states that a named marker may not be used more than once unless emulation mode is on; with native prepares you use two names, such as `:term1` and `:term2`, and bind the same value to both.
  • A search term contains % or _. Does binding it with LIKE ? keep those characters literal?
    No. Binding stops the value from changing the SQL, but inside the value `%` and `_` are still `LIKE` wildcards. Escape them before binding, for example with `addcslashes($term, '%_\\')`, so a user searching for `50%` matches that text rather than everything starting with 50.

saying these in an interview costs you the question

  • A ? inside quotes, as in LIKE '%?%', is still a placeholder
  • Named and positional placeholders can be combined in one query
  • Keys in the execute() array must include the leading colon
  • One ? can take a whole PHP array for an IN list
  • Prepared statements let you bind table and column names too