Furudateのブログ

プログラミングやネットワーク系の知識・技術がメインのブログ。技術メモ帳的な感じになるかと。岩手から発信していきます。

【PHP】mysqliとプリペアドステートメントについて

PHP

こんにちは。

今回はPHPについてです。
普段フレームワークで書くことが多いのでいざ平文でやろうとすると忘れていることが多いです。

今回はPHPからMySQLの使用についてです。

PHPを習い始めたころはmysql_connectとか使ってたんですが、現在では非推奨のようです。
MySQLへの接続は、mysqli、PDOのどちらかが推奨されています。

PDOがすごく良さげですが、今回は馴染みやすそうなmysqliについて書こうと思います。（PDOはいつかやってみたいですね）

mysqliの基本的な使い方

mysqliではオブジェクト型と手続き型の書き方がありますが、この記事ではオブジェクト型で書きます。
手続き型にする意味がないと思うので。
以下はmysqliの基本的なコードになります。

<?php
	$mysqli = new mysqli("host", "user", "password", "database");
	if ($mysqli->connect_errno)
	    echo "Failed to connect to MySQL: " . $mysqli->connect_error;

	$mysqli->set_charset("utf-8"); // 文字化け防止

	$res = $mysqli->query("SELECT 'choices to please everybody.' AS _msg FROM DUAL"); // クエリの発行
	$row = $res->fetch_assoc(); // 結果の行を連想配列で取得
	echo $row['_msg'];

	$res->close(); // 結果セットを開放
	$mysqli->close();

?>


基本的にはこんな感じです。
mysqlのときとあまり変わりませんね。
注意点としては、文字化け防止のためにUTF-8をセットすることと、結果を取得したクラス（mysqli_result)もcloseしてメモリ開放をすべきという点です。

fetch_assocのところについては、よく使うものを下にまとめておきます。mysqlとまったく一緒ですが。。

  • mysqli_result::fetch_all
    • 結果のすべての行を連想配列・数値配列あるいはその両方で取得
  • mysqli_result::fetch_array
    • 結果の行を連想配列・数値配列あるいはその両方で取得
  • mysqli_result::fetch_assoc
  • mysqli_result::fetch_row
    • 結果の行を数値配列で取得
  • mysqli_result::fetch_count
    • 結果のフィールド数を取得

プリペアドステートメントについて

プリペアドステートメントは、SQL文をコンパイルしたテンプレートのようなものです。
概念のイメージとしては、まず最初にSQL文のパラメータがない部分（insert文とかselect文とか）を用意し、その後に実際の値となるパラメータを用意して実行するという感じです。

これを使う利点としては、パラメータを変えるだけで何回も同じSQL文（クエリ）を発行しなくて良いこと、そしてなんといってもSQLインジェクションを防げるということがあります。
プリペアドステートメントでは、SQL文とパラメータ（データ）を別々にDBに渡すので、SQLインジェクションは起きないということです。

そのため、特に理由がない限りはプリペアドステートメントでの記述が推奨されています。

mysqliでは、プリペアドステートメントを表すためにmysqli_stmtクラスを使用します。
その基本的な記述方法は以下のようになります。

<?php
	$mysqli = new mysqli('host', 'username', 'password', 'database');

	/* 接続状況をチェックします */
	if (mysqli_connect_errno()) {
	    printf("Connect failed: %s\n", mysqli_connect_error());
	    exit();
	}

	// ここでSQL文を作成します（パラメータはありません）
	$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
	// ここでパラメータに実際の値となる変数を入れます。
	// sssdのところは、それぞれパラメータの型（string, string, string, double）を指定しています。
	$stmt->bind_param('sssd', $code, $language, $official, $percent);

	// ここで変数に値を代入しています。
	$code = 'DEU';
	$language = 'Bavarian';
	$official = "F";
	$percent = 11.2;

	/* プリペアドステートメントを実行します */
	$stmt->execute();
	
	// $stmt->affected_rowsでクエリ結果を取得しています。これはInsert文などで変更された行の数を返します。
	printf("%d Row inserted.\n", $stmt->affected_rows);

	/* ステートメントと接続を閉じます */
	$stmt->close();

	/* 接続を閉じます */
	$mysqli->close();
?>


こんな感じです。

ここでは$mysqli->prepareのところでSQL文を作成し、パラメータのところを？にしています（プレースホルダ）*1。
その後、$stmt->bind_paramで？のところに変数をバインド（結びつけて）います。
これがプリペアドステートメントのやり方です。

ちなみに、$mysql->prepareは成功の時true、失敗の時falseを返すので、通常はif文で成功の判断をします。

また、変数の値を変えて、もう一度$stmt->execute()をすれば、パラメータの値を変更したクエリ文を発行できます。

$stmt->bind_paramのところでは、sとかでパラメータの型を指定していますが、他にも以下の文字で指定できます。

文字 説明
i Integerを指定
d doubleを指定
s stringを指定
b blobを指定（あまり使わなそう）


また、$stmt->affected_rowsのところでは変更された行の数を返していますが、クエリ結果取得の際によく使いそうな関数を以下にまとめておきます。

  • mysqli_stmt::affected_rows
    • INSERT, UPDATE, DELETEクエリによって変更された行の数を返す（SELECTの場合はnum_rowsを使う）
  • mysqli_stmt::store_result
    • SELECT文等の結果を返すクエリの場合に実行する必要がある関数。（後述）
  • mysqli_stmt::fetch
    • SELECT文等の結果の値を取得（後述）
  • mysqli_stmt::num_rows
    • SELECTクエリの結果の行数を返す(INSERT等の場合はaffected_rowsを使う）

プリペアドステートメントで値を取得する

ここでは実際にプリペアドステートメントでSELECT文等のクエリから得られた値を取得してみたいと思います。
また、今回はプレースホルダを？ではなく名前にしたいと思います。

<?php
	$mysqli = new mysqli("host", "username", "password", "database");

	if (mysqli_connect_errno()) {
	    printf("Connect failed: %s\n", mysqli_connect_error());
	    exit();
	}
	
	// クエリ分を発行します。:nameなどがプレースホルダになります。
	$query = "select code, name from table where name=:name && value=:value";

	/* ステートメントを準備します */
	if ($stmt = $mysqli->prepare($query)) {
	    $name = "hoge";
	    $value = "huga";
	    // 変数をバインドします。
	    $stmt->bind_param(:name, $name);
	    $stmt->bind_param(:value, $value);
		
	    // 実行します
	    $stmt->execute();
	    
	    // 結果をバッファに保存
	    $stmt->store_result();

	    // 変数をプリペアドステートメントにバインドします
	    // ここではバインドするだけであり、実際に取得するのはfetch()
	    $stmt->bind_result($col1, $col2);

	    /* 値を取得します */
	    while ($stmt->fetch()) {
	        printf("%s %s\n", $col1, $col2);
	    }

	    /* ステートメントを閉じます */
	    $stmt->close();
	}
	/* 接続を閉じます */
	$mysqli->close();
?>


注意するところとしては以下の点です。

  • 名前付きプレースホルダにする場合は、最初にコロンを付ける。
    • ※mysqliでは使えませんでした・・・詳しくは追記で。
  • select文を実行する場合、$stmt->store_result()を必ず実行する
  • $stmt->fetch()をする前に$stmt->bind_result()で変数をプリペアドステートメントにバインドする


また、bind_result()で変数をバインドせずにfetch_array()やfetch_assoc, fetch_all()で連想配列で取得したい場合は、以下のようにします。（クエリに変数をバインドするところまでは省略します）

<?php
$stmt->execute(); // クエリの実行

$result = $stmt->get_result(); // ここで結果を取得。$resultは$mysqli_resultクラスとして扱える
while ($row = $result->fetch_array(){
   echo $row;
}
?>

2013/10/06追記

色々と調べたところ、どうやらmysqliでは名前付きプレースホルダは使えないようです。
PDOでは使えるのですが・・・
ということで、mysqliのときは疑問符でやっていくしかなさそうです。

それと、mysqli_stmt::get_result()を実行した時、関数がないというエラーが出ることがあります。
これは必要なmysqlドライバがインストールされていないために出るものらしいです。
もしこのエラーが出た場合は、ドライバをインストールするかget_result()を使わずにやるのが無難なようです。

ただ、Selectで持ってくるカラムが多い場合は、いちいち変数をバインドするのはめんどくさいですね。
こちらのブログではmysqli_stmtでfetch_allを実現している自作関数を紹介しています。
是非見てみて下さい。


以上です。
プリペアドステートメントは、変数をバインドすること以外はあまり変わりません。

ですのでセキュリティ的にも積極的にプリペアドステートメントを使っていきましょう！

それでは。


参考にさせていただいたサイト

PHP: Mysqli - Manual
PHPのmysqli_stmtでfetchAllっぽいものを実現する方法 - akiyan.com
PHPでデータベースに接続するときのまとめ - Qiita [キータ]

*1:？のように後から実際の値を挿入するために仮に確保したものを「プレースホルダ」といいます。