Rails 5.2.3 上的危险查询方法弃用警告 [英] Dangerous query method deprecation warning on Rails 5.2.3

问题描述

我正在将我的 Rails 应用升级到 5.2.3

I am in the process of upgrading my Rails app to 5.2.3

我在我的应用中使用以下代码.

I am using the following code in my app.

MyModel.order('LOWER(name) ASC')

它引发了以下弃用警告:

It raises the following deprecation warning:

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "LOWER(name)". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql()

我已按照弃用警告的建议更改了上述内容，并且警告消失了:

I have changed the above as the deprecation warning recommends and the warning gone away:

MyModel.order(Arel.sql('LOWER(name) ASC'))

我在此处浏览了相关讨论.似乎引入此更改是为了禁止 SQL 注入.

I have surfed about related discussion here. It seems this change is introduced to disallow the SQL injections.

但是 order 子句 LOWER(name) ASC 不包含任何用户输入.为什么这种排序被认为是不安全的?这是预期的行为还是我在这里遗漏了什么?

But the order clause LOWER(name) ASC doesn't contains any user input. Why this ordering is considered as insecure? Is this the intended behavior or Am I missing anything here?

推荐答案

这是预期的行为，您链接到正确的讨论，这正是它的本质.不过我可以再详细说明一下，这样很容易理解.

首先重新解释sql注入，仅供参考，这样做:

First, re-explaining sql injection, just for reference, doing this:

MyModel.order('LOWER(name) ASC')

表示人们可以在 order 函数中传递任意字符串，该字符串可能包含用户输入的列名和/或订单类型.

Means people can pass any arbitrary string in the order function, this string might contain column names and/or order type input from user.

现在可以说，您的网络应用程序中有一个下拉列表，用户在其中选择列，另一个用户选择 desc 或 asc 并提交.数据.

Now lets say, there is a dropdown in your web app, where user selects column and another one where user selects desc or asc and it submits. the data.

关于控制器动作，人们可能正在做的是:

On the controller action what one might be doing is:

order_sql = "#{params[:column_name]} #{params[:column_order]}"

这正是 sql 注入可能发生的地方，恶意用户可以编辑表单提交数据，而不是在 column_orderasc 或 desccode> param，他可以发送一些sql脚本，例如:asc;delete from table_name_user_guessed_or_knows 导致 SQL 注入，这就是为什么 rails 希望用户在 order 函数中使用 sql 时要谨慎.并允许Arel的用户特定的安全sql.

This is exactly where sql injection can take place, a malicious user can edit the form submission data and instead of sending asc or desc in column_order param, he can send some sql script something like: asc; delete from table_name_user_guessed_or_knows causing SQL injections, this is why rails want users to be cautious when using sql in order functions. And allow specifically the safe sql with user of Arel.

弃用警告内容如下:

弃用警告:使用非属性参数调用的危险查询方法(其参数用作原始 SQL 的方法):LOWER(name) asc".Rails 6.0 中将不允许使用非属性参数.不应使用用户提供的值调用此方法，例如请求参数或模型属性.可以通过将已知安全值包装在 Arel.sql() 中来传递它们

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "LOWER(name) asc". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql()

关注单词:non-attribute argument(s)，非属性参数是任何不是属性的东西，无论是在末尾附加的任何额外的 sqlSQL 注入或者是对属性的一些方法调用，因为方法调用也可用于改变 SQL 的预期行为.

Focus on the words: non-attribute argument(s), non attribute arguments is anything which is not attribute, be it any extra sql appended at end for an SQL injection or be it some method call on the attribute, because methods calls can also be used to alter intended behavior of the SQL.

接下来，你问:

订单子句 LOWER(name) ASC 不包含任何用户输入

the order clause LOWER(name) ASC doesn't contains any user input

Rails 根本无法知道字符串是如何形成的，它只知道它正在传递一个字符串.这就是为什么它抱怨并希望开发人员保持谨慎.

Rails simply has no way to know how a string got formed, it only knows it's a string being passed. Thats why it complains and want developers to be cautious.

这就是允许 name asc 的原因，因为它是简单的属性参数.虽然 LOWER(name) asc 因为它不是简单的属性参数而抛出警告，但在这个参数上有一个方法调用，它可能被用于 SQL 注入.
(显然攻击者可能不会使用简单的LOWER函数用于攻击，而是他会使用一些特殊的函数，也许他在之前的某个甚至相同的调用中用类似的注入方法定义了一个).

This is why name asc is allowed, because it is simple attribute argument. While LOWER(name) asc is throwing warning because its not simple attribute argument, there is a method call on this argument which can potentially be used for SQL injection.
(Obviously an attacker wont probably use simple LOWER function for attacks, but rather he will use some special functions, maybe one he defined with similar injection approach in some previous or even same call himself).

这篇关于Rails 5.2.3 上的危险查询方法弃用警告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！