laravel-5insert-updateaddslashes

Laravel 5.7 - Query builder not escaping quotes or backslashes correctly when using updateOrInsert()

I have an array with some strings and special caracter in the values.

If I use the insert method it works good. 

$result = DB::connection('host1')->table('tblName')->insert($data);

If I use the update method it works good.

$result = DB::connection('host1')->table('tblName')->where('id',$data['id'])->update($data);

But if I use the method updateOrInsert it doesn't work. It seems to be an old issue.

$result = DB::connection('host1')->table('tblName')->updateOrInsert([
            'id'=>$data['id'],
            $data
        ]);

This is the error I get:

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 'No Annual Fee `1` = ?)) as `exists`' at line 1 (SQL: select exists(select * from `tblName` where (`id` = 56429444 No Annual Fee `1` = 56429444)) as `exists`)

It seems to be something that I could solve escaping with addslashes() when using SQL EXISTS.. Maybe the first query for this method.

But even If I loop trough the array and escape each value it still not working. 

foreach ($data as $k=>$v){
   $dt[$k]=addslashes($v);
}

Any ide in how to solve it using updateOrInsert?

Solution

  • The function signature is:

    updateOrInsert(array $attributes, array $values = [])

    So you should keep separated the $attributes array that is checked for existence from the $values array, something like that:

    $result = DB::connection('host1')->table('tblName')
          ->updateOrInsert(['id'=>$data['id']], $data);