PDOのbindValueで狙った検索結果が取れない

Question

度々お世話になります。
MySQLの検索で狙った結果がどうしても取れず、
万策尽きたので質問をさせていただきます。

やりたいこと
情報テーブルのtitleから、ユーザが検索した情報を表示したい。
「hoge」でhogefugaを表示。
「fuga」でhogefugaとfugaを表示
「hoge　fuga」でhogefugaとfuganekohogeを表示。

情報テーブル

id	title
1	hogefuga
2	fuga
3	fuganekohoge

try {
  $con = new PDO(
      'mysql:host=localhost;dbname=hoge;charset=utf8mb4',
      'root',
      '',
      [
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      ]
  );

} catch (PDOException $e) {

  header('Content-Type: text/plain; charset=UTF-8', true, 500);
  exit($e->getMessage()); 

}

<h3 class="leave-comment-text">Search</h3>
<form action="search.php" method="get">
  <input name="keywords" placeholder="Search" type="text">
  <button class="submit" type="submit" name="submit"> <i class="fa fa-search" aria-hidden="true"></i> </button>
</form>

if(isset($_GET['submit'])) {
    $keywords = $_GET['keywords'];
  }
$kw = mb_convert_kana($keywords, 's');
$kw_arr = preg_split('/[\s]+/', $kw, -1, PREG_SPLIT_NO_EMPTY);
$keywordCondition = [];
foreach ($kw_arr as $keyword) {
$keywordCondition[] = 'watch_title LIKE "%' . $keyword . '%"';
}
$keywordCondition = implode(' AND ', $keywordCondition);

試したこと

$search = $con->query("
SELECT * FROM kensaku
WHERE $keywordCondition
LIMIT $page_1, $per_page // ページャー
");

$searches = $search->fetchAll(PDO::FETCH_OBJ);

上記だと求めた検索結果が出ますが、
セキュリティ的に良くないと伺ったのでprepareを使用することにしました。

上手くいかないコード

$search = $con->prepare("
SELECT * FROM watch
WHERE :keywordCondition
LIMIT :page_1, :per_page
");
$search->bindValue(':keywordCondition', $keywordCondition, PDO::FETCH_OBJ);
$search->bindValue(':page_1', (int)$page_1, PDO::PARAM_INT);
$search->bindValue(':per_page', (int)$per_page, PDO::PARAM_INT);
$search->execute();
$searches = $search->fetchAll(PDO::FETCH_OBJ);

上記の$search->bindValue(':keywordCondition', $keywordCondition, PDO::FETCH_OBJ);
の部分の第3引数に様々な定数があったので下記URLより全パターン試しましたが、
白紙か、テーブルの全てのtitleが出力されました（絞り込みできない）
https://www.php.net/manual/ja/pdo.constants.php

$search = $con->prepare("
SELECT * FROM watch
WHERE ?
LIMIT ?, ?
");
$search->bindValue(1, $keywordCondition, PDO::FETCH_OBJ);
$search->bindValue(2, (int)$page_1, PDO::PARAM_INT);
$search->bindValue(3, (int)$per_page, PDO::PARAM_INT);
$search->execute();
$searches = $search->fetchAll(PDO::FETCH_OBJ);

名前付きプレースホルダだと上手く行かないと思い、
疑問符プレースホルダでも試してみましたが名前付きプレースホルダの時と
同様に白紙か、テーブルの全てのtitleが出力されました（絞り込みできない）

以上、期待した結果を得るためのお知恵をお貸し頂ければと思います。
どうぞよろしくお願いいたします。

Comments

[退会済みユーザー]

うまくいかないコードがとおるようだとSQLインジェクションし放題です。

[terapro]

本番前に自分で自分に攻撃してみないといけないですね。ありがとうございます。

Answer 1

基本

$prepare = "SELECT * FROM `example` = ?"

は可能ですが

$prepare = "SELECT * FROM ?"

は使用できません。

考え方

$search = $con->prepare("
SELECT * FROM watch
WHERE :keywordCondition
LIMIT :page_1, :per_page
");

ではなく

$condition = '`field_1` = ? AND `field_2` = ?';
$search = $con->prepare('
SELECT * FROM watch
WHERE '.$condition.' LIMIT ?, ?');

と言う感じで$conditionを組み立てましょう。
その際には$conditionにユーザーからの入力を直接組み込んでしまわないように注意する必要があります。（フィールド名についてはホワイトリスト形式でチェックするなど）

例)

// Your code here!
//すべてAndで絞るとして、$conditions["フィールド名"][条件の値]の場合
$conditions["name"] = ["a","b"];
$conditions["title"] = ["c","d"];

//となった場合、
//`name` = ? AND `name` = ? AND `title` = ? and `title` = ?
//という文字列を作りたいので
$conditionAndList = [];
foreach($conditions as $fieldName => $valueList){
  foreach($valueList as $value){
    $conditionAndList[] = '`'.$fieldName.'` = ?';
    //後でbindValueするとき用の配列
    $bindedValueList[] = $value;
  }
}

$conditionPrepare = implode(" AND ",$conditionAndList);
//echo $conditionPrepare の結果は
//`name` = ? AND `name` = ? AND `title` = ? AND `title` = ? こんな感じになる

//var_dump($bindedValueList);の結果は
/* こんな感じ
array(4) {
  [0]=>
  string(1) "a"
  [1]=>
  string(1) "b"
  [2]=>
  string(1) "c"
  [3]=>
  string(1) "d"
}
*/

//中略

//動的に作成した?の数だけbindValueする
foreach($bindedValueList as $key => $bindedValue){
  $search->bindValue($key+1,$bindedValue);
}

//limitで使う固定的な数の?は普通にbindValueする

//後略

Comments

[terapro]

ご指摘ありがとうございます。
ただ、ユーザーは「hoge」「hoge fuga」「hoge fuga neko」・・と
一語、二語、三語と検索する可能性があるので、ANDの数を固定できず思案しております。

[tanat]

ANDの数を固定するのではなく、
?の数に合わせてbindValue()を複数回実行する方向で実装ですね。

[terapro]

すいません。。そうしたいのですがどのように書けばよいかわかりません。。

苦肉の策として、
if(isset($_GET['submit'])) {
$keywords = $_GET['keywords'];
$keywords = htmlentities($keywords, ENT_QUOTES, "utf-8");
}
とした上で、
$search = $con->query("
SELECT * FROM kensaku
WHERE $keywordCondition
LIMIT $page_1, $per_page // ページャー
");

$searches = $search->fetchAll(PDO::FETCH_OBJ);

としてしまおうかと考えているところですが、
もし、サンプルコードをご提示頂けるのであれば、是非お願いしたいです。
よろしくお願いいたします。

[m.ts10806]

（横からすみません。どうしてSQL構築時にHTMLエスケープ入れる人が後を絶たないんだろう・・・）

[terapro]

すいません。私の現在の能力ではここまでしか思いつかないのです。。
お助けください。

[m.ts10806]

いえ、htmlentities()なりhtmlspecialchars()の話ですけど、
思いつく云々ではなく「無意味」というのと「非推奨」ということです。
少なからず文字列を変換してしまうことになるので。
画面出力の時にのみ使うものです。

[tanat]

サンプルを追記してみたのでヒントにして考えてみてください。（もっと効率的な書き方はできますが、1行ずつ読みやすいような形で書きました）
* PDOというよりは、PHPでの配列と文字列の扱いの練習という要素が強いと思います。
まずは理想的な$prepareを定義して、どうすればそれを実現できるかについて簡単な例から順に考えてみてください。
自力で実装するのは割と面倒な部類になると思いますが、学習にはいい題材だと思います。

[terapro]

大変参考になりました。私の頭では理解するのに丸1日掛かりましたが良い勉強になりました。短時間でこんな風にコードが書けるのは本当に凄いです。ご指南ありがとうございました。

[退会済みユーザー]

どこぞの「気づけば～」著者のせいじゃね　→ htmlspecialchars

[terapro]

その著者さんのことは知りませんが、htmlspecialcharsで済むならコード書くのが楽なので安易にやりがちだと思います。←安易な人
PDOだとintがstringになるし、本当にセキュリティを意識したコードを一瞬で書ける人たちは神ですね。尊敬しかありません。

[退会済みユーザー]

デバッグをしてないコードを平気で書籍にしちゃう著者

あと、「FBで質問してる人に対して、私は見つけたけど見つけれてないんですか?」とかあおる著者

テンプスタッフの取締役の一人になってるようだけど

[kyoya0819]

m.ts10806さん

> どうしてSQL構築時にHTMLエスケープ入れる人が後を絶たないんだろう・・・

聞いたことがある話だと
「え？エスケープしなきゃ！DBに保存する前にやっとけばそれ使いまわせるじゃーん！！効率も、速度も速くね？俺天才じゃーん！」（一部誇大表現）
的な発想の教師の方がいるらしいです。

[退会済みユーザー]

それが　プロ並みの著者なのだよｗ

[m.ts10806]

「データの加工」という認識がないのはいかんですね。画面表示だけじゃなくて検索とかにも使うのに

Answer 2

前提として、SQLを実行するときのカラム名やテーブル名などにプリペアドステートメントは使えません。

Comments

[terapro]

カラム名やテーブル名などとは、つまり$keywordConditionにANDが入っているからダメということでしょうか？

[m.ts10806]

いえ、バインドするのは「値」です。
下記で言えば「:keywordCondition」という使い方はできません。
SELECT * FROM watch
WHERE :keywordCondition
LIMIT :page_1, :per_page

これは変数ではなく、あくまで「値」を適切に与えるための手段だからです。
watch_title LIKE '%hoge%' は「式」です。
watch_title LIKE :title とした上で、
$search->bindValue(':title ','%'.$keyword.'%');
としなければなりません。
もし「検索条件の有無で動的にWhere句が変化する」のであれば、
SQLを作る部分、バインドさせる部分も同じ条件で与える与えないを分岐させる必要があります。

[terapro]

ありがとうございます
> $search->bindValue(':title ','%'.$keyword.'%');
この考え方、非常に参考になりました。

[m.ts10806]

この考え方、、、というか仕様ですし基本です。考え方の問題ではないです。
https://www.php.net/manual/ja/pdostatement.bindvalue.php
https://qiita.com/mpyw/items/b00b72c5c95aac573b71#pdoprepare--pdostatementbindvalue--pdostatementexecute-%E3%81%AE3%E3%82%B9%E3%83%86%E3%83%83%E3%83%97%E3%81%A7%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B

[terapro]

はい、ただこの仕様だとユーザが
カラム名 LIKE %hoge%
カラム名 LIKE %hoge% AND カラム名 LIKE %fuga%
カラム名 LIKE %hoge% AND カラム名 LIKE %fuga% AND カラム名 LIKE %neko%
と、一語、二語、三語と分けて検索した場合にどのように書いてよいか分からず、思案しております。

[m.ts10806]

繰り返すことになりますが、
もし「検索条件の有無で動的にWhere句が変化する」のであれば、
SQLを作る部分、バインドさせる部分も同じ条件で与える与えないを分岐させる必要があります。

あればSQLに追加しバインドも追加
なければなにもしない

です。

$wherecon = [];
if($hoge !== ""){
$wherecon[] = " word like :hoge ";
}
//中略

$sql = " SELECT * FROM watch " ;
$sql .= (count($wherecon) > 0)? " where ".implode(" and ",$wherecon) : "";

//中略

if($hoge !== ""){
$stmt->bindValue(':hoge','%'.$hoge.'%');
}

[terapro]

いつも厳しくご指摘を頂きありがとうございます。最初は途方に暮れておりましたが、こちらのヒントでようやく実装が出来ました。おかげさまで無意味なHTMLエスケープで未来に禍根を残すような安易なコードにならずに済みました。本当に、本当にありがとうございます。
何度も何度も書き直し、動かなくて動かなくて、なんて才能がないんだと呆れる毎日でしたが、もっともっと美しいコードが書けるように頑張りたいと思います。
本当にありがとうございました。

[退会済みユーザー]

% をちゃんとエスケープしとかないと、未来に禍根を残すよｗ

[m.ts10806]

この辺ですね。
https://qiita.com/mpyw/items/b00b72c5c95aac573b71#like%E6%A4%9C%E7%B4%A2

[terapro]

なぬ。。（゜Σ゜）ﾓｳﾔﾀﾞ･･

[terapro]

te2jiさん、ご指摘ありがとうございます。おかげさまで、今度こそ未来に禍根を残さずに済みそうです。

[terapro]

m.ts10806さん、いつも本当にありがとうございます。またエラーを吐いたら私も吐きそうでしたが頂いた参考URLのおかげで難なく実装できました。ありがとうございました。