Rails中pluck内嵌自定义SQL时防范table_ids_params参数SQL注入咨询

你可以通过以下两种Rails官方推荐的方式解决SQL注入风险，完全不需要手动拼接SQL参数：

方案1：使用参数化SQL转义（改造成本最低） #

Rails内置了sanitize_sql_array方法专门用于处理带占位符的自定义SQL，会自动对传入的参数做转义处理，从根源避免注入风险，同时高版本Rails需要用Arel.sql包裹自定义SQL片段避免安全警告：

# 先构造安全的SQL片段，用?做参数占位符
safe_sql = Trigger.sanitize_sql_array([<<~INFO_SQL, table_ids_params, table_ids_params])
  timezone as timezone,
  count(1)
    FILTER(WHERE control_test_id IN (?))
    AS total,
  count(1)
    FILTER(WHERE local_run_at is null)
    AS invalid,
  array_agg(distinct control_test_id)
    FILTER(WHERE control_test_id IN (?))
    AS table_ids,
  array_agg(distinct to_char(run_at, 'HH24:MI'))
    FILTER(WHERE local_run_at is null)
    AS invalid_run_times
INFO_SQL

# 执行查询
grouped_records = invalid_records
  .unscoped
  .group(:timezone)
  .pluck(Arel.sql(safe_sql))

# 转换成你需要的Hash格式
result = grouped_records.each_with_object({}) do |row, hash|
  timezone, total, invalid, table_ids, invalid_run_times = row
  hash[timezone] = {
    total: total,
    invalid: invalid,
    table_ids: table_ids,
    invalid_run_times: invalid_run_times
  }
end

注：你原SQL中的timezone = timezone是恒真条件，属于冗余代码可以直接删除；同时你的invalid_records scope已经自带local_run_at: nil过滤，如果你不需要扩大查询范围，FILTER里的该条件也可以省略。

方案2：使用Arel构建查询（彻底避免手写SQL） #

如果想要完全避免手写SQL片段，可以用ActiveRecord底层的Arel AST构建工具生成查询，所有参数会自动转义，安全性最高：

triggers = Trigger.arel_table

# 构造各个聚合字段
total_count = triggers[:id].count.filter(
  triggers[:control_test_id].in(table_ids_params)
).as('total')

invalid_count = triggers[:id].count.filter(
  triggers[:local_run_at].eq(nil)
).as('invalid')

table_ids_agg = Arel::Nodes::NamedFunction.new('array_agg', [triggers[:control_test_id].distinct]).filter(
  triggers[:control_test_id].in(table_ids_params)
).as('table_ids')

run_times_agg = Arel::Nodes::NamedFunction.new('array_agg', [
  Arel::Nodes::NamedFunction.new('to_char', [triggers[:run_at], Arel::Nodes.build_quoted('HH24:MI')]).distinct
]).filter(
  triggers[:local_run_at].eq(nil)
).as('invalid_run_times')

# 执行查询
grouped_records = invalid_records
  .unscoped
  .group(:timezone)
  .pluck(
    triggers[:timezone],
    total_count,
    invalid_count,
    table_ids_agg,
    run_times_agg
  )

# 同样转换为目标Hash格式即可

核心注意事项 #

• 任何时候都不要直接用字符串插值把用户可控的参数拼接到SQL中，Rails的sanitize系列方法、Arel、常规where查询传参都会自动做参数转义，优先用这些官方能力
• 如果你使用的Rails版本高于6.0，自定义SQL片段必须用Arel.sql包裹，否则框架会抛出非安全SQL的警告。

内容的提问来源于stack exchange，提问作者Stefan Hansch