@blog.justoneplanet.info

日々勉強

mysqldumpする

■mysqldump

mysqldump --no-autocommit -u root -p dbname > db.dump
scp -r ./db.dump user@123.123.123.123:/home/db.dump

123.123.123.123

cd /home
mysql -u root -p --default-character-set=utf8 < db.dump

50MBくらいなら10秒で終わる。ちなみにWHEREで条件を絞ってエクスポートする場合は以下のようにする。

echo "SET NAMES utf8;SELECT * FROM table WHERE hoge = 'fuga'” | mysql -u user -p dbname > /home/piyo.txt;

INTO OUTFILEは権限が必要なので実行できない場合も多々ある。

■バックアップ

以下のようにすることでmysqldumpを利用して定期的にデータベースをbackupできる。

#!/bin/sh
TDAY=`date +%Y%m%d`
cd /var/www/hogehoge.justoneplanet.info/app/vendors/shells
mysqldump --no-autocommit -u root --password=hogehoge dbname > $TDAY.dump

crontab

以下のように記述することで、毎日1:15にバックアップが実行される。

15 1 * * * /var/www/hogehoge.justoneplanet.info/app/vendors/shells/backup.sh

SQLの性能をテストする

以下のようにEXPLAINしてみるとイイ。

EXPLAIN SELECT *
    FROM  `parent`
    LEFT JOIN  `child`
    ON  `parent`.id =  `child`.id_parent
    WHERE  `parent`.id = 1

どうやらchildテーブルを全て検索してるようだ。

table type possible_keys key ref rows
parent const PRIMARY PRIMARY const 1
child ALL NULL NULL NULL 311

■改善

以下を実行してインデックスを付加し、クエリの性能を向上させる。

ALTER TABLE `child`
    ADD CONSTRAINT `fk_child_tbl_parent_tbl`
    FOREIGN KEY (`id_parent`) REFERENCES `parent` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

劇的だ!ヾ(@⌒▽⌒@)ノchildテーブルを1行しか検索しなくなった。

table type possible_keys key ref rows
parent const PRIMARY PRIMARY const 1
child ref fk_child_tbl_parent_tbl fk_child_tbl_parent_tbl const 1

参考

type
結合型。
const。テーブルに、一致するレコードが最大で 1 つ。
ref。前のテーブルのレコードの組み合わせのそれぞれに対して、インデックス値にマッチするすべてのレコードがこのテーブルから読み取られる。ref は、『インデックスの左端の先頭部分のみが結合で使用される場合』、『インデックスが UNIQUE や PRIMARY KEY ではない場合』に使用される。
ALL。前のテーブルのレコードの組み合わせのそれぞれに対して、フルテーブルスキャンが実行される。
possible_keys
テーブル内のレコードの検索に MySQL で使用可能なインデックスを示す。
key
MySQL が実際に使用を決定したキー(インデックス)を示す。
ref
テーブルからレコードを選択する際に key とともに使用されるカラムまたは定数。
rows
クエリの実行に際して調べる必要があると MySQL によって判定されたレコードの数を示す。少ない方がイイ。

複数テーブル同時削除

以下のようにdelete文を使用すると、複数テーブルからレコードが削除できる。

DELETE
    `table`,
    `child`,
    `grandchild`
FROM
    `table`
LEFT JOIN
    `child`
ON
    `table`.id = `child`.`id_table`
LEFT JOIN
    `grandchild`
ON
    `child`.id = `grandchild`.`id_child`
WHERE
    `table`.id = 1

但し、外部キー制約でON DELETE CASCADEを使った方が早い気がする。

外部キー制約

以下のようにする。

ALTER TABLE `child`
    ADD CONSTRAINT `fk_child_table_parent_table`
    FOREIGN KEY (`id_parent`) REFERENCES `parent` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION;

こうするとparentテーブルからレコードAを削除した時に、レコードAのidとchildテーブルのid_parentが等しいレコードが同時に削除される。

ちなみにid_contentsは全く同じ型でないとALTER TABLEが失敗する。

特定のユーザにmysqlリモート接続を許可する

その前にポート3306は初期状態では閉じられているので開ける。

iptables -I INPUT -j ACCEPT -p tcp -s x.x.x.x --dport 3306

IPはクライアント側のIP。このIPからだけ接続できる。192.168.1.%のようにワイルドカードも使える。

GRANT ALL PRIVILEGES ON *.* to user@'192.168.1.1' IDENTIFIED BY 'password';

CONSTRAINT句

以下のようなSQLを実行すると、commentテーブルのカラムtask_idのレコードには、taskテーブルのカラムidに存在するものしか存在できない。この参照整合性制約機能は多用すると少々問題が起こることもある。

CREATE TABLE IF NOT EXISTS `comment` (
    `id` int(11) NOT NULL auto_increment,
    `task_id` int(11) default NULL,
    `description` text,
    `created_at` datetime default NULL,
    PRIMARY KEY  (`id`),
    KEY `comment_FI_1` (`task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
CREATE TABLE IF NOT EXISTS `task` (
    `id` int(11) NOT NULL auto_increment,
    `title` varchar(255) default NULL,
    `description` text,
    `status_id` int(11) default NULL,
    `created_at` datetime default NULL,
    `updated_at` datetime default NULL,
    PRIMARY KEY  (`id`),
    KEY `task_FI_1` (`status_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
ALTER TABLE `comment`
    ADD CONSTRAINT `comment_FK_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`);

■メリットとデメリット

メリット

  • 誤ったデータをテーブルに含めないようにできる

デメリット

  • データ移行時に整合性をチェックするため時間がかかる
  • テーブル設計が複雑になる(運用上の整合性の考慮)

■代替案

アプリケーション側での制御。

PHP Database Security(データベースのセキュリティ)

すなわちSQLインジェクションのための防御策である。

■SQLインジェクションの実例

以下のログインフォームで考える。

<form method="post" action="login.php">
Name: <input type="text" name="name" />
Password: <input type="password" name="password" />
<input type="submit" />
</form>

以下のコードで存在するユーザかどうか、DBに問い合わせて検証する。

<?php
$password = md5($_POST['password']);
$sql = "SELECT * FROM `user` WHERE `name` = '{$_POST['name']}' AND `password` = '{$password}'";
?>

一見、何の問題も無いようだが、Nameに「1′ OR 1 = 1 –」とすると以下のSQL文を実行することになる。

SELECT * FROM `user` WHERE `name` = '1' OR 1 = 1 --' AND `password` = 'password_str'

「–」以降は改行コードまで全てコメントとみなされ、条件「`name` = ‘1’」は殆ど成立しないが、条件「1 = 1」が常に成立し、全てのユーザデータが返ることになる。従って、アルゴリズムにもよるが、ログインが常にできてしまう。

対策1

以下のように、プリペアドステートメントを用いる。

<?php
try {
    $dsn = 'mysql:host=localhost;dbname=db';
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
}
catch(PDOException $e){
    //code
}
$password = md5($_POST['password']);
$sql = "SELECT * FROM `user` WHERE `name` = ? AND `password` = ?";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    $_POST['name'],
    $pasword
));
$result = $stmt->fetchAll();
?>

対策2

以下のように、エスケープ処理を忘れずに行う。

$dbh = mysql_connect(DB_HOST, DB_USER, DB_PASS);
mysql_select_db(DB_NAME, $dbh);
$password = md5($_POST['password']);
$name = mysql_real_escape_string($_POST['name']);
$sql = "SELECT * FROM `user` WHERE `name` = '{$name}' AND `password` = '{$password}'";

但し、ログインフォームなので大概において英数字のユーザ名なのでフィルタリングすることをお勧めする。

PHP MySQL Improved Extension(mysqli)

mysqli拡張サポートによって、MySQL4.1以降で使える機能を利用できる。但し、個人的には全くこの手の関数を使わない。何故ならばMySQLに特化した関数であり、仮に他のDBに移行する場合、保守のコストがとっても高くなってしまうと考えているからだ。それにラーニングコストもかかるし、PDOのメソッド名とmysqliの関数名がごっちゃになる。

■mysqliを使ったDBへの接続

<?php
$dbh = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if(mysqli_connect_errorno()){
    //code for error
}
mysqli->close();
?>

mysqliは例外をthrowしない。

<?php
$dbh = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if(!$dbh){
    //code for error
}
mysqli_close($dbh);
?>

■mysqliを使ったDBへのクエリ発行

<?php
$name = mysqli->real_escape_string($_POST['name']);
$sql = "SELECT `id`, `name` FROM `person` WHERE `name` = '$name'";
if(!$dbh->real_query($sql)){
    //print($dbh->error);
}
if($result = mysql->store_result()){
    while($row = $result->fetch_assoc()){
        print($row['id'] . ': ' . $row['name']);
    }
    $result->close();
}
?>

$resultもcloseしなきゃならんとは。。。めんどい。。。

<?php
$name = mysqli_real_escape_string($_POST['name']);
$sql = "SELECT `id`, `name` FROM `person` WHERE `name` = '$name'";
if(mysqli_real_query($dbh, $sql)){
    //print(mysqli_error());
}
if($result = mysql_store_result($dbh)){
    while($row = $result_fetch_assoc($result)){
        print($row['id'] . ': ' . $row['name']);
    }
    mysqli_free_result($result);
}
?>

うーむ。実に不愉快だ。やはりmysqliは性に合わん!

■mysqliを使ったプリペアドステートメント

<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)";
if($stmt = $dbh->prepare($sql)){
    $stmt->bindParam('s', $id, $_POST['name'], $_POST['country_id']);
    $stmt->execute();
    $stmt->bind_result($result_id, $result_name);
    while($stmt->fetch()){
        print($result_id . ': ' . $result_name);
    }
    $stmt->close();
}
?>

これじゃ、ソースを書きたくならないな。

<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)";
if($stmt = mysqli_prepare($dbh, $sql)){
    mysqli_stmt_bind_param($stmt, 's', $id, $_POST['name'], $_POST['country_id']);
    mysqli_stmt_execute()$stmt;
    mysqli_stmt_bind_result($dbh, $result_id, $result_name);
    while(mysqli_stmt_fetch()){
        print($result_id . ': ' . $result_name);
    }
    mysqli_stmt_close($stmt);
}
?>

■mysqliを使ったトランザクション

<?php
$dbh->autocommit(false);
$dbh->query("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')");
$dbh->query("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')");
if(!$dbh->commit()){
    $dbh->rollback();
}
?>
<?php
mysqli_autocommiti($dbh, false);
mysqli_query($dbh, "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')");
mysqli_query($dbh, "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')");
if(!mysqli_commit($dbh)){
    mysqli_rollback($dbh);
}
?>

mysqliの特徴

「手続き型」と「オブジェクト指向型」の記述が可能である。

サーバによってはインストールされていない事もしばしばあるようだ。ますます使わんぞ、コリャ=3・・・

命名

余計なお世話型関数。

PHP Data Object(PDO)

PDOには様々なDBに対する各々のドライバがある。一旦、インストールすれば(PDOを使用してDBへ接続している場合、)コードを殆ど書き換えることなく、DBを変更することが可能になる。

■PDOを使ったデータベースへの接続

<?php
$dsn = 'mysql:host=localhost;dbname=dbname';
$dbh = new PDO($dsn, DB_USER, DB_PASS);
?>

通常、PDOはエラーが発生しても何も表示しない(PDO::ERRMODE_SILENT)。これはセキュリティ上安全ではあるが、一方でデバッグを非常に困難にする。従って、例外を発生させ(ファイルに書き込みさせ)るようにすることも可能である。

<?php
try {
    $dsn = 'mysql:host=localhost;dbname=dbname';
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(Exception $e){
    //code
}
?>

■PDOを使ったデータベースへのクエリ発行

SELECT文

SELECT文のような、結果セットを得ることを主目的としている場合は、以下のようにqueryメソッドを用いる。また、queryメソッドを用いた場合、戻り値はPDOStatementオブジェクトである。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = 'John'";
$result = $dbh->query($sql);
foreach($result as $row){
    print($row['name'] . ': ' . $row['country_id']);
}
?>

但し、以下のように書いた場合、$rowには「数字キー」と「カラム名のキー」が両方含まれているので値が重複する。これはデフォルトのデータ取り出しモードがPDO::FETCH_BOTHであり、「数字キー」と「カラム名のキー」での配列になっているためである。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = 'John'";
$result = $dbh->query($sql);
foreach($result as $row){
    foreach($row as $value){
        print($value . PHP_EOL);
    }
}
/*
1
1
John
John
2
2
*/
?>
データ取り出しモードの変更

データ取り出しモードを変更するには、以下のように結果セットが含まれるPDOStatementオブジェクト$resultに対して、setFetchModeメソッドを実行せねばならない。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = 'John'";
$result = $dbh->query($sql);
$result->setFetchMode(PDO::FETCH_OBJ);
foreach($result as $row){
    print($row->name . ': ' . $row->country_id);
}
?>
ユーザ入力の値をSQL文に組み込むためのメソッド

通常、SQLインジェクションを回避するために、ユーザ入力の値をSQL文に含める際にはサニタイジングしなければならない。以下のように、quoteメソッドを用いることで可能である。

<?php
$name = $dbh->quote($_POST['name']);
$sql = "SELECT * FROM `person` WHERE `name` = '$name'";
$result = $dbh->query($sql);
?>

但し、いくつかのドライバはこのメソッドを実装していないので、後述のプリペアドステートメントを用いたほうが良い。

INSERT文、UPDATE文、DELETE文

INSERT文、UPDATE文、DELETE文には変更(処理)した行数を返すexecメソッドを用いる。

<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')";
$num = $dbh->exec($sql);
print($num);//1
?>

■プリペアドステートメント(Prepared Statement)を用いたクエリの発行

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

  • 1回のリクエストに対する処理の中で、SQL文の再利用を可能にする
  • DBがプリペアドステートメントをサポートしてない場合、PDOが内部の関数で可能にする
  • DBがプリペアドステートメントをサポートしている場合、DBの機能を使ってアプリケーションの性能の向上を可能にする
  • (SQLインジェクションを誘発させる)クォート漏れに対するリスク回避を可能にする
<?php
$sql = "INSERT INTO `person`(`id`, `name`, `country_id`) VALUES(?, ?, ?)";
$stmt = $dbh->prepare($sql);
$stmt->execute(array(
    $id,
    $_POST['name'],
    $_POST['country_id']
));
?>

INSERT文、UPDATE文、DELETE文には、ユーザ入力の値をSQL文に含めることが非常に多い。とはいえ、以下のようにSELECT文でも当然ながら、プリペアドステートメントを用いることはある。

<?php
$sql = "SELECT * FROM `person` WHERE `name` = ?";
$stmt = $dbh->prepare($sql);
$stmt->execute(array('John'));
//$stmt->setFetchMode(PDO::FETCH_OBJ);
$result = $stmt->fetchAll();
var_dump($result);
/*
array(1) {
  [0]=>
  array(6) {
    ["id"]=>
    string(1) "1"
    [0]=>
    string(1) "1"
    ["name"]=>
    string(4) "John"
    [1]=>
    string(4) "John"
    ["country_id"]=>
    string(1) "2"
    [2]=>
    string(1) "2"
  }
}
*/
?>

但し、PHPのバージョンによって、(MySQL独自拡張の)LIMIT句に対するプリペアドステートメントにバグがあるため、それ(LIMIT句)以外での使用が望ましいと個人的には思う。また、LIMIT句は数値の値しか許容しないため、PHPで(型変換などを使って)サニタイジングすることは比較的に容易である。さらに、PDOStatementクラスに(結果を1行だけ返す)fetchメソッドが「あるバージョン」と「ないバージョン」があるため、このメソッドの使用はなるべく避けたほうが良い。

■PDOを使ったトランザクションの実行

以下のようにして、トランザクションを利用する。

<?php
try {
    $dsn = 'mysql:host=localhost;dbname=dbname';
    $dbh = new PDO($dsn, DB_USER, DB_PASS);
    $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $dbh->beginTransaction();
    $dbh->exec("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('4', 'Jack', '2')");
    $dbh->exec("INSERT INTO `person`(`id`, `name`, `country_id`) VALUES('5', 'Emily', '1')");
    $dbh->commit();
}
catch(Exception $e){
    $dbh->rollBack();
}
?>

各メソッドについて

beginTransaction
トランザクションを開始する
commit
確定する
rollBack
破棄して最初の状態に戻す

トランザクションとは

「全て成功する」か「全て失敗する」かのどちらかしか存在しないことを保証する機能。この機能が必要な例として、たいていの参考書では「銀行の振込み」などが扱われる。