How to escape a query using an Eloquent model select?

Question

DB::select takes a second parameter as described here, but Eloquent::select does not.

Here's my query:

Feature::where('company_id', Auth::user()->company_id)
            ->select('id','name',DB::raw("exists(select * from vehicle_features vf where vf.vehicle_id=$id and vf.feature_id=feature.id) as `checked`"))
            ->orderBy('name')->get(),

How can I ensure $id is escaped properly?

score 4 · Answer 1 · edited Oct 05 '16 at 06:48

4

Use DB::getPdo()->quote($id).

->select(
    'id',
    'name',
    DB::raw(
        "exists(select * from vehicle_features vf where vf.vehicle_id="
        . DB::getPdo()->quote($id)
        . " and vf.feature_id=feature.id) as `checked`"
    )
)

edited Oct 05 '16 at 06:48

alx

2,314
2
18
22

answered Apr 27 '14 at 18:10

Marwelln

28,492
21
93
117

Jarek Tkaczyk · Answer 2 · 2014-04-27T21:12:10.497

0

You may use PDO or easier manually add binding to the Query:

Feature::select(
     'id',
     'name',
     // replace $id here
     DB::raw("exists(select * from vehicle_features vf where vf.vehicle_id=? and vf.feature_id=feature.id) as `checked`"))
     // and add this part
  ->addBinding($id)
  ->where('company_id', Auth::user()->company_id)
  ->orderBy('name')->get();

edit: as stated in the comments below, bindings are bugged and methods order does matter, so the above will work as expected.

edited Apr 27 '14 at 21:12

answered Apr 27 '14 at 18:21

Jarek Tkaczyk

78,987
25
159
157

That conflicts with the bindings used in the `where` clause; it's getting the order mixed up. – mpen Apr 27 '14 at 18:25
No, it does not mix the order unless you changed the order of that chain. – Jarek Tkaczyk Apr 27 '14 at 18:29
I don't know what to tell you, I'm looking at the query it generated my laravel-debugbar, and I can clearly see that the two parameter replacements are backwards. And that makes sense because the `where` clause actually comes after the `select` clause, even though I've added it first. – mpen Apr 27 '14 at 19:01
Mark, query bindings are added to the query in the same order as the chained methods, so it doesn't matter if it's WHERE that will go to the end or anything else. You may use Model::where(..)->select(..DB::raw())->addBinding()->where(..) ... and the order will be exactly the same. – Jarek Tkaczyk Apr 27 '14 at 19:51
I'm not convinced. My understanding is that Laravel builds a full SQL query and then runs it through PDO at the end. The parameters get pushed into an array as they're added; if the SQL comes out in a different order, then they won't align. But there's no sense arguing about it, I've written a test: [this code](http://pastebin.com/7t0SSU69) produces [this result](http://i.imgur.com/lQtjLpZ.png) – mpen Apr 27 '14 at 20:52
We can take it one step further: http://pastebin.com/8reK1hTJ When you add the binding matters! – mpen Apr 27 '14 at 20:53
1

Uhm, yes you are 100% right. Sorry for that mistake. I just checked other possibilities of chaining and apparently it's bugged. I'm going to give it a closer look and find a fix for that. – Jarek Tkaczyk Apr 27 '14 at 21:10

How to escape a query using an Eloquent model select?

2 Answers2

Linked