[sql] Rails 4 LIKE query - ActiveRecord adds quotes

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?

Try

 def self.search(search, page = 1 )
    paginate :per_page => 5, :page => page,
      :conditions => ["name LIKE  ? OR postal_code like ?", "%#{search}%","%#{search}%"],   order => 'name'
  end

See the docs on AREL conditions for more info.

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

.find(:all, where: "value LIKE product_%", params: { limit: 20, page: 1 })

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

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}%")

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)