Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

PDO throws SQLSTATE[HY093] Invalid parameter number

ErrorSQLSTATE[HY093]: Invalid parameter number: parameter was not defined

Short 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.

  • Applies to PHP 8.3, PHP 8.4, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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);
  2. 2Match what you see, then jump to its fix

    1. 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. 2'mixed named and positional parameters'.Named and question-mark placeholders are mixed in one statementFix
    3. 3The 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. 4A LIKE query with '%?%' or '%:term%' fails with a count mismatch.The placeholder is inside quotes, so it is not a placeholderFix

What does the error look like?

  • SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  • SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens
  • SQLSTATE[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

Did this fix it?

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.php

2Fix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.