総合演習：セキュリティ監査レポート

// app.ts
import express from 'express';
import cors from 'cors';
import { pool } from './db';
import crypto from 'crypto';

const app = express();
app.use(cors());
app.use(express.json());

const JWT_SECRET = 'my-app-secret-2025';

// ユーザー登録
app.post('/api/register', async (req, res) => {
  const { name, email, password } = req.body;
  const hash = crypto.createHash('md5').update(password).digest('hex');

  await pool.query(
    `INSERT INTO users (name, email, password_hash) VALUES ('${name}', '${email}', '${hash}')`
  );

  res.json({ success: true, message: `${name}さん、登録完了！` });
});

// ログイン
app.post('/api/login', async (req, res) => {
  const { email, password } = req.body;
  const hash = crypto.createHash('md5').update(password).digest('hex');

  const result = await pool.query(
    `SELECT * FROM users WHERE email = '${email}' AND password_hash = '${hash}'`
  );

  if (result.rows.length === 0) {
    return res.status(401).json({ error: `${email} のログインに失敗しました` });
  }

  const user = result.rows[0];
  const token = user.id + ':' + Date.now();

  res.json({ token, user });
});

// プロフィール取得
app.get('/api/profile/:id', async (req, res) => {
  const result = await pool.query(
    `SELECT * FROM users WHERE id = ${req.params.id}`
  );
  res.json(result.rows[0]);
});

// 商品検索
app.get('/api/products/search', async (req, res) => {
  const { q, sort } = req.query;
  let query = `SELECT * FROM products WHERE name LIKE '%${q}%'`;
  if (sort) {
    query += ` ORDER BY ${sort}`;
  }

  try {
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (error) {
    res.status(500).json({ error: error.message, stack: error.stack });
  }
});

// コメント投稿
app.post('/api/comments', async (req, res) => {
  const { postId, body } = req.body;
  await pool.query(
    'INSERT INTO comments (post_id, body) VALUES ($1, $2)',
    [postId, body]
  );
  res.json({ success: true });
});

// コメント表示
app.get('/api/posts/:id/comments', async (req, res) => {
  const result = await pool.query(
    'SELECT * FROM comments WHERE post_id = $1',
    [req.params.id]
  );

  let html = '<div>';
  for (const c of result.rows) {
    html += `<p>${c.body}</p>`;
  }
  html += '</div>';
  res.send(html);
});

// 管理者API
app.delete('/api/admin/users/:id', async (req, res) => {
  await pool.query(`DELETE FROM users WHERE id = ${req.params.id}`);
  res.json({ deleted: true });
});

// エラーハンドラ
app.use((err, req, res, next) => {
  res.status(500).json({ error: err.message, stack: err.stack });
});

app.listen(3000);

// 1. ログインエンドポイント（修正後）
import bcrypt from 'bcrypt';
import jwt from 'jsonwebtoken';

const JWT_SECRET = process.env.JWT_SECRET!;

app.post('/api/login', async (req, res) => {
  const loginSchema = z.object({
    email: z.string().email(),
    password: z.string().min(1),
  });

  const validation = loginSchema.safeParse(req.body);
  if (!validation.success) {
    return res.status(400).json({ error: 'Invalid input' });
  }

  const { email, password } = validation.data;

  try {
    const result = await pool.query(
      'SELECT id, email, password_hash, role FROM users WHERE email = $1',
      [email]
    );

    if (result.rows.length === 0) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    const user = result.rows[0];
    const isValid = await bcrypt.compare(password, user.password_hash);

    if (!isValid) {
      return res.status(401).json({ error: 'Invalid credentials' });
    }

    const accessToken = jwt.sign(
      { sub: user.id, role: user.role },
      JWT_SECRET,
      { expiresIn: '15m' }
    );

    res.json({
      accessToken,
      user: { id: user.id, email: user.email, role: user.role }
    });
  } catch (error) {
    console.error('Login error:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// 2. 商品検索（修正後）
const ALLOWED_SORT = ['name', 'price', 'created_at'];

app.get('/api/products/search', async (req, res) => {
  const searchSchema = z.object({
    q: z.string().max(200).optional(),
    sort: z.string().optional(),
  });

  const validation = searchSchema.safeParse(req.query);
  if (!validation.success) {
    return res.status(400).json({ error: 'Invalid parameters' });
  }

  const { q, sort } = validation.data;

  try {
    let query = 'SELECT id, name, price, description FROM products';
    const params: string[] = [];

    if (q) {
      const safeQ = q.replace(/[%_\\]/g, '\\$&');
      query += " WHERE name LIKE $1 ESCAPE '\\'";
      params.push(`%${safeQ}%`);
    }

    const sortColumn = ALLOWED_SORT.includes(sort || '') ? sort : 'created_at';
    query += ` ORDER BY ${sortColumn} DESC`;

    const result = await pool.query(query, params);
    res.json(result.rows);
  } catch (error) {
    console.error('Search error:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

// 3. コメント表示（修正後）
import escapeHtml from 'escape-html';

app.get('/api/posts/:id/comments', async (req, res) => {
  const id = parseInt(req.params.id, 10);
  if (isNaN(id) || id <= 0) {
    return res.status(400).json({ error: 'Invalid ID' });
  }

  try {
    const result = await pool.query(
      'SELECT id, body, created_at FROM comments WHERE post_id = $1 ORDER BY created_at ASC',
      [id]
    );

    let html = '<div>';
    for (const c of result.rows) {
      html += `<p>${escapeHtml(c.body)}</p>`;
    }
    html += '</div>';
    res.send(html);
  } catch (error) {
    console.error('Comments error:', error);
    res.status(500).send('<div>エラーが発生しました</div>');
  }
});

import helmet from 'helmet';

app.use(helmet({
  contentSecurityPolicy: {
    directives: {
      defaultSrc: ["'self'"],
      scriptSrc: ["'self'"],
      styleSrc: ["'self'", "'unsafe-inline'"],
      imgSrc: ["'self'", "data:"],
      connectSrc: ["'self'"],
      frameSrc: ["'none'"],
      objectSrc: ["'none'"],
    }
  },
  hsts: { maxAge: 31536000, includeSubDomains: true },
}));

// CORS を特定のオリジンに制限
app.use(cors({
  origin: process.env.ALLOWED_ORIGINS?.split(','),
  credentials: true,
}));

// Cookieのセキュリティ設定
// HttpOnly, Secure, SameSite=Strict