データベース：サーバにデータを保存しよう

開発言語

技術解説

連載 :

これから学ぶPHP

2015年6月19日(金)

野田貴子

データを作成する

テーブルにデータを追加するSQL文は以下のようになります。

INSERT INTO recipes (
id,
name,
category_id,
author_id,
create_date,
update_date
)
values (
    1,
    'ホットケーキ',
    2,
    1011,
    '2015/02/16 00:11:22',
    '2015/02/16 00:11:22'
);

前半ではカラム名、後半では実際に登録するデータを指定しています。

PHPからデータベースにSQL文を渡すと実行した結果が返ってきますので、成功したかどうかをチェックします。

$result = $db->query('INSERT ...');
if (DB::isError($result)) {
    exit($result->getMessage());
}

IDを連番にするには

上の例では「id」に「1」を指定しましたが、キーであるカラムでは同じ番号を重複できません。そのため新しいデータを作る際には、今あるデータ内で最大となるidの次の番号にしなければなりません。idを省略すると自動的にインクリメント（1を加算）して登録してくれるDBMSもありますが、ここでは次の番号を求めるメソッドを使いましょう。どのデータベースライブラリにも、このような機能はついているはずです。

$newId = $db->nextID();

セキュリティ対策（SQLインジェクション対策）

ユーザーから入力された情報などを登録する場合、入力されたデータは変数に入っているので、以下のようにSQL文を$sql変数に代入したいかもしれません。

$sql = "INSERT INTO recipes (id, name, category_id, author_id, create_date, update_date) values (" . $newID . ", '" . $name . "', " . $category_id . ", " . $author_id . ", '" . $current_datetime . "', '" . $current_datetime . "' );";
$result = $db->query($sql);

しかし、もしも「$name」に「ホットケーキ」ではなく次のようなデータが入力されていると、recipeテーブルのすべてのデータが削除されていまいます。

悪意ある$nameの中身

x', 1, 1, '2015/01/01 00:00:00', '2015/01/01 00:00:00'); DELETE from recipe; --

作成される危険なSQL文

INSERT INTO recipes (id, name, category_id, author_id, create_date, update_date) values (1, 'x', 1, 1, '2015/01/01 00:00:00', '2015/01/01 00:00:00'); DELETE from recipe; --', 2, 1011, '2015/02/16 00:11:22', '2015/02/16 00:11:22');

「--」はSQL文のコメントで、PHPにおける「//」と同様です。

このように任意のSQL文を挟み込まれてしまう攻撃を「SQLインジェクション」と呼びます。SQLインジェクションを使うと、データベースに登録されている他人のパスワードを盗み見たり、データを破壊したりすることができます。

これを避けるためには、SQL文と判断される文字列をカラムの値として設定できないようにします。上の例では、nameカラム用のデータのところでシングルクオート（'）が閉じられてしまったことが問題です。そこで、登録するデータをエスケープするように変更します。PHPにはSQL用のエスケープ関数がありますが、ライブラリの「プレースホルダ」を使用したほうが確実です。

$sql = "INSERT INTO recipes (id, name, category_id, author_id, create_date, update_date) values (?, ?, ?, ?, ?, ?);";
$placeholder = array(
$newID,
$name,
$category_id,
$author_id,
$current_datetime,
$current_datetime
);
$result = $db->query($sql, $placeholder);
if (DB::isError($result)) {
    exit($result->getMessage());
}

SQLに直接変数を埋める代わりに「?」で代用し、「?」に入る内容は配列（$placeholder）で指定します。こうすると、SQL文で使用される特殊文字「'」「"」「\」などがエスケープされ、文字列の場合は引用符（'）で囲われます。

悪意ある$nameの中身

x', 1, 1, '2015/01/01 00:00:00', '2015/01/01 00:00:00'); DELETE from recipe; --

作成されるSQL文は実行しても問題ない

INSERT INTO recipes (id, name, category_id, author_id, create_date, update_date) values (1, 'x\', 1, 1, \'2015/01/01 00:00:00\', \'2015/01/01 00:00:00\'); DELETE from recipe; --', 2, 1011, '2015/02/16 00:11:22', '2015/02/16 00:11:22');

今度は、$nameの中身がそのままnameカラムに登録されます。データはエスケープされていますが、取得したときに元に戻るので問題ありません。

どのデータベースライブラリを使用しても、SQL文を実行する手順は以下のようになります。

登録するデータを用意する
SQL文を用意する
SQLインジェクション対策としてプレースホルダを使い、データがエスケープされるようにする
SQL文を実行したら、成功したかどうか結果をチェックする

データベースライブラリの中には、登録するデータを配列に入れるだけでSQL文を自動で作成してくれるものもあります。

$arData = array(
    "id" => $db->GenID("seq_recipes"),
    "name" => "ホットケーキ",
    "category_id" => 2,
    "author_id" => 1011,
    "create_date" => "2015/02/16 00:11:22",
    "update_date" => "2015/02/16 00:11:22"
);
$result = $db->Replace("recipes", $arData, "id", true);
if ($result === 0) {
    exit("データの作成に失敗しました。");
}

このようなライブラリでは、SQLインジェクション対策であるデータのエスケープなども自動で行ってくれます。

データを更新する

テーブルのデータを更新するSQL文は、以下のようになります。

UPDATE
recipes
SET
category_id = 0,
update_date = '2015/02/18 11:22:33'
WHERE
    id > 10;

SET句で更新するカラムとデータを指定し、WHERE句で更新対象になる条件を指定します。

WHERE句で用いる条件式

WHERE句で指定できる条件式には以下のようなものがあります。ほとんどのDBMSでは文字列の置換などの関数も利用できます。

条件式	意味
a = b	aとbが等しい
a != b	aとbが等しくない
a b	aとbが等しくない
a > b	aがbより大きい
a >= b	aがb以上である
a	aがbより小さい
a	aがb以下である
a IS NULL	aが空である
a IS NOT NULL	aが空でない
a IN (b, c, d)	aがb、c、dのいずれかである
a LIKE 'b%'	aの文字列にbが前方一致する
a LIKE '%b'	aの文字列にbが後方一致する
a LIKE '%b%'	aの文字列にbを含む

「id > 10」も「10

またこれらの条件式は、「AND」や「OR」で複数つなげることができます。

このSQL文をPHPで実行するには以下のようにします。基本的にはデータの作成と同じ流れになります。

$sql = "UPDATE recipes SET category_id = ?, update_date = ? WHERE id = ?;";
$placeholder = array(
$category_id,
$current_datetime,
$id
);
$result = $db->query($sql, $placeholder);
if (DB::isError($result)) {
    exit($result->getMessage());
}
echo $db->affectedRows() . "件のデータを更新しました。";

データを削除する

テーブルのデータを削除するSQL文は、以下のようになります。

DELETE FROM
recipes
WHERE
category_id = 1
AND author_id = 1101;

PHP側は、データの更新と同じように書くことができます。

データを検索する

テーブルのデータを検索するSQL文は、以下のようになります。

SELECT
    id,
name,
category_id,
author_id
FROM
    recipes
WHERE
    category_id = 1
ORDER BY
    id
LIMIT
    5

このSQL文の実行結果は以下のようになります。

id	name	category_id	author_id
2	卵焼き	1	1009
5	ほうれん草の胡麻和え	1	1011

PHPでSELECT文を実行すると、結果を配列やオブジェクトで取得できます（ライブラリによります）。

配列として取得した場合

array(2) {
  [0]=>
  array(4) {
    ["id"]=>
    int(2)
    ["name"]=>
    string(9) "卵焼き"
    ["category_id"]=>
    int(1)
    ["author_id"]=>
    int(1009)
  }
  [1]=>
  array(4) {
    ["id"]=>
    int(5)
    ["name"]=>
    string(30) "ほうれん草の胡麻和え"
    ["category_id"]=>
    int(1)
    ["author_id"]=>
    int(1011)
  }
}

ORDER BY句

リレーショナルデータベースのテーブルでは、データの順番に関する情報を持っていません。ですが、何らかのデータ一覧を表示したい場合、順序というのは重要です。掲示板であれば新しい投稿から順に表示したいですし、会員名簿であれば五十音順がいいかもしれません。

ORDER BY句では、取得したデータを並び替えるための条件を指定します。条件はいくつでも指定することができ、先に書いたほうが優先されます。

ORDER BY句の例

ORDER BY句	第一条件	第二条件
id	idの昇順	なし
id ASC	idの昇順（ASCは省略可）	なし
update_date DESC, author_id	update_dateの降順	author_idの昇順

LIMIT句、OFFSET句

データの一覧画面には、ページングの機能がよくありますよね。たとえば「3ページ目：全76件中、21件～30件を表示中」というようなものです。ページングでは、データベースから全データを取得し、そのうち一部分のみを画面に表示しているわけではありません。全データを取得してしまうと、PHPで使用するメモリが膨大になり、処理が重くなってしまうからです。このような場合は、データベースから条件に合うデータのうち一部のみを取得します。その場合に使用するのが、LIMIT句とOFFSET句です。

LIMIT句では、最大で何件のデータを取得するのかを指定します。10件ずつ表示したい場合は、「LIMIT 10」と指定します。

OFFSET句では何番目以降のデータを取得するかを指定します。10件ずつ表示して3ページ目ならば、「OFFSET 20」となるでしょう。OFFSET句を指定しない場合は、「OFFSET 0」と同じ意味になります。

getAll：検索結果をすべて取得する

getAllメソッドを使用すると、SQL文の実行結果をすべて取得できます。取得したいカラムは、SELECTに続けて指定できます。

SELECT対象	意味
id	idを取得します
id, name	idとnameを取得します
id, category_id + 1	idと、category_idに1を足したものを取得します
*	すべてのカラムを取得します

$category_id = 1;
$sql = "SELECT id, name, category_id, author_id FROM recipes WHERE category_id = ? ORDER BY id LIMIT 5;";
$result = $db->getAll($sql, array($category_id));
if (DB::isError($result)) {
    exit($result->getMessage());
}

SQL実行結果（実際は配列になっています）

id	name	category_id	author_id
2	卵焼き	1	1009
5	ほうれん草の胡麻和え	1	1011

getRow：検索結果を1件のみ取得する

データを1件のみ取得する場合、SQL文で「LIMIT 1」と指定してもよいですし、getRowメソッドを使うこともできます。getRowメソッドを使用すれば、「LIMIT 1」以外は共通している既存のSQL文を流用できます。

$category_id = 1;
$sql = "SELECT id, name, category_id, author_id FROM recipes WHERE category_id = ? ORDER BY id LIMIT 5;";
$result = $db->getRow($sql, array($category_id));
if (DB::isError($result)) {
    exit($result->getMessage());
}

SQL実行結果（実際は配列になっています）

id	name	category_id	author_id
2	卵焼き	1	1009

getOne：検索結果を1カラムのみ取得する

データを1カラムのみ取得する場合、SQL文で対象カラムを指定してもよいですし、getOneメソッドを使うこともできます。getOneメソッドを使用すれば、対象カラム名以外は共通している既存のSQL文を流用できます。

$category_id = 1;
$sql = "SELECT id, name, category_id, author_id FROM recipes WHERE category_id = ? ORDER BY id LIMIT 5;";
$result = $db->getOne($sql, array($category_id));
if (DB::isError($result)) {
    exit($result->getMessage());
}

SQL実行結果（実際は配列になっています）

id
2
5

データベースライブラリによって細部は異なりますが、同じような機能を提供しているはずです。

おわりに

いかがでしたか。データベースやSQLについては、分厚い本を一冊書けてしまうくらい様々な機能があります。今回紹介したのは、そのうちの「データのエスケープを行う」「実行結果の成否判定を行う」などの基本的なことのみです。また、リレーショナルデータベースの最大の特徴は、複数のテーブルの連結や、統計的な検索などができることですが、ここでは触れていません。ぜひ専門の書籍などで勉強されることをお勧めします。それではまた。