Binding Columns in Laravel / PDO

Question

TL;DR: Do I really have to create a white-list of column names (to compare to when sanitizing user input) in order to let a user sort a dataset? That's an insane amount of overhead, is there really no way in the modern PHP world to accomplish this with a variable instead?

Full Story:

I'd like to let the user choose which column and direction to sort the results by in a data set.

I've accomplished that with the following code:

$sort = Input::get('sort'); // The column name to sort by
$direction = Input::get('direction'); // 'asc' / 'desc'
$paginator = DB::table('master')
                  ->select('Style_ID', DB::raw('MAX(?) AS `sort`'))
                  ->setBindings(array($sort))
                  ->orderBy('sort', $direction)
                  ->groupBy('Style_ID')
                  ->paginate(20);
$masters = $paginator->getCollection();

This runs with no errors, but the resulting 20 rows seem to totally ignore the aggregate MAX requested.

Here is the output of the query log on that code:

(
    [query] => select `Style_ID`, MAX(?) AS `sort` from `master` group by `Style_ID` order by `sort` desc
    [bindings] => Array
        (
            [0] => Hot
        )

    [time] => 2
)

...so the query looks fine, but the results do not.

It does work, however, if I remove the column binding and hard-code in a column instead:

$paginator = DB::table('master')
                  ->select('Style_ID', DB::raw('MAX(`Hot`) AS `sort`'))
                  ->orderBy('sort', $direction)
                  ->groupBy('Style_ID')
                  ->paginate(20);
$masters = $paginator->getCollection();

With a query log of:

(
    [query] => select `Style_ID`, MAX(`hot`) AS `sort` from `master` group by `Style_ID` order by `sort` desc
    [bindings] => Array
        (
        )

    [time] => 2
)

This leads me to believe that either Laravel or PDO itself doesn't allow the binding of column names, though that would make no sense to me whatsoever. And I would expect an error message in that case.

I'm stumped. Thanks in advance for any help.

One possible solution is if we could somehow escape the column name instead of binding it:

->select('Style_ID', DB::raw('MAX(`' . DB::escape($sort) . '`) AS `sort`'))

...but there's nothing like that, is there? mysql_real_escape_string is deprecated and PDO doesn't have a replacement for it.

Am I left with no choice except to create a white-list of all column names every time I want to do something like this to sanitize input?

According to this question/answer, there is no way to do this in PHP. What an insane world we live in.

score 3 · Accepted Answer · answered Apr 21 '14 at 19:55

Do I have to create a white-list of column names to sanitize user input any time I want to let the user enter a column name as a variable in a query (such as for sorts and filters)?

Yes.

PDO itself doesn't allow the binding of column names,

Exactly.

Is there really no way in the modern web world to safely bind a column name to a query?

Well, in a way. In my own DB wrapper I have a placeholder for the column names. Dunno for Laravel but they may have something up sleeve too.

However, it's better to whitelist anyway.

I've set up a similar array in Laravel. What a shame! Oh well, thanks bud. — Leng, Apr 21 '14 at 20:00

Binding Columns in Laravel / PDO

1 Answers1