CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
AlexQueue
AlexQueue

Reputation: 6551

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

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

Upvotes: 15

Views: 14194

Answers (1)

Luís Ramalho
Luís Ramalho

Reputation: 10208

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...
                     ^

Upvotes: 45

Related Questions