SQL Injection 방어: 파라미터 쿼리 + ORM

-- 입력: ' OR '1'='1
SELECT * FROM users WHERE email = '' OR '1'='1' AND password = '...'
-- → 항상 true → 인증 우회


-- UNION 기반
-- 입력: ' UNION SELECT password FROM users--
SELECT * FROM products WHERE category = '' UNION SELECT password FROM users--
-- → 다른 테이블 데이터 노출


-- Stacked Queries
-- 입력: '; DROP TABLE users--
SELECT * FROM products WHERE id = ''; DROP TABLE users--
-- → 테이블 삭제


-- Blind SQL Injection
-- 입력: ' AND (SELECT SUBSTR(password, 1, 1) FROM users WHERE id=1)='a'--
-- → 응답 차이로 한 글자씩 추출

// ✅ 모든 쿼리 자동 parameterized
const user = await db.user.findUnique({
  where: { email: req.body.email },
});


// ✅ raw 쿼리도 parameterized
const result = await db.$queryRaw`
  SELECT * FROM users WHERE email = ${email}
`;


// ❌ string concatenation — 절대 금지
const result = await db.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);

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

// ✅ $1, $2... 플레이스홀더
const { rows } = await pool.query(
  'SELECT * FROM users WHERE email = $1 AND status = $2',
  [email, status]
);


// ❌ 문자열 결합
const { rows } = await pool.query(
  `SELECT * FROM users WHERE email = '${email}'`  // SQL injection!
);

import { z } from 'zod';

const SearchSchema = z.object({
  query: z.string().max(100).regex(/^[a-zA-Z0-9가-힣\s]+$/),
  category: z.enum(['food', 'fashion', 'electronics']),
  minPrice: z.coerce.number().int().min(0).max(10000000),
  maxPrice: z.coerce.number().int().min(0).max(10000000),
});


app.get('/search', async (req, res) => {
  const params = SearchSchema.parse(req.query);
  // params는 검증된 안전한 값
  const results = await db.product.findMany({
    where: {
      name: { contains: params.query },
      category: params.category,
      price: { gte: params.minPrice, lte: params.maxPrice },
    },
  });
  res.json(results);
});

// 컬럼명·정렬은 parameterized 안 됨
// → 화이트리스트


const ALLOWED_ORDER_BY = ['createdAt', 'price', 'name'] as const;
type OrderBy = typeof ALLOWED_ORDER_BY[number];


function getProducts(orderBy: OrderBy, order: 'asc' | 'desc') {
  if (!ALLOWED_ORDER_BY.includes(orderBy)) {
    throw new Error('Invalid orderBy');
  }
  if (!['asc', 'desc'].includes(order)) {
    throw new Error('Invalid order');
  }

  return db.product.findMany({
    orderBy: { [orderBy]: order },
  });
}


// raw에서도 컬럼명 검증
const orderColumn = orderBy === 'price' ? 'price' :
                    orderBy === 'name' ? 'name' :
                    'created_at';

await db.$queryRaw`
  SELECT * FROM products ORDER BY ${Prisma.raw(orderColumn)} ${Prisma.raw(order)}
`;
// Prisma.raw는 신뢰된 값에만!

-- PostgreSQL function — parameterized 안전
CREATE FUNCTION get_user(p_email VARCHAR)
RETURNS users AS $$
  SELECT * FROM users WHERE email = p_email;
$$ LANGUAGE sql;


-- 호출
SELECT * FROM get_user('alice@example.com');

// ❌ 사용자 입력을 query 객체로
const user = await User.findOne({ email: req.body.email });


// 공격: req.body.email = { $ne: null }
// → 모든 사용자 매치


// ✅ 검증
const { email } = z.object({ email: z.string().email() }).parse(req.body);
const user = await User.findOne({ email });


// ✅ Mongoose strictQuery
import mongoose from 'mongoose';
mongoose.set('strictQuery', true);


// ✅ express-mongo-sanitize 미들웨어
import mongoSanitize from 'express-mongo-sanitize';
app.use(mongoSanitize());
// → $ 시작 키 자동 제거

-- 애플리케이션 사용자 — 최소 권한
CREATE USER app_user WITH PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
REVOKE ALL ON SCHEMA pg_catalog FROM app_user;


-- 읽기 전용 사용자 (분석)
CREATE USER analytics_user WITH PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;


-- 마이그레이션 사용자 (DDL)
CREATE USER migration_user WITH PASSWORD '...';
GRANT ALL ON ALL TABLES IN SCHEMA public TO migration_user;
GRANT CREATE ON SCHEMA public TO migration_user;

[Cloudflare WAF]
- 무료 plan에 SQL injection 차단 룰
- Pro: $20/mo — 추가 룰셋

[AWS WAF]
- AWS 환경
- managed rules + custom rules

[자체 — modsecurity]
- nginx 모듈
- OWASP Core Rule Set


→ Defense in Depth — 코드 + WAF 이중 보호