CODEWITHSUNDEEP
mysqlruby-on-railsescaping
joanwolk
joanwolk

Reputation: 1105

How to escape for MYSQL queries from Ruby on Rails?

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.

Upvotes: 9

Views: 13796

Answers (3)

JJD
JJD

Reputation: 51834

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

Upvotes: 10

Angelo
Angelo

Reputation: 492

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"

Upvotes: 7

joanwolk
joanwolk

Reputation: 1105

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

Upvotes: 8

Related Questions