PDO и безопасная работа с БД

PDO и безопасная работа с БД

PDO - это твой ремень безопасности при работе с БД. Можно, конечно, ездить без ремня... но лучше не надо. Сам язык запросов разбираем отдельно - в треке SQL.

ℹ️ Где взять базу для примеров

Проще всего поднять MySQL в Docker (имя базы и пароль совпадают с примерами урока):

docker run --rm -d --name mysql -e MYSQL_ROOT_PASSWORD=secret \
  -e MYSQL_DATABASE=app -p 3306:3306 mysql:8.4

Через полминуты создай таблицу для примеров:

docker exec -i mysql mysql -uroot -psecret app -e "CREATE TABLE tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  done TINYINT(1) NOT NULL DEFAULT 0)"

Нужно расширение pdo_mysql: проверь php -m | grep pdo. Без Docker можно взять SQLite - он не требует сервера: new PDO('sqlite:' . __DIR__ . '/app.db'), а AUTO_INCREMENT в таблице замени на AUTOINCREMENT после PRIMARY KEY (id INTEGER PRIMARY KEY AUTOINCREMENT).

ℹ️ Пока достаточно знать про объекты

PDO - это класс, а new PDO(...) создаёт объект подключения. Методы объекта вызываются стрелкой: $pdo->prepare(...), $stmt->execute(...). Запись PDO::ATTR_ERRMODE - константа, объявленная внутри класса PDO, к ней обращаются через ::. Больше ничего про ООП здесь не нужно, классы подробно разобраны в уроке 14.

Подключение

<?php
declare(strict_types=1);

$dsn = 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4';
$user = 'root';
$pass = 'secret';

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

Для PostgreSQL DSN будет другим:

<?php
$dsn = 'pgsql:host=127.0.0.1;port=5432;dbname=app';

ℹ️ Обязательные атрибуты

ERRMODE_EXCEPTION - чтобы ошибки SQL выбрасывали исключения вместо молчаливого false. FETCH_ASSOC - чтобы fetch() возвращал ассоциативный массив. EMULATE_PREPARES => false - чтобы использовать настоящие prepared statements на уровне БД.

Обработка ошибок подключения

<?php
try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
} catch (PDOException $e) {
    // В продакшне: логируй, не показывай детали пользователю
    error_log('DB connection failed: ' . $e->getMessage());
    http_response_code(500);
    die('Ошибка подключения к базе данных');
}

⚠️ Не показывай DSN пользователю

Сообщение PDOException содержит хост, имя базы, порт. Никогда не выводи $e->getMessage() пользователю - только в логи.

Запросы SELECT

Простой запрос без параметров:

<?php
$stmt = $pdo->query('SELECT id, name FROM users LIMIT 10');
$rows = $stmt->fetchAll();

foreach ($rows as $row) {
    echo "{$row['id']}: {$row['name']}\n";
}

Получить одну строку:

<?php
$stmt = $pdo->query('SELECT COUNT(*) as total FROM users');
$row = $stmt->fetch();
echo "Всего: {$row['total']}";

Prepared statements (главное)

Сравнение конкатенации SQL и prepared statement

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => 1]);
$user = $stmt->fetch();

🚫 Почему prepared statements безопаснее

Параметр :id передаётся отдельно от SQL. Если пользователь пришлёт 1 OR 1=1, это останется строкой, а не частью SQL-запроса. Без prepared statements это была бы SQL-инъекция.

Можно использовать позиционные плейсхолдеры ?:

<?php
$stmt = $pdo->prepare('SELECT * FROM users WHERE role = ? AND active = ?');
$stmt->execute(['admin', 1]);
$admins = $stmt->fetchAll();

Именованные плейсхолдеры (:name) читаемее, особенно когда параметров много.

INSERT

<?php
$stmt = $pdo->prepare('INSERT INTO tasks (title, done) VALUES (:title, :done)');
$stmt->execute(['title' => 'Купить молоко', 'done' => 0]);

$id = $pdo->lastInsertId();
echo "Создана задача #$id";

UPDATE

<?php
$stmt = $pdo->prepare('UPDATE tasks SET done = :done WHERE id = :id');
$stmt->execute(['done' => 1, 'id' => 42]);

$affected = $stmt->rowCount();
echo "Обновлено строк: $affected";

rowCount() - число строк, затронутых INSERT/UPDATE/DELETE. Для SELECT на него не полагайся: MySQL и другие драйверы не обязаны возвращать там число найденных строк. Считай через count($stmt->fetchAll()) или SELECT COUNT(*).

DELETE

<?php
$stmt = $pdo->prepare('DELETE FROM tasks WHERE id = :id');
$stmt->execute(['id' => 42]);

if ($stmt->rowCount() === 0) {
    echo 'Задача не найдена';
}

Режимы выборки (fetch modes)

<?php
// fetch() возвращает ОДНУ следующую строку (или false, если строк больше нет);
// все строки сразу - fetchAll()
// Ассоциативный массив (по умолчанию, если указали FETCH_ASSOC)
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// ['id' => 1, 'name' => 'Иван']

// Объект stdClass
$row = $stmt->fetch(PDO::FETCH_OBJ);
// $row->id, $row->name

// В свой класс
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
$user = $stmt->fetch();
// Экземпляр User с заполненными свойствами

// Одно значение (одна колонка)
$stmt = $pdo->query('SELECT COUNT(*) FROM tasks');
$count = $stmt->fetchColumn();
// 42

Транзакции

Когда несколько операций должны выполниться «всё или ничего»:

<?php
try {
    $pdo->beginTransaction();

    $stmt = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :from');
    $stmt->execute(['amount' => 100, 'from' => 1]);

    $stmt = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :to');
    $stmt->execute(['amount' => 100, 'to' => 2]);

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    throw $e; // или обработай иначе
}

ℹ️ Зачем транзакции?

Если перевод денег упадёт на середине (после списания, но до зачисления), деньги «потеряются». Транзакция гарантирует: либо обе операции пройдут, либо ни одна.

Паттерн: функция-обёртка для подключения

<?php
declare(strict_types=1);

// db.php
function getConnection(): PDO {
    static $pdo = null;

    if ($pdo === null) {
        $dsn = 'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4';
        $pdo = new PDO($dsn, 'root', 'secret', [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]);
    }

    return $pdo;
}

// Использование:
$pdo = getConnection();

static $pdo - подключение создаётся один раз и переиспользуется.

CRUD-функции для задач

Собираем всё вместе:

<?php
declare(strict_types=1);

function createTask(PDO $pdo, string $title): int {
    $stmt = $pdo->prepare('INSERT INTO tasks (title, done) VALUES (:title, 0)');
    $stmt->execute(['title' => $title]);
    return (int)$pdo->lastInsertId();
}

function listTasks(PDO $pdo): array {
    $stmt = $pdo->query('SELECT id, title, done FROM tasks ORDER BY id');
    return $stmt->fetchAll();
}

function completeTask(PDO $pdo, int $id): bool {
    $stmt = $pdo->prepare('UPDATE tasks SET done = 1 WHERE id = :id');
    $stmt->execute(['id' => $id]);
    // MySQL считает только реально изменённые строки: для уже выполненной
    // задачи будет 0. Нужны найденные строки - PDO::MYSQL_ATTR_FOUND_ROWS => true
    return $stmt->rowCount() > 0;
}

function deleteTask(PDO $pdo, int $id): bool {
    $stmt = $pdo->prepare('DELETE FROM tasks WHERE id = :id');
    $stmt->execute(['id' => $id]);
    return $stmt->rowCount() > 0;
}

⚠️ PDO как аргумент, не глобальная переменная

Передавай $pdo через аргументы функций. Это делает код тестируемым и предсказуемым. global $pdo внутри функций - антипаттерн (подробнее в уроке про DI).

SQL-инъекция: как НЕ надо

<?php
// ОПАСНО! Никогда так не делай!
$id = $_GET['id'];
$stmt = $pdo->query("SELECT * FROM users WHERE id = $id");
// Если id = "0 OR 1=1" - вернутся все пользователи

// ПРАВИЛЬНО:
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $_GET['id']]);

Единственный способ защититься от SQL-инъекций - prepared statements. Ни htmlspecialchars, ни addslashes не являются надёжной заменой. Кастинг к (int) безопасен только для целых чисел - это частный случай; для строк и как общее правило - только плейсхолдеры. Полный обзор веб-уязвимостей - в уроке про безопасность.

Типичные ошибки

  • Конкатенация SQL вместо prepared statements. "WHERE id = $id" открывает SQL-инъекцию, и addslashes от неё не спасает (а (int) закрывает только числовой случай). Фикс: всегда prepare() + execute([...]), любой пользовательский ввод - через плейсхолдер.
  • PDO::ATTR_ERRMODE не выставлен в ERRMODE_EXCEPTION. До PHP 8 по умолчанию был режим ERRMODE_SILENT: PDO молча возвращал false, и ошибки SQL терялись. С PHP 8 дефолт - ERRMODE_EXCEPTION, но задавай его явно, чтобы код не зависел от версии и настроек. Фикс: задавай PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION в опциях конструктора и оборачивай в try/catch.
  • Транзакция без rollBack() в catch. Исключение между beginTransaction() и commit() оставляет соединение в открытой транзакции, данные - в полусостоянии. Фикс: в catch сначала if ($pdo->inTransaction()) $pdo->rollBack();, потом ре-throw.
  • Забытый PDO::FETCH_ASSOC. По умолчанию идёт FETCH_BOTH - каждая строка приходит дважды (по индексу и по имени), вдвое больше памяти, плюс foreach ловит дубли. Фикс: PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC в опциях соединения.
  • mysqli параллельно с PDO в одном проекте. Два API, два набора ошибок, два стиля транзакций - кода становится вдвое больше без причины. Фикс: выбери PDO как единственный драйвер и удали mysqli_* вызовы.

Best practices

  • Открывай соединение с тремя опциями сразу: ERRMODE_EXCEPTION, FETCH_ASSOC, EMULATE_PREPARES => false.
  • Любой динамический фрагмент SQL - через именованный плейсхолдер :name; для LIMIT/OFFSET приводи к (int) до подстановки в строку запроса.
  • Оборачивай группу связанных INSERT/UPDATE/DELETE в beginTransaction() + commit() с обязательным rollBack() в catch.
  • Не включай PDO::ATTR_PERSISTENT без замера - persistent connections переживают request вместе с lock'ами и temp tables, ловить такие баги тяжело.
  • На реальном проекте поверх PDO бери doctrine/dbal (query builder, миграции) или Doctrine ORM как next-step - в Symfony это стандартный путь.

См. также

Мини-задание

  • Создай таблицу tasks(id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), done TINYINT DEFAULT 0)
  • Напиши createTask(PDO $pdo, string $title): int через prepared statement
  • Напиши listTasks(PDO $pdo): array и выведи результат
  • Добавь completeTask() и deleteTask() с проверкой rowCount()
  • Оберни перевод баланса между двумя записями в транзакцию

Зарегистрируйтесь бесплатно, чтобы пройти квиз, вести прогресс.