Sorting (Order by) in SQL passed values?

[toxicandy]

I have a quick question, I am hoping it is an easy answer but if I wanted a query like the following:

SELECT * FROM items ORDER BY date ASC

Is there anyway to replace the order by information with prepared statement attributes? would either of the following work?

 SELECT * FROM items ORDER BY :column :order

OR

SELECT * FROM items ORDER BY ? ?

and then use bindParam or bindValue to assign those values? If I can't do that I know I can use conditionals to have multiple queries set and then use the correct query based on if the conditional is correct or not for example:

$value = filter_input(INPUT_POST, "value", FILITER_SANITIZE_STRING);
if($value = "date1"){
    $query = "SELECT * FROM items ORDER BY date ASC";
}elseif($value = "date2"){
    $query = "SELECT * FROM items ORDER BY date DESC";
 }

I would like to use the top if at all possible as it would be much easier. Is it possible to use prepared statements that way?

[cereal]

Is there anyway to replace the order by information with prepared statement attributes?

Unfortunately this cannot be done with current drivers. You can use a whitelist approach, instead of rewriting the query you do:

if(in_array($value, array('asc', 'desc'))
    $query = "SELECT * FROM `items` ORDER BY `date` $value";

Besides: date is a reserved word, so if used it needs backticks, as in the above example.

[toxicandy]

So I can not use prepared statements to bind column names correct? I can bind the ASC/desc?

Also I was just using Date as an example, I would actually have various columns that I could potentially search by which is why I wanted to parametrize it. I could use your approach and just make $query equal to different columns to sort by and nest it in a conditional correct?

[cereal]

So I can not use prepared statements to bind column names correct?

Exactly, you cannot.

I can bind the ASC/desc?

No, the bindParam() will accept only the PDO::PARAM_* constants defined here:

• http://php.net/manual/en/pdo.constants.php

When you set:

$colname = 'created_at';
$stmt = $db->prepare("SELECT * FROM `items` ORDER BY :colname")
$stmt->bindParam(':colname', $colname, PDO::PARAM_STR);

The generated query will look like:

SELECT * FROM `items` ORDER BY 'created_at'

With quotes surrounding the :colname value, so the ORDER BY clause will not work. If you try to force it with a workaround, for example by setting it as an integer (by applying PDO::PARAM_INT) then you would get:

SELECT * FROM `items` ORDER BY 0

I could use your approach and just make $query equal to different columns to sort by and nest it in a conditional correct?

Yes, you can.