在Rails中转义查询SQL LIKE [英] escaping query for SQL LIKE in rails

问题描述

在Rails源中( https://github.com/rails/rails/blob/fe4b0eee05f59831e1468ed50f55fbad0ce11e1d/activerecord/lib/active_record/sanitization.rb#L112 )中有一个 sanitize_sql_like 方法(我希望)将在字符串之前进行清理将它们与SQL LIKE一起使用

In rails source (https://github.com/rails/rails/blob/fe4b0eee05f59831e1468ed50f55fbad0ce11e1d/activerecord/lib/active_record/sanitization.rb#L112) there is a sanitize_sql_like method that (I am hoping) will sanitize strings before using them with a SQL LIKE

但是，我似乎无法使用它，因为Rails说该方法不存在.

however, I can't seem to use that, as Rails says that method doesn't exist.

我的字符串中带有撇号，查询为

My string has an apostrophe in it and the query is

@query = "Joe's"
Model.where("lower(field) LIKE ?", "%#{@query}%") 

使用 ActiveRecord :: Base.sanitize 无效，因为查询没有结果.

Using ActiveRecord::Base.sanitize doesn't help, as there are no results for the query.

如何避开 @query 并保持我的SQL安全?

How can I escape @query, and keep my SQL secured?

推荐答案

我已经使用 ActiveRecord范围)在模型上使用它们，而不是像在示例中那样在外部使用它们.但是，从设计角度来看，最好还是将查询逻辑封装在Model中.

I've solved this same problem (on MySQL) using ActiveRecord::Sanitization::ClassMethods to properly sanitize user input. Unfortunately, the methods defined in ActiveRecord::Sanitization::ClassMethods are declared as protected, so they are only accessible in the scope of a Model class. The methods defined in ActiveRecord::Sanitization::ClassMethods are mixed in to all Model classes, so they are available directly from within the scope of a Model. That requires you to define a class/instance method or scope (ActiveRecord scope) on your model to use them, rather than using them externally, as in your example. However, design-wise, it's probably preferable to encapsulate the query logic in the Model anyway.

此解决方案还具有以下优点:不仅转义了单引号字符，而且转义了将由类似SQL的查询解释的其他字符，例如'％'字符(以及其他字符).这应该通过转义其他字符而适当地防止SQL注入，这些字符可能会导致值被解释而不是被视为文字.在 ActiveRecord :: Sanitization :: ClassMethods 中定义了其他清理方法，这些方法可用于将用户输入(或其他污染的输入)嵌入其他SQL查询上下文中.这是在MySQL上经过测试的有效解决方案.

This solution also has the advantage of not only escaping the single-quote character, but also escaping other characters that would be interpreted by a SQL like query, such as the '%' character (among others). This should properly prevent SQL injection by escaping the the other characters which might be able to cause values to be interpreted rather than being treated as literals. There are also additional sanitization methods defined in ActiveRecord::Sanitization::ClassMethods that are useful for embedding user input (or other tainted input) in other SQL query contexts. Here is a working solution, tested on MySQL.

class Model  < ActiveRecord::Base
  # Finds a Model by case-insensitive substring match on Model.field
  #
  # @param query [String] A value to use in the substring match.
  # @return [ActiveRecord::Relation] An `Relation` of `Model`s whose
  #   `field` includes the `query` substring.
  scope :find_by_field_substring, ->(query) do
    where(arel_table[:field].matches("%#{sanitize_sql_like(query)}%"))
  end
end 

然后您可以像下面这样访问此 scope :

You can then access this scope like this:

Model.find_by_field_substring "Joe's"
=> #<ActiveRecord::Relation [#<Model id: 1, field: "Joe's">]>

ActiveRecord范围和 ActiveRecord :: Relations 有充分的文档记录，但可能仅在较新版本的Rails中可用.

The usage of both ActiveRecord scopes and ActiveRecord::Relations are pretty well documented, but may only be available in newer versions of Rails.

请注意，您的数据库可能需要 sanitize_sql_like 方法的第二个参数来指定与'\'不同的转义字符.

Note that your database may require a second parameter to the sanitize_sql_like method to specify a different escape character than '\'.

还请注意，如果这样的转义对于MySQL以NO_BACKSLASH_ESCAPES模式运行，则不适用于MySQL，因为它强制采用另一种转义值的机制.如果您使用的是NO_BACKSLASH_ESCAPES模式，请参见

Also note that escaping like this doesn't work for MySQL if it is running in NO_BACKSLASH_ESCAPES mode, because it forces a different mechanism for escaping values. If you're using NO_BACKSLASH_ESCAPES mode see this answer.

我还没有测试这一部分，但是与其使用Arel(位于ActiveRecord下的SQL AST构建器)，还可以使用您在初始示例中建议的样式，只要它在Model类中定义即可.

I haven't tested this part, but rather than using Arel, the SQL AST builder that sits underneath ActiveRecord, one could probably also use the style suggested in your initial example, as long as it is defined within the Model class.

class Model  < ActiveRecord::Base
  scope :find_by_field_substring, ->(query) do
    where("lower(field) LIKE ?", "%#{sanitize_sql_like(query)}%") 
  end
end

这篇关于在Rails中转义查询SQL LIKE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！