如何安全地将动态列名传入带有Postgres转换的ActiveRecord查询中？

Question

我的应用程序中有很多时间查询，我想将其中一些查询抽象出来。

假设我有一个带有 DateTime 字段 starts_at 的模型：

Shift.where('starts_at::time > ?', '20:31:00.00')
-> SELECT "shifts".* FROM "shifts" WHERE (starts_at::time > '20:31:00.00')

这个查询语句能够正确返回所有 'starts_at' 时间大于20:31的值。

我想要动态地将列名传递到查询中，这样我就可以做如下操作:

Shift.where('? > ?', "#{column_name}::time", '20:31:00.00').
-> SELECT "shifts".* FROM "shifts" WHERE ('starts_at::time' > '20:31:00.00')

在这个例子中，这并不起作用，因为搜索将 starts_at::time 作为字符串执行，而不是带有 time 转换的列。

如何安全地将 column_name 传递到带有 ::time 转换的查询中？虽然这不接受用户输入，但我仍然希望确保考虑了SQL注入。

- chrismanderson

---

你可以尝试使用临时变量，例如 query_text = "start_at > ?"，然后使用 Shift.where(query_text, '20:31:00.00') 吗？ - inye

@inye 但问题在于他不一定知道 column_name 是 'start_at'，理论上列名可能会以 'pancakes house' 的形式出现，因此需要在发送到数据库之前进行引用/转义。 - mu is too short

当你知道column_name时，也许你可以使用一些if else来分配query_text。 - inye

Answer 1

一开始你可能会认为这比想象的要复杂得多，因为在SQL中标识符（列名、表名等）和值（'pancakes'、6等）是非常不同的东西，有不同的引号规则甚至引号字符（在标准SQL中用单引号表示字符串，用双引号表示标识符，在MySQL中用反引号表示标识符，在SQL-Server中用方括号表示标识符...）。如果你把标识符看作 Ruby 变量名，把值看作字面量 Ruby 值，那么你就可以开始意识到它们之间的区别。

当你说：

where('? > ?', ...)

两个占位符都将被视为值（而不是标识符）并进行引用。为什么？ActiveRecord无法知道哪个?应该是标识符（例如created_at列名），哪个应该是值（例如20:31:00.00）。

但是，数据库连接确实有一个专门用于引用列名的方法：

> puts ActiveRecord::Base.connection.quote_column_name('pancakes')
"pancakes"
=> nil

所以你可以这样说：

quoted_column = Shift.connection.quote_column_name(column_name)
Shift.where("#{quoted_name}::time > ?", '20:31:00.00')

这有点不太好，因为我们会感到不舒服（或者至少应该会），使用字符串插值构建SQL。但是，quote_column_name 将处理 column_name 中任何可疑或不安全的内容，所以实际上并不危险。

你也可以这样说：

quoted_column = "#{Shift.connection.quote_column_name(column_name)}::time"
Shift.where("#{quoted_name} > ?", '20:31:00.00')

如果您不需要始终将列名转换为 time。或者甚至：

clause = "#{Shift.connection.quote_column_name(column_name)}::time > ?"
Shift.where(clause, '20:31:00.00')

你也可以使用 extract 或其他日期/时间函数 来代替类型转换，但你仍将面临引号问题和有点尴尬的 quote_column_name 调用。

另一个选择是将白名单设置为 column_name，以便只允许特定有效值。然后，你可以将安全的 column_name 直接插入查询中：

if(!in_the_whitelist(column_name))
  # Throw a tantrum, hissy fit, or complain in your preferred fashion
end
Shift.where("#{column_name} > ?", '20:31:00.00')

只要您没有任何奇怪的列名，例如"gotta have some breakfast"或类似总是需要正确引用的内容，那么这应该是可以的。 您甚至可以使用Shift.column_names或Shift.columns来构建白名单。

同时使用白名单和quote_column_name可能是最安全的方法，但quote_column_name方法应该已经足够了。

- mu is too short

---

这就是我最终做的。虽然它是安全的，但插值让我感觉有点不对劲，但这是最好的糟糕解决方案！ - chrismanderson

正是我所寻找的。谢谢。 - CaDs

很棒的解决方案，正是我所需要的。谢谢！ - Andreas Gebhard

Answer 2

我决定使用这个小解决方案，利用Rails列命名约定：

  scope :field_before_and_on_date, -> (field, time) do
    column_name = field.to_s.parameterize.underscore
    where("#{column_name} <= ?", time.end_of_day)
  end

# Takes advantage of:
> "); and delete everything(); stuff(".parameterize.underscore
=> "and_delete_everything_stuff"

虽然有限，但这个概念也适用于类型转换。

- ecoologic

---

quote_column_name将其转换为字符串> ActiveRecord :: Base.connection.quote_column_name（“）;并删除everything（）; stuff（”）＃=>“\”）;并删除everything（）; stuff（\“” - Dorian