PDO 로 데이터베이스 다루기 - SQLite 로 연습

이 장에서 배우는 것

앞 장에서는 폼으로 들어온 값을 받아 안전하게 화면에 내보내는 방법을 다뤘다. 이번 장에서는 그 값을 데이터베이스(database)에 저장하고 다시 꺼내는 방법을 다룬다. 데이터베이스는 표 모양으로 정리된 자료를 보관하고 조건에 맞는 자료를 빠르게 찾아 주는 프로그램이다. PHP는 여러 종류의 데이터베이스를 같은 방식으로 다루도록 PDO(PHP Data Objects)라는 내장 확장을 제공한다. 연습에는 파일 하나, 혹은 메모리 안에서 동작하는 SQLite를 쓴다. 별도의 서버를 설치하지 않아도 되기 때문이다.

PDO로 SQLite 데이터베이스에 연결하고, 메모리 데이터베이스로 연습할 수 있다.
준비된 문장(prepared statement)으로 값을 안전하게 넘기고, SQL 인젝션이 왜 막히는지 설명할 수 있다.
트랜잭션으로 여러 변경을 하나로 묶고, 실패하면 되돌릴 수 있다.
fetch, fetchAll, fetchColumn 등으로 결과를 상황에 맞는 모양으로 가져올 수 있다.

문제 상황

빵집 사이트에 예약 주문 기능을 붙이려 한다. 지금까지는 상품 목록을 배열에 적어 두었고, 주문은 파일에 JSON으로 저장했다. 손님이 늘면 두 가지 문제가 생긴다. 하나는 "단팥빵 재고가 3개인데 5개를 주문한 사람"을 어떻게 걸러 내느냐이다. 다른 하나는 주문 기록을 남기는 것과 재고를 줄이는 것이 둘 중 하나만 반영되면 장부가 어긋난다는 점이다.

검색창도 문제다. 손님이 입력한 상품명을 SQL 문장에 그대로 이어 붙이면, 입력값이 데이터가 아니라 명령으로 읽힐 수 있다. SQL은 데이터베이스에 무엇을 찾고 무엇을 바꿀지 알려 주는 질의 언어다. 이 장은 이 세 가지, 곧 저장과 조회, 안전한 값 전달, 함께 성공하거나 함께 실패하는 묶음 처리를 한 프로그램으로 풀어 본다.

PDO 연결과 메모리 SQLite

PDO 연결은 new PDO(접속 문자열, 사용자, 비밀번호, 옵션)으로 만든다. 접속 문자열의 앞부분이 어떤 데이터베이스인지 정한다. SQLite는 사용자와 비밀번호가 없으므로 null을 넘긴다.

sqlite::memory: — 프로그램이 끝나면 사라지는 메모리 데이터베이스. 연습과 시험에 알맞다.
sqlite:/경로/bakery.db — 파일에 저장되는 데이터베이스. 웹 서비스에서는 이 형태를 쓴다.

메모리 데이터베이스는 연결마다 따로 만들어진다. 같은 프로그램에서 new PDO를 두 번 부르면 서로 다른 빈 데이터베이스가 둘 생긴다. 그래서 연결 하나를 만들어 함수에 인수로 넘겨 쓴다. 이 장의 코드가 그 방식이다.

옵션은 두 가지를 지정한다. PDO::ATTR_ERRMODE를 PDO::ERRMODE_EXCEPTION으로 두면 SQL 오류가 예외(PDOException)로 던져진다. PHP 8부터 이 값이 기본이지만, 코드에 적어 두면 읽는 사람이 동작을 바로 알 수 있다. PDO::ATTR_DEFAULT_FETCH_MODE를 PDO::FETCH_ASSOC으로 두면 결과 한 행이 열 이름을 키로 갖는 연관 배열로 나온다.

SQLite 확장이 켜져 있는지는 터미널에서 php -m을 실행해 목록에 pdo_sqlite가 있는지로 확인한다. macOS와 Linux의 일반적인 PHP 8.4 배포판에는 대부분 포함되어 있다. 이 장에서 쓰는 SQL의 기본 형태는 SQLite 공식 문서에, PDO 클래스의 전체 목록은 PHP 매뉴얼에 있다.

이 장의 예제는 웹 요청을 직접 받지 않으므로 php -S로 띄울 필요가 없다. 나중에 폼 처리 코드에 붙일 때는 접속 문자열만 파일 경로로 바꾸면 된다. 그 밖의 호출 방식은 같다.

준비된 문장과 SQL 인젝션

문자열을 이어 붙이면 생기는 일

상품명으로 검색하는 SQL을 이렇게 만들었다고 하자.

$sql = "SELECT name, price FROM products WHERE name = '" . $name . "'";

손님이 식빵을 입력하면 WHERE name = '식빵'이 되어 정상으로 동작한다. 그런데 ' OR '1'='1을 입력하면 완성된 문장은 WHERE name = '' OR '1'='1'이 된다. 입력에 들어 있던 작은따옴표가 문자열의 끝으로 읽히고, 그 뒤 내용이 SQL 문법으로 해석된 것이다. '1'='1'은 언제나 참이므로 모든 행이 나온다. 이렇게 입력값으로 SQL의 의미를 바꾸는 공격을 SQL 인젝션(SQL injection)이라 한다. 조회가 아니라 DELETE나 UPDATE 문장에서 일어나면 자료가 지워지거나 바뀐다.

틀과 값을 나누어 보낸다

준비된 문장은 SQL의 "틀"과 "값"을 따로 데이터베이스에 보낸다. 틀에는 값이 들어갈 자리에 자리표시자(placeholder)만 적는다. 데이터베이스는 틀을 먼저 해석해 두고, 값은 그 자리에 데이터로만 채운다. 값에 작은따옴표가 들어 있어도 SQL 문법으로 다시 읽히지 않는다.

문자열을 이어 붙이면 입력이 SQL의 일부가 되지만, 준비된 문장은 값을 데이터로만 다룬다.

자리표시자는 두 가지 모양이 있다.

자리표시자 두 가지 모양의 비교
모양	틀 예	값 넘기기	쓰임
이름 붙임	`WHERE name = :name`	`['name' => '식빵']`	자리가 여럿일 때 읽기 쉽다
물음표	`VALUES (?, ?, ?)`	`['식빵', 4500, 5]`	자리가 적을 때 간결하다

흐름은 prepare()로 문장을 준비하고, execute()로 값을 넘겨 실행하는 두 단계이다. 같은 문장을 값만 바꿔 여러 번 execute()할 수 있어서 반복 저장에도 쓰기 좋다. 이름 붙인 자리표시자에 값을 넘길 때는 키의 앞 콜론을 생략해도 된다. 정수임을 분명히 하고 싶으면 bindValue(':id', 3, PDO::PARAM_INT)처럼 타입을 지정할 수도 있다.

주의할 점이 하나 있다. 자리표시자는 "값" 자리에만 쓸 수 있다. 테이블 이름, 열 이름, 정렬 방향은 자리표시자로 넘길 수 없다. 이런 것은 아래 "자주 틀리는 것"에서 다루는 허용 목록으로 고른다.

트랜잭션과 결과 가져오기

트랜잭션

트랜잭션(transaction)은 여러 SQL 문장을 하나의 작업으로 묶는 기능이다. beginTransaction()으로 시작하고, 모두 잘 끝나면 commit()으로 확정한다. 중간에 실패하면 rollBack()으로 시작 전 상태로 되돌린다. 이 장의 주문 처리는 "주문 행 추가"와 "재고 감소"를 묶는다. 재고 열에는 CHECK (stock >= 0) 제약을 걸어 두었으므로 재고보다 많이 주문하면 재고 감소가 예외로 실패한다. 그러면 앞서 넣은 주문 행도 함께 사라져야 한다.

재고 감소가 실패하면 rollBack이 임시로 넣은 주문 행까지 되돌려 시작 전 상태가 된다.

코드의 뼈대는 beginTransaction() 다음에 try 블록에서 문장들을 실행하고 commit()하는 것이다. catch (PDOException)에서는 rollBack()을 부른다. 예외는 앞서 다룬 것처럼 try/catch로 잡는다. SQLite에서는 트랜잭션 없이 실행한 문장이 각각 곧바로 확정되므로, 묶지 않으면 두 번째 문장이 실패해도 첫 번째 결과가 남는다.

결과 가져오기

SELECT를 실행하면 결과 집합이 PDOStatement 객체에 담긴다. 필요한 모양에 따라 가져오는 방법이 다르다.

결과를 가져오는 방법과 돌려주는 값
방법	돌려주는 값	결과가 없을 때	알맞은 경우
`fetch()`	다음 한 행(배열)	`false`	기본 키로 한 건 조회
`fetchAll()`	모든 행의 배열	빈 배열	목록을 통째로 다룰 때
`fetchColumn()`	다음 행의 첫 열 값	`false`	`COUNT(*)` 같은 값 하나
`foreach` 순회	한 행씩 차례로	반복 없음	큰 결과를 한 행씩 처리

fetchAll(PDO::FETCH_KEY_PAIR)는 두 열을 골라 "첫 열 => 둘째 열" 배열로 만든다. fetchAll(PDO::FETCH_COLUMN)은 첫 열만 모은 목록을 준다. 방금 넣은 행의 번호는 lastInsertId()로 얻는데, 문자열로 돌아오므로 정수가 필요하면 (int)로 바꾼다. SQLite 드라이버는 정수 열을 PHP 정수로 돌려주기 때문에 price와 stock은 바로 계산에 쓸 수 있다.

완성 코드

파일 하나 main.php로 구성했다. 연결, 스키마(테이블 구조) 생성, 초기 자료 입력, 조회, 인젝션 비교, 주문 트랜잭션을 함수로 나누었다.

main.php

<?php
declare(strict_types=1);

function connect(): PDO
{
    return new PDO('sqlite::memory:', null, null, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);
}

function createSchema(PDO $pdo): void
{
    $pdo->exec('CREATE TABLE products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        price INTEGER NOT NULL,
        stock INTEGER NOT NULL CHECK (stock >= 0)
    )');
    $pdo->exec('CREATE TABLE orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        customer TEXT NOT NULL,
        product_id INTEGER NOT NULL REFERENCES products(id),
        quantity INTEGER NOT NULL
    )');
}

function seedProducts(PDO $pdo): void
{
    $stmt = $pdo->prepare(
        'INSERT INTO products (name, price, stock) VALUES (:name, :price, :stock)'
    );
    $items = [
        ['name' => '식빵', 'price' => 4500, 'stock' => 5],
        ['name' => '소금빵', 'price' => 3000, 'stock' => 10],
        ['name' => '단팥빵', 'price' => 2500, 'stock' => 3],
    ];
    foreach ($items as $item) {
        $stmt->execute($item);
    }
}

function addProduct(PDO $pdo, string $name, int $price, int $stock): int
{
    $stmt = $pdo->prepare('INSERT INTO products (name, price, stock) VALUES (?, ?, ?)');
    $stmt->execute([$name, $price, $stock]);
    return (int) $pdo->lastInsertId();
}

function findProduct(PDO $pdo, int $id): ?array
{
    $stmt = $pdo->prepare('SELECT name, price FROM products WHERE id = ?');
    $stmt->execute([$id]);
    $row = $stmt->fetch();
    return $row === false ? null : $row;
}

function findUnsafe(PDO $pdo, string $name): array
{
    $sql = "SELECT name, price FROM products WHERE name = '" . $name . "'";
    return $pdo->query($sql)->fetchAll();
}

function findSafe(PDO $pdo, string $name): array
{
    $stmt = $pdo->prepare('SELECT name, price FROM products WHERE name = :name');
    $stmt->execute(['name' => $name]);
    return $stmt->fetchAll();
}

function placeOrder(PDO $pdo, string $customer, int $productId, int $quantity): bool
{
    $pdo->beginTransaction();
    try {
        $insert = $pdo->prepare(
            'INSERT INTO orders (customer, product_id, quantity) VALUES (:c, :p, :q)'
        );
        $insert->execute(['c' => $customer, 'p' => $productId, 'q' => $quantity]);

        $update = $pdo->prepare('UPDATE products SET stock = stock - :q WHERE id = :id');
        $update->execute(['q' => $quantity, 'id' => $productId]);

        $pdo->commit();
        return true;
    } catch (PDOException) {
        $pdo->rollBack();
        return false;
    }
}

function run(): void
{
    $pdo = connect();
    createSchema($pdo);
    seedProducts($pdo);

    echo "== 상품 목록 ==\n";
    foreach ($pdo->query('SELECT id, name, price, stock FROM products ORDER BY id') as $row) {
        printf(
            "%d. %s %s원 (재고 %d)\n",
            $row['id'],
            $row['name'],
            number_format($row['price']),
            $row['stock']
        );
    }
    $newId = addProduct($pdo, '바게트', 3500, 4);
    echo '새 상품 번호: ', $newId, "\n";
    $missing = findProduct($pdo, 99);
    echo $missing === null ? '99번 상품 없음' : $missing['name'], "\n";

    echo "\n== SQL 인젝션 ==\n";
    $input = "' OR '1'='1";
    echo '입력: ', $input, "\n";
    echo '이어 붙인 SQL: ', count(findUnsafe($pdo, $input)), "건\n";
    echo '준비된 문장: ', count(findSafe($pdo, $input)), "건\n";

    echo "\n== 트랜잭션 ==\n";
    $requests = [
        ['김하늘', 1, 2],
        ['이도윤', 3, 5],
        ['박서준', 3, 3],
    ];
    foreach ($requests as [$customer, $productId, $quantity]) {
        $ok = placeOrder($pdo, $customer, $productId, $quantity);
        printf("%s님 주문 %d개: %s\n", $customer, $quantity, $ok ? '성공' : '실패(롤백)');
    }
    echo '주문 건수: ', $pdo->query('SELECT COUNT(*) FROM orders')->fetchColumn(), "\n";

    echo "\n== 주문 내역 ==\n";
    $sql = 'SELECT o.customer, p.name, o.quantity, o.quantity * p.price AS total
            FROM orders o JOIN products p ON p.id = o.product_id
            ORDER BY o.id';
    foreach ($pdo->query($sql) as $row) {
        printf(
            "%s %s x%d = %s원\n",
            $row['customer'],
            $row['name'],
            $row['quantity'],
            number_format($row['total'])
        );
    }
    $stock = $pdo->query('SELECT name, stock FROM products ORDER BY id')
        ->fetchAll(PDO::FETCH_KEY_PAIR);
    $parts = [];
    foreach ($stock as $name => $count) {
        $parts[] = "$name $count";
    }
    echo '남은 재고: ', implode(', ', $parts), "\n";
}

run();

줄별 해설

connect() — 메모리 SQLite에 연결한다. 옵션 세 개 중 ATTR_EMULATE_PREPARES => false는 PDO가 값을 문자열로 끼워 넣는 흉내를 내지 않고 데이터베이스의 준비 기능을 그대로 쓰게 한다.
createSchema() — exec()는 결과가 필요 없는 문장(테이블 생성 등)을 실행한다. INTEGER PRIMARY KEY AUTOINCREMENT는 행마다 번호를 자동으로 붙이는 열이다. CHECK (stock >= 0)는 재고가 음수가 되는 변경을 데이터베이스가 거부하게 한다.
seedProducts() — 문장을 한 번 준비하고 값만 바꿔 세 번 실행한다. 이름 붙인 자리표시자에 콜론 없는 키를 넘긴 점을 본다.
addProduct() — 물음표 자리표시자와 lastInsertId()를 쓴다. 반환 타입이 int이고 엄격 모드이므로 문자열을 (int)로 바꿔 돌려준다.
findProduct() — fetch()는 행이 없으면 false를 준다. 이를 null로 바꿔 호출한 쪽이 === null로 확인하게 했다.
findUnsafe() / findSafe() — 같은 검색을 이어 붙이기와 준비된 문장으로 각각 구현했다. 앞의 것은 비교를 위한 예이며 실제 코드에 쓰면 안 된다.
placeOrder() — 주문 행을 먼저 넣고 재고를 줄인다. 재고가 모자라면 UPDATE가 제약 위반으로 예외를 던지고, catch에서 rollBack()이 앞서 넣은 주문 행까지 취소한다. 예외 객체를 쓰지 않으므로 catch (PDOException)처럼 변수를 생략했다.
run()의 목록 출력 — query()가 돌려준 문장 객체를 foreach로 순회한다. number_format()은 천 단위 쉼표를 붙인다.
인젝션 비교 — 입력 ' OR '1'='1이 이어 붙인 쪽에서는 4건을, 준비된 문장에서는 그런 이름의 상품이 없으므로 0건을 만든다.
주문 반복 — foreach ($requests as [$customer, $productId, $quantity])는 배열을 세 변수로 나누어 받는다. 이도윤의 단팥빵 5개는 재고 3을 넘으므로 실패한다.
fetchColumn() — COUNT(*)의 값 하나를 바로 꺼낸다. 실패한 주문의 행이 되돌려졌으므로 2건이다.
주문 내역 — JOIN은 두 테이블의 행을 product_id와 id가 같은 것끼리 이어 붙인다. AS total은 계산 결과에 열 이름을 붙인다.
FETCH_KEY_PAIR — 상품명을 키로, 재고를 값으로 하는 배열을 만든다. 마지막 두 줄이 이를 한 줄로 이어 출력한다.

실행 결과

$ php main.php
== 상품 목록 ==
1. 식빵 4,500원 (재고 5)
2. 소금빵 3,000원 (재고 10)
3. 단팥빵 2,500원 (재고 3)
새 상품 번호: 4
99번 상품 없음

== SQL 인젝션 ==
입력: ' OR '1'='1
이어 붙인 SQL: 4건
준비된 문장: 0건

== 트랜잭션 ==
김하늘님 주문 2개: 성공
이도윤님 주문 5개: 실패(롤백)
박서준님 주문 3개: 성공
주문 건수: 2

== 주문 내역 ==
김하늘 식빵 x2 = 9,000원
박서준 단팥빵 x3 = 7,500원
남은 재고: 식빵 3, 소금빵 10, 단팥빵 0, 바게트 4

실무에서 자주 틀리는 것

1. 값을 문자열에 이어 붙인다

틀린 코드:

$stmt = $pdo->query("SELECT * FROM products WHERE name LIKE '%" . $word . "%'");

고친 코드. %는 값의 일부이므로 틀이 아니라 넘기는 값에 붙인다.

$stmt = $pdo->prepare('SELECT * FROM products WHERE name LIKE :kw');
$stmt->execute(['kw' => '%' . $word . '%']);

2. 열 이름이나 정렬 기준을 자리표시자로 넘긴다

틀린 코드. 자리표시자는 값 자리에만 쓸 수 있어서, 아래는 열이 아니라 문자열 'price'로 정렬하려 하므로 의도대로 정렬되지 않는다.

$stmt = $pdo->prepare('SELECT * FROM products ORDER BY :col');
$stmt->execute(['col' => $sort]);

고친 코드. 허용하는 이름을 목록으로 정해 두고, 목록에 없으면 기본값을 쓴다.

$column = in_array($sort, ['name', 'price'], true) ? $sort : 'id';
$stmt = $pdo->query("SELECT * FROM products ORDER BY $column");

3. fetch 의 false 를 확인하지 않는다

틀린 코드. 없는 번호를 조회하면 false에 배열 접근을 하게 되어 경고가 나고 결과는 null이 된다.

$row = $stmt->fetch();
echo $row['name'];

고친 코드:

$row = $stmt->fetch();
echo $row === false ? '없는 상품' : $row['name'];

4. 연관된 변경을 트랜잭션으로 묶지 않는다

틀린 코드. 두 번째 문장이 실패해도 첫 번째 주문 행이 그대로 남아 장부가 어긋난다.

$insert->execute([$customer, $productId, $quantity]);
$update->execute([$quantity, $productId]);

고친 코드. 시작, 확정, 되돌리기를 모두 갖춘다.

$pdo->beginTransaction();
try {
    $insert->execute([$customer, $productId, $quantity]);
    $update->execute([$quantity, $productId]);
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    throw $e;
}

한눈에 보기

이 장에서 쓴 PDO 기능과 주의점
주제	사용법	핵심	주의
연결	`new PDO('sqlite::memory:', null, null, $opts)`	오류 모드를 예외로 둔다	메모리 DB는 연결마다 따로다
실행	`exec()`, `query()`	값 없는 고정 문장에 쓴다	입력값을 이어 붙이지 않는다
준비된 문장	`prepare()` 후 `execute()`	틀과 값이 분리된다	식별자는 허용 목록으로 고른다
트랜잭션	`beginTransaction()`, `commit()`, `rollBack()`	여러 변경을 하나로 묶는다	실패 경로에서 반드시 되돌린다
결과	`fetch()`, `fetchAll()`, `fetchColumn()`	필요한 모양으로 가져온다	`fetch()`는 없으면 `false`

연습 문제

이름에 "빵"이 들어간 상품의 이름만 id 순서로 배열에 담는 함수 searchByWord(PDO $pdo, string $word): array를 준비된 문장으로 작성하라.
가격이 3,000원 이하인 상품의 개수를 정수로 돌려주는 함수를 fetchColumn()으로 작성하라.

아래 코드는 두 번째 exec()가 실패하면 어떤 상태가 되는지, 어떻게 고치는지 설명하라.

$pdo->beginTransaction();
$pdo->exec("UPDATE products SET stock = stock - 1 WHERE id = 1");
$pdo->exec("UPDATE products SET stock = stock - 999 WHERE id = 1");
$pdo->commit();

사용자가 $sort 값으로 name 또는 price를 골라 상품을 정렬해 보여 주려 한다. 다른 값이 들어와도 안전하게 동작하는 정렬 열 선택 코드를 작성하라.

정답과 해설

function searchByWord(PDO $pdo, string $word): array
{
    $stmt = $pdo->prepare('SELECT name FROM products WHERE name LIKE :kw ORDER BY id');
    $stmt->execute(['kw' => '%' . $word . '%']);
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

와일드카드 %는 값에 붙인다. FETCH_COLUMN은 첫 열만 모아 이름의 목록을 만든다.

function countCheap(PDO $pdo): int
{
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM products WHERE price <= :max');
    $stmt->execute(['max' => 3000]);
    return (int) $stmt->fetchColumn();
}

fetchColumn()은 false가 될 수도 있는 타입이므로 (int)로 바꿔 반환 타입을 맞춘다. COUNT(*)는 항상 한 행을 주므로 여기서는 값이 있다.

둘째 exec()는 CHECK 제약 때문에 예외를 던진다. 예외를 잡지 않았으므로 commit()에 도달하지 못하고 트랜잭션이 열린 채 남는다. 예외가 프로그램 밖으로 나가 종료되면 확정되지 않은 변경은 버려지지만, 같은 연결로 작업을 이어 가는 코드라면 첫 변경이 열린 트랜잭션 안에 남아 뒤의 작업에 섞인다. 고치려면 본문의 "4번" 패턴처럼 try/catch로 감싸고 catch에서 rollBack()을 호출한다.
```
$column = in_array($sort, ['name', 'price'], true) ? $sort : 'id';
$rows = $pdo->query("SELECT name, price FROM products ORDER BY $column")->fetchAll();
```
열 이름은 자리표시자로 넘길 수 없다. 허용 목록에 있는 값일 때만 SQL에 넣고, 나머지는 고정된 기본값을 쓰면 사용자 입력이 SQL 문법에 영향을 주지 못한다. in_array의 세 번째 인수 true는 타입까지 엄격하게 비교하게 한다.