CODEWITHSUNDEEP
phpmysqlpdosql-like
Daedalus
Daedalus

Reputation: 7722

PDO LIKE query not returning results

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.

Upvotes: 1

Views: 411

Answers (1)

Phil
Phil

Reputation: 164733

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

Upvotes: 2

Related Questions