PHP PDO and MySQL: connections, prepared statements, errors

A correct PDO connection to MySQL is five lines: a DSN with the host, port, database and charset=utf8mb4, the username and password from the environment, and three options - exceptions on error, associative arrays by default, and native prepared statements. Everything after that is using prepared statements for every value that comes from outside the code, wrapping multi-statement changes in transactions, and knowing which error code means what. This post covers each piece, including the defaults that changed in PHP 8 and the constant names that moved in PHP 8.4 and 8.5, so the code you write today does not throw deprecation warnings next year.

The DSN and a connection that is right the first time#

PDO connects with a data source name string, a username and a password. For MySQL the DSN prefix is mysql: and the parameters are separated by semicolons.

<?php$dsn = sprintf(    'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',    getenv('DB_HOST'),    (int) getenv('DB_PORT'),    getenv('DB_NAME'));$pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,    PDO::ATTR_EMULATE_PREPARES   => false,]);

The DSN parameters for MySQL:

The localhost trap catches everyone once. The MySQL client library treats the host name localhost as a request to use the local Unix socket, so host=localhost;port=3307 ignores the port and fails with SQLSTATE[HY000] [2002] No such file or directory when there is no local server. If the database is on another machine - a hosted database always is - use its real host name or address. If it is on the same machine but listening on TCP, use 127.0.0.1.

Keep the credentials out of the file. Read them from environment variables or a config file outside the web root; a db.php with a password in it is one misconfigured web server away from being served as text. Environment variables and secrets covers where they should live.

One connection per request, and the long-running exception

In ordinary PHP behind PHP-FPM, create the PDO object once per request - in a small factory function or your framework's container - and pass it to whatever needs it. Opening a fresh connection inside every function that runs a query multiplies handshakes and connections for no benefit; a page that calls twenty helpers should not open twenty connections. When the request ends, PHP destroys the object and the connection closes. There is no pool to manage, and the number of simultaneous connections is simply the number of busy FPM workers.

Long-running PHP is different. A queue worker, a WebSocket server, a scheduled script that sleeps between batches, or an application server such as RoadRunner, Swoole or FrankenPHP in worker mode keeps one PDO object alive for hours. MySQL closes idle connections after wait_timeout seconds - 28,800 by default, often set lower on shared servers - and the next query on the stale object fails with 2006 MySQL server has gone away. PDO does not reconnect by itself. The fix is to catch that error at the edge of each job, discard the PDO object, create a new one and retry the job once:

function db(bool $fresh = false): PDO{    static $pdo = null;    if ($fresh || $pdo === null) {        $pdo = new PDO($GLOBALS['dsn'], getenv('DB_USER'), getenv('DB_PASSWORD'), $GLOBALS['opts']);    }    return $pdo;}try {    handleJob(db(), $job);} catch (PDOException $e) {    if (!in_array($e->errorInfo[1] ?? 0, [2006, 2013], true)) {        throw $e;    }    handleJob(db(true), $job);  // reconnect once and retry}

Only retry work that is safe to run twice, or that was inside a transaction that the lost connection rolled back. Laravel's queue worker does a version of this for you, which is one reason framework workers are restarted periodically with --max-jobs or --max-time.

The options worth setting, and their defaults#

PDO's defaults have improved, but not all of them suit MySQL.

Set the error mode explicitly even on PHP 8, because code gets copied into projects with older settings and because it documents intent. Silent mode - where execute() returns false and you are expected to check - is how bugs that should have been loud become missing rows.

The constants moved in PHP 8.4 and 8.5

PHP 8.4 added driver-specific subclasses: Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite. PDO::connect($dsn, $user, $password, $options) returns the subclass matching the DSN, and the MySQL-specific attributes now live on it as Pdo\Mysql::ATTR_SSL_CA, Pdo\Mysql::ATTR_INIT_COMMAND, Pdo\Mysql::ATTR_FOUND_ROWS and so on. PHP 8.5 deprecates the old PDO::MYSQL_ATTR_* spellings. They still work, but they raise a deprecation notice, which breaks test suites that treat notices as failures - several frameworks hit exactly that with PDO::MYSQL_ATTR_SSL_CA in their default config. If you support PHP below 8.4, keep the old names; if your minimum is 8.4, switch now.

Prepared statements#

A prepared statement sends the SQL with placeholders and the values separately, so a value can never change the structure of the query. That is the whole defence against SQL injection, and it only works if every value from outside the code goes through a placeholder.

// Positional placeholders$stmt = $pdo->prepare('SELECT id, email FROM users WHERE status = ? AND created_at > ?');$stmt->execute(['active', '2026-01-01']);$users = $stmt->fetchAll();// Named placeholders$stmt = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');$stmt->execute(['email' => $email, 'id' => $id]);echo $stmt->rowCount();  // rows actually changed

Values passed to execute() are all bound as strings, which MySQL converts as needed. When the type matters, bind explicitly:

$stmt = $pdo->prepare('SELECT id, title FROM posts ORDER BY id DESC LIMIT :limit OFFSET :offset');$stmt->bindValue('limit', $perPage, PDO::PARAM_INT);$stmt->bindValue('offset', ($page - 1) * $perPage, PDO::PARAM_INT);$stmt->execute();

LIMIT is the classic case. With emulated prepares, binding the limit through execute() produces LIMIT '10', which is a syntax error. With native prepares or PARAM_INT it works.

What placeholders cannot do

Placeholders replace values, never identifiers or keywords. A table name, a column name in ORDER BY, or ASC/DESC cannot be bound. For those, map user input onto a fixed list:

$sortable = ['created_at' => 'created_at', 'title' => 'title'];$column = $sortable[$_GET['sort'] ?? ''] ?? 'created_at';$direction = ($_GET['dir'] ?? '') === 'asc' ? 'ASC' : 'DESC';$stmt = $pdo->query("SELECT id, title FROM posts ORDER BY $column $direction");

An IN (...) list needs one placeholder per value, built from the array:

$ids = array_map('intval', $ids);$marks = implode(',', array_fill(0, count($ids), '?'));$stmt = $pdo->prepare("SELECT * FROM products WHERE id IN ($marks)");$stmt->execute($ids);

Guard against an empty array before this; IN () is a syntax error.

Emulated versus native prepares#

By default the MySQL driver does not use server-side prepared statements at all. With ATTR_EMULATE_PREPARES on, PDO escapes each value itself and sends one finished query string. That is safe as long as the client knows the connection character set - which is the reason charset=utf8mb4 belongs in the DSN rather than in a SET NAMES query afterwards: PDO only knows about a charset it was told in the DSN, and escaping with the wrong idea of the charset is the root of the old multibyte injection tricks.

The differences that show up in practice:

• Types. Native prepares use MySQL's binary protocol, so integers come back as PHP integers and floats as floats. Emulated prepares returned everything as strings until PHP 8.1, which changed them to return native integers and floats as well. On older code, that change is worth knowing about when a strict comparison like $row['id'] === '5' suddenly fails after an upgrade.
• Repeated named placeholders. Emulation lets you use :term twice in one query. Native prepares do not - you get SQLSTATE[HY093]: Invalid parameter number. Use :term1 and :term2.
• Round trips. A native prepare is a separate request to the server before the execute. For a statement run once, that is one extra round trip; for one run in a loop, preparing once and executing many times is faster.
• Errors. With native prepares, a syntax error surfaces at prepare(). With emulation it surfaces at execute().

Either mode is secure when used correctly. Native is the better default because the types are right and errors arrive earlier; switch emulation on only if you rely on repeated named placeholders or have measured the extra round trip.

Character sets: utf8mb4 end to end#

charset=utf8mb4 sets the character set of the connection - what PHP sends and expects back. The tables need to be utf8mb4 too, which in MySQL 8.4 is the server default with the utf8mb4_0900_ai_ci collation. The old utf8 (an alias of utf8mb3) stores at most three bytes per character and rejects emoji and some CJK characters with error 1366, Incorrect string value: '\xF0\x9F\x98\x80'. If you see that, a column or table is still utf8mb3; MySQL utf8mb4 and collations covers converting it.

If you need a specific collation for the connection, set it with an init command, which runs once per connection:

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci",// PHP 8.4+: Pdo\Mysql::ATTR_INIT_COMMAND

The same option is the place to set a session time zone (SET time_zone = '+00:00') or sql_mode, if the application depends on one.

Fetching results and streaming large ones#

The fetch methods are worth knowing beyond fetchAll():

By default PDO buffers the whole result set in PHP memory before your loop sees the first row. That is what you want for normal pages and what makes a 2-million-row export run out of memory_limit. For exports, turn buffering off for that one query and iterate:

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);$stmt = $pdo->query('SELECT id, email, created_at FROM users');$out = fopen('php://output', 'w');foreach ($stmt as $row) {    fputcsv($out, $row);}$stmt->closeCursor();$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

While an unbuffered result is open, the connection cannot run another query - you get SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Finish the loop and call closeCursor() first. PHP ini settings that matter covers memory_limit and max_execution_time for the jobs that need them.

Transactions, insert IDs and row counts#

Anything that changes more than one row as a single unit belongs in a transaction:

try {    $pdo->beginTransaction();    $stmt = $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)');    $stmt->execute([$userId, $total]);    $orderId = (int) $pdo->lastInsertId();    $line = $pdo->prepare('INSERT INTO order_lines (order_id, sku, qty) VALUES (?, ?, ?)');    foreach ($items as $item) {        $line->execute([$orderId, $item['sku'], $item['qty']]);    }    $pdo->commit();} catch (Throwable $e) {    if ($pdo->inTransaction()) {        $pdo->rollBack();    }    throw $e;}

Three details in that example are easy to get wrong:

• lastInsertId() returns the AUTO_INCREMENT value of the last insert on this connection, as a string. It is per connection, so concurrent requests do not see each other's IDs.
• rowCount() after an UPDATE returns rows actually changed. Updating a row to the values it already has counts as zero, which surprises code that uses the count to decide whether the row exists. MYSQL_ATTR_FOUND_ROWS => true changes it to rows matched.
• DDL statements such as CREATE TABLE or ALTER TABLE commit implicitly in MySQL. A migration that mixes DDL into a transaction cannot be rolled back, and on PHP 8 a later commit() throws There is no active transaction.

Deadlocks (error 1213) roll back the whole transaction and should be retried; lock wait timeouts (1205) roll back only the statement by default. MySQL transactions, locking and deadlocks has a retry wrapper and the reasons each one happens.

Errors and what they mean#

A PDOException carries the SQLSTATE in getCode() and the MySQL error number in $e->errorInfo[1]. Branch on the MySQL number; SQLSTATE HY000 covers dozens of unrelated errors.

try {    $stmt->execute([$email]);} catch (PDOException $e) {    if (($e->errorInfo[1] ?? null) === 1062) {        return 'That email is already registered.';    }    throw $e;}

For 1045, remember that a MySQL account is a user and a host pattern together: 'app'@'localhost' and 'app'@'%' are different accounts with different passwords. MySQL users and privileges explains the matching rules, and MySQL connection limits and pooling covers 1040 and 2006.

Never echo $e->getMessage() to visitors in production. It can contain the host name, the user name and parts of the query. Log it and show a generic error.

Encrypted connections#

MySQL 8.4 enables TLS on the server by default, but PDO does not ask for it unless you set an SSL option. Across the internet, ask for it:

$pdo = new PDO($dsn, $user, $pass, [    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,    PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/db-ca.pem',    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,]);

If the server uses a certificate it generated for itself and you do not have its CA file, you can still encrypt by keeping an SSL option in the array and setting MYSQL_ATTR_SSL_VERIFY_SERVER_CERT to false - that encrypts the traffic but proves nothing about who answered. It is better than plain text and worse than verification. Check SHOW SESSION STATUS LIKE 'Ssl_cipher' from the PHP side: an empty value means the connection is not encrypted.

FAQ#

PDO or mysqli?

PDO, for most code. It has named placeholders, the same API for other databases, and cleaner exceptions. mysqli exposes a few MySQL-specific features PDO does not, such as asynchronous queries and multi-statement result handling. Both are maintained and both are safe with prepared statements.

Is escaping with quote() as good as a prepared statement?

It escapes correctly when the connection charset is set in the DSN, but it is easy to forget once, and forgetting once is the vulnerability. Prepared statements make the safe path the default. Use quote() only where placeholders genuinely cannot work.

Should I use persistent connections?

Usually not. They keep one open connection per PHP-FPM worker, which saves a few milliseconds of handshake per request but leaves temporary tables, session variables and sometimes half-finished transactions for the next request on that worker. Measure the handshake first; across a short network path it is small.

Why are my integers coming back as strings?

Either you are on PHP older than 8.1 with emulated prepares, or ATTR_STRINGIFY_FETCHES is on. Set ATTR_EMULATE_PREPARES to false and the binary protocol returns native integers and floats. DECIMAL columns still come back as strings by design, to avoid floating-point rounding.

How do I turn on TLS for MySQL in Laravel?

Laravel's config/database.php passes an options array to PDO for the mysql connection, and the default file already reads a CA path from the MYSQL_ATTR_SSL_CA environment variable into the SSL CA attribute. Set that variable to the CA file's path in .env. Recent Laravel versions pick the Pdo\Mysql constant on PHP 8.5 to avoid the deprecation notice; on an older config file, update that line when you move to 8.5.

How do I see the actual query PDO ran?

$stmt->debugDumpParams() prints the SQL and bound parameters, and since PHP 7.2 it includes the expanded query when prepares are emulated. On the server side, the general query log or the slow query log shows exactly what arrived.