SQL query in Rails using % and LIKE

Question

I am trying to execute a SQL query in my rails app. The following executes normally since it is looking for an exact match:

connection.query("SELECT * 
                  FROM test
                  WHERE y=#{connection.quote(name)}
                  ORDER BY x ASC")

I want to use the LIKE operator to find partial matches. In SQL it would look like:

SELECT * FROM test WHERE y LIKE '%John%' ORDER BY x ASC

How do I do this in my Ruby query? I tried adding % in a few places but it doesn't work. I get errors that say my query is looking for %'John'% instead of '%John%'

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE #{%connection.quote(name)%}
                  ORDER BY x ASC")

@spickermann I appreciate you flagging this but to me it doesn't seem like the same query structure. By reading the post you flagged, it makes me think that I may be writing my query incorrectly or, maybe more aptly, insecurely. Would you be able to re-open this question or explain to me how I can apply the answer you referenced to my situation of concatenating strings as to remove the extra `'` (apostrophe) - Thanks! — jdesilvio, Jul 11 '15 at 04:21

spickermann · Answer 1 · 2021-07-27T05:46:20.933

6

Since you use Rails anyway I suggest using ActiveRecord's query interface instead of plain SQL.

With ActiveRecord the query could be written like this:

Test.where("y LIKE ?", "%#{name}%").order(:x)

You need to have an ActiveRecord model named Test that is configured to use a database table named test (Rails default naming would be tests) to make this work:

# in app/models/test.rb
class Test < ActiveRecord::Base
  self.table_name = 'test'
end

edited Jul 27 '21 at 05:46

answered Jul 11 '15 at 04:36

spickermann

100,941
9
101
131

I really appreciate you answering my question. What are the security differences with using ActiveRecord over a straight query as I was using? Or are there other reasons to use ActiveRecord? – jdesilvio Jul 11 '15 at 04:58

score 1 · Answer 2 · answered Jul 11 '15 at 03:31

1

You want:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE '%#{connection.quote(name)}%'
                  ORDER BY x ASC")

Keep in mind that Ruby variable interpolation just replaces #{foo} with the value of foo, no more, no less. When you add the extra % inside the brackets, you are asking Ruby to evaluate %foo%, which is not a valid Ruby variable.

answered Jul 11 '15 at 03:31

steve klein

2,566
1
14
27

1

This is being evaluated as `'%'John'%'` and throwing an error – jdesilvio Jul 11 '15 at 04:15

score 0 · Accepted Answer · edited May 23 '17 at 12:09

You need to add the %s in Ruby before quoting:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE #{connection.quote('%' + name + '%')}
                  ORDER BY x ASC")

connection.quote will add single quotes to produce a valid SQL string literal and you want to get the %s inside that string literal, hence the Ruby string concatenation before connection.quote is called.

Or you could do it in SQL:

connection.query("SELECT * 
                  FROM test
                  WHERE y LIKE '%' || #{connection.quote(name)} || '%'
                  ORDER BY x ASC")

|| is the standard SQL string concatenation operator, you might need to use the concat function or something else if you're using a database that doesn't really support SQL.

You're better off using the ActiveRecord interface as spickermann suggests but sometimes you need to do it by hand so it is useful to know how.

SQL query in Rails using % and LIKE

3 Answers3