Errors and Debugging
PDOException and its SQLSTATE, catching a duplicate key on purpose, and logging the query without logging the values.
try { $stmt->execute($params); } catch (PDOException $e) { if ($e->getCode() === '23000') { /* duplicate */ } }With the error mode set to exceptions, which is the default since PHP 8.0, anything the database refuses throws a PDOException. It extends RuntimeException, so an existing catch (Throwable $e) already catches it. Left uncaught, it stops the request with a message that quotes your SQL, which is why production must never display errors.
SQLSTATE
Every failure carries a five-character SQLSTATE code, standard across databases. $e->getCode() returns it as a string, not an integer, so compare it with === against a string. The driver's own code and message are in $e->errorInfo, an array of three values: SQLSTATE, the driver's numeric code, and the driver's message.
| SQLSTATE | Meaning |
|---|---|
| 23000 | Constraint violated, including a duplicate key on MySQL |
| 23505 | Unique constraint violated on PostgreSQL |
| 42S02 | Table not found: the migration did not run |
| 42S22 | Column not found: a typo, or code ahead of the schema |
Failures you expect
A second signup with an existing email is not a bug, it is a user. Let the unique index reject it and catch 23000, rather than checking first with a SELECT and hoping nobody signs up in between.
Logging
Log the SQL text and the SQLSTATE. Do not log the bound values: they are the password, the email, the card reference. Log an id instead.
Example
When to use it
- Turn a duplicate email insert into a readable message on the signup form.
- Tell a missing table apart from a missing column when a deploy breaks.
- Keep customer emails and password hashes out of the application log.
- Diagnose 'could not find driver' and 'access denied' from the SQLSTATE alone.
More examples
Catching a duplicate signup on purpose
Checking with a SELECT first does not work: two requests can both find nothing and both insert. The unique index is the only thing that can decide, so the right design is to try the insert and translate the one SQLSTATE you expected. Everything else is rethrown, because an unknown failure must not be presented as a duplicate email. Passing $e as the third argument keeps the original in getPrevious() for the log.
<?php
declare(strict_types=1);
// The table has: UNIQUE KEY users_email_unique (email)
function createUser(PDO $pdo, string $email, string $passwordHash): int
{
$stmt = $pdo->prepare(
'INSERT INTO users (email, password_hash) VALUES (:email, :password_hash)'
);
try {
$stmt->execute(['email' => $email, 'password_hash' => $passwordHash]);
return (int) $pdo->lastInsertId();
} catch (PDOException $e) {
// 23000 on MySQL, 23505 on PostgreSQL.
if ($e->getCode() === '23000' || $e->getCode() === '23505') {
throw new RuntimeException('That email address is already registered.', 409, $e);
}
throw $e;
}
}
A log line with no customer data in it
This logs everything you need to find the bug and nothing you would have to delete later: the SQLSTATE, the driver code, the query text and the names of the placeholders, never their values. It deliberately leaves out $e->getMessage(), because the driver's message often quotes the offending value. Wrap your writes in a helper like this once, rather than remembering the rule at every call site.
<?php
declare(strict_types=1);
function runWrite(PDO $pdo, string $sql, array $params): void
{
try {
$pdo->prepare($sql)->execute($params);
} catch (PDOException $e) {
error_log(sprintf(
'db write failed sqlstate=%s driver=%s sql=%s keys=%s',
$e->errorInfo[0] ?? 'unknown',
$e->errorInfo[1] ?? 'unknown',
$sql,
implode(',', array_keys($params)),
));
throw $e;
}
}
Reading the whole error, in development only
A table-not-found error is almost always a typo or a migration that has not run, and errorInfo tells you which table it could not find. Use this shape while you are working, and never in production: the default uncaught-exception page prints the SQL, the file path and the line, which tells an attacker your schema. The two ini settings in the comment are what stop that.
<?php
declare(strict_types=1);
try {
$stmt = $pdo->prepare('SELECT id FROM userz WHERE id = :id');
$stmt->execute(['id' => 1]);
} catch (PDOException $e) {
// errorInfo: [SQLSTATE, driver code, driver message]
// ['42S02', 1146, "Table 'shop.userz' doesn't exist"]
var_dump($e->getCode(), $e->errorInfo);
}
// Dumping to the page is a development habit. In production:
// display_errors = Off
// log_errors = On
// so the visitor sees your error page and the detail goes to the log.
Discussion