Laravel Raw SQL Query named parameter binding on order by

Question

Named parameter binding on "order by" is not working on this full raw statement. An error message is not displayed. start and length work.

    $sql = "SELECT
                product.id AS 'product-id',
                product.name AS 'product-name',
                product.status AS 'product-status',
                product.ingredients 'product-ingredients',
                product.price AS 'product-price',
                category.name AS 'category-name'
            FROM
                product
            LEFT JOIN
                category ON product.category_id = category.id
            ORDER BY :orderBy
            LIMIT :start,:length";

    return DB::select($sql, [
        'orderBy' => $orderBy,
        'start' => $start,
        'length' => $length
    ]);

Any idea?

What is not working? What errors are you getting? – Dhiraj Dec 03 '17 at 00:46 — Dhiraj, Dec 03 '17 at 00:46
I do not get an error message. The result is not sorted. – Thomas Müller Dec 03 '17 at 00:48 — Thomas Müller, Dec 03 '17 at 00:48
can you show your `$orderBy` contain data?? – Mohammad Nurul Huda Rimon Dec 03 '17 at 03:32 — Mohammad Nurul Huda Rimon, Dec 03 '17 at 03:32

Mathew Tinsley · Accepted Answer · 2017-12-03T03:12:41.407

1

The problem is in the underlying PDO statement. You cannot bind table or column names in a query like you can bind values. See this answer:

Can PHP PDO Statements accept the table or column name as parameter?

You can rewrite your query without raw expressions:

return DB::table('product')
           ->select([
               product.id AS 'product-id',
               ...
           ])->leftJoin('category', 'product.category_id', '=', 'category.id')
           ->orderBy($orderBy)
           ->limit($start, $length)

If you must use raw expressions, you will have to manually sanitize the order by value and insert it into the query as a string.

edited Dec 03 '17 at 03:12

answered Dec 03 '17 at 01:09

Mathew Tinsley

6,805
2
27
37

I had already tried the first one. That will not do. The second one works without the errors. ->select('product.id as product-id', ...); ->orderBy($orderColumn, $orderDir)->offset($start)->limit($length)->get(); But why is parameter binding not working with the full raw statement in order by? – Thomas Müller Dec 03 '17 at 01:55
@ThomasMüller I'm not sure, what do you get when you output the sql query? Call `toSql()` on the query builder. – Mathew Tinsley Dec 03 '17 at 01:58
Can not call `toSql` on the full raw statement. – Thomas Müller Dec 03 '17 at 02:06
@ThomasMüller You should be able to. This doesn't work? `echo DB::select($sql, [...])->toSql()` Are you getting an error? – Mathew Tinsley Dec 03 '17 at 02:08
Call to a member function toSql() on array. DB::select returns an array. – Thomas Müller Dec 03 '17 at 02:17
https://github.com/laravel/framework/blob/5.5/src/Illuminate/Database/Connection.php#L307 – Thomas Müller Dec 03 '17 at 02:23
@ThomasMüller You're using the database connection directly rather than using the query builder https://github.com/laravel/framework/blob/5.5/src/Illuminate/Database/Query/Builder.php#L218 – Mathew Tinsley Dec 03 '17 at 02:23
@ThomasMüller I see the problem, the DB facade returns the connection if you call select on it, I was calling table on it. You should be able to use this method to log the queries: https://laravel.com/docs/5.5/database#listening-for-query-events – Mathew Tinsley Dec 03 '17 at 02:29
Yes, but we are talking about the full raw statement? `DB::table('product')->select()` => query builder. `DB::select($sql)` => connection directly – Thomas Müller Dec 03 '17 at 02:32
That is not possible? But why does it work with the limit and not with order by? – Thomas Müller Dec 03 '17 at 02:35
@ThomasMüller I did some debugging and tracked down the problem: parameter binding works on values, not table or column names. You're not getting an error because `ORDER BY 'column_name'` is valid, but it won't do what you want. – Mathew Tinsley Dec 03 '17 at 02:55

Laravel Raw SQL Query named parameter binding on order by

1 Answers1