How to escape for MYSQL queries from Ruby on Rails?

Question

When searching the MYSQL database for a Rails 2.3.14 app, I need to escape the search string appropriately so I can search for strings containing single-quotes (apostrophes). What's the best way to do this? I'm using the mysql gem, in case that matters.

Solution 1

You can use ActiveRecord's quote method (e.g. ActiveRecord::Base.connection.quote("string with ' apostrophe")), but ActiveRecord's query methods already escape your SQL for you. For example:

a = "string with ' apostrophe"
ModelName.where("field1 = ?", a)

will change "string with ' apostrophe" to "string with '' apostrophe"

Solution 2

Rails quotes strings as follows:

# Quotes a string, escaping any ' (single quote) and \ (backslash) characters.
def quote_string(s)
  s.gsub(/\\/, '\&\&').gsub(/'/, "''") # ' (for ruby-mode)
end

Solution 3

When using the mysql gem, you gain the method Mysql.escape_string(). Use as follows:

search_terms = Mysql.escape_string("it's working!")
conditions = [ "table1.name LIKE '%#{search_terms}%'" ]
# use conditions for MYSQL query as appropriate