SQLインジェクションの基礎と対策

// ❌ 脆弱な実装
async function login(username: string, password: string) {
  const query = `SELECT * FROM users WHERE username='${username}' AND password='${password}'`;
  return db.query(query);
}

-- 通常のクエリ
SELECT * FROM users WHERE username='alice' AND password='secret'

-- 攻撃者の入力後（username = "' OR '1'='1' --"）
SELECT * FROM users WHERE username='' OR '1'='1' --' AND password='...'
--                                                  ^^ ここ以降はコメントになる

-- username = "'; DROP TABLE users; --"
SELECT * FROM users WHERE username=''; DROP TABLE users; --'
-- usersテーブルが削除される

-- UNION を使ったデータ抽出
-- username = "' UNION SELECT id, email, password FROM users --"
SELECT id, name FROM products WHERE id='' UNION SELECT id, email, password FROM users --'
-- usersテーブルの認証情報が products のクエリ結果に混入する

// PostgreSQL (pg ライブラリ)
import { Pool } from 'pg';
const pool = new Pool();

// ❌ 危険: 文字列連結
const result = await pool.query(`SELECT * FROM users WHERE username='${username}'`);

// ✓ 安全: $1 プレースホルダーを使う
const result = await pool.query(
  'SELECT * FROM users WHERE username = $1 AND password_hash = $2',
  [username, passwordHash]
);

// MySQL (mysql2 ライブラリ)
import mysql from 'mysql2/promise';
const conn = await mysql.createConnection({ /* ... */ });

// ✓ 安全: ? プレースホルダーを使う
const [rows] = await conn.execute(
  'SELECT * FROM users WHERE username = ? AND password_hash = ?',
  [username, passwordHash]
);

// ✓ 安全: Prismaのfindが内部でプリペアドステートメントを使う
const user = await prisma.user.findFirst({
  where: {
    username: username,
    passwordHash: passwordHash,
  },
});

// Prismaで生のSQLを使う場合は $queryRaw テンプレートリテラルを使う
// ✓ 安全: テンプレートリテラル構文がパラメータを自動でバインドする
const users = await prisma.$queryRaw`
  SELECT * FROM users WHERE username = ${username}
`;

// ❌ 危険: $queryRawUnsafe は文字列連結と同じリスクがある
const users = await prisma.$queryRawUnsafe(
  `SELECT * FROM users WHERE username = '${username}'`
);

// ✓ 安全: Repository APIはプリペアドステートメントを使う
const user = await userRepository.findOne({
  where: { username, passwordHash },
});

// TypeORMで生のSQLを書く場合はパラメータバインドを使う
// ✓ 安全: :username のプレースホルダーを使う
const user = await userRepository
  .createQueryBuilder('user')
  .where('user.username = :username', { username })
  .andWhere('user.password_hash = :passwordHash', { passwordHash })
  .getOne();

// ❌ 危険: 文字列テンプレートリテラルで直接埋め込む
const user = await userRepository
  .createQueryBuilder('user')
  .where(`user.username = '${username}'`)  // SQLインジェクションになる
  .getOne();

// NestJS: class-validator を使ったバリデーション
import { IsString, MaxLength, IsEmail } from 'class-validator';

export class LoginDto {
  @IsString()
  @MaxLength(100)
  username: string;

  @IsString()
  @MaxLength(200)
  password: string;
}

// users.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';

@Injectable()
export class UsersService {
  constructor(private prisma: PrismaService) {}

  // ✓ 安全: Prismaのfindが内部でエスケープを行う
  async findByUsername(username: string) {
    return this.prisma.user.findFirst({ where: { username } });
  }

  // ✓ 安全: $queryRaw テンプレートリテラルはパラメータを自動バインド
  async searchByKeyword(keyword: string) {
    return this.prisma.$queryRaw`
      SELECT id, name, email
      FROM users
      WHERE name ILIKE ${'%' + keyword + '%'}
    `;
  }
}

// ❌ 危険: TypeORMの query() メソッドへの直接埋め込み
const result = await dataSource.query(
  `SELECT * FROM products WHERE category = '${category}'`
);

// ✓ 安全: 第2引数でパラメータを渡す
const result = await dataSource.query(
  'SELECT * FROM products WHERE category = $1',
  [category]
);

// ❌ 危険: カラム名をそのまま埋め込む
const result = await pool.query(
  `SELECT * FROM users ORDER BY ${sortColumn} DESC`
);

// ✓ 安全: 許可するカラム名を明示的にホワイトリストで管理する
const ALLOWED_SORT_COLUMNS = ['created_at', 'name', 'email'] as const;
type SortColumn = typeof ALLOWED_SORT_COLUMNS[number];

function isSortColumn(value: string): value is SortColumn {
  return (ALLOWED_SORT_COLUMNS as readonly string[]).includes(value);
}

const column = isSortColumn(sortColumn) ? sortColumn : 'created_at';
const result = await pool.query(
  `SELECT * FROM users ORDER BY ${column} DESC`
);

// ❌ 危険: DBのエラーをそのままクライアントに返す
try {
  const user = await db.query(...);
} catch (err) {
  res.status(500).json({ error: err.message }); // テーブル構造が漏洩する
}

// ✓ 安全: 内部エラーはログに出力し、クライアントには汎用メッセージを返す
try {
  const user = await db.query(...);
} catch (err) {
  logger.error('DB query failed', err);
  throw new InternalServerErrorException('Internal error');
}