Урок 9. База данных через PDO

Настоящий сайт хранит данные в базе: пользователей, товары, заказы, комментарии. В PHP для работы с базами есть PDO (PHP Data Objects) — один способ для разных баз: SQLite, MySQL, MariaDB, PostgreSQL. Запросы к базе пишутся на языке SQL; его основы — в теме SQL.

Учиться удобнее всего на SQLite: вся база — один файл, ставить отдельный сервер не нужно. Нужно расширение pdo_sqlite: в Ubuntu и Debian — пакет php-sqlite3, в Windows — строка extension=pdo_sqlite в php.ini (урок 1). Проверить: команда php -m должна показать pdo_sqlite.

Подключение

<?php
$pdo = new PDO('sqlite:' . __DIR__ . '/app.db', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

• Первая строка-аргумент (DSN) говорит, какая база и где: sqlite: и путь к файлу. Файла нет — SQLite создаст его.
• Второй и третий аргументы — логин и пароль; у SQLite их нет.
• ERRMODE_EXCEPTION — при ошибке в запросе PHP выбросит исключение PDOException (ошибку, которая останавливает программу, если её не перехватить), а не промолчит. В PHP 8 так и задано по умолчанию, но явная строка не помешает.
• FETCH_ASSOC — строки результата приходят ассоциативными массивами: $row['title'].

Таблица, запись и чтение

Программа ниже работает с базой в памяти (sqlite::memory:) — она исчезает после завершения, и пример можно запускать сколько угодно раз с одинаковым результатом:

<?php
$pdo = new PDO('sqlite::memory:', null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$pdo->exec('CREATE TABLE notes (id INTEGER PRIMARY KEY, title TEXT NOT NULL)');

$insert = $pdo->prepare('INSERT INTO notes (title) VALUES (?)');
foreach (['Купить хлеб', 'Позвонить маме', 'Купить молоко'] as $title) {
    $insert->execute([$title]);
}

$search = $pdo->prepare('SELECT id, title FROM notes WHERE title LIKE ? ORDER BY id');
$search->execute(['Купить%']);
foreach ($search->fetchAll() as $row) {
    echo $row['id'] . ': ' . $row['title'] . "\n";
}

$count = $pdo->query('SELECT COUNT(*) FROM notes')->fetchColumn();
echo "Всего заметок: $count\n";

1: Купить хлеб
3: Купить молоко
Всего заметок: 3

Что делают методы:

• exec выполняет запрос без результата — например, создание таблицы.
• prepare готовит запрос с местами для данных — знаками ?. execute подставляет значения из массива и выполняет запрос. Один подготовленный запрос можно выполнить много раз.
• fetchAll возвращает все строки результата массивом, fetch — одну следующую строку (или false, если строк больше нет), fetchColumn — значение первого столбца.
• query выполняет запрос сразу. Им пользуются только для запросов без данных от посетителя, как COUNT(*) выше.

Номер только что добавленной записи возвращает $pdo->lastInsertId().

Именованные места

Вместо ? можно писать имена — так понятнее, когда значений много:

<?php
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = :email AND active = :active');
$stmt->execute(['email' => $email, 'active' => 1]);
$user = $stmt->fetch();
if ($user === false) {
    echo 'Пользователь не найден';
}

SQL-инъекция: почему только prepare

Так писать нельзя:

<?php
$email = $_POST['email'];
$rows = $pdo->query("SELECT * FROM users WHERE email = '$email'")->fetchAll();

Если посетитель введёт в поле почты ' OR '1'='1, запрос превратится в SELECT * FROM users WHERE email = '' OR '1'='1' — условие верно для всех строк, и сайт выдаст всех пользователей. Другим вводом можно удалить таблицу или прочитать пароли. Это SQL-инъекция — одна из самых частых и опасных уязвимостей сайтов.

В подготовленном запросе данные не склеиваются с текстом SQL в вашем коде: их подставляет сама база или PDO по строгим правилам. Что бы ни ввёл посетитель, это останется значением поля и не станет частью команды.

Места для данных годятся только для значений. Имя таблицы или столбца так подставить нельзя — например, для сортировки по выбору посетителя выбирайте столбец из списка разрешённых:

<?php
$allowed = ['title', 'created_at'];
$sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'title';
$rows = $pdo->query("SELECT id, title FROM notes ORDER BY $sort")->fetchAll();

MySQL и MariaDB

Код тот же, меняется только строка подключения. В Ubuntu и Debian нужен пакет php-mysql, в Windows — строка extension=pdo_mysql в php.ini:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'пароль_пользователя', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

charset=utf8mb4 — кодировка соединения: с ней русский текст и эмодзи сохраняются без искажений. Базу и пользователя создают заранее, средствами самой MySQL. Логин и пароль базы держите в отдельном файле настроек вне папки сайта (урок 7).

Ошибки и вывод данных

<?php
try {
    $pdo = new PDO('sqlite:' . __DIR__ . '/app.db');
} catch (PDOException $e) {
    error_log($e->getMessage());
    exit('Сайт временно недоступен');
}

try … catch перехватывает исключение: если в блоке try случилась ошибка, выполняется блок catch, а $e->getMessage() даёт её текст. Текст записывается в журнал ошибок сервера (error_log), а посетителю показывается общая фраза: в сообщении PDO бывают имя базы, логин и путь к файлам.

Данные из базы, выводимые на страницу, тоже экранируются: <?= htmlspecialchars($row['title']) ?>. В базу могли попасть строки, которые когда-то ввёл посетитель.

Транзакции

Если несколько запросов должны выполниться только вместе (списать деньги с одного счёта и зачислить на другой), их объединяют в транзакцию:

<?php
$pdo->beginTransaction();
try {
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, 1]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, 2]);
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    throw $e;
}

commit сохраняет все изменения, rollBack отменяет их, если что-то пошло не так.

Частые ошибки

• could not find driver — не включено расширение для этой базы: pdo_sqlite или pdo_mysql (проверка — php -m).
• no such table: notes — таблица не создана или открыт не тот файл базы. Путь к файлу без __DIR__ легко указывает в другую папку, и SQLite молча создаёт там новую пустую базу.
• Access denied for user (MySQL) — неверный логин или пароль базы либо у пользователя нет прав на эту базу.
• Данные посетителя склеены с текстом запроса — сайт открыт для SQL-инъекции. Только prepare и execute.

Коротко

• PDO — один способ работы с SQLite, MySQL, MariaDB и PostgreSQL; подключение — new PDO(DSN, логин, пароль, настройки).
• Режим ошибок — ERRMODE_EXCEPTION, строки — FETCH_ASSOC.
• Данные в запрос — только через prepare и execute с местами ? или :имя; это защита от SQL-инъекций.
• fetchAll — все строки, fetch — одна, fetchColumn — одно значение, lastInsertId — номер новой записи.
• Посетителю — общая фраза об ошибке, подробности — в журнал; данные из базы на странице — через htmlspecialchars.

Проверьте себя