ロジコヤ

3. SQLクエリー

クエリー実行

ファイル構成

basic/
├── env.php
├── lib/
|    └── Database.php
└── select_users.php

DB設定読み込み

設定ファイルとデータベース接続モジュールを読み込みます。

select_users.php

<?php
require_once 'env.php';
require_once 'lib/Database.php';
?>

query()

PDOでSQLを実行するには query() メソッドを実行します。戻り値は PDO Statementオブジェクト です。

select_users.php

// PDOオブジェクトで query() を実行
// PDO Statementオブジェクトを取得
$stmt = $pdo->query(SQL);

メソッド定義と実行

users テーブルから50件選択するSQLクエリーを実行します。

SELECT * FROM users LIMIT 50;

ユーザデータ取得関数 get() を定義し、PDOStatementオブジェクトをデバッグします。

select_users.php

<?php
require_once 'env.php';
require_once 'lib/Database.php';

// ユーザ取得メソッド実行
$users = get();

// ユーザデータを取得する関数
function get($limit = 50)
{
    try {
        // DB接続
        $pdo = Database::getInstance();
        // SQL作成
        $sql = "SELECT * FROM users LIMIT {$limit};";
        // queryメソッドでSQLを実行し、PDOStatementオブジェクトを取得
        $stmt = $pdo->query($sql);
        // PDOStatementオブジェクトをデバッグ
        var_dump($stmt);
    } catch (PDOException $e) {
        error_log($e->getMessage());
        exit('システムエラーが発生しました。');
    }
}
?>

予期せぬエラーに対応して try-catch で例外処理

PDO Statement確認

PDO Statementオブジェクトをデバッグ関数で表示します。 queryString にSQLが設定されているを確認します。

object(PDOStatement)#2 (1) { ["queryString"]=> string(29) "SELECT * FROM users LIMIT 50;" }

SQLエラーの場合

SQL構文やプログラムエラーが発生すると、エラーメッセージ表示されます。

ターミナル

Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: ...

フェッチ

フェッチメソッド

データベースからデータ取得する処理をフェッチ（Fetch） といいます。PDOのフェッチ処理はメソッドがいくつかあります。

fetch()

fetch() はSQL結果を1件取得します。

$stmt->fetch(PDO::FETCH_ASSOC);

fetchAll()

fetchAll() はSQL結果をすべて取得します。

$stmt->fetchAll(PDO::FETCH_ASSOC);

フェッチモード

PDOではフェッチメソッドで取得したデータ型を指定するフェッチモードというパラメータがあります。

モード	データ形式
PDO::FETCH_BOTH	カラム名と連番の配列
PDO::FETCH_ASSOC	カラム名の配列
PDO::FETCH_COLUMN	1カラム指定時に、連番の配列
PDO::FETCH_KEY_PAIR	2カラム指定時にキーと値の連想配列
PDO::FETCH_OBJ	カラム名をプロパティとしたオブジェクト

ユーザデータ取得

fetchAll() で一括取得

fetchAll() で、usersデータを多次元連想配列として取得します。

select_users.php

function get($limit = 50)
{
    try {
        // DB接続
        $pdo = Database::getInstance();
        // SQL作成
        $sql = "SELECT * FROM users LIMIT {$limit};";
        // queryメソッドでSQLを実行し、PDOStatementオブジェクトを取得
        $stmt = $pdo->query($sql);
        // Userデータを取得
        $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
        // 取得したデータを返却
        return $users;
    } catch (PDOException $e) {
        error_log($e->getMessage());
        exit('システムエラーが発生しました。');
    }
}

HTMLレンダリング

データベースから取得したユーザデータをHTMLレンダリングします。

select_users.php

<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>ユーザ一覧</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>

<body>
    <main class="mx-auto bg-white p-6">
        <h2 class="py-2 text-2xl mt-6">ユーザ一覧</h2>
        <div class="overflow-hidden">
            <div class="grid grid-cols-4 bg-gray-200 p-2 rounded-t">
                <div>id</div>
                <div>account_name</div>
                <div>email</div>
                <div>display_name</div>
            </div>
            <?php foreach ($users as $user): ?>
                <div class="grid grid-cols-4 border-b border-gray-200 p-2">
                    <div><?= $user['id'] ?></div>
                    <div><?= $user['account_name'] ?></div>
                    <div><?= $user['email'] ?></div>
                    <div><?= $user['display_name'] ?></div>
                </div>
            <?php endforeach; ?>
        </div>
    </main>
</body>

</html>

結果

fetch() の繰り返し

fetchAll() はデータが大容量になるとメモリ不足や負荷がかかるため、while文で fetch() を1件ずつ繰り返して処理することもあります。

// データフェッチ
// $users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// while で fetch() 繰り返し
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $users[] = $row;
}

プリペアドステートメント

プリペアドステートメントとは

Prepared Statement（プリペアドステートメント） はSQL文を実行する前に準備のことで、SQL文にパラメータを安全にバインドすることができ、SQLインジェクション攻撃を防ぐことができます。

メソッド

プリペアドステートメントでよく利用されるメソッドです。

メソッド	説明
prepare()	SQLの用意
bindParam()	データを変換してバインド
bindValue()	パラメータを値渡しでバインド
execute()	SQLの実行

プレースホルダ形式

プリペアステートメントでは : や ? の特殊記号で表現したプレースホルダ形式で記述し、パラメーターを設定します。

プレースホルダ形式

$sql = "SELECT * FROM users WHERE id = :id";

prepare()

prepare() は、SQL文をデータベースにクエリー実行する前に準備するメソッドです。

$stmt = $pdo->prepare($sql);

execute()

execute() はプリペアドステートメントで作成された SQLを実行します。プレースホルダのデータバインドは引数として連想配列で指定します。

// SQL文（プレースホルダ）
$sql = "SELECT * FROM users WHERE id = :id";
// SQL事前準備
$stmt = $pdo->prepare($sql);
// プレスホルダー（:id） のパラメータを引数にSQL実行
$stmt->execute(['id' => $id]);

その他のデータバインド

execute() 以外に、明示的にパラメータをバインドするメソッドがあります。

bindParam()

bindParam() で「カラム名」「値」「データ型」を代入してプレースホルダの部分に代入します。このような処理をデータバインドといいます。

bindParam(カラム名, 値, データ型)

パラメータとデータ型

パラメータ	データ型
PDO::PARAM_BOOL	BOOL型
PDO::PARAM_NULL	NULL型
PDO::PARAM_INT	INT型
PDO::PARAM_STR	文字列型 *FLOAT型も対応

bindParam()でデータバインド

bindParam() で INT型 をデータバインドします。

$stmt->bindParam('id', $id, PDO::PARAM_INT);

bindValue()

bindValue() は、パラメータを値渡しでバインドします。バインドした値は実行時に固定され、後で値を変更してもクエリには影響しません。

$stmt->bindValue(':id', $id);

ユーザ検索

ユーザ検索処理

find() メソッドを定義し、指定したユーザIDで１件取得します。

find() メソッド定義

insert_user.php

<?php
require_once 'env.php';
require_once 'lib/Database.php';

function find($id)
{
    // DB接続
    $pdo = Database::getInstance();

    // SQL文（プレースホルダ）
    $sql = "SELECT * FROM users WHERE id = :id";

    try {
        // SQL事前準備
        $stmt = $pdo->prepare($sql);
        // プレスホルダー（:id） のパラメータを引数にSQL実行
        $stmt->execute(['id' => $id]);

        // Userデータを取得
        $user = $stmt->fetch(PDO::FETCH_ASSOC);
        return $user;
    } catch (PDOException $e) {
        error_log($e->getMessage());
        return;
    }
}
}

find() メソッド実行

GETパラメータでユーザIDを取得し、find() を実行します。

insert_user.php

<?php
require_once 'env.php';
require_once 'lib/Database.php';

// GETパラメータからユーザIDを取得
if (!empty($_GET['id'])) {
    $user_id = $_GET['id'];
    $user = find($user_id);
}
...

HTML表示

ユーザ一覧表示で「user_id」クエリパラメータ付きの a リンクを追加します。

find_user.php

<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>ユーザ検索</title>
    <script src="https://cdn.tailwindcss.com"></script>
</head>

<body>
    <main class="mx-auto bg-white p-6">
        <h2 class="text-2xl py-2 mb-6 text-gray-700">ユーザ検索</h2>
        <form action="" method="get" class="mb-8">
            <div class="flex items-center gap-4">
                <label for="id" class="text-gray-600 whitespace-nowrap">ユーザID</label>
                <input
                    type="text"
                    name="id"
                    id="id"
                    class="w-48 px-4 py-2 border border-gray-300 rounded-md"
                    value="<?= $user_id ?? '' ?>">
                <button
                    type="submit"
                    class="px-4 py-2 bg-blue-500 text-white rounded">
                    検索
                </button>
            </div>
        </form>

        <h2 class="py-2 text-2xl">SQLインジェクション</h2>
        <div class="bg-gray-100 p-4 rounded-md">
            '' OR 1=1;--
        </div>

        <h2 class="py-2 text-2xl">結果</h2>
        <?php if (!empty($user)): ?>
            <div class="flex flex-col gap-4 text-gray-800 bg-gray-100 p-4 rounded-md">
                <div>
                    <span class="block text-sm text-gray-500">ユーザID</span>
                    <div class="text-lg"><?= $user['id'] ?></div>
                </div>
                <div>
                    <span class="block text-sm text-gray-500">アカウント名</span>
                    <div class="text-lg"><?= $user['account_name'] ?></div>
                </div>
                <div>
                    <span class="block text-sm text-gray-500">メールアドレス</span>
                    <div class="text-lg"><?= $user['email'] ?></div>
                </div>
                <div>
                    <span class="block text-sm text-gray-500">パスワード</span>
                    <div class="text-lg"><?= $user['password'] ?></div>
                </div>
                <div>
                    <span class="block text-sm text-gray-500">作成日時</span>
                    <div class="text-lg"><?= $user['created_at'] ?></div>
                </div>
                <div>
                    <span class="block text-sm text-gray-500">更新日時</span>
                    <div class="text-lg"><?= $user['updated_at'] ?></div>
                </div>
            </div>
        <?php else: ?>
            <p class="text-red-500 font-semibold">ユーザが見つかりませんでした。</p>
        <?php endif ?>
    </main>
</body>

</html>

結果

SQLインジェクション

SQLインジェクションとは

SQLインジェクションは、DB連携するWebアプリケーションで発生するセキュリティ脆弱性の一種です。具体的には、攻撃者がフォームやURLパラメーターを送信し、本来の意図とは異なるSQLを実行させる攻撃手法を指します。

SQLの脆弱性

パラメーターをSQL文に直接結合するのは、SQL文を改竄される恐れがあり大変危険です。

fetch_user.php

// パラメーターをSQL文に直接結合
$sql = "SELECT * FROM users WHERE id = {$id}";

不正な値送信

フォームから不正なユーザID '' OR 1=1;-- を送信すると、つぎのSQLになります。

SELECT * FROM users WHERE id = '' OR 1=1;--;

SQLインジェクションの確認

SQLインジェクション対策していないプログラムを作成します。

find_user.php

// パラメーターをSQL文に直接結合
$sql = "SELECT * FROM users WHERE id = {$id}";
$stmt = $pdo->query($sql);

不正データの送信

不正データ '' OR 1=1;-- の送信すると、ユーザデータが表示されてしまいました。

SQLインジェクション対策

プリペアステートメントとプレスホルダーでプログラムすると、SQLインジェクション対策になります。

fetch_user.php

$sql = "SELECT * FROM users WHERE id = :id;";
$stmt = $pdo->prepare($sql);
$stmt->execute(['id' => $id]);

不正データの送信

不正データ '' OR 1=1;-- の送信しても検索できません。

<< PDOの基本

CRUD処理 >>

PHP + MySQL Webサーバプログラミング

Lesson 01 データベース設計
Lesson 02 PDOの基本
Lesson 03 SQLクエリー
Lesson 04 CRUD処理
Lesson 05 プロジェクト作成
Lesson 06 ユーザ登録
Lesson 08 ユーザログイン
Lesson 09 投稿ページ
Lesson 10 投稿処理
Lesson 16 MVC
Lesson 17 ビュー
Lesson 18 コントローラー
Lesson 19 モデル
Lesson 19 Like機能
Lesson 20 管理者ページ
Lesson 20 コンポーネント
Lesson 25 メール送信