Is it possible to use bind_param for ORDER BY?

Question

In my mind I have a query that goes 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 code block without setting the sort variable it runs without an error relating to the use of the ? in the ORDER BY clause and a result set is displayed in what appears to be a result set with "ORDER BY id".

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

Now, if I alter 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 runs correctly, with a result set that is the same as my query in phpMyAdmin.

What exactly is going on here and why the query does not run as I initially intended using bind_param.

In my mind, it should work because no error comes up relating to such use... But in practice it seems that it does not work for an ORDER BY clause. It's almost like it is not translating the sort variable while running bind_param.

EDIT:

For anyone 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...
}

score 11 · Accepted Answer · 2012-08-18T18:32:45.663

11

Only data can be bound with placeholders.

Column/table names are part of the schema and cannot be bound. (The fact that it generates "odd results" instead of simply yielding an error is a peculiarity of the implementation.)

I would suggest using a white-list of column-names and controlled string interpolation.

edited Aug 18 '12 at 18:32

answered Aug 18 '12 at 18:17

Hi and thank you for your comment. I thought that it was a problem with my reasoning. Could you please elaborate further on what it is that you are suggesting as my knowledge of PHP in general is not very great. I have taught myself everything I know but it appears I do not know about things like this :) – Craig van Tonder Aug 18 '12 at 18:18
@BlackberryFan It's not PHP per-say. This is just how placeholders work in [all that I know of] SQL implementations. They allow different data to be substituted for the same *shaped query* (same columns, table, same joins, same where operators, etc.), but do not allow altering the shape of a query. While a naive implementation *could* just perform a replace after something like an internal call to `mysql_real_escape_string`, this would lead to further issues as `ORDER BY 'some escaped data'` is .. odd at best, and incorrect in general. – Aug 18 '12 at 18:21
Alright thanks alot and that does make very much sense... But what I do not understand is the work around that you are suggesting here. I understand the issues involved in the second example and this is why I have been trying to convert the query into a prepared statement. I guess what I am asking is what would be the preferred method in dealing with something like this? (in lamens terms :) ) – Craig van Tonder Aug 18 '12 at 18:24
@BlackberryFan To use string interpolation, as done in the last post example (if there is still *data*, bind that using placeholders as normal). When doing the string interpolation *for column names* (and *column names only*), I would recommend using a *white-list*. That is, *only* accept the values of `"id"` and `"price"` for `$sort` and *reject* any other value -- this white-list *is* the sanitization process for columns. Using an array of "accepted column names" can make this an easy test. – Aug 18 '12 at 18:27
1

Ahh!!! O my goodness that was like an explosion of understanding :) That does make perfect sense as I have actually used things like this before but never quite in those terms... They are to me... "things". But really and truly thank you sooooooooooo much for your input!!! you got it spot on! – Craig van Tonder Aug 18 '12 at 18:30
Please see the update in my op, this is what you were referring to right? – Craig van Tonder Aug 18 '12 at 20:49
can a subquery be used to choose the column name in order by? If so then you could use an information schema subquery to look up the column name, using where column = ? so as to accept the order column as a parameter... completely overkill for a simple selection like this but I'm thinking if you wanted something that would be dynamic and not require code changes for new/renamed columns... – WebChemist Aug 18 '12 at 20:55
@WebChemist Not that I can tell. See `ORDER BY {col_name | expr | position}`, from the [MySQL manual for SELECT](http://dev.mysql.com/doc/refman/5.6/en/select.html); there is no provision for `exp_as_col_name`. – Aug 18 '12 at 22:00

Is it possible to use bind_param for ORDER BY?

1 Answers1

Linked

Related