Error when using native expression query in Laravel 9: SQLState[42000]: syntax error or access vitamin: 1064 you have an error in your SQL syntax;
1. When using native expression query in Laravel 9, an error is reported: SQLState[42000]: Syntax error or access Violation: 1064 you have an error in your SQL syntax;. The reason should be with Hannah OConno is related. in it. as shown in Figure 1
![在 Laravel 9 中使用原生表达查询时，报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;。原因应该与 Hannah O' Conno 有关。其中存在 '](https://www.shuijingwanwq.com/wp-content/uploads/2024/04/1.png)
Figure 1
{
"status_code": 500,
"code": "42000",
"message": "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 'Conno','444451@163.com'),('\u738b\u67d0\u4eba','444451@163.com'),('\u738b\u67d0\u67d0','44445@163.' at line 1 (SQL: select `id`, `name`, `email`, `type` from `customers` where (name,email) in (('\"Hannah O' Conno','444451@163.com'),('\u738b\u67d0\u4eba','444451@163.com'),('\u738b\u67d0\u67d0','44445@163.com'),('\u738b\u67d0\u4eba1','444451@163.com'),('\u5ba2\u6237\u59d3\u540d','44445@163.com'),('\u738b\u67d0\u67d0','4444@163.com'),('\u5ba2\u6237\u59d3\u540d100','2222200@qq.com'),('\u5ba2\u6237\u59d3\u540d200','3333300@qq.com'),('\u738b\u67d0\u4eba','44445@163.com'),('\u674e\u67d0\u67d0','4444@163.com'),('\u5ba2\u6237\u59d3\u540d','4444@163.com'),('\u738b\u67d0\u4eba','4444@163.com'),('\u5ba2\u6237\u59d3\u540d2','444444442@163.com'),('\u5ba2\u6237\u59d3\u540d1','44444@163.com'),('\u738b\u67d0\u4eba2','4444@163.com'),('Person-nl Person-nl','customer+denied@email.nl'),('zhuo lynn','zhuolynn881219@gmail.com'),('Harry Potter','fanyan154@gmail.com'),('Test Person-us','customer@email.us'),('1','18345038699@163.com'),('HF','123456@qa.com'),('Daly Siennaa','heodloklrs@iubridge.com'),('ying guo','18345038699@163.com'),('\u90ed\u5f71','425233655@qq.com')) order by `id` desc)",
"trace": {
"line": 759,
"file": "E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php",
"class": "Illuminate\\Database\\QueryException",
"trace": {
"previous": [
"#0 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php(413): PDO->prepare('select `id`, `n...')",
"#1 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php(752): Illuminate\\Database\\Connection->Illuminate\\Database\\{closure}('select `id`, `n...', Array)",
"#2 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php(719): Illuminate\\Database\\Connection->runQueryCallback('select `id`, `n...', Array, Object(Closure))",
"#3 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Connection.php(421): Illuminate\\Database\\Connection->run('select `id`, `n...', Array, Object(Closure))",
"#4 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Database\\Query\\Builder.php(2636): Illuminate\\Database\\Connection->select('select `id`, `n...', Array, true)",
"#5
]
}
}
}
2. Reference:In condition for multiple fields when using native expression query in Laravel 9
Decide to use the form of binding parameters in whereraw to escapecharacters. The second parameter is optional, and the value is an array of binding parameters. adjustment as follows
// $builder->whereRaw('(' . $criteria['whereRawIn'][0] . ') in (' . $str . ')');
$builder->whereRaw('(?) in (?)', [$criteria['whereRawIn'][0], $str]);
4. No more errors, the generated SQL is as follows. in line with expectations. hannah o\Conno has been automatically escaped. as shown in Figure 2
Figure 2
select
`id`,
`name`,
`email`,
`type`
from
`customers`
where
('name,email') in (
'(\'\"Hannah O\' Conno\',\'444451@163.com\'),(\'王某人\',\'444451@163.com\'),(\'王某某\',\'44445@163.com\'),(\'王某人1\',\'444451@163.com\'),(\'客户姓名\',\'44445@163.com\'),(\'王某某\',\'4444@163.com\'),(\'客户姓名100\',\'2222200@qq.com\'),(\'客户姓名200\',\'3333300@qq.com\'),(\'王某人\',\'44445@163.com\'),(\'李某某\',\'4444@163.com\'),(\'客户姓名\',\'4444@163.com\'),(\'王某人\',\'4444@163.com\'),(\'客户姓名2\',\'444444442@163.com\'),(\'客户姓名1\',\'44444@163.com\'),(\'王某人2\',\'4444@163.com\'),(\'Person-nl Person-nl\',\'customer+denied@email.nl\'),(\'zhuo lynn\',\'zhuolynn881219@gmail.com\'),(\'Harry Potter\',\'fanyan154@gmail.com\'),(\'Test Person-us\',\'customer@email.us\'),(\'1\',\'18345038699@163.com\'),(\'HF\',\'123456@qa.com\'),(\'Daly Siennaa\',\'heodloklrs@iubridge.com\'),(\'ying guo\',\'18345038699@163.com\'),(\'郭影\',\'425233655@qq.com\')'
)
order by
`id` desc
