too long

Question

I am trying to implement a search using php5 pdo and mysql. What I am trying to do is search for a given set of keywords in my table 'posts' and return records that contain any of the given keywords in the column 'title'. But it returns no result set even if I give keywords that I know exist in the table. I use collation 'utf8mb4_unicode_ci'. Here is my code:

 <?php
if($_SERVER['REQUEST_METHOD']=='POST'){
$charset="utf8mb4";
$dsn="mysql:host=$host;dbname=$db;charset=$charset";
$opt=[  PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC,
      PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION];
$pdo=new PDO($dsn,$user,$pass,$opt);
$keywords=$_POST['keywords'];
$keywordArray=explode(' ',$keywords);
$n=count($keywordArray);
$query="SELECT * FROM posts WHERE title LIKE ?";
$keywordArray[0]="'%".$keywordArray[0]."%'";
for($i=1;$i<$n;$i++){
  $keywordArray[$i]="'%".$keywordArray[$i]."%'";
  $query=$query." OR title LIKE ?";
}
$query=$query." LIMIT 50;";
     echo $query;
$stmt=$pdo->prepare($query);
$stmt->execute($keywordArray);
$res=$stmt->fetchAll();
echo "<br><h1>SEARCH RESULTS:</h1><br><ul>";
if($res){
foreach($res as $row){
  echo "<li>".$row['date']."<a href=\"viewpost.php?postid=".$row['id']."\">".$row['title']."</a></li><br>";
}
}
else{
echo "<h2 style=\"color:red;\">No results!</h2>";
}
echo "</ul></div>";
}
?>

Its working inside the console.

SELECT * FROM posts WHERE title LIKE '%hit%' OR title LIKE '%fifa%';

returns two rows. But searching with 'hit fifa' using the form returns zero rows.

Answer 1

Since you are using prepared statements, you don't need the single quotes around your expression. Change your code, removing those quotes, to

$query="SELECT * FROM posts WHERE title LIKE ?";
$keywordArray[0]="%".$keywordArray[0]."%";
for($i=1;$i<$n;$i++){
  $keywordArray[$i]="%".$keywordArray[$i]."%";
  $query.=" OR title LIKE ?";
}

It was treating the quote marks as being part of the value inside the parameter. So you would have ended up with SQL something like

SELECT * FROM posts WHERE title LIKE '\'%Something%\''

and clearly this won't match, because the values in the database won't have single quotes at the start and end in most cases.

With the changes, it should translate into SQL like this

SELECT * FROM posts WHERE title LIKE '%Something%'

This is because the parameterisation process handles the quoting and escaping job automatically for you - it's one way in which it protects against SQL injection attacks ( and also, incidentally, against syntax errors caused by erroneous / unescaped quote marks).

---

P.S. If a request is ever submitted to this code where no keyword at all was provided, then the code will crash because it assumes there is always a value in $keywordArray[0]. Consider revising this to either validate that a keyword was provided, or just loop the whole array and, if no keywords are submitted, simply don't add a WHERE clause to the query at all.