1. When using raw expression queries in Laravel 9, an error occurs: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;. The cause should be related to Hannah O’ Conno. It contains a ‘ character. See Figure 1.
![When using raw expression queries in Laravel 9, an error occurs: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;. The cause should be related to Hannah O' Conno. It contains a ' character](https://media.shuijingwanwq.com/2024/04/1.png)
JSON
{
"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: Using the in condition for multiple fields when querying with raw expressions in Laravel 9
3. I decided to use bound parameters in whereRaw to escape the ‘ character. The second parameter is optional, and its value is an array of bound parameters. The adjustment is as follows.
PHP
// $builder->whereRaw('(' . $criteria['whereRawIn'][0] . ') in (' . $str . ')');
$builder->whereRaw('(?) in (?)', [$criteria['whereRawIn'][0], $str]);
4. The error no longer occurs, and the generated SQL is as follows. It meets expectations. Hannah O\’ Conno has been automatically escaped. See Figure 2.
SQL
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