検索サイトを作りたいなと思い、ちょいちょいPHPやSQLについて勉強したり、コードを書いたりしているのですが、PHPでプレースホルダを使用するのに少し手間どったのでその備忘録です。
データベースとSQLインジェクション
データベース（SQL）に関する脅威といえば、みなさまご存じSQLインジェクション。
不正な入力を行うことでデータベースを操作したり、データを不正に取得したりする攻撃です。
主な対策としては、
・SQL文の組み立てをプレースホルダで行う
・エスケープ処理を行う
などがあります。
具体的な内容とか対策とかについては、以下のIPAの資料などを読んでください。
・安全なSQLの呼び出し方（PDF）
検索サイトとアウトなコード
実装の調査用に、こんな感じの簡易データベース表示サイトを作りました。
（データについては英語版WikipediaのNPBの記事より。実行環境はMAMP。）
球団名を部分一致で検索できる入力フォームを付けています。
以下は球団名に「ス」を含む球団を検索した結果。
文字列の部分検索にはLIKEを使用します。SQL文は以下。
select 列名 from テーブル名 where 列名 like '%検索文字列%'; // 部分一致
select 列名 from テーブル名 where 列名 like '検索文字列%'; // 前方一致
select 列名 from テーブル名 where 列名 like '%検索文字列'; // 後方一致今回の検索文字列を受け取って、SQLの実行と結果を取得するPHPのコードが以下です。
HTMLで入力された値を確認も何もしていないうえに、そのままSQL文に入れています。ちなみにクライアント（HTML、JavaScriptは使ってない）側でも入力チェックなどは特に何もしてません。SQLインジェクションしてくれと言わんばかりのスリーアウトなコードです。野球だけに。
$word = $_GET['word']; // HTMLから入力受付
$sql = "select * from teams where team like'%".$word."%';";
$re = $s->query($sql);
while($row = $re->fetch()){
$rows[] = $row;
}これにプレースホルダを適用してセーフなコードにします。
PHPでプレースホルダを使用する
『PHP プレースホルダ』とかで検索すると、prepareメソッドを使用することが分かりました。
いろいろ出てきたコードを基に、上記のコードを修正してみました。
$word = $_GET['word']; // HTMLから入力受付
$stmt = $s->prepare("select * from teams where team like %?%;");
$params = (1, "%".$word."%");
$re = $stmt->execute($params);
while($row = $re->fetch()){
$rows[] = $row;
}が、上手くいかないというか画面に何も表示されず真っ白になる。
更にいろいろ調べたところ、LIKEの場合のprepareの書き方が間違っていたらしい。
// 間違っている書き方
$stmt = $s->prepare("select * from teams where team like %?%;");
// 正しい書き方 値をセットする時に%%を使うっぽい
$stmt = $s->prepare("select * from teams where team like ?;");他にもたくさん間違っていたところがありました（bindValueを使用したものとか、元々のコードと混乱したのが原因）。
正しいコードはこちら。
$word = $_GET['word']; // HTMLから入力受付
$stmt = $s->prepare("select * from teams where team like ?;"); // <-正しい書き方
$params = ("%".$word."%"); // <-正しいセットの仕方
$stmt->execute([$params]); // <-配列で渡さないとダメ
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$rows[] = $row;
}ちゃんとプレースホルダ未使用の時と同じような表示結果が得られました！
なおbindValueを使う場合にはexecuteの引数が不要。
$word = $_GET['word']; // HTMLから入力受付
$stmt = $s->prepare("select * from teams where team like ?;"); // <-正しい書き方
$stmt->bindValue(1, "%".$word."%");
$stmt->execute();
while($row = $stmt->fetch()){
$rows[] = $row;
}コード全体
一応PHPとHTMLのコード全体も載せておきます。PDOのテーブル名、ID、PASSWORDには実際の値を入れてください。
調査用の簡易的なものなので、いろいろ雑なのはスルーしてください。
<?php
try{
$s = new PDO("mysql:host=localhost;dbname=テーブル名","ID","PASSWORD"); // 値適宜入れる
$s->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$word = $_GET['word']; // HTMLから入力受付
$stmt = $s->prepare("select * from teams where team like ?;"); // プレースホルダ
$params = ("%".$word."%"); // <-値のセット
$stmt->execute([$params]); // <-SQL実行
while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
$rows[] = $row;
}
}
catch(PDOException $e){
print "エラー内容:".$e->getMessage();
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
<meta charset="utf-8">
</head>
<body>
<form action="" method="GET">
<div>
球団名:<input type="text" name="word" value="<?php echo $_GET['word']?>">
</div>
</form>
<table border="1" style='white-space: nowrap'>
<tr style="background-color: lightskyblue">
<th>球団名</th>
<th>リーグ</th>
<th>本拠地</th>
<th>設立年月日</th>
</tr>
<?php
foreach($rows as $row){
?>
<tr>
<td><?php echo $row['team']?></td>
<td><?php echo $row['league']?></td>
<td><?php echo $row['city']?></td>
<td><?php echo $row['founded']?></td>
</tr>
<?php
}
?>
</table>
</body>
</html>データベースはこんな感じでした。
セリーグ球団の設立年というか設立順は知ってたけど、パリーグの方は全然知らなかったので勉強になりました。あとみんな11月~2月の間なんだなーと思ったけど単にオフシーズンってだけですね。
今年は結構荒れそうなので楽しみ半分不安半分だ……。
参考
以下のサイトを参考に解決することができました。ありがとうございます。
コメント