Parameterized query binding in ON clause for a LEF

问题:

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?

回答1:

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.

回答2:

This comes straight from the Laravel docs:

The Laravel query builder uses PDO parameter binding throughout to protect your application against SQL injection attacks. There is no need to clean strings being passed as bindings.

You shouldn't need to sanitize it at all. It should be fine. If you are worried about it though, you can use the Validator class to validate it however you want.

回答3:

Currently you can use $join->where:

$year = '2013';

$awards = DB::table('awards')
         ->leftJoin('winners', 
               function($join) use ($year)
               {
                    $join
                        ->on('awards.id','=','winners.award_id')
                        // "where" instead of "on":
                        ->where('winners.year', '=', $year);
               }
         ->where('awards.type','color')
         ->get();