vendredi 29 mars 2019

Laravel DB::raw bind parameters

I have problem when trying to bind the data in to query. I'm use php framework Laravel 5.7 version.

Trying to bind data this way:

DB::raw("(
        select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 )  * (drivers.salary_per_hour / 3600) / 10)
        from assignments
        where assignments.driver_id = drivers.id 
        and assignments.driving_day between STR_TO_DATE('?', '%Y-%m-%d') and STR_TO_DATE('?', '%Y-%m-%d') ) as salary",[
            $dates['from'],
            $dates['to']
        ])

and receive null as result.

$dates array values:

['from' => '2019-03-01', 'to' => '2019-03-31']

But if I'm doing it this way it work fine, but there is SQL injection, and I'm think it will be better if I'm bind the data.

$drivers = Driver::select([
        'id', 'name', 'surname', 'phone', 'driver_status','driver_status', 'updated_at', 'updated_at', 'photo',
        'salary_per_hour',
        DB::raw("(
            select 10 * FLOOR(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 )  * (drivers.salary_per_hour / 3600) / 10)
            from assignments
            where assignments.driver_id = drivers.id 
            and assignments.driving_day between STR_TO_DATE('".$dates['from']."', '%Y-%m-%d') and STR_TO_DATE('".$dates['to']."', '%Y-%m-%d') ) as salary"),
        DB::raw("(
            select SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(end_time, start_time)) - 3600 ))
            from assignments
            where assignments.driver_id = drivers.id 
            and assignments.driving_day between STR_TO_DATE('".$dates['from']."', '%Y-%m-%d') and STR_TO_DATE('".$dates['to']."', '%Y-%m-%d') ) as worked_hours"),
    ])->whereHas('assigments', function ($query) use ($dates) {
        $query->whereBetween('driving_day', [$dates['from'], $dates['to']]);
    });

    $table = $drivers->paginate(15);

What's wrong in my data binding?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire