Ruby-on-rails Rails 中的转义值（类似于 mysql_real_escape_string()）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4696040/
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 values in Rails (similar to mysql_real_escape_string())
提问by Jason Swett
I know about prepared statements, but if I'm using raw SQL, does ActiveRecord have a way to manually escape values?
我知道准备好的语句，但如果我使用原始 SQL，ActiveRecord 是否有办法手动转义值？
Something like this would be nice:
这样的事情会很好：
self.escape("O'Malley") # O\'Malley
回答by konus
You can do:
你可以做：
Dude.sanitize("O'Malley")
or
或者
Dude.connection.quote("O'Malley")
both with the same result: => "'O''Malley'"
两者都有相同的结果： => "'O''Malley'"
回答by jemminger
A quick dive into the ActiveRecord source reveals its method "sanitize_sql_array" for sanitizing the [string, bind_variable[, bind_variable]]type of sql statement
快速深入了解 ActiveRecord 源代码，揭示其用于清理[string, bind_variable[, bind_variable]]sql 语句类型的方法“sanitize_sql_array”
You could call it directly:
你可以直接调用它：
sql = ActiveRecord::Base.send(:sanitize_sql_array, ["insert into foo (bar, baz) values (?, ?), (?, ?)", 'a', 'b', 'c', 'd'])
res = ActiveRecord::Base.connection.execute(sql)
回答by quest
You can easily use the mysql2 gem to do this:
您可以轻松地使用 mysql2 gem 来执行此操作：
irb(main):002:0> require 'rubygems'
=> true
irb(main):003:0> require 'mysql2'
=> true
irb(main):004:0> Mysql2::Client.escape("O'Malley") # => "O\'Malley"
=> "O\'Malley"
Or if using the earlier mysql (not mysql2) gem:
或者，如果使用较早的 mysql（不是 mysql2）gem：
irb(main):002:0> require 'rubygems'
=> true
irb(main):003:0> require 'mysql'
=> true
irb(main):004:0> Mysql.escape_string("O'Malley")
=> "O\'Malley"
This will allow you to escape anything you want then insert to the db. You can also do this on most models in your rails application using the sanitize method. For instance say you have a model called Person. You could do.
这将允许您转义任何您想要的内容然后插入到数据库中。您还可以使用 sanitize 方法在 Rails 应用程序中的大多数模型上执行此操作。例如，假设您有一个名为 Person 的模型。你可以。
Person.sanitize("O'Malley")
That should do the trick.
这应该够了吧。
回答by Nathan
If you don't want the extra single quotes wrapping your string that occur when you use the solution posted by @konus, you can do this:
如果您不希望在使用@konus 发布的解决方案时出现的额外单引号包裹您的字符串，您可以这样做：
Dude.connection.quote_string("O'Malley")
This returns "O\'Malley"instead of "'O\'Malley'"
这将返回"O\'Malley"而不是"'O\'Malley'"
回答by Andy Lindeman
Even with Model.find_by_sqlyou can still use the form where question marks stand in as escaped values.
即使Model.find_by_sql你仍然可以使用问号作为转义值的形式。
Simply pass an array where the first element is the query and succeeding elements are the values to be substituted in.
只需传递一个数组，其中第一个元素是查询，随后的元素是要替换的值。
Example from the Rails API documentation:
Rails API 文档中的示例：
Post.find_by_sql ["SELECT title FROM posts WHERE author = ? AND created > ?", author_id, start_date]回答by Zeeshan
In case somebody is looking for a more concrete example of @jemminger's solution, here it is for bulk insert:
如果有人正在寻找@jemminger 解决方案的更具体示例，这里是批量插入：
users_places = []
users_values = []
timestamp = Time.now.strftime('%Y-%m-%d %H:%M:%S')
params[:users].each do |user|
users_places "(?,?,?,?)"
users_values << user[:name] << user[:punch_line] << timestamp << timestamp
end
bulk_insert_users_sql_arr = ["INSERT INTO users (name, punch_line, created_at, updated_at) VALUES #{users_places.join(", ")}"] + users_values
begin
sql = ActiveRecord::Base.send(:sanitize_sql_array, bulk_insert_users_sql_arr)
ActiveRecord::Base.connection.execute(sql)
rescue
"something went wrong with the bulk insert sql query"
end
Here is the reference to sanitize_sql_array method in ActiveRecord::Base, it generates the proper query string by escaping the single quotes in the strings. For example the punch_line "Don't let them get you down" will become "Don\'t let them get you down".
这是对ActiveRecord::Base 中 sanitize_sql_array 方法的引用，它通过转义字符串中的单引号生成正确的查询字符串。例如，妙语“不要让他们让你失望”将变成“不要让他们让你失望”。
