PHPのDB操作の備忘録

前職でPHPは弄っていたけれど、WEBサイトを作っていて、DBのことについてはすっかり忘れているなと感じたので、

PHPで出来るDB操作についてまとめることにしました。

PDO で安全にデータを取得しよう

PHP には PDO（PHP Data Objects）が用意されています。

これを使うと、SQL文と値を完全に分離でき、安全にデータを取得できます。

例えば下記のようなSQL文があり、

$sql = "SELECT * FROM users WHERE name = '$name'";

$sql = "SELECT * FROM users WHERE name = '$name'";

ユーザーが

OR 1=1 --

OR 1=1 --

と入力してしまった場合、SQL文としては

SELECT * FROM users WHERE name = '' OR 1=1 --'

SELECT * FROM users WHERE name = '' OR 1=1 --'

と解釈されレコードを全件取得されてしまったり、最悪DELETEやDROP操作までされてしまいます。

これがSQLインジェクションです。

それを防ぐために、値をSQLとして解釈させない構造がPDOです。

つまり、入力された文字列はSQL文ではなくただの文字列として解釈してくれる有難いシステムです。

先ほどの文字列は下記のように書いてこれはSQL文ではなく文字列として解釈させます。

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

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

また、上記のように記述することで、SQLクエリの構造をテンプレート化して、事前にコンパイルしておくことで、

処理を高速化できます。

これがプリペアドステートメントです。

つまり、

①事前にSQL文をコンパイル
②DB がSQL文の形を理解する
③後から値を渡す
④値はクォート/エスケープを行い、SQL構文として解釈されない

ということをやってくれています。

有難いですね。

ただ、あくまでもSQLと値を物理的に分離してくれているだけで、

mysqli_real_escape_string() まではしてくれている訳ではないので、そこは注意してください。

以上がPDOのまとめです。

つまり、最初から入力された値を信頼しない設計になっているところが PDO の特徴です。

fetch と fetchAll

PDO の一番基本的な DB のテーブルからデータを取得するには、fetch または fetchAllを使います。

大きな違いは 1件だけ取得するか全件取得かということです。

fetch

fetchの場合（1件だけ取得）：

$sql = "SELECT * FROM users WHERE id = 001";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

$sql = "SELECT * FROM users WHERE id = 001";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);

上記は、

①prepare関数でプリペアドステートメントを準備し、
②execute関数でプリペアドステートメントを実行し
③fetchで1行だけレコードを取得する

といった流れでPDOを使ったレコード取得を行う一例です。

この場合、複数レコードが見つかった場合、最後の1行を取得します。

falseにならずにレコードが返ってくるので気を付けてくださいね！

また、引数についてはリファレンスを参照して、取得したい配列の形を指定してください。

https://www.php.net/manual/ja/pdostatement.fetch.php

fetchAll

fetchAllの場合（全件取得）：

$sql = "SELECT * FROM users";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

$sql = "SELECT * FROM users";
$stmt = $this->pdo->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAll();

①prepare関数でプリペアドステートメントを準備し、
②execute関数でプリペアドステートメントを実行し
③fetchでレコードを全件取得する

といった流れでレコードを全件取得します。

fetch、fetchAllともに配列で値を返します。

※第一引数のmodeによって返ってくる配列のキーが変わります

名前付きパラメータと位置パラメータ

値の指定には名前付きパラメータでの指定と、位置パラメータでの指定の2種類あります。

どちらも便利な機能なのですが、混在は出来ないので気を付けて使ってください！

名前付きパラメータ

その名の通り、プリペアドステートメントの中に名付けを行い、

後からその名前が付いたパラメータに値を入れる形式です。

例）

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

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

上記の例の場合、通常、WHERE name = ‘$name’; にしてしまうところを

WHERE name = :name; と名前を付けておき、

後から :name は 変数$nameの値にしてくださいとPDOにお願いする安全を確保した記載方法になります。

または下記の方法でも同じく方法になります。

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = :name"
);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = :name"
);
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();

とbindValue関数を使ったり、

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = :name"
);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = :name"
);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->execute();
$result = $stmt->fetchAll();

bindParam関数を使っても安全に値を渡すことが出来ます。

位置パラメータ

プレースホルダーという位置を示すパラメータを使った方法です。

位置パラメータは「?」を使って示すことになっています。

例）

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = ?"
);
$stmt->execute($name);
$result = $stmt->fetchAll();

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = ?"
);
$stmt->execute($name);
$result = $stmt->fetchAll();

上記のように、name = ? になっているところがプレースホルダーです。

値はプリペアドステートメントを実行するexecuteの際に渡します。

位置パラメータは注意すべきところがあり、書いたプレースホルダー順に値を渡さなければなりません。

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = ? AND money > ?"
);
$stmt->execute($name, $money);
$result = $stmt->fetchAll();

$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = ? AND money > ?"
);
$stmt->execute($name, $money);
$result = $stmt->fetchAll();

これを execute($money, $name);と逆に書いてしまったりすると、正しいデータが取得できません。

位置パラメータでもbindValue関数やbindParam関数が使えるので、不安な場合はそれを使いましょう。

あわせて読みたい

PHPのWHERE句で使う「?」の数を動的に作ろう PHPでは、SQLのWHERE句で位置パラメータを使う場合こう書きますよね。 PHP$id = 123; $sql = ‘SELECT * FROM test_table WHERE id = ?’; $stmt = $this->pdo->prepare($…

query()関数

もし、プレースホルダーを使わない場合は、プリペアドステートメントを使わず下記の書き方もできます。

$result = $pdo->query("SELECT * FROM users");

$result = $pdo->query("SELECT * FROM users");

まとめ

PDOを使った安全なDB操作についてまとめてみました。

初心者による初心者のまとめですが、参考になれば嬉しいです！