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?

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