In PHP, how do PDO::prepare() and execute() run a query with user input, and how do ? and :name placeholders differ?
answer
- template first, values later
- list for ?, keyed array for :name
- never mix the two styles
- a marker is a whole value, unquoted
- HY093: Invalid parameter number
basics
~20 sPDO::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
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
Know prepare() then execute(), a list for ? and a keyed array for :name, and that a marker is never put inside quotes.
Explain the matching rules, the HY093 errors, why LIKE patterns are bound whole, and what the 8.4 parsers changed about markers inside literals.
Recognise the places binding cannot reach, identifiers and wildcards in values, and review code for markers used as partial literals.
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