Лабораторная работа 2. SQL, MySQL, MySQLi, PDO, подготовленные запросы и транзакции
Работа выполняется с ИИ и(или) ИИ-агентом. Важно освоить SQL на минимальном необходимом уровне, понять для чего он используется, как настраивается, понимать какие технологии для чего используются. Понимание глубокое PHP здесь не требуется, нужно понимать только элементы взаимодействия с БД.
Часть 1.
Разработка сайтов на PHP требует понимания двух основных групп команд SQL: DDL (определение структуры) и DML (манипулирование данными).
1. DDL (Data Definition Language) — Создание структуры таблиц
Для хранения денежных сумм (цены, итоговые чеки) в веб-приложениях запрещено использовать тип FLOAT или DOUBLE из-за проблем с точностью округления с плавающей точкой. Деньги сохраняются в целых числах (в копейках/центах,
INT или BIGINT) или с помощью DECIMAL(10, 2). В этой работе мы используем центы (total_cents, price_cents).
Выполните в песочнице DDL-скрипты создания таблиц:
-- Таблица клиентов
CREATE TABLE IF NOT EXISTS customers (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT uq_customers_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Таблица заказов
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
status VARCHAR(50) NOT NULL DEFAULT 'new',
total_cents INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- Таблица позиций заказа
CREATE TABLE IF NOT EXISTS order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
title VARCHAR(255) NOT NULL,
quantity INT NOT NULL DEFAULT 1,
price_cents INT NOT NULL,
CONSTRAINT fk_items_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Выполните базовые операции создания, чтения, обновления и удаления данных в песочнице.
- C (Create) — Вставка данных (INSERT):
-- Добавление клиентов
INSERT INTO customers (name, email) VALUES
('Иван Иванов', 'ivan@example.com'),
('Петр Петров', 'petr@example.com'),
('Анна Сидорова', 'anna@example.com');
-- Добавление заказа для клиента с id = 1 (10000 центов = 100.00$)
INSERT INTO orders (customer_id, status, total_cents)
VALUES (1, 'completed', 10000);
-- Добавление товара в заказ с id = 1
INSERT INTO order_items (order_id, title, quantity, price_cents)
VALUES (1, 'Клавиатура Logitech', 1, 10000);
- R (Read) — Выборка и фильтрация (SELECT, WHERE, ORDER BY, LIMIT):
-- Выбрать всех клиентов
SELECT id, name, email, created_at FROM customers;
-- Найти клиента по email
SELECT * FROM customers WHERE email = 'ivan@example.com';
-- Получить 5 самых свежих заказов
SELECT * FROM orders ORDER BY created_at DESC LIMIT 5;
- U (Update) — Изменение данных (UPDATE):
WHERE, иначе обновите все строки в таблице!
UPDATE customers
SET name = 'Иван Алексеев'
WHERE id = 1;
- D (Delete) — Удаление данных (DELETE):
WHERE, иначе очистите всю таблицу!
DELETE FROM customers WHERE id = 3;
JOIN) и агрегатные функции (GROUP BY)
- Объединение таблиц (INNER JOIN / LEFT JOIN):
-- Получить список заказов с именами клиентов
SELECT
o.id AS order_id,
c.name AS customer_name,
c.email,
o.status,
o.total_cents
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
-- Найти всех клиентов и их заказы (включая клиентов БЕЗ заказов)
SELECT
c.id,
c.name,
o.id AS order_id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;
- Агрегация и группировка (COUNT, SUM, GROUP BY):
-- Посчитать общее количество заказов и сумму оборота по каждому клиенту
SELECT
c.id,
c.name,
COUNT(o.id) AS total_orders,
COALESCE(SUM(o.total_cents), 0) AS total_spent_cents
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent_cents DESC;
убедитесь, что в песочнице у вас успешно выполняются следующие SQL-запросы:
- Поиск клиента по
email. - Выборка клиентов, у которых нет ни одного заказа (
WHERE o.id IS NULL). - Топ-5 клиентов с максимальной суммой выполненных заказов.
Часть 2.
Цель: реализовать безопасный слой доступа к реляционной базе данных двумя средствами PHP.
PDO предоставляет единый интерфейс доступа к различным СУБД, а MySQLi предназначен для MySQL. Для запросов с пользовательскими параметрами использовать подготовленные выражения. Документация PHP прямо указывает на использование prepared statements для параметризованных запросов и защиту от SQL-инъекций. [8]
Задание
-
Продолжите проект plain-php.
-
Создайте БД:
bizflow
-
Создайте таблицы:
customers
orders
order_items
-
Минимальная структура customers:
id
name
email
created_at
-
Минимальная структура orders:
id
customer_id
status
total_cents
created_at
-
Минимальная структура order_items:
id
order_id
title
quantity
price_cents
-
Добавьте уникальный индекс на:
customers.email
-
Добавьте внешние ключи.
-
Создайте интерфейс:
CustomerRepositoryInterface
-
Реализуйте:
MysqliCustomerRepository
PdoCustomerRepository
-
Интерфейс должен предоставлять методы:
find(int $id)
findAll()
findByEmail(string $email)
create(...)
update(...)
delete(...)
-
Во всех запросах с пользовательскими значениями использовать параметры.
-
Запрещено:
$sql = "SELECT ... WHERE email = '$email'";
-
Реализуйте переключение реализации репозитория через конфигурацию:
DB_DRIVER=pdo
или:
DB_DRIVER=mysqli
-
Пароль БД храните только локально.
-
Добавьте .env.example.
-
Файл с реальными параметрами подключения добавьте в .gitignore.
-
Реализуйте:
GET /customers
GET /customers/{id}
POST /customers
-
Реализуйте создание заказа.
-
Создание orders и всех связанных order_items должно выполняться в одной транзакции.
-
При ошибке создания позиции заказа выполнить rollback.
-
Создайте тестовый сценарий, передающий в email строку:
' OR 1=1 --
-
Тест должен подтверждать, что строка рассматривается как значение, а не изменяет структуру SQL-запроса.
-
Реализуйте перехват исключений БД без вывода пароля, DSN и stack trace пользователю.
-
Создайте минимум 10 тестовых клиентов и 15 заказов.
-
Реализуйте SQL-запросы:
список клиентов;
заказы клиента;
сумма заказов клиента;
количество заказов клиента;
клиенты без заказов;
пять клиентов с максимальным оборотом.
-
Для одного и того же запроса реализуйте вариант через PDO и MySQLi.
Обязательный запрос ИИ-аналитику
Ты — database architect.
Проанализируй предметную область:
Customer -> Order -> OrderItem.
Предложи нормализованную реляционную схему MySQL.
Требования:
внешние ключи;
индексы;
уникальный email;
денежные значения без float;
транзакционное создание заказа.
Покажи DDL и обоснуй только те индексы, которые действительно нужны.
Обязательный запрос ИИ-агенту безопасности
Ты — специалист по безопасности PHP/MySQL.
Проанализируй весь слой доступа к данным текущего проекта.
Найди:
SQL injection;
динамическую конкатенацию SQL;
утечку database credentials;
небезопасную обработку исключений;
отсутствие транзакций;
ошибочное управление commit/rollback.
Особое внимание удели PDO и MySQLi prepared statements.
Сначала перечисли проблемы.
После этого предложи патч.
Самопроверка
composer validate
php -l public/index.php
Запустить PHPUnit-тесты.
Результат
plain-php/
docs/ai/lab-02.md
Создать:
git tag lab-02
Критерии приемки
[ ] создана реляционная БД
[ ] имеются внешние ключи
[ ] работает PDO
[ ] работает MySQLi
[ ] реализован общий интерфейс репозитория
[ ] prepared statements используются в обоих вариантах
[ ] реализована транзакция заказа
[ ] тест SQL injection проходит
[ ] пароли отсутствуют в Git
[ ] имеется AI log
PDO предоставляет независимый от конкретной БД интерфейс доступа к данным, тогда как MySQLi предоставляет специализированный API MySQL; оба механизма поддерживают подготовленные запросы. [9]