Parameterized query binding in ON clause for a LEFT JOIN in Laravel Eloquent / Query Builder

eloquent, laravel, laravel-4, parameterized-query, php

Solution

Here's an odd work-around (didn't want to extend the Builder and JoinClause classes): Notice: This will break query chaining with `->` so notice the `where` was seperated below.

$query = DB::table('awards')
         ->leftJoin('winners', function($join)
               {
                    $join->on('awards.id','=','winners.award_id');
                    $join->on('winners.year','=',DB::raw('?'));  
               }
         ->setBindings(array_merge($query->getBindings(),array($year)));

$query->where('awards.type','color');

$awards = $query->get();

UPDATE: Taylor added `joinWhere`, `leftJoinWhere`... he says that "if you have a function join just use `->where` and `->orWhere` from within the Closure." I've yet to try this though.

Problem

Let's say I want to show a full list of awards with type="color": ``` Awards Type 2013 Winner ====== ==== =========== Blue Award color Tom Red Award color Green Award color Dan ``` To achieve this result I could have a query in Laravel like this: ``` $year = '2013'; $awards = DB::table('awards') ->leftJoin('winners', function($join) use ($year) { $join->on('awards.id','=','winners.award_id'); $join->on('winners.year','=',DB::raw("'".$year."'")); } ->where('awards.type','color') ->get(); ``` If you output the SQL that Laravel generates you will see that only the WHERE clause is parameterized and $year in the ON clause is left vulnerable to sql injection if I get it from an untrusted source. Also the query's caching potential is reduced because $year will change often. Note: In case you were thinking that I just add the second left join condition to the WHERE of the query, these are not the same. Any ideas on how to get the $year part of the query parameterized?

Original source

Related problems