Executing a prepared PDO statement with the like clause

Question

I am new to PHP, and am trying to learn to use PDO to connect to a test MySQL db. I have the following:

try {
    $db = new PDO('mysql:dbname=MYDBNAME;host=MYHOST', 'USERNAME', 'PASSWORD');

    $query = "select * from books where ? like '%?%'";
    $stmt = $db->prepare($query);
    $stmt->execute(array($searchtype, $searchterm));  
} catch(PDOException $e) {
    echo 'PDOException: ' . $e->getMessage();
}

When I try it I get the following warning: Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

When I remove the like clause, and the $searchterm param, it returns the result properly. I thought -- like '%?%' -- might not be a legal way to create this query under double quotes, so I tried escaping ', which did not work. I looked around for a solution, and found that someone moved '% and %' down to where $searchterm is:

$query = "select * from books where ? like ?";
...
$stmt->execute(array($searchtype, '\'%'.$searchterm.'%\'')); 

I got the same result.
Any help is appreciated. Thanks!

/ UPDATE ****/ I found on example 12 of http://us3.php.net/manual/en/pdo.prepared-statements.php

Example #12 Invalid use of placeholder

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'");
$stmt->execute(array($_GET['name']));

// Below is What they suggest is the correct way.
// placeholder must be used in the place of the whole value 
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?");
$stmt->execute(array("%$_GET[name]%"));
?> 

I tried this, and even though I no longer get a Warning, I do not get any results. However when I execute the query directly I will get a couple of results. Any thoughts?

Answer 1

Don't add the quotes when binding prepared variables and dont bind the column name

$query = sprintf( "select * from books where %s like ?", $searchtype );
...
$stmt->execute(array($searchtype, '%'.$searchterm.'%')); 

Answer 2

The problem I see is if you had written a wrapper for PDO, then you would have to somehow handle this separately. The answer I had found and loved was write your query and concat the % to the parameter. i.e. "WHERE column like concat('%', :something, '%')"

Answer 3

$stmt->execute(array($searchtype, '\'%'.$searchterm.'%\'')); 

This isn't how parameterised queries work. Inserted parameters act as literal strings already, you don't have to add quote delimiters around them or escape them (that's the whole point), and if you try, you're literally comparing against the string single-quote-searchterm-single-quote.

Consequently if you are (as I suspect) intending to compare a particular column against a literal string, you don't parameterise the column name. At the moment you are comparing a literal string to another literal string, so it'll either always be true or always false regardless of the data in the row!

So I think what you probably mean is:

$query= "SELECT * FROM books WHERE $searchtype LIKE ?";
$like= "%$searchterm%";
$stmt->execute(array($like)); 

thought naturally you will have to be very careful that $searchtype is known-good to avoid SQL-injection. Typically you would compare it against a list of acceptable column names before using it.

(Aside: there is a way of putting arbitrary strings in a schema name that you can use for a column, but it's annoying, varies across databases and there isn't a standard escaping function for it. In MySQL, you backslash-escape the backquote character, quotes and backslashes and surround the name with backquotes. In ANSI SQL you use double-quotes with doubled-double-quotes inside. In SQL Server you use square brackets. However in reality you vary rarely need to do any of this because really you only ever want to allow a few predefined column names.)

(Another aside: if you want to be able to allow $searchterm values with literal percents, underlines or backslashes in—so users can search for “100%” without matching any string with 100 in—you have to use an explicit escape character, which is a bit tedious:)

$query= "SELECT * FROM books WHERE $searchtype LIKE ? ESCAPE '+'";
$like= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $searchterm);
$stmt->execute(array("%$like%"));