L3. Застосунок: N+1, ін'єкція, пул

середнійспирається на модуль 6

Мета

Побачити на працюючому застосунку те, що в модулі 6 описано цифрами: як конкатенація віддає дані чужої таблиці, скільки запитів ховається за «простою» сторінкою і що коштує відкриття з’єднання. Після роботи ви вмієте писати доступ до бази, який не пропускає ін’єкцій, не множить запити разом із даними й не б’є по базі з’єднаннями.

Завдання

Результат: каталог labs/l03-app/solution/ із застосунком, де виправлено три вади. Заготовка лежить у labs/l03-app/starter/: це сервер на node:http без фреймворків і три файли доступу до бази, які вам треба міняти: products.js, orders.js, db.js. Драйвер — pg.

Контракт застосунку (міняти його не можна, чекер на нього спирається):

Маршрут	Що повертає
GET /products?q=&category=&sort=&dir=&limit=	{ count, items: [{ product_id, title, price }] }. q — підрядок назви без урахування регістру; category — id категорії; sort — product_id, title або price; dir — asc чи desc; limit — від 1 до 50. Некоректний category дає 4xx. Невідомі sort і dir дають 4xx або стандартне сортування.
GET /products/:id	{ product_id, title, price }
GET /customers/:id/orders	{ customer: { customer_id, full_name }, orders: [{ order_id, status, placed_at, total_amount, items: [{ product_id, quantity, unit_price, title }] }] }. Замовлення від нових до старих (placed_at DESC, order_id DESC), позиції за line_no. Покупець без замовлень дає порожній orders.

Готово, коли ./labs/l03-app/check.sh виводить «усе гаразд». Три блоки перевірок:

1. Пошук. Чекер шле в нього набір ін’єкцій (умова, що завжди істинна, UNION із customers, друга команда, число без лапок, вираз замість назви колонки) і вимагає: жодного витоку даних, жодної помилки 500, жодних змін у таблицях. При цьому звичайні запити, включно з «Д’Артаньян», мають працювати.
2. Сторінка замовлень. База рахує, скільки запитів SQL виконав ваш застосунок на одну сторінку, для покупців без замовлень, з одним, із десятком, із чотирма десятками й з понад вісьмома десятками. Їх має бути не більше п’яти для кожного, незалежно від кількості замовлень.
3. З’єднання. Під навантаженням із 50 паралельних клієнтів застосунок не має відкривати більше 40 нових з’єднань до бази, тримати одночасно понад 20 і давати менше 1 500 запитів за секунду.

Чого робити не треба: змінювати схему й дані, міняти server.js і контракт, ставити PgBouncer чи ORM, міняти check/. Кешувати теж не варто, і не з принципу: чекер бере покупців, яких ще не запитували, і змінює дані між запитами.

Перед початком

Прочитайте модуль 6, принаймні розділи про ін’єкцію, N+1 і пул. Потрібні лише Docker і bash (Git Bash на Windows, WSL, Linux, macOS): Node.js ставити не треба, застосунок і чекер працюють у контейнері node:24.14.1-alpine, залежності (пакет pg) лягають у Docker-том l03_node_modules під час першого запуску. Підніміть базу з датасетом розміру small (Датасет) і переконайтеся, що docker context ls показує локальний Docker:

docker compose --profile postgres up -d --wait
cp -r labs/l03-app/starter labs/l03-app/solution

Якщо ви запускаєте compose під власною назвою проєкту, передайте її: COMPOSE_PROJECT_NAME=моя-назва ./labs/l03-app/run.sh. Так само для measure.sh і check.sh.

Етапи

1. Запустіть заготовку й виміряйте її. В одному терміналі ./labs/l03-app/run.sh (за замовчуванням бере starter/, порт 3000, базу shop), в іншому ./labs/l03-app/measure.sh. Запишіть числа: скільки запитів SQL виконала сторінка замовлень для покупців із 1, 10, 40 і 102 замовленнями, скільки запитів за секунду дало навантаження, скільки нових з’єднань відкрито. Перевірте:

   curl -s "localhost:3000/products?q=казки&limit=3"

2. Зламайте пошук. Спробуйте руками, що станеться з цими значеннями q (у curl вони мають бути закодовані: curl -G localhost:3000/products --data-urlencode "q=…"):

   ' OR '1'='1' --
   zzz' UNION SELECT customer_id, email, 0 FROM customers --
   Д'Артаньян

   Яке з них дає чужі дані, яке виконує кілька команд, яке просто впаде? Далі спробуйте category=1 OR 1=1 і sort=(SELECT 1/0).

3. Виправте solution/products.js. Значення (q, category, limit) передайте параметрами $1…$3. Назву колонки й напрям сортування не передати параметром: виберіть їх зі списку допустимих значень. Некоректний category відхиліть помилкою HttpError(400, …) із errors.js. Запустіть run.sh solution і повторіть атаки з кроку 2. Спокуса «екранувати апострофи» тут не пройде: кроки з category і sort на це й розраховані.

4. Виправте solution/orders.js. Кількість запитів на сторінку не має залежати від числа замовлень. Способів кілька: один JOIN із розбором рядків у коді, запит на кожен рівень із WHERE order_id = ANY($1), json_agg у самій базі. Оберіть і порівняйте з початковими числами з кроку 1. Слідкуйте, щоб покупець без замовлень і замовлення без позицій лишились на сторінці, а порядок не змінився.

5. Виправте solution/db.js. Замініть new Client() на кожен запит пулом pg.Pool. Розмір оберіть самі (не більше 20) і виміряйте: запитів/с і нових з’єднань до й після.

6. Здача. ./labs/l03-app/check.sh. Кожен пункт, що не пройшов, каже, який саме запит не витримав.

Перевірка

./labs/l03-app/check.sh                  # розв'язок із labs/l03-app/solution/
./labs/l03-app/check.sh інший/каталог    # будь-який каталог із застосунком
MIN_RPS=1000 ./labs/l03-app/check.sh     # нижчий поріг пропускної здатності на слабкій машині

Чекер створює у вашому контейнері postgres допоміжні бази l03_base (копія датасету розміру small, один раз) і l03_run (свіжа копія на кожну перевірку), запускає ваш застосунок у контейнері l03-check-app від імені ролі l03_app і нічого не змінює у вашій базі shop. Перевірка триває близько 20 с.

Як він рахує запити. Застосунок бачить лише свою роль, а запити рахує сервер: чекер читає pg_stat_statements для ролі l03_app до й після запиту сторінки, відкидаючи BEGIN, COMMIT і службові команди сеансу. Лічильник у коді застосунку студент міг би обійти, а запити, які дійшли до сервера, обійти не можна. Побічний висновок: функція в базі, що збирає сторінку, вважається одним запитом, бо pg_stat_statements не показує запитів усередині функції, і це допустимий розв’язок. Свіжість даних чекер перевіряє окремо: додає покупцеві замовлення й змінює назву товару, а потім вимагає побачити зміну на сторінці. Покупці на кожен запуск обираються випадково.

З’єднання рахує pg_stat_database.sessions (скільки сеансів відкрито за час навантаження) і вибірка pg_stat_activity (скільки одночасно). Поріг у 1 500 запитів/с занижений навмисно, щоб пройти на слабкій машині: початкова заготовка на нашому стенді давала близько 1 000–1 200, а виправлена понад 7 000, тож саме відмінність у кількості з’єднань відрізняє пул від його відсутності.

Часті помилки

ORDER BY $1. Параметр стає сталою, а не назвою колонки: запит виконується без помилки, але сортування не змінюється (перевірено на PostgreSQL 18.6). Потрібен білий список.

Необов’язковий фільтр ($2 IS NULL OR category_id = $2). База відповідає 42P08 could not determine data type of parameter: тип параметра в першій частині умови невідомий. Приведіть його: $2::int.

new Pool() усередині query(). Тоді кожен запит створює власний пул, і нічого не змінилось. Пул має бути один на застосунок, на рівні модуля.

pool.connect() без release(). З’єднання не повертається, після max запитів застосунок зависає. Найпростіше брати pool.query(): він сам позичає й повертає з’єднання.

Promise.all замість виправлення N+1. Запити йдуть паралельно й сторінка швидша, але запитів лишається 299, а пул виснажується. Чекер рахує запити, а не час.

INNER JOIN замість LEFT JOIN. Покупець без замовлень і замовлення без позицій зникають, і чекер повідомить, скільки замовлень очікував.

Порядок. Після групування в коді замовлення втратили порядок placed_at DESC, а позиції порядок line_no. Чекер порівнює послідовності.

Docker дивиться на віддалену машину. Якщо docker context ls показує зірочку біля SSH-адреси, контейнери запускаються там, а не у вас. docker context use desktop-linux (Docker Desktop) або default (Linux).

Далі, якщо цікаво

Запустіть PgBouncer перед базою (edoburu/pgbouncer із фіксованим тегом) у режимі transaction і переключіть DATABASE_URL: що зміниться в числах measure.sh? Увімкніть іменовані prepared statement (name: у query) і подивіться, чи переживають вони PgBouncer із різними max_prepared_statements. Створіть для застосунку роль без права UPDATE на products і подивіться, що лишиться від атаки з другою командою. Складіть EXPLAIN для title ILIKE '%…%' і подивіться, чому його не прискорить звичайний індекс: про це в модулі 8.