php - 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!

question from:https://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement

Answer 1

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 the direction.

I wrote a whitelisting helper function to be used in such cases, it greatly reduces the amount of code that needs to be written:

$order = white_list($order, ["name","price","qty"], "Invalid field name");
$direction = white_list($direction, ["ASC","DESC"], "Invalid ORDER BY direction");

$sql = "SELECT field from table WHERE column = ? ORDER BY $order $direction";
$stmt = $db->prepare($sql);
$stmt->execute([$is_live]);

The idea here is to check the value and raise an error in case it is not correct.