How to bind a column in laravel Builder?

Question

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:

Why does the first code returns such a strange result?
How to bind a column?

Why use a raw query at all? `DB::table('orders')->select(['id'])->get()` does exactly what you want. Although it may not be really secure if you use user input to dynamically select columns (see password or access token columns). — Namoshek, May 29 '18 at 19:37
@Namoshek It's just a simplified situation to get answers for my questions, not more — John Kent, May 29 '18 at 19:41
Well, as I answered, in your simplified situation, performing a normal select works perfectly fine. So maybe you try that or you give us the real thing to solve. :) — Namoshek, May 29 '18 at 19:42
@Namoshek look at questions at the bottom of the post. You answered not my questions — John Kent, May 29 '18 at 19:47
You are right, I did not answer it directly. Hence the comment. But between the lines I tried to tell you that is isn't possible. PDO does not allow column and table names to be dynamically bound (see [here](https://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-or-column-name-as-parameter)). — Namoshek, May 29 '18 at 19:50
Its because PDO wraps bound paramters in quotes, so you're SQL would actually be something like `SELECT 'id' FROM orders`. You're selecting the string `id`, not the column. — Phil Cross, May 29 '18 at 20:29

Phil Cross · Accepted Answer · 2018-05-29T20:42:11.673

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());

Thanks. But I assume we need to use ''select'' method instead "selectRaw" in this case — John Kent, May 29 '18 at 20:40
Sorry, you're right, you'd need to use `select` instead of `selectRaw`. Although, you can still use `selectRaw` if you really need to by imploding the `$columns` array: `implode(', ', $columns);` — Phil Cross, May 29 '18 at 20:41

How to bind a column in laravel Builder?

1 Answers1