SELECT文をPHPで簡単に生成しよう
皆さんは苦手な物はありますか?
私は,SQL文が大の苦手です。
しかし,最近は業務の都合でDB周りに触れ始めてきました。
業務で使うとなれば嫌いだの苦手など言ってられませんがSQLは書きたくない,そんな私のような人に向けて
SQL文を生成する関数を作ってみました。
環境
PHP : 8.1.6
MariaDB : 10.4.24
Apache : 2.4.53 (Win64)
テーブル構成
テーブル名:thread
今回は2チャンネルのような掲示板の簡易版を制作をすることを仮定して進めていきます。
SELECT文の生成
SELECTで受け取ったデータを一覧表示するdisp.php
<?php
require_once 'method/SQL_select.php';
// ここで呼び出す
// DB_select('テーブル名', 'カラム名=??'...)第一引数は必須
$result = DB_select('thread');
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="UTF-8">
<title>掲示板</title>
<link rel="stylesheet" href="style.css">
</head>
<body>
<h1>スレッド一覧</h1>
<table>
<?php foreach ($result as $row) {?>
<tr>
<?php
$thread_id_param = 'thread_id='.$row['id'];
$url = 'url.php?'.$thread_id_param;
?>
<td><a href="<?php echo $url;?>"><?php echo htmlspecialchars($row["title"])?></a></td>
<td><?php echo $row['create_time']?></td>
</tr>
<?php } ?>
</table>
</body>
</html>
SELECT文を生成するSQL_select.php
<?php
function DB_select() : array {
// DB接続===================================
//伏せてます
$user = 'XXXX';
$dbName = 'XXXXXX';
$password = 'XXXXXX';
$host = 'localhost';
$dsn = "mysql:host={$host};dbname={$dbName};charset=utf8";
// DE接続終わり================================
//PDOオブジェクトもといインスタンスの生成
$pdo = new PDO($dsn, $user, $password);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL文を格納する変数$sql
$sql = "SELECT * FROM";
// 引数を配列で取得
$args_list = func_get_args();
// 引数の数を取得
$args_quantity = func_num_args();
// １つ目の引数はテーブル名であるためstr変数$sqlに最初に格納
$table = $args_list[0];
$sql .= " $table";
// １つ目の引数はテーブル名であるためstr変数$sqlに最初に格納終わり
// 引数が二個以上ある際にはWHERE句とそれ以降の文を生成
// 引数の数だけ処理を行う なお引数は"カラム=??"の形式のみを想定する
if (isset($args_list[1])) {
// カラムを格納するリスト
$column_date_list=[];
// $sqlにWHERE句を格納
$sql.= " WHERE";
for ($i=1; $i < $args_quantity;) {
$column = $args_list[$i];
// 例:カラム名= の形のSQLを想定しているためexplode()でイコールの前後で文章を配列で区切る
$column = explode("=", $column);
// SQlインジェクション対策でPDO->bindvalue()で使うために配列に格納
array_push($column_date_list, [$column[0], $column[1]]);
// $this->sqlにカラム文字列を追加+SQLインジェクション
$sql.= " $column[0] = :$column[0]";
$i++;
// 最後の引数を処理したあとは";"をSQL文に追加,最後じゃなければ"&&"を格納し続行
$i >= $args_quantity ? $sql .= ";" : $sql .= " &&";
}
}
$stm = $pdo->prepare($sql);
// 引数(カラム名)を格納したリスト$column_date_listのデータをSQLインジェクション対策用に
// プリペアドステートメントbindValue()を一つずつ実行
if (isset($column_date_list)) {
foreach ($column_date_list as $column_date) {
// 受渡される$column_dateは配列['カラム名','条件式']になる
//bindValueセット
$stm->bindValue(":$column_date[0]", $column_date[1]);
}
}
}
$stm->execute();
$result = $stm->fetchall(PDO::FETCH_ASSOC);
return $result;
}
?>
結構長いコードになりましたね。ここからコードについて解説していきます。
$sql = "SELECT * FROM";
$args_list = func_get_args();
$args_quantity = func_num_args();
単純ですが$sqlにSQL文の文字列を格納していくだけのコードです。関数自体は引数を指定していないですが,第一引数は必須です。
DB_select(str"DBテーブル名",str"テーブルカラム名=??"...)
このような形を想定しています。
func_get_argsで引数全体を配列として受け取りその数に応じてWHERE句やANDを生成していきます。
$table = $args_list[0];
$sql .= " $table";
第一引数はテーブル名を想定してるので
SELECT * FROM $table
の形になるように$sqlにテーブル名を文字列として格納していきます。
if (isset($args_list[1])) {
$column_date_list=[];
$sql.= " WHERE";
for ($i=1; $i < $args_quantity;) {
$column = $args_list[$i];
$column = explode("=", $column);
array_push($column_date_list, [$column[0], $column[1]]);
$sql.= " $column[0] = :$column[0]";
$i++;
$i >= $args_quantity ? $sql .= ";" : $sql .= " &&";
}
}
第二引数以降(WHERE句以降)がある場合はここの部分が実行されます。
$column_date_list=[];
ここで$column_date_listという配列を生成しています。
これは,後述するSQLインジェクション対策用にカラムと条件式をそれぞれ格納していくためのリストになります。
カラムを使うということはWHERE句を使うという事になるので$sqlにWHEREを文字列で追加していきます。
この時点で$sqlの中身は
SELECT * FROM $table WHERE
になっています。
for ($i=1; $i < $args_quantity;) {
$column = $args_list[$i];
$column = explode("=", $column);
array_push($column_date_list, [$column[0], $column[1]]);
$sql.= " $column[0] = :$column[0]";
$i++;
$i >= $args_quantity ? $sql .= ";" : $sql .= " &&";
}
第二引数以降は
str"テーブルカラム名=??"
を想定しています。
この文をそのまま$sqlに格納といきたいのですが,世の中にはSQLインジェクション攻撃をしてくる不届き者がいるらしいので今回はそれの対策を踏まえ"カラム名"と"??"の部分を一旦分けます。
$column = ["カラム名", "??"]
こんな感じになります。そしてこれを先程定義した,$column_date_listに格納していきます。
その後,$sqlに"カラム=:カラム名"の形で格納していきます。
そしてこのfor文の最後の部分
$i >= $args_quantity ? $sql .= ";" : $sql .= " &&";
これは
if ($i >= $args_quantity) {
$sql .= ";";
}else{
$sql .= " &&";
}
を省略した形になります。ここの部分は引数が最後であれば$sqlに";"それ以外であれば" &&"を格納しています。
SQLの生成文自体はここで終了です。一度ここでexitとechoを使いSQL文を見てみましょう。
まずはテーブル全体を取得するSQLを生成します。
$result = DB_select("thread");
echo($result)
SELECT * FROM thread
期待通りですね
次にWHERE句を用いて条件指定も見てみます。
$result = DB_select('thread', 'user_id=5', 'title=test');
echo($result);
SELECT * FROM thread WHERE user_id = :user_id && title = :title;
ちゃんとSELECT文を生成できていますね。
ではここからは実際にSELECT文を動かせるようにしてみます。
SQLインジェクション対策
そもそもSQLインジェクションとは？簡単に説明すると,SQLインジェクションとは、アプリケーションの脆弱性により本来の意図ではない不当な「SQL」文が作成されてしまい,実行されることによって,データベースを不正に操作される攻撃です。 悪意のあるユーザー(不届き者)がWebアプリに悪意のあるSQLを埋め込んでデータベースを不正な操作したり消したりめちゃくちゃにしてくることです。Webアプリを開発するエンジニアはこういった攻撃の対策も忘れてはいけません。
それでは早速,SQLインジェクション対策済みのSQLの生成をやっていきましょう。
プリペアドステートメント
プリペアドステートメントとは?簡単に説明すると, SQL文に対して一部分的を変更の可能な場所として定義し、定義したものを自由に変更できるようにするためのデータベース機能のことを指します。 PHPでプリペアドステートメントを使用する場合は,prepare()関数を使用することで可能となります。
prepare()関数を使用するためには、PDOオブジェクトを生成しなければいけません。
PODオブジェクトは、データベースを利用するために重要なオブジェクトとなっています。
そしてそのオブジェクトの定義はコードの最初の方に定義しています。
$pdo = new PDO($dsn, $user, $password);
PDOオブジェクトの生成時にデータベースの接続情報を渡すことで、対象となるデータベースに対してSQLを実行することが可能になります。
プリペアドステートメントを利用してクエリを実行するには以下の流れで実行していきます。
1.PDOオブジェクトの作成 //ここまで終わってる
↓
2.prepareメソッドでSQL文をセット
↓
3.bindValue or bindParamでパラメータに値をセット
↓
4.executeメソッドでクエリを実行
既に1は終わっているためここからは2以降をやっていきます。
$stm = $pdo->prepare($sql);
if (isset($column_date_list)) {
foreach ($column_date_list as $column_date) {
$stm->bindValue(":$column_date[0]", $column_date[1]);
}
}
pdoインスタンスのprepare関数に生成したsqlをセットします。
次にカラム(WHERE句以降)がある場合はbindValue()にパラメータをセットしていきます。
ここで$column_date_listからcolumn_dateに要素を一つずつ渡していきます。$column_dateの中身は
$column_date["カラム名", "??"];
になっています。
そしてこのカラムと??をvindValueにセットすることでSQLインジェクションをエスケープすることができます。
$stm->bindValue(":$column_date[0]", $column_date[1]);
何故エスケープができるかについてはここでは割愛させていただきます。
気になる方はぜひ調べてみてください。
投稿主が参考にしているサイトでもプリペアドステートメントについて解説している物もありましたのでリンクを貼りますので,興味があれば一読してみてください。
【PHP学習】プリペアドステートメントの基礎知識と活用方法
PDO prepare プリペアドステートメントの使い方
$stm->execute();
$result = $stm->fetchall(PDO::FETCH_ASSOC);
return $result;
最後はexecute()で実行し結果を返しています。
では実際に動かしてみましょう。
テストデータ入れ,まずは全部のスレッドを取得してみます。
$result = DB_select('thread');
成功です。
続いては,user_id=3の人のスレッドを抽出してみます。
$result = DB_select('thread', 'user_id=3');
成功です。
user_id 3番の人結構個性的なスレを立ててますね。
以上でSQL(SELECT)を生成する関数の紹介は終わりです。
これ,SELECTの部分をDELETEに変えるだけでDELETE文の生成もできちゃいますね。