在 Laravel 9 中使用原生表达查询时，报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

1、在 Laravel 9 中使用原生表达查询时，报错：SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;。原因应该与 Hannah O’ Conno 有关。其中存在 ‘ 。如图1

图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、参考：在 Laravel 9 中使用原生表达查询时，in 条件用于多个字段

3、决定在 whereRaw 中采用绑定参数的形式，以转义 ‘ 字符。第二个参数是可选项，值是一个绑定参数的数组。调整如下

	// $builder->whereRaw('(' . $criteria['whereRawIn'][0] . ') in (' . $str . ')');
	$builder->whereRaw('(?) in (?)', [$criteria['whereRawIn'][0], $str]);

4、不再报错，生成的 SQL 如下。符合预期。Hannah O\’ Conno 已经自动转义。如图2

图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