在 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
![在 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)
图1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | { "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 中采用绑定参数的形式，以转义 ‘ 字符。第二个参数是可选项，值是一个绑定参数的数组。调整如下
1 2 | // $builder->whereRaw('(' . $criteria['whereRawIn'][0] . ') in (' . $str . ')');$builder->whereRaw('(?) in (?)', [$criteria['whereRawIn'][0], $str]); |
4、不再报错，生成的 SQL 如下。符合预期。Hannah O\’ Conno 已经自动转义。如图2
图2
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 |
近期评论