Quotes cause SQL havoc

Rails
1

Hi-

I cannot seem to find an answer to this. I have a simple question
with quotes- I want to allow apostrophes in a string in a model. I
then want to allow searching on that field. In ActiveRecord, when I
create a search using find, and say

:conditions=> “title like ‘%#{query}%’ or body like ‘%#{query}%’”

where query is the search string, I get an exception when the user
enters an apostrophe because it messes with the SQL (the apostrophe
closes the query). How do I escape apostrophes but keep them in there
so they’ll match records in the db?

Thanks,
Dino

2

Hi Dino,

You can use ActiveRecord’s built in escaping by changing that code
slightly - you can pass arguments separately from your condition
string:

:conditions => [“title LIKE ?”, “%#{query}%”]

If you need to use the ‘query’ variable more than once, you can use
placeholders:

:conditions => [“title LIKE :query OR body LIKE :query”, {:query =>
“%#{query}%”}]

This is also safer as you’ll be protected from SQL injection attacks.

Hope that helps,

Steve

3

Thanks Steve! Works great.

Dino