PHPでデータベースにアクセスして、データの保存や抽出する方法を紹介します。ここではMySQLを使います。
データベースの扱い
データベースはファイルと違ってデータを整理保存するのに適したシステムです。
直接ファイルを扱ってデータを管理する場合はいろいろなことを考慮する必要がりますが、データベースは煩わしいことを扱いやすく考慮されています。
一般的にはデータベースはデータベース管理システム（DBMS:DataBase Management System）とよばれています。
データベースへのアクセスはSQLクエリを使います。
またいろいろな製品のDBMSがありますが、ここではMySQLを使った例を説明します。
データベースへのアクセスは大きく以下の2つの手順で行います。
- データベースへの接続
- SQLクエリの実行（データの保存や抽出など）
この処理を繰り返すことでデータベスを操作します。
データベースへの接続
具体的な処理を行う前にデータベースの概要を説明します。
データベースはDBMS製品によって詳細な仕様は違いますが、基本的な構造は同じ考えで作られています。
大きく把握するとデータベースという入れ物にテーブルが複数入っていて、そのテーブルの中にデータが複数入っています。
データは列ごとに指定した型のものが格納されます。
例えば、列1の型が文字列型だとすると列1の場所には文字列が格納されます。
テーブルの中にあるデータはエクセルなどの表をイメージするとわかりやすいかと思います。
列と行があって、列の種類分のデータが行数だけ格納されているイメージです。
この構造を踏まえてデータベースに接続しますが、まずはそのデータベースを作っておきます。
// データベース作成
CREATE DATABASE foodatabase;
// ユーザ作成
GRANT ALL ON foodatabase.* to 'foo-user'@'localhost' IDENTIFIED BY 'foo-pass'
MySQLに入ってこれらのSQLクエリでデータベース作成とユーザ作成をしておきます。
それではPHPでこのデータベースに接続しましょう。
PHPでデータベースを扱うにはPDO（PHP Data Objects）というデータベース抽象化レイヤを使います。
PDOはデータベースを取り扱いやすくするPHPの拡張機能です。
// PDOのインスタンス生成
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');これで先程作成したユーザでfoodatabaseデータベースに接続します。
もしなんらかの理由で接続できなかった場合はPDOは例外を発生します。
なので、PDOのインスタンス生成時は以下のように記述します。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
} catech (PDOException $e) {
print '接続できませんでした' . $e->getMessage();
}うまく接続できると$db変数にPDOのインスタンスが格納されて、データベースを操作する機能が使えるようになります。
テーブルの作成
データを格納するテーブルを作成する際には格納したいデータの種類を整理し、それぞれを列の型として定義します。
ここでは趣味で集めたコレクションの情報をデータベースに格納するためのテーブルを作ります。
テーブル名は「collection」にし、格納したい情報は以下とします。
- ID（項目番号）
- 名前（集めているものの名前）
- 価格（集めているものの価格）
- 好き度（集めているものを気に入っている度合い）
これらのデータを格納するためのテーブルを以下のように作成します。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = $db->exec("CREATE TABLE collection (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
price INT,
favorite INT) DEFAULT CHARACTER SET=utf8");
} catech (PDOException $e) {
print '作成できませんでした' . $e->getMessage();
}通常はテーブルに格納するデータには必ず主キーが含まれます。
これはデータを一意のものと識別できる唯一のデータとなります。
ここでは「id」を主キーとして整数を自動的に割り当てることができるようにしています。
これで「collection」テーブルが作成できました。
テーブルへデータの格納（書き込み）
データを格納するときもテーブル作成と同じようにデータベースへ接続してSQLクエリを発行します。
ここでは以下の内容を格納します。
- ID（自動割当なので指定しない）
- 名前（赤べこ）
- 価格（2,500円）
- 好き度（4）
このコードでデータを格納します。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = $db->exec("INSERT INTO collection (name, price, favorite)
VALUES ('赤べこ', 2500, 4)");
} catech (PDOException $e) {
print '書き込みできませんでした' . $e->getMessage();
}SQLクエリの「INSERT INTO」を使います。
これでデータの格納ができます。
データの更新
すでにあるデータの内容を更新したい場合は更新のSQLクエリを発行します。
ここでは赤べこの価格を2,500円から3,500円へ変更します。
このコードでデータを更新します。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = $db->exec("UPDATE collection SET price = 3500
WHERE name = '赤べこ'");
} catech (PDOException $e) {
print '更新できませんでした' . $e->getMessage();
}SQLクエリの「UPDATE SET」を使います。
これでデータの更新ができます。
データの削除
データを削除したい場合は削除のSQLクエリを発行します。
ただし、削除の場合は対象を明確に指定しないとすべてのデータを削除する危険があるので気をつけましょう。
削除で使うSQLクエリは「DELETE FROM」ですが、WHERE句を指定せず実行するとすべて削除されます。
ここでは赤べこのデータを削除します。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$q = $db->exec("UPDATE collection SET price = 3500
WHERE name = '赤べこ'");
} catech (PDOException $e) {
print '削除できませんでした' . $e->getMessage();
}これでデータの削除ができます。
データの安全な操作
SQLクエリで無害化していないデータを使うと、SQLインジェクション攻撃と呼ばれる問題が発生します。
例えば以下のSQLクエリを見てみます。
$q = $db->exec("INSERT INTO collection (name) VALUES ('{$_POST['new_name']}')");フォームで入力されたデータをPOST送信で受け取って、データベースに格納しようとしています。
もし、$_POST[‘new_name’]の内容に以下のようなデータが入っていたときどのようになるでしょうか？
x'); DELETE FROM collection; INSERT INTO collection (name) VALUES ('y
SQLクエリは「;」で区切ることで複数のクエリを実行できます。
なので、このデータを挿入して実行すると、name列にxを格納したあと、テーブルのデータをすべて削除して、name例にyを格納して終了してしまいます。
これはクロスサイトスクリプティング（XSS）攻撃と同じように、フォームデータを無害化せずにそのまま使うと、意図しないSQLクエリを実行してしまい、データベースの内容を削除してしまったり変更してしまうような問題が発生する可能性があります。
そして安全にSQLクエリを行うためにPDOにはプリペアドステートメントという機能があります。
プリペアドステートメントではクエリ実行を2段階に分割します。
先程まではexec()メソッド1回の実行で処理していましたが、まずprepare()メソッドにSQL内の値を入れたい箇所にプレースホルダの文字を配置し、そのあとexecute()メソッドを呼び出してプレースホルダの文字を置換します。
置換される文字はシングルクォート「’」で囲んでからクエリに入れられるのでSQLインジェクション攻撃を防ぎます。
以下はその例です。
$stmt = $db->prepare('INSERT INTO collection (name, price, favorite) VALUES (?, ?, ?);
$stmt->execute(array($_POST['new_name'], $_POST['new_price'], $_POST['favorite']));この場合はプレースホルダ文字は「?」となり、execute()の第1引数が、prepare()の引数にある最初の「?」に置き換わります。
続いて第2引数は2つ目の「?」、第3引数は3つ目の「?」、、という具合に置き換わっていきます。
bindValue()とbindParam()
プリペアドステートメントを使うときはbindValue()とbindParam()メソッドを使ってプレースホルダに値をバインドすることもできます。
bindValue(パラメータID, バインドする変数, [, PDOデータ型定数])
bindParam(パラメータID, バインドする変数, [, PDOデータ型定数[, PDOデータ型長[, ドライバオプション]]])
パラメータIDはプレースホルダに「?」を使っていた場合は1から始まる数値を入れ、「:name」形式の場合はそのパラメータ名を指定します。
バインドする変数はPHPプログラム内の変数を指定します。
PDOデータ型定数はオプションでパラメータに対してPDO::PARAM_*定数（PDO::PARAM_STRなど）を使ったデータ型を指定します。
PDOデータ型定数を省略した場合はPDO::PARAM_STRが指定されます。
PDOデータ型長はオプションでデータの長さを指定します。
ドライバオプションはオプションでUTF8の文字列をバインドするなどのドライバオプションを指定します。
上記オプションの引数は省略可能です。
以下に使用例を記述します。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $db->prepare("INSERT INTO collection (name, price, favorite)
VALUES (?, ?, ?)");
$name = '寄木細工';
$price = 4500;
$favorite = 3;
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$stmt->bindValue(2, $price, PDO::PARAM_INT);
$stmt->bindValue(3, $favorite, PDO::PARAM_INT);
$stmt->execute();
} catech (PDOException $e) {
print '書き込みできませんでした' . $e->getMessage();
}また「:name」のような名前付きプレースホルダを使う場合は以下となります。
try {
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $db->prepare("INSERT INTO collection (name, price, favorite)
VALUES (:name, :price, :favorite)");
$name = '有田焼';
$price = 3000;
$favorite = 5;
$stmt->bindParam(:name, $name, PDO::PARAM_STR);
$stmt->bindParam(:price, $price, PDO::PARAM_INT);
$stmt->bindParam(:favorite, $favorite, PDO::PARAM_INT);
$stmt->execute();
} catech (PDOException $e) {
print '書き込みできませんでした' . $e->getMessage();
}bindValue()とbindParam()の違い
bindValue()はこの関数が実行されるときに引数で渡した値がバインドされます。
bindParam()はexecute()が実行されるときに引数で渡した値がバインドされます。
このためbindParam()の引数に変数を使ってバインドする値を渡したあとでも、変数の値が変わってからexecute()を実行するとその変更された値でバインドします。
つまり、1度だけbindParam()を実行し、そのあと引数に指定した変数の値を変えてexecute()を何度も実行して異なった値をバインドすることができます。
$db = new PDO('mysql:host=localhost;dbname=foodatabase', 'foo-user', 'foo-pass');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $db->prepare("INSERT INTO collection (name, price, favorite)
VALUES (:name, :price, :favorite)");
$name = '信楽焼';
$price = 2000;
$favorite = 3;
$stmt->bindParam(:name, $name, PDO::PARAM_STR);
$stmt->bindParam(:price, $price, PDO::PARAM_INT);
$stmt->bindParam(:favorite, $favorite, PDO::PARAM_INT);
$stmt->execute();
$name = '達磨';
$price = 1500;
$favorite = 4;
$stmt->execute();
$name = 'こけし';
$price = 2500;
$favorite = 3;
$stmt->execute();このように変数の値を変えて何度も同じSQLクエリを実行することができます。