can you use activerecord to find substring of a field? (quick & dirty keyword finder)

Question

Suppose a database contains a field 'keywords' and sample records include: "pipe wrench" "monkey wrench" "crescent wrench" "crescent roll" "monkey bars"

is there a way in activerecord to find the records where the keyword field contains the substring "crescent"?

(It's just a quick and dirty lookup for a quick concept prototype)

score 108 · Accepted Answer · answered Feb 18 '11 at 17:21

108

Yeah, just use a LIKE statement in MySQL.

In Rails 2.x:

Table.find(:all, :conditions => ['keywords LIKE ?', '%crescent%'])

In Rails 3.x:

Table.where('keywords LIKE ?', '%crescent%').all

answered Feb 18 '11 at 17:21

Pan Thomakos

34,082
9
88
85

1

Isn't this vulnerable to sql injection? – Edward Nov 24 '12 at 20:21
12

No, Rails auto-escapes the parameter. `where("keyword LIKE '#{query}'")` is vulnerable, but `where("keyword LIKE ?", query)` is not. – Pan Thomakos Dec 10 '12 at 21:55
10

Rails 4 works the same way as Rails 3 w/ regards to this question. – Pan Thomakos Sep 23 '14 at 06:22
Awesome! I'm sure I will come back to this answer if the Syntax happens to escape me again. Thanks a bunch. – aaron-coding Jun 04 '15 at 19:35
To be clear, is it autoescaping or using parameterized queries?. Because autoescaping is dangerous regardless of whats claimed. Theres a long history of the idea failure (notably to unicode substitution exploits) , the most famous being PHPs notorious "magic quotes" dumpster fire (Which was then followed by two separate attempts at explicit escape functions before the whole thing was marked "Thar be dragons!" and people started using parameterized queries. – Shayne Nov 04 '16 at 07:52
3

Wondering how expensive this query is? I ran it on a large database, and it froze. – Petr Gazarov Dec 02 '16 at 04:51
1

Not that this still has a sql vulnerability. If the input the user provides is '%%%' they're going to get all results unless you manually perform an escaping op – Courtland Caldwell Mar 16 '17 at 23:57

score 22 · Answer 2 · edited Feb 17 '14 at 07:57

22

The Postgres database syntax would be:

YourModelName.where("yourFieldName like ?", "%" + yourSearchTerm + "%")

edited Feb 17 '14 at 07:57

user2314737

27,088
20
102
114

answered Feb 17 '14 at 07:34

Martin Åhlin

2,730
1
20
27

score 9 · Answer 3 · answered Feb 18 '11 at 17:23

9

It all depends on your DB. Is it Postgres? MySQL? MongoDB? Anything else?

With Postgres, you could use something like :

Rails 2.x => Model.find(:all, :conditions=>["models.keywords ~= ?", 'crescent'])
Rails 3.x => Model.where("models.keywords ~= ?", 'crescent')

You just have to find the right syntax for your DB / Rails / ActiveRecord version.

answered Feb 18 '11 at 17:23

christianblais

2,448
17
14

1

Thank you that was also helpful. – jpw Feb 18 '11 at 18:00

score 1 · Answer 4 · answered Sep 20 '18 at 18:45

I had a similar issue. I needed to see if there are keywords passed from conrolled input in the frontend component in the body of any questions in my questions table. Here is how I did it in my controller:

   def search
     input = params[:q]
       @questions = Question.all
       search_words = input.split(' ')
       @found_question = [];

       search_words.each do |word|
         @found_question << Question.where("questions.body LIKE ?", "%#{word}%")
     end
   end

can you use activerecord to find substring of a field? (quick & dirty keyword finder)

4 Answers4

Linked

Related