PHP PDO prepared statement -- MySQL LIKE query

Question

I am trying to do a search through php's PDO class (mysql driver). I have the following query working with the MySQL client (table names changed to protect the innocent):

SELECT    hs.hs_pk,            hs.hs_text,            hs.hs_did,            hd.hd_did,            hd.hd_text,            hv.hv_text,            hc.hc_text  FROM      hs  LEFT JOIN hd   ON       hs.hs_did = hd.hd_did  LEFT JOIN hd   ON       hd.hd_vid = hv.hv_id  LEFT JOIN hc   ON       hd.hd_pclass = hc.hc_id WHERE     hs.hs_text LIKE "%searchTerm%" LIMIT 25; 

This works like a charm regardless of the search term that I use. However, when I move to php, I can't get it to return anything. I have tried several different syntaxes that seem logical to work, but nothing I have tried works. here's my existing code:

$handle = fopen('/foo/bar/test.log', 'w+'); fwrite($handle, "doSearch, with search term: $searchTerm\n"); $sql =  'SELECT   hs.hs_pk,            hs.hs_text,            hs.hs_did,            hd.hd_did,            hd.hd_text,            hv.hv_text,            hc.hc_text  FROM      hs  LEFT JOIN hd   ON       hs.hs_did = hd.hd_did  LEFT JOIN hd   ON       hd.hd_vid = hv.hv_id  LEFT JOIN hc   ON       hd.hd_pclass = hc.hc_id WHERE     hs.hs_text LIKE :searchTerm LIMIT 25';  try {  $dbh = new PDO('mysql:host=localhost;dbname=awdb', "user", "password");  fwrite($handle, "connected to DB\n");  $prep = $dbh->prepare($sql);  $ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"'));   while ($row = $prep->fetch(PDO::FETCH_ASSOC)) {   $i++;   $result[$i]['subText'] = $row['hs_pk'];   $result[$i]['subText'] = $row['hs_text'];   $result[$i]['subDid'] = $row['hs_did'];   $result[$i]['devDid'] = $row['hd_did'];   $result[$i]['devText'] = $row['hd_text'];   $result[$i]['vendorText'] = $row['hv_text'];   $result[$i]['classText'] = $row['hc_text'];  }     $dbh = null; }    catch (PDOException $e) {   print "Error!: " . $e->getMessage() . "<br/>";   die(); } 

I've tried the following as well (SQL WHERE clause & prep->execute lines are all that change):

WHERE hs.hs_text LIKE CONCAT(\'%\', ?, \'%\')  $ret = $prep->execute(array($searchTerm));  WHERE hs.hs_text LIKE "%:searchTerm%"  $ret = $prep->execute(array(':searchTerm' => $searchTerm));  WHERE hs.hs_text LIKE ":searchTerm"  $ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%')); 

etc...

Answer 1

$ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"')); 

This is wrong. You don't need the double quotes.

WHERE hs.hs_text LIKE ":searchTerm"  $ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%')); 

This is also wrong. Try with:

$prep = $dbh->prepare($sql); $ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%')); 

Explanation: Prepared statements don't simply do a string-replace. They transport the data completely separate from the query. Quotes are only needed when embedding values into a query.