petitviolet blog

    PHPでPDOを使ってAND検索(OR検索)を実装する

    2014-06-16

    QiitaPHPPDO

    概要

    • 日本語で検索
    • GET パラメータをキーワードとして検索
    • PDO 使って MySQL に接続し、プレースホルダを使って SQL を構築する
    • 可変長な AND(OR)検索

    ポイント

    日本語対策

    • mb_internal_encoding('UTF-8');をファイルの先頭に書く

      • 今回は特に関係ないっぽいですが、とりあえず書いてます
    • PDO作成の際にutf8を指定する

    • MySQL の設定を変更する

      • こうなってたら動いた

        >mysql> show variables like 'character_set_%';
        
        >| Variable_name            | Value                                     |
        

        |:-------------------------|:------------------------------------------| | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 |

    tips

    • SQL の組み立てから実行まで 1. SQL 文をprepareする 1. プレースホルダに変数をbindParamする 1. executeする 1. while文の中でfetchして一行ずつ取り出す

    • プレースホルダにbindParamをする際に型を指定できる - デフォルトだと数値を入れたら文字列にされてしまう - 第 3 引数にPDO::PARAM_INTPDO::PARAM_STRを指定すれば良い

    • SQL でカラム名を指定する場合(FROM 句や ORDER BY 句) - sprintf とかで文字列として埋め込む必要がある - ホワイトリストを作って対処する - 参考:http://blog.a-way-out.net/blog/2013/12/19/sql-injection-prevention/

    • $_GET のエスケープ - htmlspecialchars()は DB から得た結果を html として出力する際に呼ぶといいらしい - プレースホルダを使って SQL を組み立てる場合は必要ない - 自分でやる場合はPDO::quoteが使えるっぽいが、prepareしましょう

    • bindParamする変数に注意する - 同じ変数を書き換えながら複数のプレースホルダにbindParamすると、その変数の最後の値が全てのプレースホルダに入ってしまう - 実行時にバインドされるっぽい - bindValueを使えばその場でバインドされる

    • SELECT文を実行した際の結果数を取得したい - 別でSELECT COUNT(*)を実行しないとだめっぽい...なんでだよ...

    AND 検索、OR 検索

    • SQL での AND 検索と OR 検索

      select * from product where name like "hoge" and (name like "foo" or name like "bar") limit 100 order by id;
      `
      
      る
      

    関数から複数の返り値を返し、受け取る

    • 返り値 - return array($foo, $bar);
    • 受け取る - list($foo, $bar) = ...;

    コード

    1. productテーブルからwhere句でnameで AND 検索をして、limitorder byを指定する

    流れ

    1. $_GETからパラメータを取り出す
    2. パラメータのバリデーション
    3. AND 検索をするために前処理
      • もちろん、1 単語なら AND 検索にはならない
    4. PDOに SQL をprepareしてプレースホルダにbindParamしてexecute
    5. 結果の取得

    function.php

    functions.php
    <?php
    mb_internal_encoding('UTF-8');
    require('./_config.php');  // MySQL接続用の定数を定義してある
    
    function connectDB() {
      // DB接続用
      try {
        // utf8で接続する
        $data_source =
          sprintf("mysql:dbname=%s;host=%s;charset=%s", DB_NAME, DB_HOST, "utf8");
        $pdo = new PDO($data_source , DB_USERNAME, DB_PASSWD);
        return $pdo;
      } catch (PDOException $e) {
        var_dump($e->getMessage());
        exit('cannot connect to database');
      }
    }
    
    
    

    search.php

    search.php
    <?php
    mb_internal_encoding('UTF-8');
    require_once('./functions.php');
    
    function productSearchAPI() {
      // $_GETからパラメータを取り出してバリデーション
      list($params, $paramsType) = getValidParamater($_GET);
      if ($params[":query"] === "") {
        exit("invalid query...");
      }
      // SQLを組み立てて実行
      $results = doSearch($params, $paramsType);
    }
    
    function getValidParamater($params){
      // GETパラメータをチェックする
      if(isset($params['query']) && is_string($params['query'])) {
        // 全角スペース => 半角
        $query = $params['query'];
        $query = str_replace(" ", " ", $query);
      } else {
        $query = "";
      }
    
      if(isset($params['limit']) && is_int($params['limit'])) {
        $limit = $params['limit'];
      } else {
        $limit = 100;
      }
      if(isset($params['orderBy']) && is_string($params['orderBy'])) {
        $orderBy = $params['orderBy'];
      } else {
        $orderBy = "id";
      }
    
      $valid_params = array(
        ":query" => $query,
        ":orderBy" => $orderBy,
        ":limit" => $limit
      );
      $paramsType = array(
        ":query" => PDO::PARAM_STR,
        ":limit" => PDO::PARAM_INT
      );
      // 複数の値を返す
      return array($valid_params, $paramsType);
    }
    
    function whiteOrder($order) {
    	// ORDER BY句に入れるためのホワイトリスト
       if (in_array($order, array("id", "name", "detail"))) {
    		return $order;
    	} else {
    		return "id";
    	}
    }
    
    function makeQuery($dbh, $params, $paramsType) {
      // SQLを組み立てる
      // もととなるSQL
      $sql = "SELECT name, detail FROM product WHERE";
      // あとで付け足すSQL
      // カラム名を指定するには文字列として埋め込む必要がある
      $orderSql = " ORDER BY ".whiteOrder($params[":orderBy"])." LIMIT :limit";
    
      $query = $params[":query"];
      if(stristr($query, " ")){
        // クエリにスペースを含む時
        $queries = explode(" ", $query);
        $queryCount = count($queries);
    
        for ($i = 0; $i < $queryCount; $i++) {
          // 最初はANDを付けない
          if($i != 0){
            $sql .= " AND";
          }
          // placeholderに番号を付ける
          $sql .= " name LIKE :query".$i;
        }
      } else {
        // スペースが無い時
        $queryCount = 1;
      }
    
      // sqlの:queryに単語をbindする
      if ($queryCount == 1) {
        // 単単語クエリ
        $sql = $sql." name LIKE :query".$orderSql;
        $query = '%'.$query.'%';
        $stmt = $dbh->prepare($sql);
        $stmt->bindParam(':query', $query, $paramsType[":query"]);
      } else {
        // 複単語クエリ
        $sql .= $orderSql;
        $stmt = $dbh->prepare($sql);
        for($i = 0; $i < $queryCount; $i++) {
          // placeholderは:query0, :query1となっている
          $placeholder = ':query'.$i;
          $queries[$i] = '%'.$queries[$i].'%';
          $stmt->bindParam($placeholder, $queries[$i], $paramsType[":query"]);
          /*
          $_query = '%'.$queries[$i].'%';
          $stmt->bindParam($placeholder, $_query, $paramsType[":query"]);
          とすると、実行時の$_queryの参照先が全て$queries[$queryCount-1]になってしまう
          bindValueにすれば上の$_queryを使って問題ない
          */
        }
      }
    
      // 後ろのlimit句にバインド
      $stmt->bindParam(':limit', $params[":limit"], $paramsType[":limit"]);
      return $stmt;
    }
    
    function doSearch($params, $paramsType) {
      // SQLを組み立てて実行
      $dbh = connectDB();
      $stmt = makeQuery($dbh, $params, $paramsType);
      $ret = $stmt->execute();
    
      // sqlを確認できる
      // $stmt->debugDumpParams();
    
      if (!$ret) {
        $err = $dbh->errorInfo();
        exit('SELECT 失敗:' . $err[2] . var_dump($params));
      }
    
      $results = makeResult($stmt);
      // 接続を閉じる
      $dbh = null;
      return $results;
    }
    
    function makeResult($stmt) {
      // MySQLから返ってきた値を辞書に格納する
      // 取得結果はカラム名をキーとする辞書になっている
      $names = [];
      while($row = $stmt->fetch()) {
        $names[] = trim($row["name"]);
        $details[] = trim($row["detail"]);
      }
      // 結果が無い場合
      // select count(*)するのが嫌だった
      if ($names === []) {
        return [];
      }
      $results = array(
        'names' => $names,
        'details' => $details
      );
      return $results;
    }
    ?>
    
    

    感想

    PHP あんまり慣れてないし間違ってるかも知れない コードがあんまり綺麗な分割じゃなくて気持ち悪いけど、とりあえず要件は満たせるはず

    型を指定する必要がなければ

    $stmt->execute($params)
    

    と出来るが、型を指定するにはいちいちbindParamしないとだめでめんどくさい

    from: https://qiita.com/petitviolet/items/a3e96a44898bebcb07ce