如何清理 Rails 中的 sql 片段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3009023/
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 sql fragment in Rails
提问by dimus
I have to sanitize a part of sql query. I can do something like this:
我必须清理 sql 查询的一部分。我可以做这样的事情：
class << ActiveRecord::Base
public :sanitize_sql
end
str = ActiveRecord::Base.sanitize_sql(["AND column1 = ?", "two's"], '')
But it is not safe because I expose protected method. What is a better way to do it?
但这并不安全，因为我公开了受保护的方法。什么是更好的方法？
回答by HashDog Team
You can just use:
你可以只使用：
ActiveRecord::Base::sanitize_sql(string)
回答by dimus
ActiveRecord::Base.connection.quotedoes the trick in Rails 3.x
ActiveRecord::Base.connection.quote在 Rails 3.x 中做到了这一点
回答by Bryan Dimas
This question does not specify that the answer has to come from ActiveRecordnor does it specify for which version of Rails it should be. For that reason (and because it is one of the top and few) answers on how to sanitize parameters in Rails...
这个问题没有指定答案必须来自，ActiveRecord也没有指定它应该是哪个版本的 Rails。出于这个原因（并且因为它是最重要的和少数之一）关于如何清理 Rails 中的参数的答案......
Here a solution that works with Rails 4:
这是一个适用于 Rails 4 的解决方案：
In ActiveRecord::Sanitization::ClassMethodsyou have sanitize_sql_for_conditionsand its two other aliases:
?sanitize_conditionsand sanitize_sql. The three do literally the exact same thing.
在ActiveRecord::Sanitization::ClassMethods你有sanitize_sql_for_conditions和它的另外两个别名：？sanitize_conditions和sanitize_sql。这三个实际上做完全相同的事情。
sanitize_sql_for_conditions
sanitize_sql_for_conditions
Accepts an array, hash, or string of SQL conditions and sanitizes them into a valid SQL fragment for a WHERE clause.
接受 SQL 条件的数组、散列或字符串，并将它们清理为 WHERE 子句的有效 SQL 片段。
Also in ActiveRecord you have
同样在 ActiveRecord 你有
sanitize_sql_for_assignmentwhich
sanitize_sql_for_assignment哪一个
Accepts an array, hash, or string of SQL conditions and sanitizes them into a valid SQL fragment for a SET clause.
接受 SQL 条件的数组、散列或字符串，并将它们清理为 SET 子句的有效 SQL 片段。
- The methods above are included in ActiveRecord::Base by default and therefore are included in any ActiveRecord model.
- 上述方法默认包含在 ActiveRecord::Base 中，因此包含在任何 ActiveRecord 模型中。
See docs
查看文档
Also, however, in ActionController you have ActionController::Parameterswhich allows you to
此外，但是，在 ActionController 中，您可以使用ActionController::Parameters它
choose which attributes should be whitelisted for mass updating and thus prevent accidentally exposing that which shouldn't be exposed. Provides two methods for this purpose: requireand permit.
选择应将哪些属性列入白名单以进行大规模更新，从而防止意外公开不应公开的属性。为此提供了两种方法：require和permit。
? ?
? ?
params = ActionController::Parameters.new(user: { name: 'Bryan', age: 21 })
req ?= params.require(:user) # will throw exception if user not present
opt ?= params.permit(:name) ?# name parameter is optional, returns nil if not present
user = params.require(:user).permit(:name, :age) # user hash is required while `name` and `age` keys are optional
The "Parameters magic" is called Strong Parameters (docs here) and you can use that to sanitize parameters in a controller before sending it to a model.
“参数魔法”称为强参数（此处为文档），您可以使用它在将控制器中的参数发送到模型之前对其进行清理。
- The methods above are included by default in
ActionController::Baseand therefore are included in any Rails controller.
- 上述方法默认包含在中
ActionController::Base，因此包含在任何 Rails 控制器中。
I hope that helps anyone, if only to learn and demystify Rails! :)
我希望对任何人都有帮助，如果只是为了学习和揭开 Rails 的神秘面纱！:)
回答by Lucian Tarna
As of rails 5 the recomended way is to use: ActiveRecord::Base.connection.quote(string)
从 rails 5 开始，推荐的方法是使用： ActiveRecord::Base.connection.quote(string)
as stated here: https://github.com/rails/rails/issues/28947
如此处所述：https: //github.com/rails/rails/issues/28947
ActiveRecord::Base::sanitize(string)is deprecated
ActiveRecord::Base::sanitize(string)已弃用
回答by pilcrow
You can bypass the protectedness of the method by invoking indirectly:
您可以protected通过间接调用来绕过方法的本质：
str = ActiveRecord::Base.__send__(:sanitize_sql, ["AND column1 = ?", "two's"], '')
... which will at least spare you having to refashion that method as public.
...这至少会让您不必将该方法改造成public.
(I'm a bit suspicious that you actually need to do this, but the above will work.)
（我有点怀疑你真的需要这样做，但上面的方法会起作用。）
回答by Yarin
Note that when it comes to sanitizing SQL WHERE conditions, the best solution wassanitize_sql_hash_for_conditions, because it correctly handled NULL conditions (e.g. would generate IS NULLinstead of = NULLif a nil attribute was passed).
请注意，当谈到清理SQL WHERE 条件时，最好的解决方案是sanitize_sql_hash_for_conditions，因为它正确处理了 NULL 条件（例如IS NULL，= NULL如果传递了 nil 属性，则会生成而不是）。
For some reason, it was deprecated in Rails 5. So I rolled a future-proofed version, see here: https://stackoverflow.com/a/53948665/165673
出于某种原因，它在 Rails 5 中已被弃用。所以我推出了一个面向未来的版本，请参见此处：https: //stackoverflow.com/a/53948665/165673
