Pattern matching in rails ( "where column LIKE '%foo%") with Postgres

Question

I have a Person model that includes names, and I want to search these as simply as possible.

Is there a rails/ActiveRecord method along the lines of People.like(:name => "%#{query}%"), like what DataMapper has? I couldn't find anything like this in the ActiveRecord docs but I'm shocked if it's simply not possible.

Currently I have it doing Person.where "name LIKE '%#{query}%'", which works great but is an obvious SQL-injection vulnerability.

Rails 3.2

"The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension." — AlexQueue
– AlexQueue, Commented Jul 9, 2013 at 18:16

markquezada · Accepted Answer · 2016-10-21 21:12:55Z

45

Use a parameterized query instead to avoid SQL-injections, like so:

Person.where('name LIKE ?', '%' + query + '%')

Note that the percent signs must be part of the parameter, not the where clause or Rails will escape it and you'll get a syntax error. (At least on postgres.)

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "%"
LINE 1: ...name LIKE %'John...
                     ^

edited Oct 21, 2016 at 21:12

markquezada

8,5656 gold badges47 silver badges53 bronze badges

answered Jul 9, 2013 at 18:11

Luís Ramalho

10.2k4 gold badges54 silver badges69 bronze badges

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

Collectives™ on Stack Overflow

Pattern matching in rails ( "where column LIKE '%foo%") with Postgres

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