在 SQL 查询中转义单引号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8179760/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Escaping a single quotation within SQL query
提问by Leem.fin
I have a table companies, which has two columns named nameand address. By running the following code, new data are inserted into the table:
我有一个表companies，其中有两列名为name和address。通过运行以下代码，新数据被插入到表中：
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_namevalue from "my company name"to "John's company", I will get a syntax error. This is because the query becomes:
如果我将my_name值从更改"my company name"为"John's company"，则会出现语法错误。这是因为查询变为：
"INSERT INTO companies (name,address) VALUES ('John's company','ABC');"
and 'John's company'has a single quotation mark within it.
并且里面'John's company'有一个单引号。
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?
鉴于我已经在查询字符串定义中使用了双引号，我该如何摆脱关于我的值中单引号的错误？
回答by mu is too short
If you must do it this way then use the quotemethodon the connection object:
如果您必须这样做，请使用连接对象上的quote方法：
quote(value, column = nil)
Quotes the column value to help prevent SQL injection attacks.
quote(value, column = nil)
引用列值以帮助防止 SQL 注入攻击。
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.
永远不要试图处理你自己的引用。并且不要尝试使用双引号来引用 SQL 字符串文字，这就是单引号的用途；双引号用于在大多数数据库中引用标识符（例如表名和列名），但 MySQL 对此使用反引号。
