Leem.fin Leem.fin - 1 month ago 8
SQL Question

SQL query execution (with single quotation mark value)

I have a simple problem with SQL query execution(I know the reason but do not know how to get rid of it).

I am using MySQL database. Suppose I have a table named companies , and the companies table has two columns named "name" and "address".

I need to execute SQL query in Rails in the following way:

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

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

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


by running the above code, the new data inserted into the table successfully.

BUT, if I change
my_name
value from "my company name" to "John's company", I will get error:

Mysql2::Error: You have an error in your SQL syntax;


I know the reason,which is because of the single quotation mark in
my_name
value, which yield the query to be:

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


You see the
'John's company'
part which have 3 single quotation mark, and it cause the SQL syntax error.

How to get rid of this error if I have a single quotation mark in my value and I have already used double quotation mark for the query string definition (I mean I have already used
query="...."
)?

Answer

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.