🔌 PDO, подготовленные запросы и транзакции
PDO — встроенный в PHP слой доступа к базе: один API для MySQL, PostgreSQL и SQLite, подготовленные запросы и транзакции. Поверх него работают Eloquent и Doctrine DBAL, поэтому, когда ORM делает что-то странное, разбираться приходится на уровне PDO: как запрос уходит в базу, почему плейсхолдер спасает от инъекции не везде и что случается с транзакцией, если скрипт упал
🧠 Как это устроено
Соединение и подклассы по драйверу
new PDO($dsn, $user, $password, $options) открывает соединение сразу. Ошибка подключения всегда бросает PDOException, какой бы режим ошибок ни стоял
С PHP 8.4 у каждого драйвера свой подкласс: Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite и другие. Получить его можно двумя способами: PDO::connect($dsn, ...) выбирает класс по DSN, или подкласс создают напрямую через new Pdo\Sqlite(...). Обычный new PDO('sqlite::memory:') по-прежнему возвращает голый PDO. Подклассы нужны для методов конкретной базы: у Pdo\Pgsql это copyFromArray и getNotify, у Pdo\Sqlite — createFunction. В PHP 8.5 старые константы вида PDO::MYSQL_ATTR_* объявлены устаревшими, вместо них Pdo\Mysql::ATTR_*
$pdo = PDO::connect('sqlite::memory:');
echo $pdo::class; // Pdo\Sqlite
echo (new PDO('sqlite::memory:'))::class; // PDOРежим ошибок
С PHP 8.0 режим по умолчанию — PDO::ERRMODE_EXCEPTION: неудачный запрос бросает PDOException. До 8.0 был ERRMODE_SILENT, и execute() тихо возвращал false. В легаси-коде без явной настройки ошибки SQL проглатываются, отсюда «запрос не работает, но ошибок нет». В новом коде ATTR_ERRMODE всё равно пишут явно: так читателю не нужно помнить, с какой версии поменялся дефолт
Подготовленный запрос и почему он защищает
В запросе вместо значений стоят плейсхолдеры, ? или :name, а значения передаются отдельно в execute(). Защита держится на том, что текст запроса и значения не смешиваются: база разбирает SQL раньше, чем видит значения, и строка x' or '1'='1 остаётся строкой для сравнения, а не куском условия
$name = "x' or '1'='1";
$rows = $pdo->query("select * from users where name = '$name'")->fetchAll();
echo count($rows); // 3 — вернулись все пользователи
$st = $pdo->prepare('select * from users where name = ?');
$st->execute([$name]);
echo count($st->fetchAll()); // 0 — пользователя с таким именем нетЕсть и вторая выгода: запрос, который выполняется в цикле тысячу раз, база разбирает один раз. Но на собеседовании ждут первую
Нативный prepare и эмуляция
Подготовить запрос можно двумя способами, и за это отвечает PDO::ATTR_EMULATE_PREPARES
| Нативный | Эмуляция | |
|---|---|---|
| Кто подставляет значения | сервер базы | PDO на стороне PHP, экранируя строки |
| Что уходит в базу | шаблон отдельно, значения отдельно | готовый SQL с вписанными значениями |
| Сетевых обменов | prepare и execute, минимум два | один |
| Ошибка в SQL видна | у MySQL и SQLite на prepare() | только на execute() |
Один :name дважды в запросе | нельзя | можно |
| Дефолт | PostgreSQL, SQLite | MySQL |
PDO_MYSQL по умолчанию эмулирует prepare, так написано в документации драйвера. От инъекции эмуляция защищает: PDO экранирует значение с учётом кодировки соединения. Поэтому кодировку задают в DSN (charset=utf8mb4), а не запросом SET NAMES: про SET NAMES PDO не знает и будет экранировать по старой кодировке
У эмуляции свои побочные эффекты. execute([10]) передаёт все значения как строки, и limit ? превращается в limit '10', на что MySQL отвечает синтаксической ошибкой. Лечится bindValue(1, $limit, PDO::PARAM_INT) или выключением эмуляции. До PHP 8.1 эмуляция ещё и возвращала числа строками, с 8.1 MySQL и SQLite отдают int и float
Выключать эмуляцию или нет — вопрос вкуса команды. Нативный режим строже и честнее показывает ошибки, эмуляция экономит сетевой обмен на каждом запросе
Где подготовленный запрос не помогает
Плейсхолдер заменяет только целое значение. Имя таблицы или колонки, asc и desc, часть строки внутри like '%...%' через него не передать
- ORDER BY.
order by ?с параметром'name'не ошибка, а сортировка по константной строке: порядок строк не меняется. Имя колонки берут из белого списка - IN со списком. Один
?принимает одно значение, массив в него не передать. Плейсхолдеры генерируют по длине массива - LIKE. Не
like '%?%', аlike ?и значение"%$q%". Если пользователь не должен задавать свои шаблоны,%и_в его строке экранируют
Как только запрос собирается конкатенацией, защита от prepare заканчивается. prepare("... order by $sort") так же уязвим, как query()
Fetch-режимы коротко
По умолчанию fetch() отдаёт PDO::FETCH_BOTH: каждая колонка дважды, по имени и по номеру. Обычно ставят ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC. Остальное по задаче:
FETCH_COLUMN— плоский массив значений одной колонкиFETCH_KEY_PAIR— массивid => nameиз запроса на две колонкиFETCH_CLASS— объекты класса, свойства заполняются до конструктораfetchAll()держит всю выборку в памяти, на большой таблице читайfetch()в цикле. У MySQL ответ буферизуется целиком и тогда, отключается это черезPdo\Mysql::ATTR_USE_BUFFERED_QUERY
Транзакции
Уровни изоляции и то, что база делает внутри транзакции, разобраны на странице «Транзакции и уровни изоляции» в модуле «Базы данных». Здесь — сторона кода. beginTransaction() выключает автокоммит до commit() или rollBack(). Откат при ошибке пишут в catch по Throwable, чтобы ловить и Error, и PDOException, и пробрасывают исключение дальше
Что будет с открытой транзакцией, если до commit() дело не дошло:
- Исключение без
catch,exit, фатальная ошибка. Скрипт завершается, соединение закрывается, и PDO откатывает транзакцию сам: баланс счёта после любого из трёх случаев остаётся прежним - Транзакция открыта запросом
BEGINчерезexec(). PDO о ней не знает и откатить не сможет - DDL в MySQL.
create tableилиalter tableвнутри транзакции неявно её коммитят. ПоследующийrollBack()уже ничего не откатит, аcommit()бросит «There is no active transaction»
Вложенные транзакции и savepoint'ы
Вложенных транзакций в PDO нет. Второй beginTransaction() бросает PDOException с текстом «There is already an active transaction». Проверить состояние можно через inTransaction()
Частичный откат делают savepoint'ом обычным SQL: SAVEPOINT sp1, затем ROLLBACK TO SAVEPOINT sp1. Откатится только то, что после точки, внешняя транзакция продолжится. Фреймворки строят «вложенные транзакции» именно так: Laravel и Doctrine DBAL считают уровень вложенности, на первом уровне вызывают beginTransaction(), на следующих ставят savepoint
Постоянные соединения и почему в FPM нет пула
PDO::ATTR_PERSISTENT => true в опциях конструктора не закрывает соединение в конце запроса. Соединение остаётся в процессе, и следующий запрос с теми же DSN и логином получает его без нового TCP-хендшейка и авторизации. Через setAttribute() после создания объекта это не включается
Пулом это назвать нельзя. Воркер PHP-FPM — отдельный процесс, и соединение живёт внутри него: передать его другому процессу или вернуть в общий пул нельзя. Каждый занятый воркер держит своё соединение, с ATTR_PERSISTENT его держит и простаивающий воркер
Отсюда расчёт. Соединений к базе в пике: pm.max_children на каждом сервере приложения, умноженное на число серверов, плюс воркеры очередей, крон и миграции. Четыре сервера по pm.max_children = 50 дают 200 соединений, а дефолтный max_connections у MySQL равен 151, у PostgreSQL 100. Для PostgreSQL, где у каждого соединения свой процесс на сервере, ставят PgBouncer, для MySQL ProxySQL. Настоящий пул внутри PHP бывает только в долгоживущих воркерах, о них страница «Долгоживущие воркеры и асинхронность»
Документация PDO прямо предупреждает: постоянное соединение никто не чистит. Временные таблицы, блокировки, переменные сессии вроде SET time_zone переходят к следующему запросу этого воркера
🛠️ На практике
Перевод денег между счетами: списание с проверкой баланса, зачисление, откат при любой ошибке
function transfer(PDO $pdo, int $from, int $to, int $amount): void
{
$pdo->beginTransaction();
try {
$st = $pdo->prepare('update acc set balance = balance - ? where id = ? and balance >= ?');
$st->execute([$amount, $from, $amount]);
if ($st->rowCount() === 0) {
throw new DomainException('Недостаточно средств');
}
$pdo->prepare('update acc set balance = balance + ? where id = ?')->execute([$amount, $to]);
$pdo->commit();
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
}
// счета 1 => 100, 2 => 0; два перевода по 70
// второй бросит «Недостаточно средств», итог: 1 => 30, 2 => 70Условие balance >= ? стоит в самом update, а не в отдельном select перед ним: проверка и списание идут одной операцией, и два параллельных перевода не уведут счёт в минус
Список в IN и сортировка из запроса пользователя:
$ids = [1, 3];
$ph = implode(', ', array_fill(0, count($ids), '?')); // "?, ?"
$st = $pdo->prepare("select name from users where id in ($ph)");
$st->execute($ids);
// имя колонки и направление — только из белого списка
$columns = ['name' => 'name', 'created' => 'created_at'];
$sort = $columns[$_GET['sort'] ?? ''] ?? 'id';
$dir = ($_GET['dir'] ?? '') === 'desc' ? 'desc' : 'asc';
$rows = $pdo->query("select * from users order by $sort $dir")->fetchAll();Пустой $ids даст in (), это синтаксическая ошибка. Его проверяют до запроса и сразу возвращают пустой результат. Если в списке тысячи значений, упрёшься в лимит плейсхолдеров у базы, тогда список кладут во временную таблицу или в PostgreSQL передают массивом: where id = any(?) со строкой '{1,3}'
PHP · Savepoint внутри транзакции
$pdo->beginTransaction();
$pdo->exec("insert into log values ('order')");
$pdo->exec('SAVEPOINT sp1');
try {
$pdo->exec("insert into log values ('bonus')");
throw new RuntimeException('bonus failed');
} catch (RuntimeException) {
$pdo->exec('ROLLBACK TO SAVEPOINT sp1'); // откатился только бонус
}
$pdo->commit();
// в таблице log одна строка: order⚠️ Где ошибаются
- Подставляют в prepare имя колонки из запроса.
prepare("... order by {$_GET['sort']}")выглядит безопасно, потому что вызванprepare. Инъекция проходит так же, как вquery() - Пишут
limit ?и передают массив вexecute(). С эмуляцией на MySQL этоlimit '10'и синтаксическая ошибка. НуженbindValueсPDO::PARAM_INT - Ловят в транзакции только
PDOException.TypeErrorиз кода между запросами пролетит мимоcatch, и транзакция останется открытой до конца запроса. ЛовиThrowable - Включают
ATTR_PERSISTENTради скорости. Соединений к базе становится ровноpm.max_childrenна сервер, даже ночью, а состояние сессии одного запроса переходит в следующий. Сначала считают соединения, потом решают - Шлют письмо или вызывают внешний API внутри транзакции. Откат базы письмо не вернёт, а транзакция держит блокировки всё время, пока ждёт ответа сети
🎯 Вопросы с собеседований
Почему подготовленные выражения защищают от SQL-инъекций?
Потому что текст запроса и значения передаются раздельно. В нативном режиме база разбирает шаблон с плейсхолдерами, а значения получает потом, и они уже не могут стать частью синтаксиса: ' or '1'='1 сравнивается как строка. Защита работает только для значений. Имя колонки, направление сортировки или кусок запроса, собранный конкатенацией, через плейсхолдер не передаются, их берут из белого списка
Что такое эмуляция prepare и чем она отличается от нативного?
При эмуляции PDO сам экранирует значения и вписывает их в SQL, в базу уходит готовый запрос за один сетевой обмен. При нативном prepare сервер получает шаблон и значения отдельно, это минимум два обмена. Управляет режимом PDO::ATTR_EMULATE_PREPARES, у MySQL эмуляция включена по умолчанию. От инъекции защищают оба режима, если кодировка задана в DSN. Отличия в мелочах: с эмуляцией ошибка SQL видна только на execute(), один :name можно использовать дважды, а limit ? из execute() превращается в строку
Как подставить массив в IN?
Один плейсхолдер — одно значение, поэтому их генерируют по длине массива: implode(', ', array_fill(0, count($ids), '?')), и массив целиком уходит в execute(). Из строк собирается только список знаков вопроса, значения по-прежнему передаются отдельно. Пустой массив проверяют заранее, иначе получится in (). В PostgreSQL можно обойтись одним параметром: where id = any(?) с массивом
Как безопасно сделать динамический ORDER BY?
Плейсхолдер тут не поможет: order by ? сортирует по константе и молча ничего не меняет. Пользователь присылает ключ, а код ищет его в белом списке вида ['name' => 'name', 'created' => 'created_at']. Неизвестный ключ получает сортировку по умолчанию. Направление проверяют так же: desc или asc, остальное — asc
Что будет с транзакцией при исключении и при фатальной ошибке?
Если исключение поймано, откат делаешь сам в catch через rollBack() и пробрасываешь его дальше. Если не поймано, или скрипт упал с фатальной ошибкой, или вызван exit, соединение закрывается в конце запроса, и PDO откатывает незакоммиченную транзакцию автоматически. Исключения два: транзакция, открытая сырым BEGIN, о которой PDO не знает, и DDL в MySQL, который коммитит транзакцию неявно
Есть ли в PDO вложенные транзакции?
Нет. Второй beginTransaction() бросает исключение «There is already an active transaction». Вложенность делают на уровне приложения: считают глубину, на первом уровне начинают настоящую транзакцию, на следующих ставят SAVEPOINT и при ошибке откатываются к нему. Так работают DB::transaction в Laravel и Doctrine DBAL
Почему в PHP-FPM нет пула соединений и как посчитать нагрузку на базу?
Воркер FPM — отдельный процесс, а соединение принадлежит процессу, передать его другому воркеру нельзя. Без ATTR_PERSISTENT соединение открывается и закрывается на каждый запрос, с ним остаётся жить в воркере. В обоих случаях в пике соединений столько же, сколько занятых воркеров: pm.max_children, умноженное на число серверов, плюс очереди и крон. Это число сравнивают с max_connections базы, а пул ставят снаружи, PgBouncer или ProxySQL
📚 Что почитать
- Подготовленные запросы — почему плейсхолдеры защищают и что через них передать нельзя
- PDO::prepare — повтор
:nameпри эмуляции и примечание проIN - Транзакции и автокоммит — автоматический откат в конце скрипта и неявный коммит DDL
- Соединения и постоянные соединения — как работает
ATTR_PERSISTENTи какое состояние в нём остаётся