Пошук уроків, статей та іншого контенту
Застосуєте підготовлені запити для захисту від SQL-ін’єкцій і повторного виконання однакових SQL-команд.
Підготовлений запит — це SQL-команда, у якій значення передаються окремо від тексту запиту.
Замість підстановки значення безпосередньо в SQL:
const email = "user@example.com";
const query = `
SELECT id, display_name
FROM users
WHERE email = '${email}'
`;використовують параметр:
const query = `
SELECT id, display_name
FROM users
WHERE email = $1
`;
const values = [email];У цьому випадку PostgreSQL отримує окремо:
структуру SQL-команди;
значення параметра email.
СУБД не сприймає вміст параметра як частину SQL-коду. Саме це захищає запит від SQL-ін’єкцій.
SQL-ін’єкція виникає, коли дані користувача об’єднуються з текстом SQL-команди.
Небезпечний приклад:
const email = req.body.email;
const query = `
SELECT id, display_name
FROM users
WHERE email = '${email}'
`;Якщо користувач передасть спеціально сформований рядок, він може змінити логіку запиту. Наприклад, значення на кшталт:
' OR '1'='1може перетворити умову на таку:
WHERE email = '' OR '1' = '1'У результаті запит може повернути всі записи.
Не потрібно намагатися вручну екранувати такі значення. Надійніший спосіб — передавати їх як параметри.
Для роботи з PostgreSQL у Node.js часто використовують пакет pg.
Встановлення:
npm install pgПараметри передаються другим аргументом методу query:
const result = await pool.query(
`
SELECT id, display_name
FROM users
WHERE email = $1
`,
[email]
);Позначення $1 означає перший параметр у масиві. Наступні параметри позначаються $2, $3 і так далі:
const result = await pool.query(
`
SELECT id, display_name
FROM users
WHERE email = $1 AND status = $2
`,
[email, "active"]
);Порядок параметрів важливий:
$1 отримує значення values[0];
$2 отримує значення values[1];
$3 отримує значення values[2].
Параметризований запит уже захищає значення від SQL-ін’єкції. Щоб PostgreSQL також міг повторно використовувати підготовлену SQL-команду, запиту передають ім’я через властивість name.
const result = await pool.query({
name: "find-user-by-email",
text: `
SELECT id, display_name
FROM users
WHERE email = $1
`,
values: [email]
});Під час першого виконання PostgreSQL підготує запит для цього з’єднання. Під час наступних виконань із тим самим іменем драйвер зможе використовувати підготовлену команду повторно.
Ім’я підготовленого запиту має бути стабільним для однакової SQL-команди:
name: "find-user-by-email"Не потрібно створювати унікальне ім’я для кожного виклику:
// Погано: кожен виклик створює нове ім’я запиту
name: `find-user-${Date.now()}`Так повторне використання підготовленого запиту втрачає сенс.
Підготовлений запит прив’язаний до конкретного з’єднання з PostgreSQL. Якщо використовується пул, кожне з’єднання може підготувати цей запит під час свого першого виконання.
Наведений приклад створює таблицю, додає користувача та виконує пошук за електронною поштою. Для пошуку використовується іменований підготовлений запит.
Перед запуском переконайтеся, що PostgreSQL запущений і база даних доступна.
const { Pool } = require("pg");
const pool = new Pool({
connectionString:
process.env.DATABASE_URL ||
"postgresql://postgres:postgres@localhost:5432/app"
});
async function createUsersTable() {
await pool.query(`
CREATE TABLE IF NOT EXISTS users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
display_name TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'active'
)
`);
}
async function createUser(email, displayName) {
const result = await pool.query({
name: "create-user",
text: `
INSERT INTO users (email, display_name)
VALUES ($1, $2)
RETURNING id, email, display_name, status
`,
values: [email, displayName]
});
return result.rows[0];
}
async function findUserByEmail(email) {
const result = await pool.query({
name: "find-user-by-email",
text: `
SELECT id, email, display_name, status
FROM users
WHERE email = $1
`,
values: [email]
});
return result.rows[0] || null;
}
async function main() {
try {
await createUsersTable();
const email = "olena@example.com";
const displayName = "Olena";
const existingUser = await findUserByEmail(email);
if (!existingUser) {
const createdUser = await createUser(email, displayName);
console.log("Створено користувача:", createdUser);
}
const user = await findUserByEmail(email);
console.log("Знайдено користувача:", user);
// Спеціальні символи залишаються звичайним значенням параметра
const suspiciousEmail = "' OR '1'='1";
const suspiciousUser = await findUserByEmail(suspiciousEmail);
console.log("Результат небезпечного значення:", suspiciousUser);
} catch (error) {
console.error("Помилка виконання запиту:", error.message);
} finally {
await pool.end();
}
}
main();У цьому прикладі значення suspiciousEmail не змінює SQL-команду. PostgreSQL шукає користувача, у якого електронна пошта буквально дорівнює рядку:
' OR '1'='1Підготовлені запити використовуються не лише для SELECT.
await pool.query(
`
INSERT INTO users (email, display_name)
VALUES ($1, $2)
`,
["petro@example.com", "Petro"]
);await pool.query(
`
UPDATE users
SET display_name = $1
WHERE id = $2
`,
["Petro Kovalenko", 10]
);await pool.query(
`
DELETE FROM users
WHERE id = $1
`,
[10]
);У всіх випадках дані передаються окремо від тексту SQL-команди.
Розглянемо функцію, яка багато разів шукає користувача за електронною поштою:
async function findUserByEmail(email) {
const result = await pool.query({
name: "find-user-by-email",
text: `
SELECT id, email, display_name
FROM users
WHERE email = $1
`,
values: [email]
});
return result.rows[0] || null;
}Текст запиту та його ім’я залишаються незмінними, а значення $1 змінюється:
await findUserByEmail("olena@example.com");
await findUserByEmail("petro@example.com");
await findUserByEmail("anna@example.com");Це дає дві переваги:
значення не об’єднуються з SQL-текстом;
однакова команда може бути підготовлена та повторно використана PostgreSQL.
Підготовлені запити особливо корисні для часто виконуваних команд: пошуку за ідентифікатором, отримання об’єкта за унікальним полем, оновлення запису або додавання однотипних даних.
Параметри $1, $2 та інші призначені для значень. Вони не можуть замінювати імена таблиць, стовпців або ключові слова SQL.
Це не спрацює:
const column = "email";
await pool.query(
`
SELECT id, $1
FROM users
`,
[column]
);У такому запиті $1 буде значенням, а не назвою стовпця.
Якщо потрібно дозволити сортування за одним із кількох стовпців, використовуйте список дозволених значень:
const allowedSortColumns = {
name: "display_name",
email: "email",
id: "id"
};
const requestedColumn = "name";
const sortColumn = allowedSortColumns[requestedColumn] || "id";
const result = await pool.query(`
SELECT id, email, display_name
FROM users
ORDER BY ${sortColumn}
`);Користувацьке значення спочатку перевіряється та перетворюється на заздалегідь відому назву стовпця. Без такої перевірки вставляти його в SQL-текст небезпечно.
Значення фільтра при цьому все одно потрібно передавати параметром:
const result = await pool.query(
`
SELECT id, email, display_name
FROM users
WHERE status = $1
ORDER BY ${sortColumn}
`,
["active"]
);NULLДля передачі SQL-значення NULL використовується JavaScript-значення null:
await pool.query(
`
UPDATE users
SET display_name = $1
WHERE id = $2
`,
[null, 10]
);Однак порівняння з NULL має особливі правила SQL. Умова:
WHERE display_name = $1не знайде рядки, якщо $1 дорівнює NULL. Для перевірки відсутнього значення використовують IS NULL:
await pool.query(`
SELECT id, email
FROM users
WHERE display_name IS NULL
`);Небезпечно:
const query = `
SELECT *
FROM users
WHERE email = '${email}'
`;Безпечно:
const query = `
SELECT *
FROM users
WHERE email = $1
`;
const result = await pool.query(query, [email]);Параметри потрібно використовувати для всіх даних, незалежно від типу:
рядків;
чисел;
дат;
ідентифікаторів;
значень boolean;
значень null.
Не слід використовувати одне ім’я для різних SQL-команд:
// Погано: одна назва для різних команд
name: "user-query"Краще давати імена, які відповідають конкретній команді:
name: "find-user-by-email"
name: "create-user"
name: "update-user-status"Параметр призначений для значення, а не для назви таблиці чи стовпця. Для динамічних імен використовуйте попередньо визначений список дозволених варіантів.
Запит без властивості name, але з $1, також захищає від SQL-ін’єкцій:
await pool.query(
"SELECT * FROM users WHERE id = $1",
[userId]
);Властивість name додатково вказує драйверу використовувати іменований підготовлений запит для повторного виконання.
Метод query повертає об’єкт із полем rows:
const result = await pool.query({
name: "find-user-by-id",
text: `
SELECT id, email, display_name
FROM users
WHERE id = $1
`,
values: [userId]
});
const user = result.rows[0] || null;Для запитів, які змінюють дані, можна використовувати rowCount:
const result = await pool.query({
name: "delete-user-by-id",
text: `
DELETE FROM users
WHERE id = $1
`,
values: [userId]
});
if (result.rowCount === 0) {
console.log("Користувача не знайдено");
}Підготовлений запит відокремлює SQL-команду від даних.
Параметри PostgreSQL позначаються як $1, $2, $3 тощо.
У Node.js значення передаються другим аргументом query або через поле values.
Параметризовані запити захищають від SQL-ін’єкцій.
Властивість name дає змогу повторно використовувати підготовлену SQL-команду.
Параметри не замінюють назви таблиць і стовпців.
Для динамічних імен SQL-об’єктів потрібно використовувати список дозволених значень.
Не слід об’єднувати введені користувачем дані з текстом SQL за допомогою шаблонних рядків або конкатенації.