postgresql 如何清理 Rails 4 中的原始 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34402835/
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
How to sanitize raw SQL in Rails 4
提问by Colton Voege
In Rails 3 I could use sanitize_sql_arrayto sanitize raw SQL for those occassional moments where a raw SQL query is needed. But this appears to have been removed in Rails 4, or not so much removed, but moved to ActiveRecord::Sanitization. However, I can not figure out how to call sanitize_sql_arraynow, so what's the best way to sanitize raw SQL in Rails 4?
在 Rails 3 中，我可以在sanitize_sql_array偶尔需要原始 SQL 查询的情况下清理原始 SQL。但这似乎已在 Rails 4 中删除，或者没有删除太多，而是移至 ActiveRecord::Sanitization。但是，我现在不知道如何调用sanitize_sql_array，那么在 Rails 4 中清理原始 SQL 的最佳方法是什么？
I want to clarify that I am talking about a full raw SQL query here, not using Rail's models. I'm aware that this is not best practice, this is just what I have to do for this specific query since it can't be represented by Rails's nice ActiveRecord interface (Trust me, I've tried).
我想澄清一下，我在这里谈论的是完整的原始 SQL 查询，而不是使用 Rail 的模型。我知道这不是最佳实践，这正是我必须为这个特定查询做的事情，因为它不能用 Rails 漂亮的 ActiveRecord 接口表示（相信我，我已经试过了）。
Here is a sample call, which is obviously simpler than what my query actually looks like:
这是一个示例调用，它显然比我的查询实际看起来更简单：
query = "SELECT * FROM users
LEFT OUTER JOIN posts ON users.id=posts.user_id
AND posts.topic_id = '#{topic.id}'"
# ^- Obviously bad and very vulnerable, this is what we're trying to fix
ActiveRecord::Base.connection.select_all(query)
回答by gabrielhilal
回答by lbaeyens
The quote method and other ActiveRecord::Basesanitization methods have been deprecated, and were never part of the public API.
quote 方法和其他ActiveRecord::Base清理方法已被弃用，并且从未成为公共 API 的一部分。
https://github.com/rails/rails/issues/28947
https://github.com/rails/rails/issues/28947
The official sanitization methods are
官方的消毒方法是
http://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html
http://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html
回答by NickGnd
From the Active Record docs, the best way to sanitize a SQL query is to avoidto build our own conditions as pure strings, in other words, inserts the parameters directly into the query, like this:
根据Active Record docs，清理 SQL 查询的最佳方法是避免将我们自己的条件构建为纯字符串，换句话说，将参数直接插入查询中，如下所示：
User.find_by("user_name = '#{user_name}' AND password = '#{password}'")
and instead use array or hash conditions.
而是使用数组或哈希条件。
Array conditions:
数组条件：
Client.where("orders_count = ? AND locked = ?", params[:orders], false)
Hash conditions:
哈希条件：
Client.where(is_active: true)
A clarifying example:
一个澄清的例子：
class User < ActiveRecord::Base
# UNSAFE - susceptible to SQL-injection attacks
def self.authenticate_unsafely(user_name, password)
where("user_name = '#{user_name}' AND password = '#{password}'").first
end
# SAFE
def self.authenticate_safely(user_name, password)
where("user_name = ? AND password = ?", user_name, password).first
end
# SAFE
def self.authenticate_safely_simply(user_name, password)
where(user_name: user_name, password: password).first
end
end
Here are some references:
以下是一些参考：
