OXY NOTES

WordPressのプラグインでデータベースを作成・削除・アップデートする方法

oxy

セキュリティへの配慮が欠かせないデータベースとのやり取り

「WordPressプラグインの解説」第3弾。

WordPressのプラグインでは独自のテーブルを作成して利用することもできます。
そうなればWordPressの仕様に縛られること無く、自由なプラグインの開発が可能になります。

しかし自由には責任が付きもので、セキュリティに配慮した設計が重要になります。
このページではデータベースの基本的な使い方と、必要になるセキュリティ対策について解説をします。

目次

本当に独自のテーブルが必要か考える

プラグイン独自のデータをデータベースに保存する方法は、新規のテーブルを追加するだけではありません。

何でもかんでも新規のテーブルを作るのではなく、WordPressで予め用意されたテーブルが利用できないか調べてみましょう。

投稿に関連する情報なら <接頭辞> + postmeta
コメントに関連する情報なら <接頭辞> + commentmeta
ユーザーに関連する情報なら <接頭辞> + usermeta
プラグインの設定に関する情報なら <接頭辞> + options

他にもWordPressは拡張性の優れた仕組みが用意されており、カスタム投稿タイプやカスタムフィールド、カスタム分類などで代用できないかも検証してみてください。

こうした既存の仕組みでは対応できない場合にだけ、独自のテーブルを追加します。

プラグイン専用テーブルの作成

WordPressの作法で、データベースを扱う際にはwpdbクラスを呼び出します。
テーブルの作り方はテンプレートのようなところがあるので、このように書くもんだと覚えると楽です。

Codexの「プラグインでデータベーステーブルを作る」で詳しく解説されているので合わせて参照してください。

今回の例は私の自作プラグインでテーブルを作成した時の例です。

function create_tables() {
	global $wpdb;

	$sql = "";
	$charset_collate = "";

	// 接頭辞の追加（socal_count_cache）
	$table_name = $wpdb->prefix . 'oxy_table';

	// charsetを指定する
	if ( !empty($wpdb->charset) )
		$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset} ";

	// 照合順序を指定する（ある場合。通常デフォルトのutf8_general_ci）
	if ( !empty($wpdb->collate) )
		$charset_collate .= "COLLATE {$wpdb->collate}";

	// SQL文でテーブルを作る
	$sql = "
		CREATE TABLE {$table_name} (
			postid bigint(20) NOT NULL,
			day datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			all_count bigint(20) DEFAULT 0,
			twitter_count bigint(20) DEFAULT 0,
			facebook_count bigint(20) DEFAULT 0,
			google_count bigint(20) DEFAULT 0,
			hatena_count bigint(20) DEFAULT 0,
			pocket_count bigint(20) DEFAULT 0,
			feedly_count bigint(20) DEFAULT 0,
			PRIMARY KEY  (postid)
		) {$charset_collate};";

	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $sql );
}

まずglobal $wpdbでwpdbクラスのグローバルオブジェクトを呼び出します。
接頭辞やcharset、照合順序などはWordPressで既に設定されているものを取得します。
接頭辞などはサイトによって設定が異なるので、自分サイトがそうだからといって「wp1_」等と指定しないようにしてください。

この例では + oxy_tableという名前のテーブルを作成しています。

後は取得した情報を変数で渡しつつ、SQL文でテーブルを作成するだけです。

require_onceでupgrade.phpを呼び出して、dbDeltaにクエリを渡してます。
dbDeltaはWordPressで用意されたテーブルを作成・更新するための関数で、必要に応じてテーブルを更新したり、変更したりと柔軟に対応してくれます。
ただ、フィールド名にアポストロフィを使わないなど、記述方法に癖があるので上のCodexの解説を確認しておいてください。

テーブルのバージョンアップ処理

プラグインのバージョンアップでテーブルの構成が変更になるのはよくあることです。
予めテーブルのバージョンを設定しておき、構成が変更になった時にバージョンを上げてアップデート処理をします。

// テーブルアップデート用テーブルバージョンの指定
public $oxy_db_version = "1.0";

function create_tables() {
	global $wpdb;

	$sql = "";
	$charset_collate = "";

	// 接頭辞の追加（socal_count_cache）
	$table_name = $wpdb->prefix . 'oxy_table';

	// charsetを指定する
	if ( !empty($wpdb->charset) )
		$charset_collate = "DEFAULT CHARACTER SET {$wpdb->charset} ";

	// 照合順序を指定する（ある場合。通常デフォルトのutf8_general_ci）
	if ( !empty($wpdb->collate) )
		$charset_collate .= "COLLATE {$wpdb->collate}";

	// SQL文でテーブルを作る
	$sql = "
		CREATE TABLE {$table_name} (
			postid bigint(20) NOT NULL,
			day datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
			all_count bigint(20) DEFAULT 0,
			twitter_count bigint(20) DEFAULT 0,
			facebook_count bigint(20) DEFAULT 0,
			google_count bigint(20) DEFAULT 0,
			hatena_count bigint(20) DEFAULT 0,
			pocket_count bigint(20) DEFAULT 0,
			feedly_count bigint(20) DEFAULT 0,
			PRIMARY KEY  (postid)
		) {$charset_collate};";

	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
	dbDelta( $sql );

	update_option( 'oxy_db_version', $oxy_db_version );
}

変更したのは2行目と、39行目です。

まず2行目でテーブルアップデート用のバージョンを指定しています。

39行目にあるupdate_option()で<接頭辞> + optionsテーブルにデータベースのバージョンを保存しています。

アップデート時の処理

アップデート時にはバージョンを増やし、以下のようにします。
このようにしておけば、プラグインのバージョンとデータベースのバージョンがひも付けされるので、アップデートはもちろん、ダウングレードした際にもデータベースを更新することができます。

public $oxy_db_version = "1.1"; // プラグインの新しいバージョンを指定

function update_tables() {
	$installed_ver = get_option( "oxy_db_version" ); // オプションに登録されたデータベースのバージョンを取得
	if ( $installed_ver != $oxy_db_version ) {
		// バージョンが異なる場合にアップデート用の処理
	}
}

テーブルの削除処理

プラグイン有効時にテーブルを作成したら、停止時に削除する処理も必要です。そうしないと利用者のデータベースに無駄なテーブルを残すことになります。
ログ等を残したいプラグインもあると思いますが、そのような実装は多くの場合ありがた迷惑です。（最低でも停止時にログを残すか確認を取るべきです。）

前回の投稿で解説したregister_deactivation_hook()のコールバック関数で以下のようにしておきましょう。

function uninstall() {
	global $wpdb;

	$table_name = $wpdb->prefix . 'oxy_table';

	$wpdb->query("DROP TABLE IF EXISTS $table_name");

	delete_option( "oxy_db_version" );
}

その他、追加したデータベースのバージョンなどの設定も削除するのを忘れないようにしてください。

データベースへ安全にデータを保存する方法

データベースへの書き込みはプラグインの中でも一番気を使うところです。
SQLインジェクション関連の脆弱性報告は毎日のようにされています。また、一番攻撃されるのもプラグインを介したSQLインジェクションだそうです。

対策は星の数ほどありますが、WordPressではプレースホルダーを利用するのが最も安全です。プレースホルダーは別名プリペアドステートメント、もしくは静的ホルダーとも呼ばれる対策です。

SQLインジェクションの危険性について全く理解していないという方は、IPAで配布されている「安全なウェブサイトの作り方」を一読しておくことを強くお勧めします。

Codexに「SQL インジェクション攻撃からクエリを保護する」という解説がありますが少しわかりにくいので実例で見ていきます。

※2017/10/05追記：プレースホルダの仕様が変更になったようです。以下の位置指定子は使えなくなったようです。対策としてプレースホルダの数だけ引数を追加してください。

※2017/10/20追記：以下の位置指定子を利用した脆弱性が発見されました。詳しい解説は避けますが、以下の補足でも指摘した文字列のシングルクォートの扱いに関する不備です。そのため位置指定子を利用している場合、速やかに利用を停止する必要があります。

function set_database( $postid = NULL ) {

	global $wpdb;

	// テーブルの接頭辞と名前を指定
	$table_name = $wpdb->prefix . "oxy_db_version";

	// 本来引数として渡されますがテスト用
	$postid = 1;

	// SNSのカウントを代入（サンプルなので適当な値）
	$socials = array();
	$socials['all'] = 15;
	$socials['twitter'] = 0;
	$socials['facebook'] = 1;
	$socials['google'] = 2;
	$socials['hatena'] = 3;
	$socials['pocket'] = 4;
	$socials['feedly'] = 5;

	// 現在の時間をSQL形式で取得
	$now = current_time('mysql');

	// ON DUPLICATE KEY UPDATEでプライマリキーのpostidをフラグに無ければINSERT
	$result = $wpdb->query( $wpdb->prepare(
		"INSERT INTO {$table_name}
		(postid, day, all_count, twitter_count, facebook_count, google_count, hatena_count, pocket_count, feedly_count)
		VALUES (%d, %s, %d, %d, %d, %d, %d, %d, %d)
		ON DUPLICATE KEY UPDATE day = '%2\$s',
		all_count = %3\$d,
		twitter_count = %4\$d,
		facebook_count = %5\$d,
		google_count = %6\$d,
		hatena_count = %7\$d,
		pocket_count = %8\$d,
		feedly_count = %9\$d",
		$postid,
		$now,
		$socials['all'],
		$socials['twitter'],
		$socials['facebook'],
		$socials['google'],
		$socials['hatena'],
		$socials['pocket'],
		$socials['feedly']
	));
}

これは取得したSNSのカウントを、日時とともにデータベースへ書き込むというSQL文です。（入力する値などはテスト用に適当に入れてます）

プレースホルダは$wpdb->query( $wpdb->prepare( "SQL文", "プレースホルダに対応する値" ) )という書式です。プレースホルダは%d、%s、%fで指定します。
%dは整数型、
%sは文字列、
%fは少数をそれぞれ指定します。
PHPのsprintfにおける型指定子（置換指示詞）と同じですね。
対応する値はプレースホルダの数だけ、カンマ区切りで先頭から順番に指定していきます。

また、この例ではday = '%2\$s'という指定もしています。「%s」という型指定子の間に「2\$」という位置指定子を挟んでます。
これはプレースホルダに対応する値の2番目のもの（上の例で言う$now）を使うという指定です。ちなみにこの方法で位置を指定した場合、文字列は「‘(シングルクォート)」で囲む必要があります。
シングルクォートを付けないと「Query was empty for query made by require…」なるエラーが出ます。（個人的にはWordPress側のミスな気がしますが、注意してください）
同じ値を繰り返し使うような場合は、このように指定します。

以上のようにプレースホルダを使えば、カンマをエスケープしたり、SQL文をサニタイズしたりという手間が省けます。
「渡す前に数列にするから安全だ」といった思い込みが一番危険なので、面倒でも入力値は全てプレースホルダを利用しましょう。
もしそれでもSQLインジェクションが発生したとしたら…、もうそれはWordPressの実装が悪いということです。諦めましょうｗ

$wpdb->prepareのエラーメッセージ

$wpdb->prepareで第2引数を使わないと以下の警告が出ます。
Warning: Missing argument 2 for wpdb::prepare(), called in…

第2引数移行を使わないということは、プレースホルダを使っていないということです。セキュリティに配慮した親切な警告ですね。間違っても第2引数に「''」を指定してエラーだけを回避するという対策はしないでください。

他にも行の追加は$wpdb->insert()だったり、更新ならupdate()、削除ならdelete()という専用の関数も用意されてます。
$tableにはテーブル名、$dataには配列でカラム名=>データ、$formatはオプションでプレースホルダに指定した型指定子を指定します。

	$wpdb->insert( $table, $data, $format );
	$wpdb->update( $table, $data, $format );

これはWordPress内部でプレースホルダを利用しているので、SQLインジェクション対策は必要ありません。
単純に特定のテーブルにデータを渡すだけという場合はこちらを利用しましょう。

データベースへ保存したデータを取得する方法

これは特に難しいことはありません。気をつけるとしたら、無駄に「*（アスタリスク）」で必要のないデータを取得しないことです。必要なカラムのデータだけを取得しましょう。

function get_database( $postid = NULL ) {
	global $wpdb;
	$table_name = $wpdb->prefix . "oxy_db_version";
	$postid = 1;

	$query = "SELECT twitter_count,facebook_count FROM {$table_name} WHERE postid = {$postid}";

	$result = $wpdb->get_row( $query );

	$twitter_count = $result->twitter_count; // 実際にはエスケープ処理が必要（後述）
	$facebook_count = $result->facebook_count;

	var_dump( $twitter_count );
	var_dump( $facebook_count );
}

取得するデータのクエリをget_row()に渡してます。
複数の列がある場合はget_col()。複数行に渡る場合はget_results()を使います。

オリジナルのクエリを組み立てるなら、保存の時と同じようにquery()を使います。

もしオプション名など、ユーザーの入力値を使ってクエリを組み立てる場合は、保存時と同じように$wpdb->prepare()でプレースホルダを利用してください。

データベースから取得したデータのエスケープ

また、忘れてはならないのがエスケープ処理です。データベースから値を取得して出力する場合は全てエスケープ処理します。

なぜエスケープ処理が必要か

例えばプラグインオリジナルの設定画面を作成してフォームに以下のデータを入力されたとします。

<script>alert('hoge');</script><p>ふが</p>

これがバリデーションや、プリペアドステートメントをすり抜けた場合、JavaScriptが実行されてしまいます。
これは後述するオプションページの実装時でも同じです。「アドミン権限で入力するフォームにXSSが仕込まれるわけ無い」と思われるかもしれませんが「どこをエスケープして、どこをしないか」などと考えて記述すれば、必ず漏れがおきます。そのため、データベースから取得した値は、問答無用でエスケープ処理をしてください。

もしも手を抜くと以下のようにJavaScriptが実行されてしまいます。

これがエスケープしておけば以下の様にフォームにタグが表示されます。

一番良いエスケープのタイミングはHTMLに出力する直前です。
テンプレートタグ等の場合はデータを渡す直前、もしくは変数に渡すタイミングです。

エスケープの方法はセオリー通りで構いませんが、WordPressでは専用の関数を用意してくれているので、利用しましょう。
主なエスケープは以下の通りです。

esc_html <>&”’をエンティティ化
esc_attr <>&”’をエンティティ化して、HTMLタグを取り除く
esc_url URL文字列として最適化する

上の例で言えば以下のようにしておきましょう。

	$twitter_count = esc_html( $result->twitter_count );
	$facebook_count = esc_html( $result->facebook_count );

esc_htmlとesc_attrの違い

Codexや解説サイトを見てもesc_htmlと、esc_attrの違いが理解できません。
ネット上の解説を見ると、フォーム等のvalueなんかにはesc_attrを使おうという解説が多いようです。
意味もわからずセキュリティ対策を勧めるのも何なので、違いを調べてみました。

どちらもincludeディレクトリのformatting.phpで定義されています。


function esc_html( $text ) {
	$safe_text = wp_check_invalid_utf8( $text );
	$safe_text = _wp_specialchars( $safe_text, ENT_QUOTES );
	/**
	 * Filter a string cleaned and escaped for output in HTML.
	 *
	 * Text passed to esc_html() is stripped of invalid or special characters
	 * before output.
	 *
	 * @since 2.8.0
	 *
	 * @param string $safe_text The text after it has been escaped.
 	 * @param string $text      The text prior to being escaped.
	 */
	return apply_filters( 'esc_html', $safe_text, $text );
}

function esc_attr( $text ) {
	$safe_text = wp_check_invalid_utf8( $text );
	$safe_text = _wp_specialchars( $safe_text, ENT_QUOTES );
	/**
	 * Filter a string cleaned and escaped for output in an HTML attribute.
	 *
	 * Text passed to esc_attr() is stripped of invalid or special characters
	 * before output.
	 *
	 * @since 2.0.6
	 *
	 * @param string $safe_text The text after it has been escaped.
 	 * @param string $text      The text prior to being escaped.
	 */
	return apply_filters( 'attribute_escape', $safe_text, $text );
}

全く同じですね…。

ということで、データベースの値を取得して出力する前にはesc_html()を使っておけば問題ないようです。
関数を見れば分かる通り、配列を直接渡すことはできないので注意してください。

次は「WordPressプラグイン用の設定を追加する方法」です。