Rails: How to find_by a field containing a certain string

Question

I have a model named Topic, that has a name as a field.

So say I have a term I'm searching for, apple.

If I do a

Topic.find_by_name("apple")

I get a record back with the name apple. That's good -- but how do I change find_by_name so that it can find "apple juice" as well as "apple" -- basically, find names that contain the original query or exactly match the original query?

Edit: Thanks for all the response. I guess I should've been a little more clear earlier, but what if I want to find by a variable name (obviously I'm not going to want to find by the name "apple" everytime :) )?

How do I manipulate Topic.where to accommodate for this? So something like...

@topic = Topic.where(......., @name)

Solution 1

I think something like this should work:

Topic.where("name like ?", "%apple%")

To accomodate for your edit:

Topic.where("name like ?", "%#{@search}%")

Basic string interpolation, you're using the value of @search inside the string %%, so you @search = "apple" then you end up with %apple%

Solution 2

With PostgreSQL you can also use match operators:

Topic.where("name ~* ?", @search)

Solution 3

Looks like in Rails 3 you would want to use the where:

Topic.where("name ILIKE ?", "%apple%")

Solution 4

Don't put string directly like that. Its called SQL injection. You should instead use .where:

Topic.where("name like '?%' ", params[:name])

Solution 5

Try

Topic.where("name like ?",'%apple%')
Topic.find(:all, :conditions => ["name like ?","%apple%"])

View more solutions

Comments

[Michael Durrant]

Not sure if this would work - is "~=" valid sql? Or are you thinking of the ruby pattern matcher "=~". If so it wouldn't work here as it isn't sql

[ScottJShea]

Yeah I noticed that in the post I referenced and just took it at face value... I will change to LIKE and be safe. Thanks!

[varatis]

Is that supposed to be "name like '?%' " or "name like '%?%' "

[Tom Harrison]

The ~= operator works for PostgreSQL -- it's a regex match. But yeah, Scott's answer should work. You might want to use ILIKE, which gives a case-insensitive search.

[Tom Harrison]

SQL injection in first example, should be Topic.where("name like ?","%apple%"). Second one's OK.

[Tom Harrison]

This will not work exactly as noted because Rails will automatically quote the string. You'll need to tack on the % before the substitution, for example Topic.where("name like ?", "#{params[:name]}%").

[Aswin Ramakrishnan]

Do you think this method leads to SQL injection?

[GKnight]

As far as I can tell here the where method does nothing to protect against SQL injection. However, the question con only truly be answered by considering if that variable is ever exposed to user input, either through a stored unsanitized value, or directly. That should be the question of interest. The best way to counter SQL injection is to sanitize values upon application entry, even before storage.

[Mario Zigliotto]

Beautiful answer. 😎

[zx1986]

how about not contain a certain string?

[Paul Danelli]

You can also do this in Rails 4+: Topic.find_by("name like ?", "%apple%")

[bkunzi01]

Rails prevents SQL injection as long as you use the ? for the argument as shown above. You'd be left open if you did "name like %@search%" instead.

[Fabrizio Bertoglio]

@bkunzi01 is right as stated in guides.rubyonrails.org/… and guides.rubyonrails.org/security.html#sql-injection