質問編集履歴

$dbのreturnを削除し、以下を可変長プレースホルダの形に書き換えました。また、HTMLのhtmlspecialchars部分をphp内に記述してみました。

2019/11/26 15:32

投稿

TaraIkura

スコア5

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -170,91 +170,73 @@
 function getUserData() {
-  //DBの接続情報
   include_once( 'database.php' );
-  //DBコネクタを生成
-  try {
+   try {
     $db = new PDO( $dsn, $usr, $passwd );
-    $db->exec( 'SET NAMES utf8' );
     $db->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
     $db->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );
+    $q = ( string )filter_input( INPUT_GET, 'q' );
+    $maxKeywords = -1;
+    $keywords = preg_split( '/(?:\p{Z}|\p{Cc})++/u', $q, $maxKeywords, PREG_SPLIT_NO_EMPTY );
+    if ( $keywords ) {
+      foreach ( $keywords as $keyword ) {
+        $holders[] = "((search_name LIKE ? ESCAPE '!') OR (color LIKE ? ESCAPE '!') OR (category LIKE ? ESCAPE '!'))";
+        $values[] = $values[] = '%' . preg_replace( '/(?=[!_%])/', '!', $keyword ) . '%';
+      }
+      $sql = "SELECT * FROM TABLE1 WHERE (' . implode(' AND ', $holders) . ')";
+      $UserDataSet = $bd->prepare( $sql );
+      $UserDataSet->execute( $values );
+      $userData = $UserDataSet->fetchAll( PDO::FETCH_ASSOC );
+    }
+    print '接続に成功しました';
   } catch ( PDOException $e ) {
+    header( 'Content-Type: text/plain; charset=UTF-8', true, 500 );
-    die( "接続エラー1: {$e->getMessage()}" );
+    exit( '接続できませんでした' . $e->getMessage() );
-  }
+  }
-  return $db;
-　//ここまでは接続確認済です。
-  //queryのデータ
+  function h( $str ) {
-  try {
-　  $query = "SELECT * FROM TABLE1 WHERE search_name LIKE ? ESCAPE '!' AND color LIKE ? ESCAPE '!' AND category LIKE ? ESCAPE '!' ";
-  $qPart = array_fill( 0, count( $userData ), "(?, ?, ?, ?, ?)" );
+    return htmlspecialchars( $str, ENT_QUOTES, 'UTF-8' );
-  $query .= implode( ",", $qPart );
-  $search_name = $_POST[ 'search_name' ];
-  $color = $_POST[ 'color' ];
-  $category = $_POST[ 'category' ];
-  //SQL文を実行する
-  $UserDataSet = $db->prepare( $query );
-  $i = 1;
-  foreach ( $userData as $row ) { //bind the values one by one
-    $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $search_name ) . '%' ], PDO::PARAM_STR );
-    $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $color ) . '%' ], PDO::PARAM_STR );
-    $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $category ) . '%' ], PDO::PARAM_STR );
-  }
-  $UserDataSet->execute();
-  //扱いやすい形に変える
-  $result = [];
-  while ( $row = $UserDataSet->fetch(PDO::FETCH_ASSOC)) {
-    $result[] = $row;
-  }
-  return $result;
-  } catch ( PDOException $e ) {
-    die( "接続エラー2: {$e->getMessage()}" );
   }
@@ -270,7 +252,7 @@
 include_once( 'search.php' );
-$userData = getUserData( $_POST );
+$userData = getUserData( $_GET );
 ?>
@@ -284,7 +266,7 @@
 　　//キーワード検索、チェックボックス、セレクションの３タイプで検索
-　　<input name="search_name" value="<?php echo isset($search_name) ? htmlspecialchars($search_name) : '' ?>">
+　　<input name="search_name" value="<?php echo isset($search_name) ?= h($search_name) : '' ?>">
    <input type="checkbox" name="color" value="白" <?php echo isset($color) && $color == '白' ? 'selected' : '' ?>><label for="color">白</label>//他省略
@@ -298,19 +280,29 @@
-//上記項目以外のデータで結果表示
+　　//$holders[]に指定した列以外のデータで結果表示
-   <?php if(isset($userData)): ?>
+   <?php if (isset($err)) : ?>
+      <p><?= h($err->getMessage()); ?></p>
+    <?php endif; ?>
+    <?php if (0 < count($userData)): ?>
-   <?php foreach($userData as $row): ?>
+      <?php foreach ($userData as $row) : ?>
+        <div class="box_warp">
+          <div><img class="circular" height="80" src="../<?= h($row['img']); ?>"></div>
-     <img src="../<?php echo htmlspecialchars($row['img']); ?>">
+          <div class="break-word"><?= h($row['original_name']); ?></div>
-     <?php echo htmlspecialchars($row['original_name']) ?>
+      </div>
-   <?php endforeach; ?>
+      <?php endforeach; ?>
-   <?php endif; ?>
+    <?php endif; ?>
 </body>

1. 接続が確認できている部分にコメント追加 2. database.phpに設定した$usertable をテーブル名に変更 3. ->execute();の位置を変更

2019/11/26 15:32

投稿

TaraIkura

スコア5

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -30,7 +30,7 @@
   $Mysqli->set_charset( 'utf8' );
-  $query = "SELECT * FROM $usertable";
+  $query = "SELECT * FROM TABLE1";
   //接続状況チェック
@@ -42,8 +42,6 @@
   }
   //入力された検索条件からSQl文を生成
   $where = [];
@@ -70,11 +68,11 @@
     $whereSql = implode( " AND ", $where );
-    $sql = "select * from $usertable where " . $whereSql;
+    $sql = "select * from TABLE1 where " . $whereSql;
   } else {
-    $sql = "select * from $usertable";
+    $sql = "select * from TABLE1";
   }
@@ -192,17 +190,21 @@
   } catch ( PDOException $e ) {
-    die( "接続エラー: {$e->getMessage()}" );
+    die( "接続エラー1: {$e->getMessage()}" );
   }
   return $db;
+　//ここまでは接続確認済です。
   //queryのデータ
+  try {
-  $query = "SELECT * FROM $usertable WHERE search_name LIKE ? AND color LIKE ? AND category LIKE ?";
+　  $query = "SELECT * FROM TABLE1 WHERE search_name LIKE ? ESCAPE '!' AND color LIKE ? ESCAPE '!' AND category LIKE ? ESCAPE '!' ";
   $qPart = array_fill( 0, count( $userData ), "(?, ?, ?, ?, ?)" );
@@ -226,13 +228,15 @@
   foreach ( $userData as $row ) { //bind the values one by one
+    $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $search_name ) . '%' ], PDO::PARAM_STR );
-    $UserDataSet->bindValue( $i++, $row[ '%' . addcslashes( $search_name, '\_%' ) . '%' ], PDO::PARAM_STR );
+    $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $color ) . '%' ], PDO::PARAM_STR );
-    $UserDataSet->bindValue( $i++, $row[ '%' . addcslashes( $color, '\_%' ) . '%' ], PDO::PARAM_STR );
-    $UserDataSet->bindValue( $i++, $row[ '%' . addcslashes( $category, '\_%' ) . '%' ], PDO::PARAM_STR );
+    $stmt->bindValue( $i++, $row[ '%' . preg_replace( '/(?=[!_%])/', '!', $category ) . '%' ], PDO::PARAM_STR );
-  }
+  }
+  $UserDataSet->execute();
   //扱いやすい形に変える
@@ -246,7 +250,13 @@
   return $result;
-  $UserDataSet->execute();
+  } catch ( PDOException $e ) {
+    die( "接続エラー2: {$e->getMessage()}" );
+  }
 }