轨动态SQL查询 [英] rails dynamic where sql query

问题描述

我有一个带有一堆代表可搜索模型属性的属性的对象，我想仅使用设置的属性来动态创建sql查询。我在下面创建了方法，但是我认为它容易受到sql注入攻击。我进行了一些研究，并阅读了Rails活动记录查询界面指南，但似乎where条件始终需要静态定义的字符串作为第一个参数。我也试图找到一种方法来清理由我的方法产生的sql字符串，但是似乎也没有一种很好的方法。

I have an object with a bunch of attributes that represent searchable model attributes, and I would like to dynamically create an sql query using only the attributes that are set. I created the method below, but I believe it is susceptible to sql injection attacks. I did some research and read over the rails active record query interface guide, but it seems like the where condition always needs a statically defined string as the first parameter. I also tried to find a way to sanitize the sql string produced by my method, but it doesn't seem like there is a good way to do that either.

我该如何做得更好？我应该使用where条件还是以某种方式清除此sql字符串？

How can I do this better? Should I use a where condition or just somehow sanitize this sql string? Thanks.

def query_string
  to_return = ""

  self.instance_values.symbolize_keys.each do |attr_name, attr_value|
    if defined?(attr_value) and !attr_value.blank?
      to_return << "#{attr_name} LIKE '%#{attr_value}%' and "
    end
  end
  to_return.chomp(" and ")
end

推荐答案

您尝试解决错误问题的方法有些偏离。您正在尝试构建一个传递给ActiveRecord的字符串，以便在您仅应尝试构建查询时就可以构建一个查询。

Your approach is a little off as you're trying to solve the wrong problem. You're trying to build a string to hand to ActiveRecord so that it can build a query when you should simply be trying to build a query.

：

Model.where('a and b')

这与说的一样：

Model.where('a').where('b')

，您可以说：

Model.where('c like ?', pattern)

而不是：

Model.where("c like '#{pattern}'")

将这两个想法与您的 self.instance_values 您可能会得到类似的内容

Combining those two ideas with your self.instance_values you could get something like:

def query
  self.instance_values.select { |_, v| v.present? }.inject(YourModel) do |q, (name, value)|
    q.where("#{name} like ?", "%#{value}%")
  end
end

甚至：

def query
  empties      = ->(_, v) { v.blank? }
  add_to_query = ->(q, (n, v)) { q.where("#{n} like ?", "%#{v}%") }
  instance_values.reject(&empties)
                 .inject(YourModel, &add_to_query)
end

那些假设您已经正确地将所有实例变量列入了白名单。如果还没有，那么应该。

Those assume that you've properly whitelisted all your instance variables. If you haven't then you should.

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