【PHP】文字列検索のSQL文・LIKEでプレースホルダを使用する

検索サイトを作りたいなと思い、ちょいちょいPHPやSQLについて勉強したり、コードを書いたりしているのですが、PHPでプレースホルダを使用するのに少し手間どったのでその備忘録です。

データベースとSQLインジェクション
検索サイトとアウトなコード
PHPでプレースホルダを使用する
コード全体
参考

データベースとSQLインジェクション

データベース（SQL）に関する脅威といえば、みなさまご存じSQLインジェクション。
不正な入力を行うことでデータベースを操作したり、データを不正に取得したりする攻撃です。

主な対策としては、
・SQL文の組み立てをプレースホルダで行う
・エスケープ処理を行う
などがあります。

具体的な内容とか対策とかについては、以下のIPAの資料などを読んでください。

安全なウェブサイトの作り方 - 1.1 SQLインジェクション | 情報セキュリティ | IPA 独立行政法人情報処理推進機構

情報処理推進機構（IPA）の「安全なウェブサイトの作り方 - 1.1 SQLインジェクション」に関する情報です。

・安全なSQLの呼び出し方（PDF）

検索サイトとアウトなコード

実装の調査用に、こんな感じの簡易データベース表示サイトを作りました。
（データについては英語版WikipediaのNPBの記事より。実行環境はMAMP。）

球団名を部分一致で検索できる入力フォームを付けています。
以下は球団名に「ス」を含む球団を検索した結果。

文字列の部分検索にはLIKEを使用します。SQL文は以下。

select 列名 from テーブル名 where 列名 like '%検索文字列%';  // 部分一致
select 列名 from テーブル名 where 列名 like '検索文字列%';  // 前方一致
select 列名 from テーブル名 where 列名 like '%検索文字列';  // 後方一致

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;
}

$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;
}

$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 ?;");

// 間違っている書き方

$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;
}

$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;
}

$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>