Что такое PreparedStatement?

PreparedStatement: Безопасность и производительность SQL запросов

Что такое PreparedStatement?

PreparedStatement — это способ выполнения SQL запросов в которых SQL шаблон и данные разделены. Это предотвращает SQL инъекции и улучшает производительность.

Основная идея: сначала отправляем SQL шаблон на сервер БД, потом отправляем только параметры. Сервер БД компилирует запрос один раз и переиспользует его для разных параметров.

Проблема: SQL инъекции

Уязвимый код (string concatenation):

const userId = req.query.id; // '1\' OR \'1\'=\'1'
const query = `SELECT * FROM users WHERE id = '${userId}'`;
// Результат: SELECT * FROM users WHERE id = '1' OR '1'='1'
// ОШИБКА! Вернет все пользователей!
await db.query(query);

Безопасный код (PreparedStatement):

const userId = req.query.id; // '1\' OR \'1\'=\'1'
const query = 'SELECT * FROM users WHERE id = ?';
// Параметры отправляются отдельно
await db.query(query, [userId]);
// Сервер БД гарантирует, что userId не будет интерпретирован как SQL код

PreparedStatement в Node.js

С использованием pg (PostgreSQL):

const { Pool } = require('pg');
const pool = new Pool();

// Пример 1: Простой запрос
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',
  ['user@example.com']  // Параметр вместо ??
);

// Пример 2: Несколько параметров
const result = await pool.query(
  'UPDATE users SET name = $1, age = $2 WHERE id = $3',
  ['John', 30, 123]
);

// Пример 3: IN запрос
const userIds = [1, 2, 3, 4, 5];
const result = await pool.query(
  'SELECT * FROM users WHERE id = ANY($1)',
  [userIds]
);

С использованием mysql2 (MySQL):

const mysql = require('mysql2/promise');
const pool = mysql.createPool({ /* config */ });

const connection = await pool.getConnection();

// Использование ?
const [rows] = await connection.query(
  'SELECT * FROM users WHERE email = ?',
  ['user@example.com']
);

// Несколько параметров
const [result] = await connection.query(
  'INSERT INTO posts (userId, title, content) VALUES (?, ?, ?)',
  [123, 'Hello', 'World']
);

Как работают PreparedStatements

Фаза 1: Подготовка (prepare)

Клиент → Сервер БД: "SELECT * FROM users WHERE id = $1"
Сервер БД: Парсит SQL, создает план выполнения, отправляет OK

Фаза 2: Выполнение (execute)

Клиент → Сервер БД: [123] (только параметры)
Сервер БД: Подставляет параметры в заготовленный план, выполняет

Преимущества:

1. SQL инъекции невозможны — параметры не интерпретируются как SQL
2. Производительность — план выполнения кешируется на сервере
3. Сетевой трафик — отправляем только параметры, не весь запрос

Примеры SQL инъекций, которые предотвращают PreparedStatements

Инъекция 1: Login bypass

// Уязвимо:
const username = "admin' -- "; // Комментарий отключает проверку пароля
const query = `SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`;
// SELECT * FROM users WHERE username = 'admin' -- ' AND password = '123'

// Безопасно:
const query = 'SELECT * FROM users WHERE username = $1 AND password = $2';
await db.query(query, [username, password]);
// Параметры не интерпретируются, username = строка 'admin\' -- '

Инъекция 2: Data extraction

// Уязвимо:
const searchTerm = "'; DROP TABLE users; -- ";
const query = `SELECT * FROM products WHERE name LIKE '%${searchTerm}%'`;
// SELECT * FROM products WHERE name LIKE '%; DROP TABLE users; -- %'

// Безопасно:
const query = 'SELECT * FROM products WHERE name LIKE $1';
await db.query(query, [`%${searchTerm}%`]);

Инъекция 3: Union-based injection

// Уязвимо:
const id = "1 UNION SELECT username, password FROM users -- ";
const query = `SELECT * FROM products WHERE id = ${id}`;

// Безопасно:
const query = 'SELECT * FROM products WHERE id = $1';
await db.query(query, [id]); // id будет обработан как число

PreparedStatement с ORM (TypeORM)

import { getRepository } from 'typeorm';

const userRepository = getRepository(User);

// TypeORM под капотом использует PreparedStatements
const user = await userRepository.findOne({
  where: { email: 'user@example.com' }
});

// QueryBuilder также использует PreparedStatements
const users = await userRepository
  .createQueryBuilder('user')
  .where('user.email = :email', { email: userEmail })
  .andWhere('user.age > :age', { age: 18 })
  .getMany();

Named vs Positional параметры

Positional (с номерами):

// PostgreSQL использует $1, $2, $3
await db.query(
  'INSERT INTO users (name, email, age) VALUES ($1, $2, $3)',
  ['John', 'john@example.com', 30]
);

// MySQL использует ?
await db.query(
  'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
  ['John', 'john@example.com', 30]
);

Named параметры:

// Это особенно полезно для сложных запросов
const result = await db.query(
  `SELECT * FROM users 
   WHERE age > :minAge 
   AND age < :maxAge 
   AND status = :status`,
  {
    minAge: 18,
    maxAge: 65,
    status: 'active'
  }
);

Практический пример: User Service

class UserService {
  private db: Pool;

  // ❌ ПЛОХО: Уязвимо к SQL инъекциям
  async getUserByEmailBad(email: string): Promise<User> {
    const query = `SELECT * FROM users WHERE email = '${email}'`;
    const result = await this.db.query(query);
    return result.rows[0];
  }

  // ✅ ХОРОШО: Использует PreparedStatement
  async getUserByEmail(email: string): Promise<User> {
    const query = 'SELECT * FROM users WHERE email = $1';
    const result = await this.db.query(query, [email]);
    return result.rows[0];
  }

  // ✅ ХОРОШО: Несколько параметров
  async searchUsers(
    minAge: number,
    maxAge: number,
    status: string
  ): Promise<User[]> {
    const query = `
      SELECT * FROM users 
      WHERE age >= $1 
      AND age <= $2 
      AND status = $3
      ORDER BY created_at DESC
    `;
    const result = await this.db.query(query, [minAge, maxAge, status]);
    return result.rows;
  }

  // ✅ ХОРОШО: IN запрос с массивом
  async getUsersByIds(ids: number[]): Promise<User[]> {
    const query = `
      SELECT * FROM users 
      WHERE id = ANY($1)
      ORDER BY id
    `;
    const result = await this.db.query(query, [ids]);
    return result.rows;
  }

  // ✅ ХОРОШО: UPDATE с параметрами
  async updateUser(
    id: number,
    name: string,
    email: string
  ): Promise<User> {
    const query = `
      UPDATE users 
      SET name = $1, email = $2, updated_at = CURRENT_TIMESTAMP 
      WHERE id = $3 
      RETURNING *
    `;
    const result = await this.db.query(query, [name, email, id]);
    return result.rows[0];
  }
}

Performance: PreparedStatement vs String concat

Сценарий: 1000 запросов к БД

❌ String concatenation:
- Сетевой трафик: 1000 полных SQL запросов × 500 bytes = 500KB
- Парсинг на сервере: 1000 раз
- Компиляция плана: 1000 раз
- Время: ~2 сек

✅ PreparedStatement:
- Сетевой трафик: 1 SQL шаблон (500 bytes) + 1000 параметров (50 bytes) = ~50KB
- Парсинг на сервере: 1 раз
- Компиляция плана: 1 раз
- Время: ~200 мс

Улучшение: в 10 раз быстрее!

Когда НЕ нужны PreparedStatements

Технически параметры не всегда возможно параметризировать:

// Нельзя параметризировать названия таблиц/колонок
const column = req.query.sortBy; // 'id' или 'name'
// Нельзя: WHERE $1 = value
// Нужно: построить запрос вручную + валидировать

const allowedColumns = ['id', 'name', 'email', 'created_at'];
if (!allowedColumns.includes(column)) {
  throw new Error('Invalid column');
}
const query = `SELECT * FROM users ORDER BY ${column} DESC`;
await this.db.query(query);

Лучшие практики

1. ВСЕГДА использовай PreparedStatements для пользовательского ввода
2. Никогда не concatenate строки для SQL запросов
3. Используй ORM если возможно (TypeORM, Sequelize)
4. Белый список для названий колонок и таблиц
5. Код ревью — проверяй SQL запросы в pull request
6. Тесты безопасности — проверяй на SQL инъекции

Вывод: PreparedStatement — это не опционально, это ОБЯЗАТЕЛЬНО. Это фундамент безопасности любого приложения, которое работает с БД. Никаких исключений.