こんにちは。ライターの亀田です。
CakePHPでは、データベースにアクセスする方法が大きく分けて二つあります。
一つは、ORMと言う、フレームワーク独自の方法を用いる方法、そしてもう一つが、SQL命令を直接実行する方法です。
この記事では、後者について説明します。
まずは、
・sqlとは
・sqlの基本的な使い方
のように、sqlに関する説明から始まり、
・CakePHPでSQLクエリを実行する
・SQL文をlogに出力する方法
・セキュリティ対策（SQLインジェクション）
といった流れで、実用的な使い方について説明していきます。
sqlとは
EXCELやスプレッドシートのような表の形式でデータを管理しているタイプのデータベースを、一般に、リレーショナルデータベースと言います。
リレーショナルデータベースには様々な種類が有りますが、SQLという言語で処理を行うのが一般的です。
SQLで操作できるデータベースは様々な種類があります。
有名なものに、「MySQL」,「PostgresSQL」,「SQLite」,「SQLServer」,「Oracle」などがあげられます。
皆SQL文の命令で操作しますが、独自に拡張した命令や方言などが存在します。
sqlの基本的な使い方
CakePHPでは、先にあげた様々なデータベースにアクセスすることが可能です。
ここでは、手軽な方法としてWindowsのXAMPPを用いてMySQLにアクセスするケースについて説明しましょう。
XAPPで、MySQLを使用する方法は簡単です。XAMPPをダウンロードし、任意のディレクトリに展開したら、コントローラーを起動させてください。
XAMPP
ApacheのStartボタンを押すとApacheが、MySQLのStartボタンを押すと、MySQLが起動します。
ApacheとMySQLを起動すると、ローカルで、データベースの操作を行う、phpMyAdminを利用することが出来ます。
http://localhost/phpmyadmin/
と入力してください。すると、phpMyAdminが利用できます。これで、簡単にXAMPPのMySQLを操作することが出来ます。
phpMyAdmin
これで準備が完了です。
では、実際にここに簡単なテーブルを作成し、testと言うデータベースに、hogeというデータベースを作り、データを投入してみましょう。
まずはメニューからSQLを選んでください。そこで、SQL文を実行することが出来るようになります。
ここで、まずデータベースtestを選択します。
use test;
これで、以下の操作はデータベースtestで行えるようになります。
次は、テーブルを生成しましょう。
CREATE TABLE hoge(
id int,
name varchar(16)
);
さらに、ここに、データを投入してみましょう。
INSERT INTO hoge VALUES(1,"Tom"); INSERT INTO hoge VALUES(2,"Mike"); INSERT INTO hoge VALUES(3,"Steve");
次に、これをCakePHPを用いて表示してみましょう。
CakePHPでMySQLにアクセスするには、あらかじめ設定をする必要があります。
config/app.phpに、以下のように設定してください
'Datasources' => [
'default' => [
…（中略）…
//'port' => 'non_standard_port_number',
'username' => 'root',
'password' => '',
'database' => 'test',
'encoding' => 'utf8',
'timezone' => 'UTC',
'flags' => [],
'cacheMetadata' => true,
'log' => true,
usernameおよびpasswordは、MySQLにアクセスするためのユーザー名およびパスワードです。
databaseはアクセスするデータベース名（この場合は、test)としてください。
デフォルトで設定されているログの設定を、
'log' => false,
となっているのを、以下のように
'log' => true,
と変更してください。これは、MySQLの操作と直接関係はありませんが、後ほど説明するログを出力するために大切な設定です。
なお、rootを直接つかって接続するのは簡便ですが、セキュリティなどの観点からあまり行儀の良い方法とは言えません。
そこで、接続用のユーザーを作り、そのユーザーからCakePHPにつなげる方法も覚えておきましょう。
まずはユーザーを作成し、パスワードと権限を設定します。
phpMyAdminに以下のSQL文を入力してください。名前とパスワードは自由に決めてもらって構いません。
create user 'cakephptestdbuser'@'localhost' identified by 'passwordstringAngelF00dC4k3~'; grant all on test.* to 'cakephptestdbuser'@'localhost' identified by 'passwordstringAngelF00dC4k3~';
そして上と同様に、config/app.phpに以下の設定を書き込みます
'Datasources' => [
'default' => [
…（中略）…
//'port' => 'non_standard_port_number',
'username' => 'cakephptestdbuser',
'password' => 'passwordstringAngelF00dC4k3~',
'database' => 'test',
'encoding' => 'utf8',
'timezone' => 'UTC',
'flags' => [],
'cacheMetadata' => true,
'log' => true,
CakePHPでSQLクエリを実行する
queryメソッドの使い方
次に、このデータベースにアクセスし、SELECT文で、hogeテーブルの内容を表示するサンプルを作って見ましょう。
src/controller/SqlController.php
<?php
namespace App\Controller;
use App\Controller\AppController;
use Cake\Datasource\ConnectionManager;
class SqlController extends AppController {
function index() {
// SQL文
$sql = "SELECT * FROM hoge";
// DB接続を取得
$connection = ConnectionManager::get('default');
// SQLのログ出力を有効化
$connection->logQueries(true);
// SQL文の設定
$data = $connection->query($sql)->fetchAll('assoc');
// SQLのログ出力を無効化
$connection->logQueries(false);
// 出力結果のデータを渡す
$this->set('data', $data);
}
}
src/Template/Sql/index.ctp
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
</head>
<body>
<h1>実行結果</h1>
<table style="border:1px; width:400px;"><tr><th>ID</th><th>名前</th></tr>
<?php
print '';
foreach($data as $element){
print "<tr><td>{$element['id']}</td><td>{$element['name']}</td></tr>\n";
}
?>
</table>
</body>
</html>
以上の設定をしたら、
http://localhost/[cakephpをインストールしたディレクトリ]/sql
としてください。
すると、以下のようにして、hogeテーブルの内容が表示されます。
SQL文を設定するのは、queryメソッドです。
$data = $connection->query($sql)->fetchAll('assoc');
queryメソッドでSQLを設定し、fetchAllメソッドで、結果を取得します。
この処理は分割することも出来ますが、SELECT文のような処理の場合、このように一つの行で実行してしまうと地大変便利です。
結果が、$data変数に入り、最後に、この値をテンプレートに渡すため、結果が画面に表示されます。
実行結果
非常に簡単ですね。
なお、queryメソッドやSQLの命令文を実行できるクエリービルダー(QueryBuilder)については、以下の記事でも詳しく解説しています！
SQL文をlogに出力する方法
ところで、このサンプルには、
$connection->logQueries(true);
および、
$connection->logQueries(false);
という箇所が有ります。
最初が、ログを残す処理を開始する、そして、次の処理が、ログを残す処理を終了します。
この2つのステートメントで挟まれた部分のSQLは、ログファイルに記述されます。
なお、出力されるファイルは、logs/queries.logにあります。
中を見てみると、以下のようになっていることがわかります。
2017-08-07 07:11:13 Debug: duration=1 rows=3 SELECT * FROM hoge
このように、実行されたSQL文が表示されます。
データベースのアクセスのデバッグは、非常に大変なので、この方法を用いるとプログラマーはとても楽になります。
特に、次に説明するSQLインジェクションというセキュリティ対策を施すと、なおさらどのようなSQL文が実行されているかが、わからなくなります。
そのため、このログをとる処理は非常に大事です。
セキュリティ対策（SQLインジェクション）
CakePHPに限らず、プログラムの中で直接SQL文を発行する際に気をつけなくてはならないのが、セキュリティ対策、特にSQLインジェクション対策と呼ばれるものです。
SQLインジェクションとは、アプリケーションからのデータベースアクセスにSQLを使用している際に、その呼び出し方に不備があるときに発生する脆弱性（ぜいじゃくせい）のことです。
これにより、データを盗まれたり、改竄(かいざん)されたりする可能性があります。
今回のソースコードの場合はサンプルだから良いのですが、あからさまにテーブル名やカラム名などがすぐにわかるようなプログラムになっていると、何らかの方法でその情報を盗まれてしまう可能性があります。
商用アプリの場合、SQL対策がなされていないと損害賠償を求められる可能性もあります。
ですから、直にSQLを操作するアプリを作る場合は、以下のような情報を参考にして、SQLインジェクション対策を怠らないようにしましょう。
■MySQLインジェクション対策
PHP での SQL インジェクション対策はどのように行いますか？
■CakePHPのセキュリティ対策
まとめ
この記事では、CakePHPでsqlを扱う際に
・sqlとは
・sqlの基本的な使い方
・CakePHPでSQLクエリを実行する
・SQL文をlogに出力する方法
・セキュリティ対策（SQLインジェクション）
といったトピックについて解説しました。
CakePHPでデータベースにアクセスする方法は、複数ありますが、
sqlのクエリを直接実行するのは手軽な方法です。
しかし、同時にセキュリティホールを生み出してしまう可能性もあります。
十分に気をつけてプログラミングしましょう。
