Mysql2::Error: You have an error in your SQL syntax

Question

I'm newby in ruby on rails, I have search textbox then everytime I type an apotraphe (') e.g testing' word ..... I always recieved error:

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%')

can you help me with my problem.

Could you post the code where you create/generate the SQL request ? — Baldrick
– Baldrick, Commented Jul 13, 2012 at 8:46
You should briefly explain your problem to get right solution. Like include your code etc. — Ganesh Kunwar
– Ganesh Kunwar, Commented Jul 13, 2012 at 9:05
I have this error when I navigate to rails admin and try to edit one of my users.do you have any ideas ? — Afsanefda
– Afsanefda, Commented Sep 5, 2017 at 10:32

FOOM · Accepted Answer · 2012-07-13 09:26:27Z

1

Try escaping your apostrophe, eg english_name LIKE '%testing\'s word%'

if you're using '%PHRASE%' and there's another ' inside %PHRASE%, it thinks you've ended the like clause and gives you an error - if you escape with the escape character, eg \', it should work: so with your error phrase, something like:

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

Notice I've used '%testing\'s word%' to ensure it doesn't think the second apostrophe ends the LIKE clause

edited Jul 13, 2012 at 9:26

answered Jul 13, 2012 at 8:48

FOOM

4285 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lian Over a year ago

how do i put it here in my query? please help me it gives me a headache.. here's my query... search_query = "AND jos_product.product_code LIKE '%#{ search_val }%' OR jos_product.name LIKE '%#{ search_val }%' OR pc.name LIKE '%#{ search_val }%'"

FOOM Over a year ago

OKay, so you'll need to make sure that search_val has escaped quotes in it, so something like: search_val = search_val.gsub("'", "\\\\'") . Note you should put this BEFORE you use the variable in your search string.

Collectives™ on Stack Overflow

Mysql2::Error: You have an error in your SQL syntax

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related