PHP PDO와 SQLite: SQL 인젝션을 직접 재현하고 고치기

시리즈: PHP 강좌 4/6 (중급). PHP 클래스를 쓸 줄 알고 이제 데이터베이스가 필요한 분을 위한 글입니다. 다 읽고 나면 PDO로 데이터베이스를 열고, prepared statement로 조회하고, 동료의 코드에서 SQL 인젝션이 실제로 일어난다는 것을 보여 준 뒤 고칠 수 있습니다.

이 글은 데이터베이스에 접근하는 PHP를 쓰거나 리뷰하는 분, 특히 오래된 관리자 페이지나 게시판 코드에서 . 연산자로 SQL을 조립하는 부분을 물려받은 분을 위한 것입니다. PHP에 기본으로 들어 있는 SQLite를 쓰므로 DB 서버가 필요 없습니다. 모든 예제는 PHP 8.5.6 CLI + SQLite 3.53.4, Apple Silicon Mac(M1 Pro)에서 실행했습니다. 인젝션은 진짜입니다. 아래 페이로드는 로그인 확인으로 보호하려던 테이블의 모든 행을 돌려줬습니다.

실제 운영 사례로는 10년 된 PHP 백엔드에서 인젝션과 출력 수정 90건을 하고 출력이 바이트 단위로 같음을 증명한 레거시 PHP SQLi/XSS 감사 글이 있습니다. 이 강좌는 그 밑바탕입니다. 테이블은 모두 일반적인 users와 posts입니다.

PDO 연결과 ERRMODE_EXCEPTION

<?php
declare(strict_types=1);

$file = __DIR__ . '/app.sqlite';
$pdo = new PDO('sqlite:' . $file, options: [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$pdo->exec(<<<SQL
    CREATE TABLE users (
        id    INTEGER PRIMARY KEY,
        email TEXT NOT NULL UNIQUE,
        name  TEXT NOT NULL,
        role  TEXT NOT NULL DEFAULT 'member'
    );
    CREATE TABLE posts (
        id      INTEGER PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id),
        title   TEXT NOT NULL,
        body    TEXT NOT NULL
    );
SQL);

$ins = $pdo->prepare('INSERT INTO users (email, name, role) VALUES (:email, :name, :role)');
foreach ([
    ['alice@example.com', 'Alice', 'admin'],
    ['bob@example.com',   'Bob',   'member'],
    ['carol@example.com', 'Carol', 'member'],
] as [$email, $name, $role]) {
    $ins->execute(['email' => $email, 'name' => $name, 'role' => $role]);
}
$p = $pdo->prepare('INSERT INTO posts (user_id, title, body) VALUES (?, ?, ?)');
$p->execute([1, '100% sure', 'Discount 50% off_today']);
$p->execute([1, 'Hello', 'first post']);
$p->execute([2, 'under_score', 'a_b']);
$p->execute([3, 'Points', 'you have 500 points']);
echo "users: ", $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn(), PHP_EOL;   // users: 3

ERRMODE_EXCEPTION은 PHP 8.0부터 기본값입니다(8.5.6에서 getAttribute(PDO::ATTR_ERRMODE)가 이 값을 돌려주는 것을 확인했습니다). 그래도 명시적으로 설정하세요. 코드는 오래된 서버로 복사되기도 하고, 예전 기본값은 "조용히 실패"였습니다. 조용하면 이렇게 됩니다.

ERRMODE_SILENT:  $pdo->query('SELECT * FROM nope') returns bool(false)
                 errorInfo() = [HY000, 1, no such table: nope]
                 then: Fatal error: Uncaught Error: Call to a member function fetchAll() on false

진짜 원인(no such table)은 errorInfo() 안에 있고, 눈에 보이는 에러는 한 줄 뒤에서 false 이야기를 합니다. 예외 모드라면 원인이 메시지에 나옵니다.

PHP 8.4에서는 드라이버별 하위 클래스도 생겨서 PDO::connect('sqlite::memory:')가 Pdo\Sqlite 객체를 돌려줍니다. 8.5에서는 PDO 클래스에 있던 드라이버 전용 상수가 Deprecated가 되어, MySQL 설정을 건드리는 순간 보입니다.

Deprecated: Constant PDO::MYSQL_ATTR_INIT_COMMAND is deprecated since 8.5, use Pdo\Mysql::ATTR_INIT_COMMAND instead in ~/demo/b8_misc.php on line 4

실제 쿼리로 SQL 인젝션 재현하기

수많은 튜토리얼에 나오는 패턴입니다. 입력을 받아 그대로 SQL에 이어 붙입니다.

$input = $argv[1] ?? "nobody@example.com' OR '1'='1";   // 실제로는 $_GET['email'] 이라고 가정

// 취약
$sql = "SELECT id, email, role FROM users WHERE email = '" . $input . "'";
echo "SQL: $sql\n";
$rows = $pdo->query($sql)->fetchAll();
echo "concat   -> ", count($rows), " rows\n";
foreach ($rows as $r) echo "  ", implode(' | ', $r), "\n";

// 수정
$st = $pdo->prepare('SELECT id, email, role FROM users WHERE email = ?');
$st->execute([$input]);
echo "prepared -> ", count($st->fetchAll()), " rows\n";

SQL: SELECT id, email, role FROM users WHERE email = 'nobody@example.com' OR '1'='1'
concat   -> 3 rows
  1 | alice@example.com | admin
  2 | bob@example.com | member
  3 | carol@example.com | member
prepared -> 0 rows

입력 안의 작은따옴표가 문자열 리터럴을 닫았고, OR '1'='1'이 조건의 일부가 됐습니다. 사용자 한 명을 돌려줘야 할 쿼리가 admin을 포함한 세 명 전부를 돌려줬습니다. UNION 페이로드라면 쿼리가 고르지 않은 열까지 읽어 갑니다.

SQL: SELECT id, email, role FROM users WHERE email = 'x' UNION SELECT id, name || ':' || email, role FROM users --'
concat   -> 3 rows
  1 | Alice:alice@example.com | admin
  ...
prepared -> 0 rows

prepare()에서는 값이 존재하기 전에 SQL 문이 전달되고 해석됩니다. 값은 그 뒤에 데이터로 바인딩되어 문자열 통째로 비교되므로, 따옴표는 아무도 갖고 있지 않은 이메일 주소의 한 글자일 뿐입니다.

같은 실행에서 알게 된 것이 두 가지 더 있습니다.

• O'Brien@example.com 같은 평범한 이름도 이어 붙인 쿼리를 깨뜨려 PDOException: SQLSTATE[HY000]: General error: 1 near "Brien": syntax error가 났습니다. 작은따옴표가 들어간 입력에서 에러가 난다면 거의 확실히 인젝션 구멍이 있습니다. 한국어 이름에는 따옴표가 거의 없어서 이 증상을 못 보고 지나가기 쉬우니, 테스트 데이터에 작은따옴표가 든 값을 하나 꼭 넣어 두세요.
• x'; DROP TABLE users; --를 query()로 보내면 0행이 나오고 테이블은 남았습니다. PDO SQLite의 query()는 첫 번째 문장만 실행하기 때문입니다. 여기에 기대면 안 됩니다. 같은 형태의 페이로드를 exec()로 보내면 두 문장이 모두 실행됐습니다. UPDATE users SET name = '…'에 x' WHERE 1=0; DROP TABLE posts; --를 넣었더니 posts 테이블이 사라졌습니다. 그리고 MySQL의 PDO는 prepare를 에뮬레이션할 때 기본으로 여러 문장을 허용합니다.

흔한 실수 → 실제로 일어나는 일 → 해결

실수: "addslashes로 이스케이프했으니 안전하다." addslashes는 연결의 문자셋도, 데이터베이스의 따옴표 규칙도 모릅니다. SQLite는 백슬래시 이스케이프를 아예 쓰지 않습니다. O'Brien의 올바른 따옴표 처리는 'O''Brien'이고, $pdo->quote("O'Brien")도 그 값을 돌려줬습니다. 페이로드 x' OR 1=1 --를 addslashes에 통과시켜 실행한 결과입니다.

SELECT id FROM users WHERE email = 'x\' OR 1=1 --'
  rows: 3

SQLite에게 'x\'는 백슬래시가 들어간 완결된 문자열입니다. addslashes가 "이스케이프했다"는 따옴표가 리터럴을 닫았고 세 행이 모두 나왔습니다. 해결: 값에는 플레이스홀더를 씁니다. quote()도 있지만 구조를 바꿀 수 없는 코드를 위한 최후의 수단으로 생각하세요. 위에 링크한 감사 글이 바로 그런 상황입니다.

실수: 테이블 이름이나 열 이름을 바인딩. 플레이스홀더에는 값만 들어갑니다. ORDER BY ?는 상수를 바인딩할 뿐이라 그 열로 정렬되지 않습니다. ORDER BY ? DESC에 'id'를 넣으니 에러 없이 [1,2,3]이 나왔습니다. 해결: 허용 목록을 씁니다. $col = in_array($_GET['sort'], ['id', 'email'], true) ? $_GET['sort'] : 'id';

MySQL에서 에뮬레이션 prepare가 중요한 이유 (설명만, 실행 안 함)

이 강좌에서는 MySQL 서버를 쓰지 않았으므로, 이 절은 PDO 문서와 이전 운영 작업을 바탕으로 한 설명이지 여기서 실행한 결과가 아닙니다. SQLite에서는 setAttribute(PDO::ATTR_EMULATE_PREPARES, true)가 그냥 false를 돌려줬습니다. 드라이버가 항상 네이티브로 prepare합니다.

PDO_MySQL은 기본으로 prepare를 에뮬레이션합니다. PHP가 직접 이스케이프한 값을 SQL 문에 끼워 넣고, 완성된 문자열 하나를 서버로 보냅니다. 그 결과:

1. 문자셋. 이스케이프는 PHP가 실제 연결 문자셋을 알 때만 안전합니다. DSN에 charset=utf8mb4를 넣으세요. 나중에 SET NAMES를 실행하면 PDO에 알리지 않고 서버 쪽 문자셋만 바뀌는데, 이것이 예전 멀티바이트 이스케이프 공격의 전제 조건입니다. EUC-KR로 만든 오래된 시스템을 옮길 때 특히 조심해야 하는 부분입니다.
2. 타입. 에뮬레이션에서는 숫자가 보통 문자열로 오고, 네이티브 prepare에서는 INT 열이 PHP int로 옵니다. 즉 JSON이 바뀝니다. mysqli 버전 이야기는 prepared statement 로 바꾸면 JSON 숫자 타입이 바뀐다를 보세요.
3. 여러 문장이 에뮬레이션에서는 기본으로 허용됩니다(Pdo\Mysql::ATTR_MULTI_STATEMENTS 참고).
4. 에러가 늦게 납니다. 문법 오류가 prepare()가 아니라 execute()에서 나타납니다.

새 MySQL 코드라면 DSN에 charset=utf8mb4, ATTR_EMULATE_PREPARES => false, Pdo\Mysql::ATTR_MULTI_STATEMENTS => false. 전환한 뒤에는 JSON 출력을 다시 테스트하세요.

fetch 모드

기본 fetch 모드는 FETCH_BOTH이고, 모든 열을 두 번씩 돌려줍니다.

FETCH_BOTH: ["id"=>1, 0=>1, "email"=>"alice@example.com", 1=>"alice@example.com", "role"=>"admin", 2=>"admin"]

메모리는 두 배, json_encode에서는 키가 중복됩니다. 기본값을 FETCH_ASSOC로 두고 용도에 맞는 모드를 고르세요.

타입 있는 프로퍼티를 가진 클래스로 FETCH_CLASS하면 id가 int(1)로 들어왔습니다. PDO SQLite는 정수 열을 PHP int로 돌려줍니다. 행이 없으면 fetch()와 fetchColumn() 모두 false를 돌려주므로 === false로 확인하세요. 진짜 값 0이나 빈 문자열을 "행 없음"으로 착각하면 안 됩니다.

IN 목록과 LIKE 이스케이프

플레이스홀더 하나로 끝내려는 실수입니다.

$ids = [3, 1, 999];
$st = $pdo->prepare('SELECT id FROM users WHERE id IN (?)');
$st->execute([implode(',', $ids)]);       // -> []

에러 없이 0행이었습니다. 데이터베이스가 각 id를 문자열 하나 '3,1,999'와 비교했기 때문입니다(MySQL이라면 이 문자열을 3으로 캐스팅해 한 행을 돌려줍니다. 다른 종류의 오답입니다). 해결은 값마다 플레이스홀더를 하나씩 만드는 것입니다.

function inList(PDO $pdo, string $sqlWithIn, array $values): PDOStatement
{
    if ($values === []) {
        throw new InvalidArgumentException('empty IN list: return early (IN () is a syntax error in MySQL)');
    }
    $marks = implode(',', array_fill(0, count($values), '?'));
    $st = $pdo->prepare(str_replace('(:in)', "($marks)", $sqlWithIn));
    $st->execute(array_values($values));
    return $st;
}
$st = inList($pdo, 'SELECT id, email FROM users WHERE id IN (:in) ORDER BY id', $ids);  // -> [1, 3]

array_values()가 중요합니다. array_filter()를 거치면 키가 [0, 2]가 될 수 있는데, IN (?,?)에 execute([0 => 1, 2 => 3])을 넘기면 General error: 25 column index out of range로 실패했습니다. SQLite는 빈 IN ()을 받아들여 0행을 돌려줬지만 MySQL은 문법 오류이므로 []는 PHP에서 처리하세요.

prepared statement는 LIKE의 인젝션도 막지만, 사용자 입력 속 %와 _는 여전히 와일드카드입니다.

function likeEscape(string $s, string $esc = '\\'): string
{
    return str_replace([$esc, '%', '_'], [$esc . $esc, $esc . '%', $esc . '_'], $s);
}
$safe = $pdo->prepare("SELECT title FROM posts WHERE body LIKE ? ESCAPE '\\'");
$safe->execute(['%' . likeEscape($q) . '%']);

search 50%  naive=["100% sure","Points"]                            escaped=["100% sure"]
search _    naive=["100% sure","Hello","under_score","Points"]      escaped=["100% sure","under_score"]
search %    naive=["100% sure","Hello","under_score","Points"]      escaped=["100% sure"]

_ 검색이 모든 글에 걸렸습니다. 큰 테이블에서 사용자가 %를 입력하면 전체 행 스캔을 강제할 수 있어, 손쉬운 서비스 거부가 됩니다. 이스케이프 문자부터 먼저 이스케이프하세요(str_replace의 배열은 왼쪽부터 처리됩니다).

한글 검색에서는 따로 확인한 함정이 하나 있습니다. 눈으로는 똑같은 한글이라도 유니코드 정규화 방식이 다르면 다른 문자열입니다. 완성형 NFC와, 자모를 풀어 쓴 NFD를 하나씩 저장하고 =로 조회했습니다(intl 확장의 Normalizer 사용).

NFC len 6 NFD len 18          (strlen 바이트 수)
mb_strlen NFC 2 NFD 6         (글자 수도 다름)
WHERE body = NFC        -> [6]   NFC 로 저장한 행만
WHERE body = NFD        -> [7]   NFD 로 저장한 행만
WHERE body = NFD->NFC   -> [6]   검색어를 NFC 로 바꾸면 NFC 행과 일치

Mac에서 복사해 온 파일 이름이나 일부 앱의 입력에는 NFD가 섞여 들어올 수 있습니다. 그러면 검색에서 안 걸리고, UNIQUE 제약도 "다른 값"으로 보고 중복을 허용합니다. 저장하기 전과 검색하기 전에 Normalizer::normalize($s, Normalizer::FORM_C)로 맞추고, 그다음에 likeEscape()를 적용하세요. 인젝션 문제는 아니지만, prepared statement로 바꾼 직후 "검색이 안 된다"는 문의가 오면 먼저 의심할 곳입니다.

실제 PDOException 메시지

위 데이터베이스에 스크립트 하나로 낸 에러입니다.

[typo in table]    SQLSTATE[HY000]: General error: 1 no such table: user
[typo in column]   SQLSTATE[HY000]: General error: 1 no such column: mail
[UNIQUE violation] SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: users.email
[NOT NULL]         SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: users.name
[too many params]  SQLSTATE[HY000]: General error: 25 column index out of range
[missing :name]    SQLSTATE[HY000]: General error: 25 column index out of range
[bad file path]    SQLSTATE[HY000] [14] unable to open database file
[too few params]   no exception

볼 점:

• UNIQUE 위반의 getCode()는 문자열 '23000', 연결 에러에서는 int 14였습니다. "이미 가입된 이메일" 판정은 getCode() === 23000이 아니라 $e->errorInfo[0] === '23000'(또는 드라이버 코드 errorInfo[1] === 19)로 하세요.
• 파라미터가 모자라도 SQLite에서는 예외가 나지 않았습니다. 빠진 ?는 NULL로 바인딩됩니다. SELECT ?, ?에 값 하나만 넘기니 "1"과 NULL이 나와서 확인했습니다. 쿼리는 조용히 아무것도 찾지 못합니다. MySQL은 여기서 SQLSTATE[HY093]: Invalid parameter number를 내므로, SQLite로만 테스트한 코드는 운영에서 실패할 수 있습니다.
• 이름 있는 파라미터 오타(:id인데 uid)는 "파라미터 없음"이 아니라 알아보기 어려운 column index out of range였습니다. 이 메시지가 보이면 배열의 키와 플레이스홀더를 비교해 보세요.

트랜잭션: INSERT 1만 건 실측

트랜잭션 밖의 INSERT는 하나하나가 트랜잭션입니다. SQLite는 저널을 쓰고 디스크에 플러시한 뒤에 돌아옵니다. 트랜잭션 하나로 묶으면 그 일이 한 번만 일어납니다.

$st = $pdo->prepare('INSERT INTO posts (user_id, title) VALUES (?, ?)');
$t = hrtime(true);
if ($mode === 'tx') $pdo->beginTransaction();
for ($i = 0; $i < 10_000; $i++) {
    $st->execute([$i % 100, "title $i"]);
}
if ($mode === 'tx') $pdo->commit();
$ms = (hrtime(true) - $t) / 1e6;

실행마다 새 DB 파일, PHP 8.5.6 CLI, SQLite 3.53.4(기본값: journal_mode=delete, synchronous=2/FULL), Apple Silicon Mac(M1 Pro), 내장 SSD, 3회 실행입니다.

트랜잭션 하나로 약 530배 빨라졌습니다. WAL 모드는 묶을 수 없는 경우(요청마다 한 행을 쓰는 웹 앱)에 크게 도움이 되지만, 대량 작업에서 트랜잭션을 대신하지는 못합니다. 트랜잭션은 정확성, 즉 "전부 아니면 전무"를 위한 것이기도 합니다.

function transfer(PDO $pdo, callable $work): void
{
    $pdo->beginTransaction();
    try {
        $work($pdo);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

before: 4
rolled back: SQLSTATE[23000]: Integrity constraint violation: 19 NOT NULL constraint failed: posts.title
after: 4
PDOException: There is already an active transaction
PDOException: There is no active transaction

첫 번째 INSERT는 성공하고 두 번째가 실패했으며, 롤백으로 둘 다 사라졌습니다. 마지막 두 줄은 PDO에 중첩 트랜잭션이 없다는 뜻입니다. 두 번째 beginTransaction()은 예외를 던지고, 롤백 뒤의 commit()도 마찬가지입니다. 바깥 트랜잭션 안에서 돌 수도 있는 헬퍼라면 $pdo->inTransaction()을 확인하거나 SAVEPOINT를 쓰세요.

판단표

한계와 버전 메모

• PHP 8.5.6 CLI와 SQLite 3.53.4에서만 실행했습니다. ERRMODE_EXCEPTION이 기본값이 된 것은 PHP 8.0, 드라이버별 하위 클래스(Pdo\Sqlite, PDO::connect)는 8.4, PDO::MYSQL_ATTR_* Deprecated는 8.5부터입니다(Deprecated 메시지를 직접 확인했습니다).
• MySQL 동작(에뮬레이션 prepare, HY093, IN () 에러, IN에서의 문자열 캐스팅)은 문서와 이전 운영 작업을 바탕으로 한 설명이며 여기서 실행하지 않았습니다.
• 트랜잭션 시간은 macOS·내장 SSD 기준입니다. macOS의 SQLite는 기본으로 F_FULLFSYNC를 쓰지 않으므로, 여기서의 자동 커밋은 완전한 내구성을 보장할 때보다 빠르게 나왔습니다. 공유 호스팅의 네트워크 스토리지라면 자동 커밋은 더 느리다고 보세요.
• 한글 NFC/NFD 결과는 SQLite 기본 비교(BINARY) 기준입니다. MySQL의 콜레이션별 동작은 확인하지 않았습니다.

연습 문제

findPosts(PDO $pdo, array $userIds, string $q): array를 작성하세요. 작성자가 $userIds에 있고 본문에 $q가 글자 그대로 들어 있는(_는 밑줄 그 자체) 글의 id, title을 돌려줍니다. $userIds가 비어 있으면 데이터베이스를 건드리지 않고 []를 돌려줘야 합니다.

정답

function findPosts(PDO $pdo, array $userIds, string $q): array
{
    $userIds = array_values(array_map('intval', $userIds));
    if ($userIds === []) {
        return [];
    }
    $marks = implode(',', array_fill(0, count($userIds), '?'));
    $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $q) . '%';
    $st = $pdo->prepare(
        "SELECT id, title FROM posts WHERE user_id IN ($marks) AND body LIKE ? ESCAPE '\\' ORDER BY id"
    );
    $st->execute([...$userIds, $like]);
    return $st->fetchAll(PDO::FETCH_ASSOC);
}

echo json_encode(findPosts($pdo, [1, 2], '_')), PHP_EOL;
echo json_encode(findPosts($pdo, [], '_')), PHP_EOL;
echo json_encode(findPosts($pdo, [2, 3], '50')), PHP_EOL;

[{"id":1,"title":"100% sure"},{"id":3,"title":"under_score"}]
[]
[{"id":4,"title":"Points"}]

SQL 안에서 IN의 ?가 먼저 나오므로 배열에서도 IN 값을 먼저 둡니다. 이 순서를 뒤바꾸는 것은 에러 없이 넘어가는 대표적인 버그입니다.

이전: PHP 강좌 3 — 클래스, 인터페이스, enum · 다음: PHP 강좌 5 — XSS, CSRF, 세션