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)

Added an edit to my answer related to your latest edit, let me know if that helps! — Deleteman, Mar 14 '12 at 19:59
See my comment to @Alisher's answer for answer to your edited question. `Topic.where("name like ?","#{@name}%")` would be one way. — Tom Harrison, Mar 14 '12 at 20:02

Deleteman · Accepted Answer · 2012-03-14T19:59:13.780

182

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%

edited Mar 14 '12 at 19:59

answered Mar 14 '12 at 19:09

Deleteman

8,500
6
25
39

3

Do you think this method leads to SQL injection? – Aswin Ramakrishnan May 13 '15 at 19:42
2

As far as I can tell [here](http://rails-sqli.org/) 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. – GKnight Nov 08 '15 at 00:37
how about not contain a certain string? – zx1986 Feb 15 '17 at 08:17
5

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. – bkunzi01 Mar 31 '17 at 14:20
3

@bkunzi01 is right as stated in https://guides.rubyonrails.org/active_record_querying.html#array-conditions and https://guides.rubyonrails.org/security.html#sql-injection – Fabrizio Bertoglio Oct 24 '18 at 15:52

score 15 · Answer 2 · answered Apr 14 '17 at 08:03

15

With PostgreSQL you can also use match operators:

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

answered Apr 14 '17 at 08:03

Szymon Rut

835
1
9
13

score 12 · Answer 3 · edited May 23 '17 at 10:30

12

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

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

edited May 23 '17 at 10:30

Community

1
1

answered Mar 14 '12 at 19:13

ScottJShea

7,041
11
44
67

1

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 – Michael Durrant Mar 14 '12 at 19:19
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! – ScottJShea Mar 14 '12 at 19:20
6

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 Mar 14 '12 at 19:42

score 11 · Answer 4 · edited Oct 05 '16 at 09:01

11

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

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

edited Oct 05 '16 at 09:01

Outside_Box

447
1
4
16

answered Mar 14 '12 at 19:15

Alisher Ulugbekov

2,039
2
13
8

Is that supposed to be "name like '?%' " or "name like '%?%' " – varatis Mar 14 '12 at 19:35
3

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]}%").` – Tom Harrison Mar 14 '12 at 19:58
Beautiful answer. – Mario Zigliotto Apr 16 '16 at 16:04

asitmoharna · Answer 5 · 2012-03-14T19:51:02.933

2

Try

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

edited Mar 14 '12 at 19:51

answered Mar 14 '12 at 19:10

asitmoharna

1,484
11
15

SQL injection in first example, should be `Topic.where("name like ?","%apple%")`. Second one's OK. – Tom Harrison Mar 14 '12 at 19:48
2

You can also do this in Rails 4+: `Topic.find_by("name like ?", "%apple%")` – Paul Danelli Mar 15 '17 at 17:23

Rails: How to find_by a field containing a certain string

5 Answers5

Linked

Related