PHP PDO: MySQL 연결과 prepared statement

PDO(PHP Data Objects)는 데이터베이스와 대화하는 내장 방법입니다. new PDO($dsn, $user, $password)로 연결한 다음, 값이 들어가는 쿼리는 모두 prepare()와 execute()로 실행해 값이 SQL과 따로 전송되게 하세요.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'shop_user', 'secret', [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$stmt = $pdo->prepare('SELECT id, name FROM users WHERE email = ?');
$stmt->execute([$email]);
$user = $stmt->fetch();      // an array, or false if no row matched

이 페이지의 예제는 데이터베이스 서버가 필요하므로 일반 코드로 보여 줍니다. 아래쪽의 SQL 인젝션 예제는 실행됩니다.

PDO로 MySQL에 연결하기

첫 번째 인수는 DSN입니다. 드라이버 이름 다음에 연결 정보가 옵니다. 연결은 한곳에 두고 필요한 곳에서 포함하세요.

<?php
// db.php
$dsn = 'mysql:host=127.0.0.1;port=3306;dbname=shop;charset=utf8mb4';

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,  // the default since PHP 8.0
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,        // rows as ['id' => 1, ...]
    PDO::ATTR_EMULATE_PREPARES   => false,                   // real prepared statements, real int types
];

try {
    $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), $options);
} catch (PDOException $e) {
    error_log($e->getMessage());
    http_response_code(500);
    exit('Database unavailable.');
}

return $pdo;

• charset=utf8mb4는 DSN에 들어가야 합니다. 없으면 악센트와 이모지가 ?나 깨진 바이트로 도착할 수 있습니다.
• $e->getMessage()를 방문자에게 절대 출력하지 마세요. 호스트와 사용자 이름이 담길 수 있습니다. 기록하세요.
• 비밀번호는 코드 밖, 환경 변수나 웹 루트 밖의 설정 파일에 두세요.

다른 데이터베이스는 DSN만 바뀝니다. PostgreSQL은 pgsql:host=localhost;dbname=shop, SQLite 파일은 sqlite:/var/data/app.db입니다. 각각 PDO 드라이버(pdo_mysql, pdo_pgsql, pdo_sqlite)가 설치되어 있어야 하며, php -m이 가진 것을 나열합니다.

?와 :name을 쓰는 prepared statement

자리 표시자는 값이 들어갈 곳을 표시합니다. 리스트와 함께 위치 기반 ? 자리 표시자를 쓰거나, 연관 배열과 함께 이름 있는 자리 표시자를 쓰세요.

<?php
// Positional
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = ? AND price < ?');
$stmt->execute(['books', 20]);

// Named: easier to read when there are many values
$stmt = $pdo->prepare('SELECT * FROM products WHERE category = :cat AND price < :max');
$stmt->execute(['cat' => 'books', 'max' => 20]);

// bindValue when you want to set the type explicitly
$stmt = $pdo->prepare('SELECT * FROM products ORDER BY id LIMIT :limit');
$stmt->bindValue('limit', 10, PDO::PARAM_INT);
$stmt->execute();

자리 표시자를 따옴표로 감싸지 마세요. WHERE name = '?'는 글자 ?와 비교합니다. prepared statement는 다른 값으로 여러 번 실행할 수도 있으므로, 반복문 안이 아니라 반복문 전에 한 번 준비하세요.

SQL에서 문자열 연결이 위험한 이유

이 페이지에서 실행되는 유일한 블록입니다. 같은 입력에 대해 쿼리를 잘못된 방법과 올바른 방법으로 만듭니다. $email을 바꾸고 다시 실행해 보세요.

<?php
$email = "nobody@example.com' OR '1'='1";

// Wrong: the input becomes part of the SQL
$sql = "SELECT * FROM users WHERE email = '" . $email . "'";
echo "Concatenated:\n$sql\n\n";

// Right: the SQL stays fixed, the value travels separately
$sql = 'SELECT * FROM users WHERE email = ?';
$params = [$email];
echo "Prepared:\n$sql\n";
echo 'Values: ', json_encode($params), "\n";

연결한 쿼리에서는 입력 속 따옴표가 문자열을 닫고, OR '1'='1'이 모든 행에 대해 조건을 참으로 만듭니다. 쿼리가 모든 사용자를 반환합니다. 로그인 폼이라면 비밀번호를 모르고 로그인하는 것이고, 드라이버가 한 번의 호출에 여러 문장을 받는다면 '; DROP TABLE users; -- 같은 입력은 공격자가 고른 두 번째 문장을 실행합니다. prepared 버전에서는 데이터베이스가 SQL과 값을 따로 받으므로 따옴표는 아무와도 일치하지 않는 이메일 안의 문자일 뿐입니다.

addslashes() 같은 이스케이프 함수는 해결책이 아닙니다. 연결의 문자 집합에 따라 달라지는 경우를 놓칩니다. 숫자와 "내 코드에서 온" 값을 포함해 모든 값에 자리 표시자를 쓰세요.

행 가져오기: fetch, fetchAll, fetchColumn

<?php
// One row
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
$stmt->execute([42]);
$user = $stmt->fetch();                 // ['id' => 42, 'name' => 'Ada', ...] or false
if ($user === false) {
    http_response_code(404);
}

// All rows
$rows = $pdo->query('SELECT id, name FROM users ORDER BY name')->fetchAll();

// Loop row by row without building an array of all rows
$stmt = $pdo->query('SELECT id, name FROM users');
foreach ($stmt as $row) {
    echo $row['name'], "\n";
}

// One value
$count = $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();

// Two columns as key => value
$names = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);  // [42 => 'Ada', ...]

query()는 SQL에 바깥에서 온 값이 없을 때는 괜찮습니다. 변수가 관련되는 순간 prepare()로 바꾸세요.

삽입, 수정, 삭제

<?php
$stmt = $pdo->prepare('INSERT INTO users (email, name, password_hash) VALUES (?, ?, ?)');
$stmt->execute([$email, $name, password_hash($password, PASSWORD_DEFAULT)]);
$id = (int) $pdo->lastInsertId();

$stmt = $pdo->prepare('UPDATE users SET name = ? WHERE id = ?');
$stmt->execute([$newName, $id]);
echo $stmt->rowCount();                 // rows changed

$stmt = $pdo->prepare('DELETE FROM sessions WHERE expires_at < NOW()');
$stmt->execute();

lastInsertId()는 문자열을 반환하므로 형 변환하세요. MySQL에서 UPDATE 뒤의 rowCount()는 실제로 바뀐 행을 세므로, 이름을 이미 가진 값으로 설정하면 0이 됩니다. 비밀번호는 평문이 아니라 password_hash로 저장하세요.

트랜잭션

트랜잭션은 여러 문장이 함께 성공하거나 함께 실패하게 합니다. 무엇이든 예외를 던지면 롤백하고 아무것도 저장되지 않습니다.

<?php
try {
    $pdo->beginTransaction();

    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([100, $from]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([100, $to]);
    $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)')->execute([$from, $to, 100]);

    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

이는 에러가 예외를 던진다는 것(PHP 8.0부터 기본값)에 의존하므로 실패한 문장은 catch로 넘어갑니다. MySQL에서는 테이블이 InnoDB를 써야 하며, MyISAM은 트랜잭션을 무시합니다. Throwable과 다시 던지기는 try/catch 페이지에서 다룹니다.

배열과 함께 쓰는 WHERE IN

? 하나는 값 하나만 담으므로 배열과 함께 쓴 IN (?)는 동작하지 않습니다. 값마다 자리 표시자를 하나씩 만드세요.

<?php
$ids = [3, 8, 15];

if ($ids) {
    $placeholders = implode(',', array_fill(0, count($ids), '?'));   // "?,?,?"
    $stmt = $pdo->prepare("SELECT * FROM products WHERE id IN ($placeholders)");
    $stmt->execute($ids);
    $products = $stmt->fetchAll();
} else {
    $products = [];                     // IN () would be a syntax error
}

SQL에 끼워 넣는 문자열은 물음표와 쉼표뿐이므로 쿼리는 여전히 안전합니다.

자리 표시자의 흔한 실수

• 테이블과 컬럼 이름. ORDER BY ?는 컬럼이 아니라 상수 문자열로 정렬합니다. 식별자는 허용 목록에서 고르세요: $sort = in_array($_GET['sort'] ?? '', ['name', 'price'], true) ? $_GET['sort'] : 'name'; 그다음 $sort를 SQL에 넣으세요.
• 에뮬레이트된 prepare와 LIMIT ?. MySQL의 기본 ATTR_EMULATE_PREPARES에서 execute(['10'])는 LIMIT '10'을 보내며, 구문 에러입니다. PDO::PARAM_INT로 바인딩하거나 에뮬레이션을 끄세요.
• 같은 이름 있는 자리 표시자를 두 번. WHERE first = :q OR last = :q는 에뮬레이트된 prepare에서만 동작합니다. 진짜 prepare에서는 SQLSTATE[HY093]: Invalid parameter number가 나므로 :q1과 :q2를 쓰세요.
• fetch()가 false를 반환한다는 것을 잊기. false에 대한 $user['name']은 경고와 null입니다. 먼저 결과를 확인하세요.