I have following query.

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

Query must return result in following format:

{
  "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"] 
  }
}

In filter FILTER(where control_test_id IN (#{table_ids_params}) need get all control-tests where ids are in table_ids_params. The variable table_ids_params is not safe for preventing SQL Injections? is any ways to solve it?

CodePudding user response：

Based on your updated information, it seems to me that you don't need to filter (again) by control_test_id, as the filter is applied to the where condition. You don't need to add the timezone to the filter, as it is implied by the GROUP BY clause.

Then there is a surprising unscoped at the start of your chain, why do you unscope a scope? (Update: based on the default scope you defined, you just need to remove ordering)

If you modify your code as follows, you should be able to get your data right.

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