Escaping a single quotation within SQL query

Question

I have a table companies, which has two columns named name and address. By running the following code, new data are inserted into the table:

my_name = "my company name"
my_address = "ABC"

query = "INSERT INTO companies (name,address) VALUES ('#{my_name}','#{my_address}');"

ActiveRecord::Base.connection.execute(query);

If I change my_name value from "my company name" to "John's company", I will get a syntax error. This is because the query becomes:

"INSERT INTO companies (name,address) VALUES ('John's company','ABC');"

and 'John's company' has a single quotation mark within it.

Given that I have already used double quotation mark for the query string definition, how can I get rid of this error regarding the single quotation mark in my value?

Answer 1

If you must do it this way then use the quote method on the connection object:

quote(value, column = nil)
Quotes the column value to help prevent SQL injection attacks.

So something like this:

my_name    = ActiveRecord::Base.connection.quote("John O'Neil")
my_address = ActiveRecord::Base.connection.quote("R'lyeh")

query = "INSERT INTO companies (name,address) VALUES (#{my_name}, #{my_address})"

ActiveRecord::Base.connection.execute(query);

Never ever try to handle your own quoting. And don't try to use double quotes for quoting an SQL string literal, that's what single quotes are for; double quotes are for quoting identifiers (such as table and column names) in most databases but MySQL uses backticks for that.