Rails 4 LIKE query - ActiveRecord adds quotes
Solution 1
Your placeholder is replaced by a string and you're not handling it right.
Replace
"name LIKE '%?%' OR postal_code LIKE '%?%'", search, search
with
"name LIKE ? OR postal_code LIKE ?", "%#{search}%", "%#{search}%"
Solution 2
Instead of using the conditions
syntax from Rails 2, use Rails 4's where
method instead:
def self.search(search, page = 1 )
wildcard_search = "%#{search}%"
where("name ILIKE :search OR postal_code LIKE :search", search: wildcard_search)
.page(page)
.per_page(5)
end
NOTE: the above uses parameter syntax instead of ? placeholder: these both should generate the same sql.
def self.search(search, page = 1 )
wildcard_search = "%#{search}%"
where("name ILIKE ? OR postal_code LIKE ?", wildcard_search, wildcard_search)
.page(page)
.per_page(5)
end
NOTE: using ILIKE
for the name - postgres case insensitive version of LIKE
Solution 3
While string interpolation will work, as your question specifies rails 4, you could be using Arel for this and keeping your app database agnostic.
def self.search(query, page=1)
query = "%#{query}%"
name_match = arel_table[:name].matches(query)
postal_match = arel_table[:postal_code].matches(query)
where(name_match.or(postal_match)).page(page).per_page(5)
end
Solution 4
ActiveRecord is clever enough to know that the parameter referred to by the ?
is a string, and so it encloses it in single quotes. You could as one post suggests use Ruby string interpolation to pad the string with the required %
symbols. However, this might expose you to SQL-injection (which is bad). I would suggest you use the SQL CONCAT()
function to prepare the string like so:
"name LIKE CONCAT('%',?,'%') OR postal_code LIKE CONCAT('%',?,'%')", search, search)
Solution 5
If someone is using column names like "key"
or "value"
, then you still see the same error that your mysql query syntax is bad. This should fix:
.where("`key` LIKE ?", "%#{key}%")
Comments
-
Harry Forbess over 3 years
I am trying to do a like query like so
def self.search(search, page = 1 ) paginate :per_page => 5, :page => page, :conditions => ["name LIKE '%?%' OR postal_code like '%?%'", search, search], order => 'name' end
But when it is run something is adding quotes which causes the sql statement to come out like so
SELECT COUNT(*) FROM "schools" WHERE (name LIKE '%'havard'%' OR postal_code like '%'havard'%')):
So you can see my problem. I am using Rails 4 and Postgres 9 both of which I have never used so not sure if its and an activerecord thing or possibly a postgres thing.
How can I set this up so I have like
'%my_search%'
in the end query? -
jdscosta91 over 9 yearsIsn't this vulnerable to SQL Injection? I mean, are those
search
strings sanitized? -
house9 over 9 years@jdscosta91 the
?
in the where will take care of sanitizing -
Barry Kelly over 9 years@house9 instances of
%
and_
insidesearch
will not be sanitized, under this approach. -
David Hoelzer almost 9 yearsWhen using '?' in this way in rails it is converted to a parameterized query. The data within the parameter isn't sanitized (the %) but it is impossible to change context out of the query and turn it into processed SQL statements.
-
cevaris about 8 yearsShould really be easy to create a func to do this dynamically with a list of attributes
-
Pedro Rolo about 7 yearsUntested, though it could be something like this: scope search_attributes, ->(query, attributes){ arel_attributes = attributes.map{|a| arel_table[a]} arel_queries = arel_attributes.map{|a| a.matches(query)} return where(arel_queries.reduce{|res,q| res.or q}) }
-
fuzzygroup about 7 yearsI just implemented this in an application and it worked great. Thank you John.
-
sekmo over 4 yearsIs this still valid for Rails 5? Because if I have
Movie.where("title ILIKE :s", s: search_string)
it gets translated toSELECT 1 AS one FROM "movies" WHERE (title ILIKE 'test') LIMIT $1
by ActiveRecord (Rails 5.1.6) - please notice that there is no percentage symbol after the ILIKE) -
house9 over 4 years@sekmo - it should work, percentage would go in the
search_string
variable. I think theSELECT 1 AS one
output is in rails console only or you are usinglimit(1)
? FYI: Rails 5.1.6 has security issues, use 5.1.6.2 or 5.1.7 instead -
estani over 4 yearsregarding injections, no it does not and in any case this makes no difference. The string is inserted into the sql and rails should have validated it before (doesn't matter if a
%
is appended/prepended or not). Either it works as expected or rails has a major bug affecting both cases.