К содержимому
Войти

PHP Road Map

🔌 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, SQLiteMySQL

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

📚 Что почитать