Laravel Eloquent/Query Builder 中 LEFT JOIN 的 ON 子句中的参数化查询绑定

Parameterized query binding in ON clause for a LEFT JOIN in Laravel Eloquent / Query Builder(Laravel Eloquent/Query Builder 中 LEFT JOIN 的 ON 子句中的参数化查询绑定)

问题描述

假设我想显示 type="color" 的完整奖项列表:

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  

为了达到这个结果，我可以在 Laravel 中进行这样的查询:

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

如果您输出 Laravel 生成的 SQL，您将看到只有 WHERE 子句 被参数化，并且 ON 子句 中的 $year 容易受到 sql 注入，如果我从不受信任的来源获取它.此外，查询的缓存潜力也会降低，因为 $year 会经常更改.注意:如果您认为我只是将第二个左连接条件添加到查询的 WHERE，这些不一样.

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.

关于如何将查询的 $year 部分参数化有什么想法吗?

Any ideas on how to get the $year part of the query parameterized?

推荐答案

这里有一个奇怪的解决方法(不想扩展 Builder 和 JoinClause 类):
注意:这会破坏 -> 的查询链，所以请注意 where 在下面被分隔.

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

更新:泰勒添加 joinWhere, leftJoinWhere... 他说如果你有一个函数连接，只需使用 ->where 和 ->orWhere从封闭内."不过我还没有尝试过.

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.

这篇关于Laravel Eloquent/Query Builder 中 LEFT JOIN 的 ON 子句中的参数化查询绑定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！