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

Solution 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.

Solution 2:

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.

Solution 3:

It's possible use prepared statements in ORDER BY clause, unfortunately you need pass the order of column insted of the name and is required set PDO_PARAM_INT with type.

In MySQL you can get the order of columns with this query:

SELECT column_name, ordinal_position FROM information_schema.columns 
WHERE table_name = 'table' and table_schema = 'database'

PHP code:

$order = 2;

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