PDO LIKE query not returning results

Question

Well, I've been at this for a few hours, and for the life of me I can't figure out what is wrong. The code is as follows:

$str = "%" . $_POST['str'] . "%";
$offset = (int) $_POST['offset'];

try {
    $stmt = $dbh->prepare("SELECT * FROM Spells WHERE :col LIKE :str ORDER BY :sort LIMIT 10 OFFSET :offset");
    $stmt->bindParam(":col",$_POST['col']);
    $stmt->bindParam(":str",$str);
    $stmt->bindParam(":offset",$offset, PDO::PARAM_INT);
    $stmt->bindParam(":sort",$_POST['sort']);

    $stmt->execute();
}
catch (PDOException $e) {
    echo "MySQL error: " . $e->getMessage() . "<br/>";
    die();
}

The connection to the database works fine, no errors occur. If I type in % into the search field(which would be output as %%% in the query), results return as expected.

I've attempted the same query in phpMyAdmin and it works fine. I've been updating this script from the deprecated mysql_* functions, which worked fine before.

Example of the previous, deprecated query:

$sql = "SELECT * FROM Spells WHERE " . $col . " LIKE '%" . $str . "%' ORDER BY " . $sort . " LIMIT 10 OFFSET " . $offset;

As I may have already stated, I've been searching on this site as well, trying to find a solution; nothing has worked, not even MySQL's CONCAT('%',:str,'%').

The server I'm testing this on is running off of php version 5.3.17.

My question, in case I did not make it clear, is what am I doing wrong here? For those wondering(I thought that I put this but apparently I did not), there are no error messages.

If you're not getting an error message I'm assuming it's not throwing any error, so it's actually making the query, which in return does not return/output anything. Did you try the `PDO::PARAM_STR` in the third parameter in the `bindParam()` method for `:str` yet? — inhan, Oct 17 '12 at 00:45
[See this](http://stackoverflow.com/a/1376838/913097) to debug/see your query. — inhan, Oct 17 '12 at 00:50
I'd suggest simplifying this as much as possible to make sure it really is the LIKE that's breaking (remove all the other params). — John Carter, Oct 17 '12 at 00:57
@therefromhere I removed everything after `:str`, and still no change. @phil, #1: What would you suggest I do then to correctly sort? #2: just below the query, using while loop to iterate through `$stmt->fetch()`: `while ($row = $stmt->fetch()) { etc }` — Daedalus, Oct 17 '12 at 01:08

score 2 · Accepted Answer · answered Oct 17 '12 at 01:58

The issue is that you cannot use parameters in place of identifiers. This means you cannot parameterise column or table names.

What your query essentially looks like when it is executed is

SELECT * FROM Spells WHERE 'some_column_name' LIKE '%something%'...

I would establish a whitelist of search and sort column names and use those to construct your query. Here's a very simple example

$search = array('col1', 'col2', 'col3');
$defaultSearch = 'col1';

$sort = array('col1', 'col2');
$defaultSort = 'col1';

$col = in_array($_POST['col'], $search) ? $_POST['col'] : $defaultSearch;
$sort = in_array($_POST['sort'], $sort) ? $_POST['sort'] : $defaultSort;

$sql = sprintf('SELECT * FROM Spell WHERE %s LIKE :str ORDER BY %s LIMIT 10 OFFSET :offset',
    $col, $sort);

$stmt = $dbh->prepare($sql);
// bind :str and :offset, and so on

Note that if you wanted to generate a column whitelist dynamically you could use the `information_schema` database (assuming MySql) — John Carter, Oct 17 '12 at 02:07
@therefromhere sure, but that would only be if you wanted to search / sort by any and all columns — Phil, Oct 17 '12 at 02:07

PDO LIKE query not returning results

1 Answers1