CODEWITHSUNDEEP
phplaravel
John Kent
John Kent

Reputation: 386

How to bind a column in laravel Builder?

I have the following code(Laravel 5.6 PHP 7.0): 

$orders = DB::table('orders')
            ->selectRaw('?', ['id'])
            ->get();

In my imagination that code has to perform such sql:

SELECT `id` FROM orders

and has to return a result like:

array [
   0 => {
     "id": "1"
   }
   1 => {
     "id": "2"
   }

unfortunately it returns this:

array [
   0 => {
     "?": "id"
   }
   1 => {
     "?": "id"
   }

But if I run the following code everything works as I want:

$orders = DB::table('orders')
             ->selectRaw('id')
             ->get();

So, my questions are:

  1. Why does the first code returns such a strange result?

  2. How to bind a column?

Upvotes: 0

Views: 1027

Answers (1)

Phil Cross
Phil Cross

Reputation: 9302

Its because PDO wraps bound parameters in quotes, so you're SQL would actually be something like:

SELECT 'id' FROM orders

You're selecting the string id, not the column. You cannot bind column names, only values.

To get your query to work, in the same format as what you want, you'd need to do this:

$columns = ['id'];

$orders = DB::table('orders')
        ->select($columns)
        ->get();

If you want to be safer, for example, if you get the column names from a querystring or something, you can do:

$allowedColumns = ['id', 'product_name', 'some_other_field'];
$columns = array_only($request->get('columns'), $allowedColumns);

$orders = DB::table('orders')
        ->select($columns)
        ->get();

If you ever need to have a look at the raw SQL Laravel is generating, and the bound paramters, you can do the following:

$query = DB::table('table_name')->select(['id', 'column1']);

dd($query->toSql(), $query->getBindings());

Upvotes: 1

Related Questions