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

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

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

Подключение

<?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';

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

<?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('Ошибка подключения к базе данных');
}

Запросы 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 (главное)

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

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

<?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";

DELETE

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

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

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

<?php
// Ассоциативный массив (по умолчанию, если указали 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]);
    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;
}

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

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

// ПРАВИЛЬНО:
$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. По умолчанию PDO молча возвращает false, ошибки SQL теряются. Фикс: задавай 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 это стандартный путь.

• Go - Работа с базами данных - prepared statements, транзакции и миграции в Go: те же принципы, другой синтаксис

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

• Создай таблицу 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()
• Оберни перевод баланса между двумя записями в транзакцию