PDO prepared statements and error handling in PHP

Most PDO bugs are not about connecting. They are about the space between prepare() and the row you get back: a placeholder array with one key too many, a boolean that arrives at MySQL as an empty string, a bindParam() inside a loop that binds every row to the last value, an IN () list built by string concatenation, a lastInsertId() read one statement too late, and a catch block that compares the wrong error code. Each of those has a short, exact rule. This post is the reference for them: how placeholders are matched, how values are typed, how errors are reported and what each common SQLSTATE means, which fetch mode to use for which shape of data, and how transactions, insert IDs and row counts really behave on MySQL.

It assumes a working connection. The DSN, charset=utf8mb4, the three options worth setting, the localhost socket trap and TLS are covered in PHP PDO and MySQL; the examples here use a $pdo created that way, with ERRMODE_EXCEPTION, FETCH_ASSOC as the default fetch mode and ATTR_EMULATE_PREPARES set to false.

Positional or named placeholders#

PDO accepts two placeholder styles. Positional placeholders are a bare ?, matched to values by order. Named placeholders are a colon followed by a name, :email, matched by name. Both produce the same prepared statement; the choice is about what reads better and what is easier to keep correct as the query grows.

// Positional: values in the order the ? appear$stmt = $pdo->prepare('SELECT id FROM users WHERE email = ? AND status = ?');$stmt->execute([$email, 'active']);// Named: keys match names, order does not matter$stmt = $pdo->prepare('SELECT id FROM users WHERE email = :email AND status = :status');$stmt->execute(['status' => 'active', 'email' => $email]);

Positional is fine for two or three values. Past that, named placeholders win, because inserting a new condition in the middle of a positional query silently shifts every later value by one - and if the types happen to be compatible, MySQL runs the wrong query without complaint.

The rules that trip people up:

• You cannot mix the two styles in one statement. WHERE a = ? AND b = :b fails with SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters.
• The colon in the array key is optional. ['email' => $x] and [':email' => $x] both work. Pick one and keep it, because a codebase that uses both makes searching harder.
• The count must match exactly. Passing a key the query does not use is not ignored - it is HY093: Invalid parameter number: number of bound variables does not match number of tokens. That catches people who pass a whole $_POST array or a reused parameter array to a query that needs only part of it.
• Positional arrays must be lists. After array_filter() or unset() the keys are no longer 0, 1, 2, and execute() cannot match them. Wrap the array in array_values() first.
• A named placeholder can appear once when prepares are native. WHERE title LIKE :q OR body LIKE :q works under emulation and fails natively with HY093. Use :q1 and :q2 and pass the value twice.
• Placeholders hold values only. Table names, column names, ASC/DESC and SQL keywords cannot be bound; they come from an allow-list in your code.

A placeholder also cannot sit inside a quoted string. LIKE '%:q%' is a literal string containing the characters :q, not a placeholder. Build the pattern in PHP and bind the whole thing: LIKE :q with '%' . $term . '%'. If the search term can contain % or _ and they should match literally, escape them first with addcslashes($term, '%_\\'), because both are wildcards inside LIKE and a placeholder does not change that.

bindValue, bindParam and value types#

execute($array) is the short form. It binds every value as PDO::PARAM_STR, except null, which is sent as SQL NULL. MySQL converts strings to numbers when a column or comparison needs it, so for most queries this is exactly right. The long form is binding values one at a time, which is how you control the type.

There are two binding methods and they behave differently:

bindParam() exists so that one prepared statement can be executed repeatedly while you change the variable between runs. That reference behaviour is also its classic bug:

// Wrong: every placeholder ends up bound to the last $valueforeach ($params as $key => $value) {    $stmt->bindParam($key, $value);}// Right: bindValue copies the value nowforeach ($params as $key => $value) {    $stmt->bindValue($key, $value);}

In the first loop each bindParam() call binds the same variable, $value, which by the time execute() runs holds whatever the last iteration left in it. Use bindValue() unless you specifically want the reference.

The type constants that matter on MySQL:

Positional placeholders are numbered from 1 when you bind them individually: $stmt->bindValue(1, $id, PDO::PARAM_INT). Named ones take the name, with or without the colon.

Booleans, NULL and money

Three values cause most type surprises.

A PHP false passed through execute() is converted to a string, and false as a string is ''. MySQL's default sql_mode in 8.0 and 8.4 includes STRICT_TRANS_TABLES, so inserting '' into a TINYINT column fails with error 1366, Incorrect integer value: '' for column 'is_active'. Without strict mode the same insert succeeds with a warning and stores 0, which hides the bug until someone turns strict mode on. Cast booleans to integers before binding - (int) $isActive - and the problem disappears in both modes.

NULL binds correctly, but it does not compare the way people expect. WHERE deleted_at = ? with a null value matches no rows at all, because in SQL NULL = NULL is not true. To match a value that may be NULL, use MySQL's null-safe operator: WHERE deleted_at <=> ?. It treats two NULLs as equal and otherwise behaves like =.

Money and other exact decimals should be bound as strings, not floats. '19.99' reaches a DECIMAL(10,2) column exactly; 19.99 as a PHP float is a binary approximation that MySQL then rounds. The same applies on the way back out: DECIMAL columns are returned as strings even with native prepares, by design, so the value is not damaged by float conversion.

IN() lists done properly#

There is no way to bind an array to a single placeholder. WHERE id IN (?) with [1, 2, 3] does not work, and imploding the values into the SQL string undoes the point of preparing. The answer is one placeholder per value, generated from the array.

With positional placeholders:

if ($ids === []) {    return [];  // IN () is a syntax error; an empty list matches nothing anyway}$marks = implode(',', array_fill(0, count($ids), '?'));$stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($marks)");$stmt->execute(array_values($ids));

When the same query has other conditions, positional placeholders make you merge the arrays in exactly the order the ? marks appear, which is fragile. Named placeholders keep it readable:

$params = ['status' => 'active'];$names = [];foreach (array_values($ids) as $i => $id) {    $names[] = ":id$i";    $params["id$i"] = (int) $id;}$sql = 'SELECT id, name FROM products WHERE status = :status AND id IN ('     . implode(', ', $names) . ')';$stmt = $pdo->prepare($sql);$stmt->execute($params);

The generated names come from a loop counter, never from the input, so nothing user-supplied enters the SQL text.

Very long lists

Two ceilings apply. MySQL's protocol allows at most 65,535 placeholders in one prepared statement; past that, prepare() fails with error 1390, Prepared statement contains too many placeholders. Long before that, a query with tens of thousands of values becomes slow to parse and plan, and the whole packet must fit inside max_allowed_packet (64 MB by default in MySQL 8). In practice, chunk the list at 500 to 1,000 values and run the query once per chunk:

$rows = [];foreach (array_chunk($ids, 1000) as $chunk) {    $marks = implode(',', array_fill(0, count($chunk), '?'));    $stmt = $pdo->prepare("SELECT id, name FROM products WHERE id IN ($marks)");    $stmt->execute($chunk);    array_push($rows, ...$stmt->fetchAll());}

An alternative on MySQL 8 is to send the list as one JSON string and unpack it on the server with JSON_TABLE, which needs only a single placeholder whatever the list length:

SELECT p.id, p.nameFROM products pJOIN JSON_TABLE(?, '$[*]' COLUMNS (id INT PATH '$')) AS j ON j.id = p.id;

Bind json_encode(array_values($ids)) to it. The statement text never changes, which helps when the same query runs with lists of different lengths. Avoid FIND_IN_SET(id, ?) with a comma-separated string: it works, but it cannot use an index and scans the table.

What native prepares change about errors#

ATTR_EMULATE_PREPARES => false is the recommended setting, and the connection post explains why: real server-side statements, native integer and float types, and errors that arrive early. It also changes where and how errors appear, which matters for error handling.

• Syntax errors arrive at `prepare()`. With native prepares, prepare() sends the SQL to MySQL, which parses it and rejects a typo immediately with SQLSTATE 42000, error 1064. Under emulation, prepare() only parses the placeholders locally and the same typo surfaces at execute(). If your try block wraps only execute(), native mode moves the exception outside it.
• Unknown tables and columns arrive at `prepare()` too - 42S02 and 42S22 - for the same reason.
• One statement per prepare. A native prepared statement holds exactly one SQL statement. prepare('UPDATE a SET x = 1; UPDATE b SET y = 2') is a syntax error. Under emulation the MySQL driver accepts multiple statements by default, which is convenient for migration scripts and a gift to anyone who finds an injection point. Setting PDO::MYSQL_ATTR_MULTI_STATEMENTS => false in the connection options closes that off even when emulation is on (on PHP 8.4 and later, the constant is Pdo\Mysql::ATTR_MULTI_STATEMENTS).
• Statements are server resources. Each native prepare creates a statement on the server that lives until the PDOStatement object is destroyed. MySQL caps the total across all connections with max_prepared_stmt_count, default 16,382. A long-running worker that calls prepare() inside a loop and keeps every statement object in an array can hit error 1461, Can't create more than max_prepared_stmt_count statements. Prepare once outside the loop and execute many times, or let the objects go out of scope.

Error modes and what a PDOException carries#

PDO has three error modes, set with PDO::ATTR_ERRMODE:

The constructor is the exception: new PDO(...) throws a PDOException on a failed connection whatever the error mode, because there is no object to call errorInfo() on. Everything after construction follows the mode.

Silent mode is the source of the most expensive PDO bugs, because a failed execute() returns false, the code carries on, and the result is a missing order line rather than an error. In silent mode, errors also live in two places: a failed prepare() is recorded on the PDO object ($pdo->errorInfo()), and a failed execute() on the statement ($stmt->errorInfo()). Code that checks the wrong one sees 00000 - success. If you inherit silent-mode code, switching it to exceptions is usually the single best fix available.

A PDOException carries the error in three forms:

try {    $stmt->execute($params);} catch (PDOException $e) {    $sqlstate = $e->getCode();          // '23000' - a string, not an int    $driverCode = $e->errorInfo[1] ?? null;  // 1062 - the MySQL error number    $driverMsg = $e->errorInfo[2] ?? null;   // "Duplicate entry 'a@b.c' for key ..."}

getCode() returns the five-character SQLSTATE as a string, which is why PDOException is one of the few exceptions where getCode() is not an integer; a strict comparison with 23000 as a number never matches. errorInfo is an array of SQLSTATE, driver error number and driver message. For failed connections, use the message and getCode(), which holds the MySQL number such as 1045 or 2002; do not rely on errorInfo being filled there.

Branch on the MySQL number where you can. SQLSTATE is a category, and some categories are crowded: 23000 covers duplicates, foreign key failures and NULL in a NOT NULL column, and HY000 covers dozens of unrelated errors.

Catch narrowly, log fully

Catch a PDOException only where you can do something specific with it - turn a duplicate email into a form error, retry a deadlock - and rethrow everything else. Let unexpected database errors reach one handler at the top of the application that logs the full message and shows the visitor a generic page. The message can contain host names, user names and fragments of the query and data, so it belongs in the log, never in the response. The database security checklist covers the rest of what should not leak.

SQLSTATE and MySQL error reference#

The errors you will actually meet from queries, as opposed to connections (those are in the connection post). First, constraint and data errors:

Then statement and runtime errors:

Three of these deserve a word. HY093 has no MySQL number, because it is raised by PDO before anything reaches the server; it is always a mismatch between placeholders and the values supplied, and the cases are listed in the placeholder section above. 22001 and 22003 only appear in strict mode - without STRICT_TRANS_TABLES, MySQL truncates the string or clamps the number, stores it and issues a warning nobody reads. And 1064 on a query that looks correct is often a column called order, key, rank or groups, all reserved words that need backticks.

The 50 seconds for 1205 is the default innodb_lock_wait_timeout. Deadlocks and lock waits are covered with a retry wrapper in MySQL transactions, locking and deadlocks.

Fetch modes beyond FETCH_ASSOC#

FETCH_ASSOC as the default suits most code. The other modes save a loop each time the shape you need is not "list of rows". Pass them to fetch() or fetchAll(), or set them on a statement with $stmt->setFetchMode().

FETCH_UNIQUE is the one people miss. SELECT id, name, price FROM products with fetchAll(PDO::FETCH_UNIQUE) returns [17 => ['name' => ..., 'price' => ...], ...], keyed by ID, which is exactly the lookup table you would otherwise build with a foreach. If the first column is not unique, later rows overwrite earlier ones without warning.

FETCH_GROUP can be combined with FETCH_COLUMN: SELECT category, name FROM products with fetchAll(PDO::FETCH_GROUP | PDO::FETCH_COLUMN) returns ['tools' => ['saw', 'drill'], 'paint' => [...]].

Hydrating classes

FETCH_CLASS creates an object per row and assigns columns to properties of the same name:

final class Product{    public int $id;    public string $name;    public string $price;    public function __construct() { /* runs after properties are set */ }}$stmt = $pdo->query('SELECT id, name, price FROM products');$products = $stmt->fetchAll(PDO::FETCH_CLASS, Product::class);

Two behaviours surprise people. By default PDO sets the properties before calling the constructor, so a constructor that assigns defaults overwrites the data it was given. Add PDO::FETCH_PROPS_LATE (FETCH_CLASS | FETCH_PROPS_LATE) to run the constructor first. And a column with no matching property is still assigned, as a dynamic property, which raises a deprecation notice since PHP 8.2 unless the class allows dynamic properties. Select only the columns the class declares, or alias them to match.

Typed properties interact with the fetch types: under native prepares an INT column arrives as an int and fits an int property; a DECIMAL arrives as a string, which is why $price is declared string above.

Empty results

fetch() and fetchColumn() return false when there are no more rows; fetchAll() returns an empty array. That makes if ($row = $stmt->fetch()) the right existence check, and it means a fetchColumn() on a COUNT(*) query is safe - a count always returns one row. Beware fetchColumn() on a column that can legitimately hold false-like values: a 0 or '0' is returned as itself, but code that tests the result with if (!$value) treats it as "no row".

Transactions, savepoints and retries#

The basic pattern - beginTransaction(), the work, commit(), and rollBack() in a catch - is in the connection post. The edges are where code goes wrong.

PDO does not nest transactions. Calling beginTransaction() while one is active throws There is already an active transaction. Code that calls a helper which opens its own transaction, from inside another transaction, needs either a wrapper that tracks depth or savepoints, which MySQL supports with plain SQL:

function transactional(PDO $pdo, callable $work): mixed{    static $depth = 0;    $outer = $depth === 0;    $outer ? $pdo->beginTransaction() : $pdo->exec("SAVEPOINT sp$depth");    $depth++;    try {        $result = $work($pdo);        $depth--;        $outer ? $pdo->commit() : $pdo->exec("RELEASE SAVEPOINT sp$depth");        return $result;    } catch (Throwable $e) {        $depth--;        if ($outer) {            if ($pdo->inTransaction()) {                $pdo->rollBack();            }        } else {            $pdo->exec("ROLLBACK TO SAVEPOINT sp$depth");        }        throw $e;    }}

An inner failure rolls back only to its savepoint; the outer transaction decides whether to continue. Laravel's DB::transaction() works the same way, which is why nesting there does not throw.

Things that end a transaction without asking:

• DDL. CREATE, ALTER, DROP and TRUNCATE commit implicitly in MySQL. The changes before them are committed and cannot be rolled back, and on PHP 8 the later commit() throws There is no active transaction.
• A deadlock. Error 1213 rolls back the whole transaction on the server. Retrying the last statement is wrong; retry the whole unit from beginTransaction().
• A lost connection. Error 2006 or 2013 mid-transaction means the server rolled back. The PDO object is no longer usable.

Lock wait timeouts (1205) are the odd one: by default they roll back only the statement that timed out, leaving the transaction open. Roll back explicitly before you retry, or you will commit half the work.

Keep transactions short and keep slow things out of them. A transaction that holds row locks while it calls a payment API or sends an email makes every other request touching those rows wait - and wait timeouts follow.

lastInsertId and rowCount edge cases#

$pdo->lastInsertId() returns the AUTO_INCREMENT value generated by the most recent statement on this connection, as a string. It is per connection, so concurrent requests never see each other's IDs. The edges:

• Read it immediately. A later statement that does not generate an ID can reset the value to 0. Call it right after the INSERT, before any other query on that connection.
• Multi-row inserts return the first ID. INSERT INTO tags (name) VALUES (?), (?), (?) returns the ID of the first row. Do not compute the others by adding 1 and 2; depending on innodb_autoinc_lock_mode and concurrent inserts, that assumption is not guaranteed. If you need every ID, insert row by row inside a transaction, or read them back by a unique key.
• Tables without `AUTO_INCREMENT` - UUID keys, natural keys - return '0'. Generate the key in PHP and you already have it.
• `INSERT IGNORE` that skipped the row generates no ID. Check rowCount() to know whether anything was inserted.

INSERT ... ON DUPLICATE KEY UPDATE needs care for both. MySQL reports rowCount() as 1 for a new row, 2 for an updated existing row and 0 for an existing row that already had those values. And when the row already existed, lastInsertId() does not give you its ID - unless you use MySQL's documented trick of passing the ID through LAST_INSERT_ID(expr):

INSERT INTO users (email, name) VALUES (:email, :name)ON DUPLICATE KEY UPDATE name = VALUES(name), id = LAST_INSERT_ID(id);

Now lastInsertId() returns the row's ID in both cases. MySQL 8.0.20 deprecated VALUES(col) in this clause in favour of a row alias (VALUES (...) AS new ... UPDATE name = new.name); VALUES() still works in 8.4 but raises a warning, so new code should use the alias form.

For UPDATE, rowCount() counts rows changed, not rows matched, so updating a row to its current values returns 0. Setting PDO::MYSQL_ATTR_FOUND_ROWS => true at connection time switches it to matched rows. For SELECT, do not use rowCount() at all: the PHP manual warns that its result for a SELECT is not portable. Count with COUNT(*), or count the array you fetched.

Where this runs on RE:NODE#

None of the above depends on the host, but a few facts help if you run it here. MySQL on database hosting is MySQL 8.4 LTS, so JSON_TABLE, the row-alias form of ON DUPLICATE KEY UPDATE and the 8.x defaults described in this post all apply; the root password, an application database and an application user are generated when the server is created. The Laravel / PHP app line runs PHP 8.3, 8.4 or 8.5 on FrankenPHP, which matters for the Pdo\Mysql constant names, and app and web plans include database slots created in the panel with a generated host, user and password. "Open in phpMyAdmin" on a slot is the quickest way to run SHOW CREATE TABLE when you need to see which column is still not utf8mb4 or which key a 1062 refers to.

FAQ#

Do prepared statements make a query completely safe from SQL injection?

They make values safe. Anything concatenated into the SQL text - a column name for sorting, a table name, a direction keyword, a LIMIT built from a string - is outside the protection. Map those onto a fixed allow-list in code. The other gap is stored data reused later: a value safely inserted with a placeholder is still untrusted when you read it back and must go through a placeholder again.

Is it worth preparing a statement I only run once?

Yes, for safety. The cost of a native prepare is one extra round trip to the server, which on a nearby database is well under a millisecond. Code that switches between query() and prepare() depending on whether a value is involved eventually gets it wrong; preparing everything that takes input removes the decision.

Why does execute() return true but nothing changes?

Either the WHERE matched no rows - rowCount() will be 0 - or the work ran inside a transaction that was never committed. With autocommit off, or after a beginTransaction() with no commit(), the changes are rolled back when the connection closes at the end of the request. Check $pdo->inTransaction() at the end of the script while debugging.

Can I reuse one PDOStatement with different values?

Yes, and it is the intended use. Prepare once, then call execute() with a new array as many times as you like; with native prepares the server parses the SQL only once. If a previous SELECT on the statement left rows unread, call closeCursor() before executing it again.

How do I log the SQL with the values filled in?

PDO does not produce the final query string with native prepares, because the server receives the SQL and values separately. $stmt->debugDumpParams() prints the SQL and each bound parameter, which is usually enough. For a complete record of what the server ran, use MySQL's general query log on a development machine, or the slow query log in production for the queries that matter.