PDO throws SQLSTATE[HY093] Invalid parameter number
SQLSTATE[HY093]: Invalid parameter number: parameter was not definedShort answer
HY093 means the values you give PDO do not line up with the placeholders in the SQL. The usual reasons: named and question-mark markers mixed in one query, the same named marker used twice without emulation, a marker inside quotes, or a different number or spelling of values. Count the markers, then count the values.
Fix it fast
1Find out which cause you have
Print the SQL string and the array you pass to execute() side by side, then compare names and counts one by one.
var_dump($sql, $params); $stmt = $pdo->prepare($sql); $stmt->execute($params);2Match what you see, then jump to its fix
- 1
'number of bound variables does not match number of tokens', or 'parameter was not defined'.The values array does not match the placeholdersFix - 2
'mixed named and positional parameters'.Named and question-mark placeholders are mixed in one statementFix - 3
The query uses :name in two places; it works on one server or config and fails on another.The same named placeholder is used twice without emulationFix - 4
A LIKE query with '%?%' or '%:term%' fails with a count mismatch.The placeholder is inside quotes, so it is not a placeholderFix
- 1
What does the error look like?
SQLSTATE[HY093]: Invalid parameter number: parameter was not definedSQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokensSQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters
Cause 1 of 4
The values array does not match the placeholders
1Check
execute() expects as many values as there are bound parameters, and a key that does not exist in the SQL makes the statement fail. A typo in a name, a leftover key, or a placeholder removed from the SQL but still in the array are typical.
preg_match_all('/:\w+|\?/', $sql, $m);
var_dump(count($m[0]), count($params));2Fix
Make the SQL and the array carry exactly the same names, with a leading colon in the SQL and (optionally) in the keys.
$stmt = $pdo->prepare('SELECT * FROM users WHERE colour = :colour AND age > :age');
$stmt->execute([':colour' => $colour, ':age' => $age]);3Verify
The statement runs and returns rows.
var_dump($stmt->fetchAll());Confirmed by php.net
Cause 2 of 4
Named and question-mark placeholders are mixed in one statement
1Check
A statement template may use named markers or question-mark markers, not both.
grep -n "?.*:[a-z]\|:[a-z].*?" src/Repository.php2Fix
Use one style in the whole query.
$stmt = $pdo->prepare('SELECT * FROM t WHERE a = ? AND b = ?');
$stmt->execute([$a, $b]);3Verify
The prepare and execute calls no longer raise HY093.
var_dump($stmt->rowCount());Confirmed by php.net
Cause 3 of 4
The same named placeholder is used twice without emulation
1Check
A named marker may be used more than once only when emulation mode is on. PDO MySQL emulates prepares by default, so this shows up when ATTR_EMULATE_PREPARES was switched off or the driver is not MySQL.
grep -rn "ATTR_EMULATE_PREPARES" src/ config/2Fix
Give each occurrence its own name and bind the same value to both.
$stmt = $pdo->prepare('SELECT * FROM t WHERE a = :v1 OR b = :v2');
$stmt->execute([':v1' => $v, ':v2' => $v]);3Verify
The query runs with emulation on and off.
var_dump($stmt->fetchAll());Confirmed by php.net
Cause 4 of 4
The placeholder is inside quotes, so it is not a placeholder
1Check
A marker inside a quoted string is just text. The marker has to stand for the whole value.
grep -n "LIKE '%" src/2Fix
Put the wildcards in the value, not in the SQL.
$stmt = $pdo->prepare('SELECT * FROM registry WHERE name LIKE ?');
$stmt->execute(['%' . $term . '%']);3Verify
The search returns rows.
var_dump($stmt->fetchAll());Confirmed by php.net
How do I stop it coming back?
- Use one placeholder style per project and stick to it.
- Never reuse a named placeholder in one query; give each place its own name.
- Build the values array right next to the SQL string it belongs to.
- Turn on PDO::ERRMODE_EXCEPTION so a mismatch is an exception with a clear message, not a silent warning.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
CodeIgniter 4 where() builds a different condition than you meant
A SQL syntax error that appears only when a value contains a quote character
The query builder joins every where() with AND and every orWhere() with OR, with no brackets.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter cannot connect to the MySQL database server
Unable to connect to your database server using the provided settings.
CodeIgniter only reports that its connection failed.
4 causes, each with a fixBack end
Laravel insert fails with 'Field user_id doesn't have a default value'
The insert works when the column is removed or made nullable
Error 1364 means Laravel sent an INSERT without a value for a NOT NULL column that has no default, and MySQL strict mode refuses it.
3 causes, each with a fixBack end
Laravel migration cannot drop a column that has a foreign key
Error on rename of '%s' to '%s' (errno: %d - %s)
MySQL refuses to drop a column while a foreign key constraint still uses it.
2 causes, each with a fix