Mysql error on sql like operator rails

Question

I get an 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 '%booked%)

How can I solve this?

Model

scope :with_type, lambda { |type|
   where("LOWER(reservations.message) LIKE %#{type}%")
}

Sql statement on server

SELECT  `reservations`.*
FROM `reservations`
LEFT OUTER JOIN `users` ON `users`.`id` = `reservations`.`from_id`
WHERE (`users`.`access_level_id` != 1)
AND `reservations`.`id_to` IS NULL
AND (LOWER(reservations.message) LIKE %booked%)
ORDER BY reservations.created_at desc LIMIT 10 OFFSET 0)

mu is too short · Accepted Answer · 2017-12-02 02:48:53Z

2

SQL's LIKE operator takes strings on both sides so the SQL you want to produce is:

LOWER(reservations.message) LIKE '%booked%'

i.e. %booked% in single quotes. The easiest way to do that is to use a placeholder in your scope:

scope :with_type, ->(type) { where('LOWER(reservations.message) LIKE ?', "%#{type}%") }

That puts the string interpolation in Ruby and hands that string to where to quote it so you're also protected from injection issues.

answered Dec 2, 2017 at 2:48

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

Mysql error on sql like operator rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related