PHP / MYSQL - Prepared MySQLi Statements - Can I use bind_param for ORDER BY? (streamlining the result set)

Question

In my mind, I have a query that looks something like this:

$sort = isset($sort) ? sanitize($_sort) : 'id'; if ($result = $link->prepare(" SELECT id, price FROM items ORDER BY ? ")) { $result->bind_param("s", $sort); $result->execute(); etc... } 

When I run this block of code without setting the sort variable, it runs without using error? in the ORDER BY clause, and the result set appears in what appears to be the result set with "ORDER BY id".

HERE IS THE PROBLEM IN ONE CASE:

Now, if I set the sort variable to something like "ASC price", I still get a result set that seems to be "ORDER BY id" instead of "ORDER BY price ASC".

When I run the query as a query in phpmyadmin, it returns the correct result set based on the “ASC price” if I define the query this way.

Now, if I change the code and run it like this:

 $sort = isset($sort) ? sanitize($_sort) : 'id'; if ($result = $link->prepare(" SELECT id, price FROM items ORDER BY $sort ")) { $result->execute(); etc... } 

It works correctly, with a result set that matches my query in phpmyadmin ...

Can someone tell me what exactly is happening here and why the request is not being executed, since I originally assumed using bind_param.

In my opinion, this should work, because there are no errors regarding this use ... But in practice, it seems that this does not work for the ORDER BY clause. It almost does not translate the sort variable when bind_param is run.

Thank you for reading my question, and any materials related to it would be very grateful!

EDIT:

For everyone who is interested -

 if (isset($sort)) { $acceptableSortValues = array('name', 'price ASC', 'price DESC'); $sort = sanitize($sort); if (!in_array($sort, $acceptableSortValues)) { $sort = 'name'; } } else { $sort = 'name'; } if ($result = $link->prepare(" SELECT name, price FROM items ORDER BY $sort ")) { $result->execute(); etc... } 

Answer 1

Only data can be attached to placeholders.

Column / table names are part of the schema and cannot be related. (The fact that it generates "odd results" instead of just getting an error is a feature of the implementation.)

I would suggest using a whitelist of column names and controlled row interpolation.