I was trying to save a data with DB::raw of mysql CONCAT function, but I was stucked and keeps failing on saving once the case if the input is special characters I guess. bellow is my code.
$sample_input = `from mobile$#@@hggg$_-++?!;:hh:'"#1f`;
$histCon = 'CONCAT(history, "'.$sample_input.'" Updated from [E] on 2020-03-19 20:09:50 by: nix")';
$model = ModelTable::query()->where('encounter_nr', $enc_no)->first();
$model->final_diag = $sample_input;
$model->other_diag = 'sample others value';
$model->modify_date = Carbon::now()->toDateTimeString();
$model->modify_id = $personelUsername;
$model->history = DB::raw($histCon);
$res = $model->save();
but I keep getting this error
"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '") where `encounter_nr` = ?' at line 2 (SQL: update `modelTable` set `final_diagnosis` = from mobile$#@@hggg$_-++?!;:hh:'"#1f, `modify_date` = 2020-03-19 20:09:50, `history` = CONCAT(history, "Final Diagnosis: from mobile$#@@hggg$_-++?!;:hh:'"#1f Updated from [E] on 2020-03-19 20:09:50 by: nix ↵") where `encounter_nr` = ?)"
As you can see in the error message, where the whole query where suppose to be in parameterized form.
I guess the issue is in the DB::raw, since its value contains with special characters, and causes bug on the entire eloquent builder, so how should implement safe values DB::raw queries applied with update/insert transactions in Laravel eloquent way?
Edited: I tried apply paremetrized on DB::raw
$histValue = "Final Diagnosis: $sample_input Updated from [E] on 2020-03-20 02:51:51 by: nix 2020-03-20 02:51:51 by: nix ";
$histCon = 'CONCAT(history, :histValue)';
$model->history = DB::raw($histCon,[
'histValue' => $histValue
]);
$res = $model->save();
but I got this error instead.
"SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters (SQL: update `seg_soa_diagnosis` set `final_diagnosis` = from mobile$#@@hggg$_-++?!;:hh:'"#1f, `other_diagnosis` = `sample others value`, `modify_date` = 2020-03-20 02:52:45, `history` = CONCAT(history, :histValue) where `encounter_nr` = 2020300091)"
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire