SQL Injection: 공격 원리와 방어

# ⚠️ 이 코드는 교육 목적이며 허가된 환경에서만 사용하세요.
import sqlite3

# ❌ 취약: f-string으로 입력값 직접 삽입
def login_vulnerable(username: str, password: str) -> bool:
    conn = sqlite3.connect('users.db')
    cur = conn.cursor()
    query = f"SELECT * FROM users WHERE username='{username}' AND password='{password}'"
    # 공격 입력: username = "admin' --"  → 패스워드 우회
    cur.execute(query)
    return cur.fetchone() is not None


# ✅ 안전 1: 파라미터화 쿼리 (Prepared Statement)
def login_safe_v1(username: str, password: str) -> bool:
    conn = sqlite3.connect('users.db')
    cur = conn.cursor()
    cur.execute(
        'SELECT * FROM users WHERE username=? AND password=?',
        (username, password),
    )
    return cur.fetchone() is not None


# ✅ 안전 2: ORM (SQLAlchemy)
from sqlalchemy.orm import Session
from sqlalchemy import select

def login_safe_v2(session: Session, username: str, password: str) -> bool:
    stmt = select(User).where(
        User.username == username,
        User.password == password,
    )
    return session.execute(stmt).first() is not None


# ✅ 안전 3: 입력 검증 (정규식 화이트리스트)
import re
USERNAME_RE = re.compile(r'^[a-zA-Z0-9_]{3,20}$')

def validate_username(username: str) -> bool:
    return bool(USERNAME_RE.match(username))

# ⚠️ 이 코드는 본인 소유 코드 점검용입니다.
import ast
from pathlib import Path

class SQLInjectionDetector(ast.NodeVisitor):
    def __init__(self):
        self.warnings = []

    def visit_Call(self, node):
        # cursor.execute(f"...") 또는 cursor.execute("..." + var) 탐지
        if (isinstance(node.func, ast.Attribute)
            and node.func.attr in ('execute', 'executemany')):
            for arg in node.args:
                if isinstance(arg, (ast.JoinedStr, ast.BinOp)):
                    self.warnings.append({
                        'line': node.lineno,
                        'issue': '동적 SQL 문자열 (파라미터화 권장)',
                    })
        self.generic_visit(node)


def scan_file(path: Path):
    tree = ast.parse(path.read_text())
    detector = SQLInjectionDetector()
    detector.visit(tree)
    return detector.warnings


for py in Path('src/').rglob('*.py'):
    warnings = scan_file(py)
    if warnings:
        print(f'{py}:')
        for w in warnings:
            print(f"  L{w['line']} {w['issue']}")