CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
WAC0020
WAC0020

Reputation: 379

PDO prepared statement with conditions not working

I am trying to to get my data from MYSQL using PDO but I am not having any luck.

Here is what I have tried:

$postQuery = $DBH->prepare("SELECT title, views, rating, thumb FROM posts WHERE category=:category and status=1 ORDER BY :sort DESC");
$postQuery ->bindParam(':category', $category);
$postQuery ->bindParam(':sort', $sort);
$postQuery ->execute();

This works without errors but it returns all of the posts in alphabetical order, ignoring the category and the sort.

I tried this:

$postQuery = $DBH->query("SELECT title, views, rating, thumb FROM posts WHERE category={$category} and status=1 ORDER BY {$sort} DESC");

This did work but I don't get the protection of the prepared statement. Any ideas on why one statement works but the other one does not?

Upvotes: 0

Views: 1104

Answers (2)

Benny Hill
Benny Hill

Reputation: 6240

You can't use place holders in ORDER BY clauses. See this question: How do I set ORDER BY params using prepared PDO statement?

Upvotes: 1

eggyal
eggyal

Reputation: 125835

Your bound parameter :sort gets expanded to a string literal, not a SQL identifier. That is, you are effectively evaluating something along the lines of:

ORDER BY 'rating' DESC

Since literals like this are constant for every record, it has no effect on the order of the resultset.

You can't parameterise identifiers, so must concatenate that part of the SQL into your prepared statement (the safest way is to set $sort from a predetermined set of safe values, based on whatever logic is appropriate to your needs).

Upvotes: 3

Related Questions