Mysql2:: error : SQL语法中存在错误

Question

我是ruby on rails中的newby，我有搜索文本框，然后每次我键入一个省略(')例如测试‘单词.....我总是收到错误：

Mysql2::Error: 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 's  word%' OR english_name LIKE '%testing's  word%' OR chinese_name LIKE '%testin' at line 1: SELECT COUNT(DISTINCT `jos_store`.`id`) FROM `jos_store` LEFT OUTER JOIN (SELECT id as store_replenishment, store, MAX(stock_movement) AS stock_movement FROM jos_store_replenishment GROUP BY store) AS replenishment ON replenishment.store = jos_store.id LEFT OUTER JOIN jos_stock_movement ON jos_stock_movement.id = replenishment.stock_movement WHERE (store_id LIKE '%testing's  word%' OR english_name LIKE '%testing's  word%' OR chinese_name LIKE '%testing's  word%')

你能帮我解决我的问题吗？

Answer 1

尝试转义撇号，例如english_name LIKE '%testing\'s word%‘

如果你使用的是‘%Phras%’，并且有另一个‘inside %PHRASE%’，它会认为你已经结束了like子句，并给你一个错误--如果你用转义字符进行转义，例如\'，它应该会起作用:因此，对于你的错误短语，如下所示：

<snip /> WHERE (store_id LIKE '%testing\'s  word%' OR english_name LIKE '%testing\'s  word%' OR chinese_name LIKE '%testing\'s  word%')    

请注意，我使用了‘% the \’的单词%‘来确保它不会认为第二个撇号结束LIKE子句