1. 今回の学習ゴールと練習課題（仕様）

Webアプリケーションの心臓部といえば「データベース（DB）」とのやり取りです。会員検索、商品一覧の絞り込み、新規データの登録など、あらゆる画面でSQLを発行します。

しかし、ユーザーが入力した値を安易にSQL文字列へ連結してしまうと、悪意ある文字列を注入されてDBの中身を丸ごと盗まれたり破壊されたりする「SQLインジェクション脆弱性」が発生します。PHPではこれを防ぐために標準で PDO（PHP Data Objects） と プリペアドステートメント が用意されています。

今回のドリルでは、ローカル環境にMySQLサーバーがなくてもPHPさえあればターミナルで即座に動作確認できるよう、「インメモリSQLite（sqlite::memory:）を利用した安全なユーザー検索リポジトリ」を実装します。

満たすべき要件仕様

  1. 堅牢なPDO初期化:
    • データベース接続時に以下の安全な属性（オプション）を必ず設定すること：
      • エラーモードを例外送出に設定（PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION）
      • デフォルトのフェッチモードを連想配列に設定（PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC）
      • SQLインジェクション防止のためプリペアドステートメントのエミュレーションを無効化（PDO::ATTR_EMULATE_PREPARES => false）
    • テスト用の users テーブル（id, username, email, role, is_active）を自動作成し、初期テストデータを投入すること。
  2. 安全な新規ユーザー登録 (createUser):
    • ユーザー名、メールアドレス、役割（role）を受け取り、プリペアドステートメント（名前付きプレースホルダ）を用いて安全に INSERT すること。
    • 登録完了後、採番された lastInsertId を整数の int 型で返却すること。
  3. 安全なID指定の単一レコード取得 (findUserById):
    • ユーザーIDを受け取り、対象ユーザー1件を連想配列（見つからない場合は null）で返却すること。
    • fetchAll() ではなく、単一取得に適した fetch() を使用すること。
  4. 安全な複合あいまい検索 (searchUsers):
    • 「ユーザー名の部分一致キーワード（あいまい検索）」と「権限ロール（完全一致）」を受け取る。
    • SQLインジェクション攻撃用の文字列（例: ' OR '1'='1）が入力されても、構文エラーや意図しない全件取得が発生せず、安全に無害化されて検索されること。
    • 部分一致（LIKE）検索を行う際、SQL文の中に % を直書きせず、プレースホルダへバインドする値の前後で % を付与すること。

期待される実行結果

スクリプトを実行した際、ターミナルに以下のようなテスト結果が明瞭に出力されることを目指します。

=== 1. テストテーブル初期化と初期データ投入 ===
初期データ投入完了: 3件のユーザーを登録しました。

=== 2. 新規ユーザー登録テスト（createUser） ===
新規ユーザー作成成功! 発行されたUser ID: 4

=== 3. ID指定の単一ユーザー取得テスト（findUserById） ===
[ID: 1 の検索結果]
  Username: alice_dev
  Email: alice@example.com
  Role: admin
[存在しない ID: 999 の検索結果]
  結果: ユーザーが見つかりませんでした (null)

=== 4. 安全なあいまい検索テスト（searchUsers） ===
[検索条件: keyword='bob', role='member']
  ヒット件数: 1件
  - bob_designer (bob@example.com) [member]

=== 5. SQLインジェクション攻撃文字列の防御テスト ===
[攻撃入力: keyword="' OR '1'='1", role='admin']
  安全に実行されたSQLプレースホルダ検索
  ヒット件数: 0件 (攻撃コードが無力化され、該当なしとして安全に処理されました)

まずはここでスクロールを一度止め、自分ならどうPDOオプションを設定し、どうプレースホルダを記述するか頭の中でイメージしてみてください！

2. 初心者がハマるNGコード（Before）と解説

PHP初心者やWeb制作あがりの独学者が、DB操作を書いたときに最もやりがちな致命的NGコードを見てみましょう。

<?php
// ❌ 初心者がやりがちな致命的NGコード例

function searchUsersDangerous(PDO $pdo, string $keyword, string $role): array {
    // NG①: 変数をダブルクォーテーションや文字列連結（.）でSQLに直埋めしている！
    // 攻撃者が $keyword に "' OR '1'='1" を渡すと全データが漏洩する
    $sql = "SELECT * FROM users WHERE username LIKE '%" . $keyword . "%' AND role = '" . $role . "'";
    
    // NG②: prepare() を通さずに query() でそのまま即時発行している
    $stmt = $pdo->query($sql);
    
    return $stmt->fetchAll();
}

function findUserWrong(PDO $pdo, $id): ?array {
    // NG③: 型チェックをせず、SQLに生変数を連結
    $sql = "SELECT * FROM users WHERE id = {$id}";
    $stmt = $pdo->query($sql);
    
    // NG④: 1件しか返らないのに fetchAll() を使い、先頭を取り出そうとしてキー未定義エラー
    $rows = $stmt->fetchAll();
    return $rows[0] ?? null;
}
?>

なぜこの書き方は現場で絶対にリジェクトされるのか？

3. コピペで即動く模範解答コード（After）

それでは、PHP 8.3/8.4基準のモダンで安全な模範解答コードを作成しましょう。外部のMySQLを起動する必要はありません。このコードを pdo_drill.php という名前で保存し、ターミナルで php pdo_drill.php と実行するだけでその場で全動作が検証できます。

<?php

declare(strict_types=1);

/**
 * 安全なPDO接続とユーザーデータ操作を担うリポジトリクラス
 */
final class UserRepository
{
    private PDO $pdo;

    /**
     * コンストラクタ: 安全なオプション設定でPDOを初期化
     */
    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * テスト用のテーブル作成と初期データ投入
     */
    public function initializeTable(): void
    {
        $createTableSql = <<pdo->exec($createTableSql);

        // 初期データの投入（プレースホルダによる安全なバッチ登録）
        $sampleUsers = [
            ['username' => 'alice_dev', 'email' => 'alice@example.com', 'role' => 'admin'],
            ['username' => 'bob_designer', 'email' => 'bob@example.com', 'role' => 'member'],
            ['username' => 'carol_tester', 'email' => 'carol@example.com', 'role' => 'member'],
        ];

        $insertSql = <<pdo->prepare($insertSql);

        foreach ($sampleUsers as $user) {
            $stmt->execute([
                ':username'   => $user['username'],
                ':email'      => $user['email'],
                ':role'       => $user['role'],
                ':created_at' => date('Y-m-d H:i:s'),
            ]);
        }
    }

    /**
     * 新規ユーザーを登録する
     *
     * @param string $username
     * @param string $email
     * @param string $role
     * @return int 発行された新規ユーザーID
     */
    public function createUser(string $username, string $email, string $role): int
    {
        $sql = <<pdo->prepare($sql);

        // 名前付きプレースホルダに値をバインドして実行
        $stmt->execute([
            ':username'   => $username,
            ':email'      => $email,
            ':role'       => $role,
            ':created_at' => date('Y-m-d H:i:s'),
        ]);

        return (int) $this->pdo->lastInsertId();
    }

    /**
     * ユーザーIDによる単一レコード取得
     *
     * @param int $id
     * @return array|null 見つからない場合は null
     */
    public function findUserById(int $id): ?array
    {
        $sql = 'SELECT id, username, email, role, is_active, created_at FROM users WHERE id = :id LIMIT 1';

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute([':id' => $id]);

        // 単一レコード取得には fetch() を使用
        $record = $stmt->fetch();

        // 該当なしの場合は false が返るため null に変換
        return $record === false ? null : $record;
    }

    /**
     * 部分一致キーワードと役割による安全な複合検索
     *
     * @param string $keyword ユーザー名の部分一致キーワード
     * @param string $role 権限ロール
     * @return list> 検索結果リスト
     */
    public function searchUsers(string $keyword, string $role): array
    {
        $sql = <<pdo->prepare($sql);

        // 【最重要ポイント】
        // LIKEのワイルドカード '%' はSQL文字列ではなく、バインドする値に含める！
        $stmt->execute([
            ':keyword' => '%' . $keyword . '%',
            ':role'    => $role,
        ]);

        /** @var list> $results */
        $results = $stmt->fetchAll();

        return $results;
    }
}

// ==========================================
// 実行・検証コード（PHP CLIで即動く）
// ==========================================

// 1. PDO接続（インメモリSQLiteを使用: 外部DBサーバー不要）
$dsn = 'sqlite::memory:';
$options = [
    // エラー発生時に必ず例外（PDOException）をスローさせる
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    // 取得結果をカラム名をキーとした連想配列にする
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    // 静的プレースホルダを強制し、DBエンジン側で確実に安全にパースさせる
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    $pdo = new PDO($dsn, null, null, $options);
} catch (PDOException $e) {
    fwrite(STDERR, 'DB接続失敗: ' . $e->getMessage() . PHP_EOL);
    exit(1);
}

$repository = new UserRepository($pdo);

// 2. テーブル初期化
echo "=== 1. テストテーブル初期化と初期データ投入 ===" . PHP_EOL;
$repository->initializeTable();
echo "初期データ投入完了: 3件のユーザーを登録しました。" . PHP_EOL . PHP_EOL;

// 3. 新規ユーザー作成テスト
echo "=== 2. 新規ユーザー登録テスト（createUser） ===" . PHP_EOL;
$newId = $repository->createUser('dave_lead', 'dave@example.com', 'admin');
echo "新規ユーザー作成成功! 発行されたUser ID: {$newId}" . PHP_EOL . PHP_EOL;

// 4. ID指定検索テスト
echo "=== 3. ID指定の単一ユーザー取得テスト（findUserById） ===" . PHP_EOL;
$foundUser = $repository->findUserById(1);
if ($foundUser !== null) {
    echo "[ID: 1 の検索結果]" . PHP_EOL;
    echo "  Username: {$foundUser['username']}" . PHP_EOL;
    echo "  Email: {$foundUser['email']}" . PHP_EOL;
    echo "  Role: {$foundUser['role']}" . PHP_EOL;
}

$notFoundUser = $repository->findUserById(999);
echo "[存在しない ID: 999 の検索結果]" . PHP_EOL;
echo "  結果: " . ($notFoundUser === null ? "ユーザーが見つかりませんでした (null)" : "取得成功") . PHP_EOL . PHP_EOL;

// 5. 安全なあいまい検索テスト
echo "=== 4. 安全なあいまい検索テスト（searchUsers） ===" . PHP_EOL;
$searchResults = $repository->searchUsers('bob', 'member');
echo "[検索条件: keyword='bob', role='member']" . PHP_EOL;
echo "  ヒット件数: " . count($searchResults) . "件" . PHP_EOL;
foreach ($searchResults as $user) {
    echo "  - {$user['username']} ({$user['email']}) [{$user['role']}]" . PHP_EOL;
}
echo PHP_EOL;

// 6. SQLインジェクション攻撃文字列の防御テスト
echo "=== 5. SQLインジェクション攻撃文字列の防御テスト ===" . PHP_EOL;
$maliciousKeyword = "' OR '1'='1"; // 典型的なSQLインジェクション攻撃ペイロード
$attackRole = 'admin';

echo "[攻撃入力: keyword=\"{$maliciousKeyword}\", role='{$attackRole}']" . PHP_EOL;
$attackResults = $repository->searchUsers($maliciousKeyword, $attackRole);

echo "  安全に実行されたSQLプレースホルダ検索" . PHP_EOL;
echo "  ヒット件数: " . count($attackResults) . "件 (攻撃コードが無力化され、該当なしとして安全に処理されました)" . PHP_EOL;
echo "==================================================" . PHP_EOL;

4. 現場のプロ（kixking）による徹底コード解説

この模範コードには、現場で「コードレビューを一発でパスする」ための重要なノウハウと安全設計が詰め込まれています。特に重要な4つのポイントを徹底的に解説します。

① なぜプリペアドステートメントはSQLインジェクションを100%防げるのか？

「なぜ :keyword というプレースホルダを使うだけで、悪意ある文字列を無力化できるのか？」――その仕組みを正しく理解しておきましょう。

従来の文字列連結方式では、SQLの「構文構造（命令）」と「データ（値）」が混ざり合った状態でデータベースエンジンに送られていました。そのため、値の中にクォート（'）や OR が含まれていると、SQLパーサーがそれを「SQL構文の一部」だと勘違いして実行してしまったのです。

一方、プリペアドステートメントは処理を以下の2段階に完全に分離します：

  1. 準備フェーズ（prepare）:
    データベースエンジンは、プレースホルダ（穴あき部分）を含んだSQL文を先に受け取り、「SQLの文法・実行計画」を確定させてロックします。
  2. 実行フェーズ（execute）:
    あとから渡された値（パラメータ）を、確定済みの穴に「単なる文字データのかたまり」として流し込みます。

実行計画はすでに確定しているため、あとからどんなに ' OR '1'='1; DROP TABLE users; という凶悪な文字列が渡されても、DBは「そういう奇妙な名前のユーザー名という文字データなんだな」としか認識しません。構文として解釈される余地が1ミリもないため、原理的にインジェクションが起き得ないのです。

② PDO::ATTR_EMULATE_PREPARES => false を設定すべき致命的な理由

実務でPDOを扱う際、絶対に忘れてはならないのが ATTR_EMULATE_PREPARES の設定です。

デフォルト（true）のままだと、PHPはデータベースエンジンにプリペアドステートメントを送らず、「PHP側で適当に文字列エスケープしてSQL文字列を合成する（エミュレーション）」という疑似動作を行います。

しかし、文字コード（特に Shift_JIS や GBK などのマルチバイト文字）の設定不備があった場合、エミュレーションによるエスケープ処理をすり抜けてSQLインジェクションが成立してしまう既知の脆弱性が存在します。
PDO::ATTR_EMULATE_PREPARES => false を明示することで、DBサーバー本来の「本物のプリペアドステートメント（サーバーサイド・プリペア）」が強制され、安全性が飛躍的に向上します。

③ LIKE 検索時の %（ワイルドカード）の正しい置き場所

初学者が最もよく書く間違いがこちらです：

// ❌ ダメな例: SQL文の中に %:keyword% と書いてしまう
$sql = "SELECT * FROM users WHERE username LIKE '%:keyword%'";

// ❌ これもダメ: プレースホルダと % をSQL側で連結しようとする
$sql = "SELECT * FROM users WHERE username LIKE '%' || :keyword || '%'";

プレースホルダは「値そのもの」を置き換える仕組みです。SQL文の中で '%:keyword%' と書いてしまうと、DBは「:keyword という文字そのものを探している」と判断してしまい、変数が一切展開されなくなります。

正解は模範コードにある通り、「SQL文にはプレースホルダ単体（LIKE :keyword）を記述し、PHP側で '%' . $keyword . '%' と結合した文字列を値として渡す」ことです。このルールを身体に染み込ませてください。

④ fetch() の戻り値 false と型の整合性

PDOの fetch() メソッドは、該当レコードが存在しないときに null ではなく false を返します。

しかし、現代の型安全なPHP（PHP 8.2+）において、「ユーザーの連想配列または false を返す」という関数シグネチャ（array|false）を作ると、呼び出し元で false チェックを強制され、コードが非常に読みづらくなります。

模範解答のように三項演算子で $record === false ? null : $record と変換し、関数の戻り値型を ?array（Nullable Array）として統一するのが、現代PHPのクリーンな作法です。

5. もう一歩進める！発展チャレンジ課題

模範コードが手元で動いたら、ぜひ以下の実務レベルの発展課題にチャレンジしてみてください。現場で即戦力となるSQL＆PDOスキルが身につきます。

チャレンジ1：トランザクション（Transaction）による原子性の担保

「ユーザー登録と同時に、ウェルカムポイント100ptをポイントテーブルに付与する」という複合処理を考えてみましょう。
途中でエラーが起きた場合にユーザーだけ登録されてポイントが付かない事態を防ぐため、$pdo->beginTransaction()、$pdo->commit()、そして catch (PDOException $e) ブロック内での $pdo->rollBack() を使ったトランザクション処理を実装してみてください。

チャレンジ2：LIMIT / OFFSET のページネーションと型指定バインド

大量のデータをページ送り表示する場合、SQLに LIMIT :limit OFFSET :offset を追加します。
しかし、ATTR_EMULATE_PREPARES => false の環境下では、execute([':limit' => 10]) のように配列で渡すとPHPが自動的に文字列型として扱ってしまい、DBによっては「LIMIT句に文字列は使えない」とシンタックスエラーを吐くことがあります。
これを解決するために、$stmt->bindValue(':limit', 10, PDO::PARAM_INT) のように明示的に整数型を指定してバインドする実装を試してみましょう。

チャレンジ3：キーワード内の特殊文字（% や _）のエスケープ

ユーザーが検索窓に % や _ を入力した場合、SQLのLIKE検索ではこれらが「任意の文字列」「任意の1文字」を意味する特殊文字として機能してしまいます。
ユーザーが「純粋な文字としての %」を検索したい場合、入力文字列内の % や _ を preg_replace や str_replace で \%、\_ にエスケープし、SQL側で ESCAPE '\' を明示する上級者向けのサニタイズ処理を考えてみてください。