PHP LIKE query keeps turning up empty

Question

For some reason my PHP PDO sql search isn't turning up anything. I'm trying to implement some kind of search engine into my site, but I can't seem to get anything. I've been trying a bunch of different solutions up until now but the search keeps turning up empty, even given exactly the right parameters. The $find variable is entered by the user, and the $field variable is a dropdown list from which the user can choose what to search for, which returns values that equal the names of columns in the database.

            $find = strtoupper($_POST['find']);
            $find = "%" . $find . "%";
            $field = $_POST['field'];

            $qry = $conn->prepare("SELECT * FROM \"MovieDB\" WHERE upper(:field) LIKE :find%");
            $qry->bindParam(':find', $find, PDO::PARAM_STR, 16);
            $qry->bindParam(':field',$field, PDO::PARAM_STR,16);
            $qry->execute();
            $results = $qry->fetchAll();

Try `upper(:field) LIKE :find` (no trailing `%`). The percentages are bound by the parameter `$find`. — Sam, Jun 13 '13 at 21:15
Nope, the trailing % was a typo indeed, but removing it didn't solve it. — Ghijs Kilani, Jun 13 '13 at 21:18
I don't think you can bind field (or table) names. You may need to inject those values directly into the query. But be sure to sanitize! http://stackoverflow.com/questions/8314043/how-to-dynamically-build-queries-with-pdo — showdev, Jun 13 '13 at 21:21
Yay! Thanks showdev, you can put that as an answer, because it worked! A thousand thanks to you! — Ghijs Kilani, Jun 13 '13 at 21:25

score 0 · Accepted Answer · edited May 23 '17 at 12:16

PDO does not allow binding of field or table names.

I suggest injecting the field value directly into your query string.
Be sure to sanitize it to avoid SQL injection!

$qry = $conn->prepare("SELECT * FROM \"MovieDB\"
                       WHERE upper(`".$field_santizied."`) LIKE :find");
$qry->bindParam(':find', $find, PDO::PARAM_STR, 16);

How to dynamically build queries with PDO

php.net references inability to bind table names:
http://us3.php.net/manual/en/book.pdo.php#69304

PHP LIKE query keeps turning up empty

1 Answers1