База і застосунок
Навіщо це
Section titled “Навіщо це”Сторінка «Мої замовлення» для покупця 1458 будується 1.4 с, хоча кожен запит до бази триває соті частки мілісекунди. У цього покупця 102 замовлення зі 195 позиціями, і застосунок надсилає 299 окремих запитів, кожен через нове з’єднання.
Поле пошуку на тому самому сайті віддає адреси всіх 5000 покупців, якщо вписати в нього спеціально складений рядок. А під 50 паралельними клієнтами застосунок відкриває з’єднання на кожен запит і видає близько тисячі запитів за секунду, хоча пул із десяти з’єднань дає понад сім тисяч. Усі числа в модулі з датасету «Крамниця» взято на розмірі small (Датасет).
Усі три вади лежать на межі між кодом і базою. Виправляєте їх у лабораторній L3, а тут розібрано, що ламається: параметри, ORM, пул і межа між логікою застосунку та бази. Приклади на Node.js із драйвером pg, у Python із psycopg те саме.
Передумови. Запити й JOIN: модуль 3. INSERT, UPDATE, обмеження: модуль 4. Чому з’єднання з PostgreSQL коштує процесу: модуль 6 курсу «Операційні системи». Що відбувається в TCP при відкритті з’єднання: модуль 11 курсу «Комп’ютерні мережі».
Як застосунок звертається до бази
Section titled “Як застосунок звертається до бази”Драйвер — бібліотека, яка розмовляє з сервером мережевим протоколом PostgreSQL. Перш ніж виконати перший запит, він відкриває з’єднання: встановлює TCP-сесію, надсилає ім’я користувача й бази, проходить автентифікацію (типово SCRAM-SHA-256) і чекає повідомлення «готовий до запиту». Головний процес postmaster породжує для цього з’єднання окремий процес-обробник.
Далі застосунок надсилає текст запиту, а база повертає рядки. Найпростіше зібрати текст самому, вписавши значення від користувача всередину.
SQL-ін’єкція на пошуку товарів
Section titled “SQL-ін’єкція на пошуку товарів”Пошук зібрано так, як роблять, поки про ін’єкції не чули:
let sql = "SELECT product_id, title, price FROM products WHERE title ILIKE '%" + q + "%'";if (category) sql += ' AND category_id = ' + category;sql += ' ORDER BY ' + sort + ' ' + dir + ' LIMIT ' + limit;const result = await query(sql); // значень окремо немає: простий протоколДля q=' OR '1'='1' -- до бази йде … ILIKE '%' OR '1'='1' --%' ORDER BY …. Умова стала завжди істинною, а -- зробив коментарем усе, що застосунок дописав далі, включно з LIMIT. Відповідь містить усі 3500 товарів.
Для q=zzz' UNION SELECT customer_id, email, 0 FROM customers -- UNION дописує до результату рядки іншої таблиці. Типи колонок сумісні (bigint, text, число), тож запит виконується:
GET /products?q=zzz' UNION SELECT customer_id, email, 0 FROM customers --{"count":5000,"items":[{"product_id":"3758","title":"nadiia_kovalchuk994@mail.example","price":"0"}, …У пісочниці видно запит, який збирає код вище:
Можна й більше: q=x'; UPDATE products SET price = 0.01; -- виконує другу команду. Після такого запиту до стартового застосунку всі товари коштують 0.01, чекер L3 перевіряє саме це на копії бази.
Чому параметри це закривають, а екранування ні
Section titled “Чому параметри це закривають, а екранування ні”Драйвер має два шляхи для запиту. У простому протоколі клієнт надсилає одне повідомлення з повним текстом, де можна написати кілька команд через ;, а значення мусять бути вже вписані. У розширеному три кроки йдуть окремо: Parse розбирає текст із позначками $1, $2, Bind передає значення, Execute виконує. Текст у Parse містить лише одну команду, а значення ніколи не зливаються з текстом. pg вибирає простий протокол, коли query викликано без масиву значень, і розширений, коли значення є або запит названо.
// розширений протокол: текст і значення їдуть окремоawait client.query('SELECT title FROM products WHERE product_id = $1', [39]);// іменований prepared statement: Parse один раз на з'єднання, далі Bind і Executeawait client.query({ name: 'by-id', text: 'SELECT title FROM products WHERE product_id = $1', values: [39] });У Python psycopg 3 робить так само: cur.execute("… WHERE product_id = %s", (39,)) іде на сервер як $1. Перевірка на psycopg 3.3.6: два запити в одному execute із параметрами дають cannot insert multiple commands into a prepared statement. psycopg 2 підставляє значення в текст на клієнті (mogrify("SELECT %s", ("O'Reilly",)) повертає SELECT 'O''Reilly').
Коли застосунок пише WHERE title ILIKE '%' || $1 || '%' і передає q окремо, сервер розбирає текст до того, як побачить значення. Що б не було в q, воно лишається даними: апострофи, -- і слово UNION не стають синтаксисом, бо синтаксис уже розібрано. Тому пошук «Д’Артаньян» працює без жодної обробки, і чекер L3 перевіряє його окремо: рішення, що вирізає апострофи, провалюється.
Екранування вручну (подвоїти апостроф) лікує один контекст із кількох.
- У рядковому літералі подвоєння працює:
zzz' OR '1'='1стаєzzz'' OR ''1''=''1, шматком рядка. Пошук заqпісля такої правки перестає ламатися. - Число без лапок нічого не екранує:
category=1 OR 1=1не має жодного апострофа, аcategory_id = 1 OR 1=1повертає всі товари. Те самеcategory=0 UNION SELECT …. - У назві колонки для сортування лапки не допомагають:
sort=(SELECT 1/0)потрапляє вORDER BYяк вираз. - Правила екранування залежать від кодування й налаштувань сервера, а кожна нова точка входу потребує нового правила.
Саме на числовому й ідентифікаторному входах «частково виправлений» варіант у L3 і провалюється.
Ідентифікатори параметром не передаються: $1 завжди значення, а не назва колонки чи напрям сортування. Якщо користувач обирає, за чим сортувати, застосунок вибирає фрагмент зі списку допустимих значень (білого списку) і вставляє власний рядок:
const SORT = { product_id: 'product_id', title: 'title', price: 'price' };const DIR = { asc: 'ASC', desc: 'DESC' };if (!SORT[sort] || !DIR[dir]) throw new HttpError(400, 'sort: product_id, title або price');await query(`SELECT … ORDER BY ${SORT[sort]} ${DIR[dir]} LIMIT $3`, [q, category, limit]);Число в category перевіряється в коді (/^\d+$/), а далі теж іде параметром. У текст запиту можна вставляти лише те, що застосунок склав сам.
ORM не скасовує цього правила. Sequelize, наприклад, підставляє значення в текст на клієнті: у його журналі пошук O'Reilly виглядає як WHERE "title" = 'O''Reilly'. Поки екранує бібліотека, це нормально, а рядок, який ви самі вставили в where чи order, вона не захистить.
ORM і проблема N+1
Section titled “ORM і проблема N+1”ORM (object-relational mapper) відображає рядки таблиць на об’єкти й сам будує SQL із викликів методів. Значення в ньому завжди параметри чи екрановані бібліотекою, схема описана один раз, типові вибірки не треба писати руками. Ціна в тому, що запити зникають з коду, а відповідальність за їхню кількість лишається в нас.
Проблема N+1 виникає, коли код завантажує список і звертається до пов’язаних об’єктів по одному. Сторінка замовлень робить запит на покупця, запит на його замовлення, потім по запиту на позиції кожного з N замовлень і по запиту на товар кожної з K позицій: 2 + N + K. Для покупця 1458 це 2 + 102 + 195 = 299. Ліниве завантаження (lazy loading) робить це непомітно: звернення до order.items саме виконує запит. Sequelize з getOrders(), getItems() і getProduct() виконав на цьому покупцеві рівно 299.
Час сторінки виміряно на Docker (PostgreSQL 18.6, Node 24, застосунок у контейнері поруч із базою), медіана з 24 запитів:
| Реалізація | Запитів для 1458 | Мс для 1458 | Покупець із 10 замовленнями |
|---|---|---|---|
| Цикл, нове з’єднання на запит | 299 | 1 403 | 126 мс, 27 запитів |
| Той самий цикл, пул | 299 | 70 | 9.7 мс |
Один JOIN |
2 | 3.8 | 1.9 мс, 2 запити |
Запит на рівень, WHERE order_id = ANY($1) |
3 | 3.9 | 1.8 мс |
json_agg у підзапиті |
2 | 2.8 | 2.1 мс |
Пул прибрав лише вартість з’єднань, а запитів лишилось 299. Час сторінки й далі росте лінійно з N, і в продакшні, де база за мережею, до кожного запиту додається затримка. Виправляють форму запитів: їхня кількість не має залежати від числа замовлень. У Sequelize це include (один JOIN: 1 запит, 10–12 мс) або include із separate: true (запит на рівень: 3 запити, 12–17 мс). В інших ORM це selectinload (SQLAlchemy), prefetch_related (Django), include (Prisma).
JOIN теж має ціну: замовлення з вісьмома позиціями приходить вісім разів, а LIMIT на результаті JOIN обрізає рядки, а не замовлення. = ANY($1) не розмножує рядки й лишає кількість запитів сталою. Кеш товарів чи сторінки N+1 не лікує: перший запит залишається повільним, а кешована сторінка застаріває. Тому чекер L3 бере покупця, якого ще не запитували, і міняє дані між запитами.
Скільки запитів виконано, видно з боку застосунку (logging у Sequelize, log: ['query'] у Prisma, echo=True у SQLAlchemy, connection.queries у Django) і з боку бази. Друге надійніше: воно рахує те, що дійшло до сервера.
Пул з’єднань
Section titled “Пул з’єднань”З’єднання коштує дорожче за запит. На Docker між контейнерами відкриття з’єднання до PostgreSQL 18.6 зайняло 2.7 мс у медіані (p95 3.8 мс), а SELECT по ключу на вже відкритому з’єднанні 0.09 мс. Різниця в 30 разів складається з кроків, описаних на початку модуля, а між справжніми машинами додаються ще кругові проходи мережі. Короткі з’єднання накопичують у того, хто закриває першим, стан TIME_WAIT.
Крім того, PostgreSQL створює процес на кожне з’єднання, а max_connections типово дорівнює 100. Сотні активних процесів змагаються за процесор і блокування, і база від цього не швидшає.
Пул з’єднань — набір відкритих з’єднань, які застосунок позичає на час запиту й повертає. У pg це new pg.Pool({ max: 10 }), а в L3 різниця між new Client() на кожен запит і пулом — кілька рядків у db.js. Вимір на Docker: 50 клієнтів, SELECT по ключу без HTTP, шість секунд.
| Варіант | Запитів/с | Нових з’єднань |
|---|---|---|
| Нове з’єднання на запит | 1 464 | 8 783 |
Пул, max = 5 |
22 563 | 6 |
Пул, max = 10 |
22 978 | 11 |
Пул, max = 20 |
22 682 | 21 |
Пул, max = 50 |
22 239 | 52 |
Між 5 і 50 різниці немає: база виконує запити швидше, ніж застосунок їх подає. У документації HikariCP (пул для JVM) відправною точкою названо 2 × ядра + дискові шпинделі, формулу вона приписує проєкту PostgreSQL. На рівні HTTP у L3 те саме: близько тисячі запитів/с без пулу проти семи-восьми тисяч із ним.
Пул у процесі не рятує, коли застосунків багато: 50 екземплярів по 20 з’єднань дають 1 000 з’єднань проти ста в базі.
PgBouncer
Section titled “PgBouncer”PgBouncer — окремий легкий процес, який приймає сотні клієнтських з’єднань і мультиплексує їх на кілька серверних до PostgreSQL.
Режим пулу каже, коли серверне з’єднання повертається до загального пулу. У session — коли клієнт відключився, поведінка як без PgBouncer, економії мало. У transaction — коли завершилась транзакція, це найбільше мультиплексування. У statement — після кожного запиту, і транзакції з кількох запитів заборонені.
У transaction дві транзакції одного клієнта можуть потрапити на різні серверні з’єднання, і все, що живе в сеансі, розходиться. Виміряно на PgBouncer 1.26.0 (образ edoburu/pgbouncer:v1.26.0-p0, 10 серверних з’єднань, ще 30 клієнтів у фоні):
- вісім послідовних
SELECT pg_backend_pid()одного клієнта дали сім різних процесів (без PgBouncer вісім однакових); - після
SET statement_timeout = '7s'тридцятьSHOW statement_timeoutпоказали7sлише двічі, решта0; pg_advisory_lock(4242)і даліpg_advisory_unlock(4242)повернулиfalse: розблокування пішло на інше з’єднання, а блокування лишилося на першому. Наступнийpg_advisory_lock(4242)від іншого клієнта чекав, поки PgBouncer перезапустили;- SQL-команди
PREPAREіEXECUTEпадають:26000 prepared statement "p1" does not exist; SET LOCALусередині транзакції працює: у 20 спробах із 20 значення було на місці.
Документація PgBouncer називає несумісними з transaction ще LISTEN, курсори WITH HOLD, LOAD і тимчасові таблиці з PRESERVE ROWS чи DELETE ROWS.
З prepared statement протоколу історія змінилась. Prepared statement (підготовлений запит) — запит, розібраний сервером наперед і доступний у сеансі під іменем. Він економить розбір, але існує лише в тому з’єднанні, де його створено, тому для PgBouncer це важливо. До PgBouncer 1.21.0 (жовтень 2023) іменовані prepared statement у режимі transaction не підтримувалися. 1.21.0 додав підтримку: PgBouncer стежить за ними й готує їх на тому серверному з’єднанні, яке дістається клієнту, а кількість тримає параметр max_prepared_statements. За документацією його типове значення 200, і в нашому вимірі іменований запит pg відпрацював 40 разів із 40. З max_prepared_statements = 0 33 виклики із 40 закінчились помилкою 26000. Підтримка стосується протоколу, а не SQL-команд PREPARE і EXECUTE.
Логіка в базі
Section titled “Логіка в базі”Збережена функція живе в базі й викликається з SELECT. Збережена процедура викликається командою CALL і може сама виконувати COMMIT та ROLLBACK. Тригер база викликає до або після зміни рядка. Пишуть їх зазвичай на PL/pgSQL, процедурному розширенні SQL зі змінними й циклами, а просту функцію можна й на чистому SQL. Сума замовлення в датасеті дорівнює сумі за його позиціями, і це правило можна зберігати функцією:
CREATE FUNCTION order_total(p_order_id bigint) RETURNS numericLANGUAGE sql STABLE AS $$ SELECT coalesce(sum(round(quantity * unit_price * (1 - discount_pct / 100), 2)), 0) FROM order_items WHERE order_id = p_order_id$$;
SELECT order_id, total_amount, order_total(order_id) AS recomputedFROM orders WHERE order_id IN (1, 2, 3) ORDER BY order_id; order_id | total_amount | recomputed----------+--------------+------------ 1 | 13325.00 | 13325.00 2 | 1048.00 | 1048.00 3 | 2005.20 | 2005.20А правило «updated_at оновлюється при кожній зміні» зручно віддати тригеру: його не обійде жоден клієнт.
CREATE FUNCTION touch_order() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN NEW.updated_at := now(); RETURN NEW;END $$;
CREATE TRIGGER orders_touch BEFORE UPDATE ON ordersFOR EACH ROW EXECUTE FUNCTION touch_order();Логіку варто класти в базу в трьох випадках. Перший: це інваріант, який мусить діяти для будь-якого клієнта (обмеження, журнал змін, службові колонки). Другий: вона перебирає великий набір рядків, і віддавати його застосунку безглуздо. Третій: функція замінює N+1, бо одна функція, що повертає замовлення у json, дає один круг до бази.
Важку обчислювальну логіку туди не кладуть: застосунків можна запустити сто, а основний сервер бази один. Код у базі гірше версіонується й тестується. Тригер дописує ціну кожному UPDATE: масове оновлення 22 030 рядків (small) у тимчасовій копії orders зайняло 29–42 мс без тригера й 53–66 мс із ним. Якщо інваріант має триматись за будь-якого клієнта, він належить базі, а процеси й інтеграції із зовнішнім світом належать коду.
pg_stat_statements за замовчуванням рахує лише виклики верхнього рівня, тож запитів усередині функції він не бачить, і чекер L3 вважає таку функцію одним запитом.
Як це насправді
Section titled “Як це насправді”Підніміть postgres, запустіть застосунок (./labs/l03-app/run.sh) і подивіться, що він зробив із боку бази. pg_stat_statements у контейнері є, читає його суперкористувач:
docker compose exec postgres psql -U postgres -d shop -c "SELECT pg_stat_statements_reset()"curl -s localhost:3000/customers/1458/orders > /dev/nulldocker compose exec postgres psql -U postgres -d shop -c \ "SELECT calls, left(query, 60) AS query FROM pg_stat_statements WHERE userid = 'shop'::regrole ORDER BY calls DESC LIMIT 4" calls | query-------+-------------------------------------------------------------- 195 | SELECT title FROM products WHERE product_id = $1 102 | SELECT product_id, quantity, unit_price FROM order_items WHE 1 | SELECT customer_id, full_name FROM customers WHERE customer_ 1 | SELECT order_id, status, placed_at, total_amount FROM ordersПерший рядок і є N+1: той самий запит зі 195 викликами. pg_stat_statements нормалізує значення до $1, тож різні product_id складаються в один запис. ./labs/l03-app/measure.sh друкує цю кількість для покупців із різною кількістю замовлень і швидкість під навантаженням.
З’єднання та їхні стани видно в pg_stat_activity, ліміт показує SHOW max_connections:
SELECT usename, state, count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1, 2;Під навантаженням стартовий застосунок тримає кілька десятків з’єднань, які постійно відкриваються й закриваються, а з пулом їх десять. Скільки сеансів відкрито за весь час, рахує pg_stat_database.sessions: чекер читає різницю в ньому до й після навантаження.
Розбір: SQL-ін’єкція в TalkTalk, 2015
Section titled “Розбір: SQL-ін’єкція в TalkTalk, 2015”У жовтні 2015 року зловмисники через SQL-ін’єкцію в трьох веб-сторінках британського оператора TalkTalk, успадкованих від Tiscali, дістали персональні дані 156 959 клієнтів, а в 15 656 із них і номери банківських рахунків із sort code. За висновками регулятора ICO, тим самим способом компанію атакували й раніше, 17 липня та 2–3 вересня 2015, і вона не відреагувала. Програмне забезпечення бази було застарілим і мало помилку, виправлення якої існувало понад три з половиною роки. У жовтні 2016 року ICO наклав штраф у £400 000.
Тут той самий прийом, що й у пошуку «Крамниці». Показове те, що було довкола: відомий захист не застосували, дві атаки лишились непоміченими, оновлення не ставили.
Типові помилки розуміння
Section titled “Типові помилки розуміння”«Якщо екранувати апострофи, ін’єкції не буде». Екранування захищає лише рядковий літерал. Число без лапок, назва колонки й напрям сортування лишаються відкритими.
«ORM захищає від ін’єкцій». Значення, які ви передаєте методам, захищені. Рядок, який ви самі вставили у where чи order, ні.
«N+1 лікується кешем». Перший запит після очищення кешу лишається повільним, а кеш віддає застарілі дані. Виправляє кількість запитів на сторінку.
«Чим більший пул, тим швидше». Додаткові з’єднання лише чекають, а кожне з них — процес у базі. У нашому вимірі пули з 5 і з 50 з’єднань дали однаковий результат.
«PgBouncer у режимі transaction для застосунку прозорий». Ні: SET, сеансові advisory locks, LISTEN і SQL-PREPARE ламаються, бо наступна транзакція потрапляє на інше з’єднання.
Перевір себе
Лабораторна
Section titled “Лабораторна”L3. Застосунок: N+1, ін’єкція, пул: маленький HTTP-застосунок «Крамниці» з трьома вадами. Ви знаходите їх, виправляєте, а чекер атакує пошук, рахує запити на сторінку замовлень і дає 50 паралельних клієнтів.
Джерела
Section titled “Джерела”- PostgreSQL 18, документація: Message Flow, pg_stat_statements, Connections and Authentication, PL/pgSQL.
- node-postgres, Queries і Pooling.
- PgBouncer: Features, Configuration, Changelog.
- HikariCP, About Pool Sizing.
- OWASP, SQL Injection Prevention Cheat Sheet.
- ICO, TalkTalk cyber attack: how the ICO investigation unfolded.