问题描述
我有一个表公司，其中有两列名为 name 和 address 。通过运行以下代码，新数据将插入到表中：
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);
如果我将 my_name 值从我的公司名称到John's company，我会收到一个语法错误。这是因为查询变成：
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');"
和'约翰公司'有一个引号在其中。
鉴于我已经为查询字符串定义使用双引号，我如何能够摆脱我的单引号错误价值？
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?
推荐答案
如果你必须这样做，然后使用： p>
If you must do it this way then use the quote method on the connection object:
所以这样的东西：
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);
从来没有试过处理你自己的引用。并且不要尝试使用双引号引用SQL字符串文字，这是单引号;双引号用于引用大多数数据库中的标识符（如表和列名称），但MySQL使用反引号。
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.
这篇关于在SQL查询中转义单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！