SQL 注入防护使用带有方法提取和<<~INFO_SQL的参数

Question

我有以下查询:

invalid_records
.unscoped
.group(:timezone)
.pluck(<<~INFO_SQL)
timezone as timezone,
count(1)
FILTER(WHERE control_test_id IN (#{table_ids_params}) 
AND local_run_at is null 
AND timezone = timezone)
AS records_count,
array_agg(distinct control_test_id)
FILTER(WHERE control_test_id IN (["?", #{table_ids_params}]))
AS control_test_ids,
array_agg(distinct run_at::text)
FILTER(WHERE timezone = timezone 
AND local_run_at is null)
AS invalid_run_times
INFO_SQL

def invalid_records
scope = Trigger
.where(control_test_id: table_ids)
.where(local_run_at: nil)
.where.not(run_at: nil)
scope = scope.where(timezone: timezone) if timezone
scope = scope.where(run_at: run_at) if run_at
scope
end

class Trigger < ApplicationRecord

default_scope lambda {
if $flipper[:explicit_id_ordering].enabled?
order(position: :asc, updated_at: :asc, id: :asc)
else
order(position: :asc)
end
} 
# some code 
end

查询必须以下列格式返回结果:

{
"Berlin": {
total: 323,                                    # total count of triggers with given params 
invalid: 100,                                  # count of triggers with empty `local_run_at` field
table_ids: [23, 4343, 34, 44],                 # ids of tables invalid triggers belongs to
invalid_run_times: ["14:00", "23:00", "12:30"] # list of run times of invalid triggers
},
"Puerto Rico": {
total: 3,
invalid: 1,
table_ids: [77],
invalid_run_times: ["13:00"] 
}
}

在过滤器FILTER(where control_test_id IN (#{table_ids_params})需要得到所有的控制测试，其中id是在table_ids_params。变量table_ids_params是不安全的防止SQL注入?有什么办法能解决它吗?

Answer 1

根据您更新的信息，在我看来，您不需要(再次)通过control_test_id进行过滤，因为该过滤器适用于where条件。您不需要将时区添加到过滤器中，因为GROUP BY子句暗示了这一点。

然后在链的开始有一个令人惊讶的unscoped，为什么要取消作用域?(更新:基于你定义的默认作用域，你只需要删除排序)

如果你像下面这样修改你的代码，你应该能够得到正确的数据。

invalid_records
.unscope(:oorder)
.group(:timezone)
.pluck(<<~INFO_SQL)
timezone as timezone,
count(1)
FILTER(WHERE local_run_at is null 
AND timezone = timezone)
AS records_count,
array_agg(distinct control_test_id)
AS control_test_ids,
array_agg(distinct run_at::text)
FILTER(WHERE local_run_at is null)
AS invalid_run_times
INFO_SQL