Framework: Parameter binding on joins, especially leftJoins

[prograhammer]

Illuminate/Database/Query/Builder class
Currently only values from the where clause get added to the bindings array, and values in join on clauses are not parameterized and therefore susceptible to SQL injection. Also it causes the query to not achieve its full caching potential.

At many times it may be fine to rewrite additional on conditions as where conditions, however for leftJoins, using thewhere is not always an equivalent query (see here).

Additionally, many developers prefer to make use of the additional on conditions because it makes the SQL much more readable, logically seperated and understandable when debugging or reviewing (especially if you are not the developer who wrote the query) as opposed to the conditions being unnecessarily lumped together in a large whereclause.

I propose a solution that allows for the passing of an array of bindings to be coupled with a query Expression, for example:

$query = DB::table('users');
$query->select('users.name','roles.type');
$query->leftJoin('roles', function($join)
            {
                 $join->on('roles.id','=','users.role_id');
                 $join->on('roles.type','=',DB::raw('?'),array('admin')); // <-- binding(s) added here
            });
return $query->get();

| All Users | is Admin? |
| --- | :-: |
| Tom | admin |
| Dan | |
| John | admin |

I'm new to gitHub and hope to upload some Laravel packages soon. But thought I would bring this up. Any ideas?

[prograhammer]

The current work-around I'm using (I didn't want to extend classes like Builder and JoinClause):
(notice: this breaks the query chaining with ->)

$query = DB::table('users');
$query->select('users.name','roles.type');
$query->leftJoin('roles', function($join)
            {
                 $join->on('roles.id','=','users.role_id');
                 $join->on('roles.type','=',DB::raw('?')); 
            })
       ->setBindings(array_merge($query->getBindings(),array("admin"))); // <-- binding(s) added here
return $query->get();

[ammont]

+1

this is needed also on addSelect and select, I guess it is better to add the bindings to the Expression object so we can use it like this:

DB::raw("roles.type = ?", array('admin'));

[taylorotwell]

Done. joinWhere, leftJoinWhere... if you have a function join just use ->where and ->orWhere from within the Closure.

[pilot911]

please, explain how to use joinWhere