How do I set ORDER BY params using prepared PDO statement?

Question

I'm having problems using params in the ORDER BY section of my SQL. It doesn't issue any warnings, but prints out nothing.

$order = 'columnName';
$direction = 'ASC';

$stmt = $db->prepare("SELECT field from table WHERE column = :my_param ORDER BY :order :direction");
$stmt->bindParam(':my_param', $is_live, PDO::PARAM_STR);
$stmt->bindParam(':order', $order, PDO::PARAM_STR);
$stmt->bindParam(':direction', $direction, PDO::PARAM_STR);
$stmt->execute();

The :my_param works, but not :order or :direction. Is it not being internally escaped correctly? Am I stuck inserting it directly in the SQL? Like so:

$order = 'columnName';
$direction = 'ASC';

$stmt = $db->prepare("SELECT * from table WHERE column = :my_param ORDER BY $order $direction");

Is there a PDO::PARAM_COLUMN_NAME constant or some equivalent?

Thanks!

Answer 1

I don't think you can :

• Use placeholders in an order by clause
• Bind column names : you can only bind values -- or variables, and have their value injected in the prepared statement.

Answer 2

Here comes the question shows that widely loved prepared statements is not the silver bullet, hehe :)

Yes, you're stuck inserting it directly in the SQL With some precautions, of course. Every operator/identifier must be hardcoded in your script, like this:

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders));
$order=$orders[$key];
$query="SELECT * from table WHERE is_live = :is_live ORDER BY $order";

Same for direction.

Note that bindParam does no escaping, as no escaping needed at all. it does binding.

Answer 3

If I'm not entirely mistaken, Pascal is right.
The only binding possible in PDO is the binding of values, as you did with the ':my_param' parameter.
However, there's no harm done in:

$stmt = $db->prepare("SELECT field from table WHERE column = :my_param ORDER BY ".$order ." ".$direction);
$stmt->bindParam(':my_param', $is_live, PDO::PARAM_STR);
$stmt->execute();

The only thing to take notice of would be the correct escaping of $order and $direction, but since you set them manually and didn't set them via user input, I think you're all set.

Answer 4

I don't think you can get ASC/DESC as part of the prepared statement, but the column you can.

 order 
    by 
       case :order
           when 'colFoo' then colFoo
           when 'colBar' then colBar
           else colDefault
       end
       $direction

Since ASC/DESC is only two possible values, you can easily validate and select between them as hardcoded values.

You could also make use of the ELT(FIELD(,,,,,),,,,,) functions for this, but then ordering will always be done as a string, even if it's a numeric column.