在SQL查询中转义单引号 [英] Escaping a single quotation within SQL query

问题描述

我有一个表公司，其中有两列名为 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?

推荐答案

如果你必须这样做，然后使用 quote 方法： p>

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

引用（value，column = nil）

引用列值来帮助防止SQL注入攻击。

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

所以这样的东西：

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查询中转义单引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！