prograhammer prograhammer - 9 months ago 158
PHP Question

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

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)

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?


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)


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