Блог

Я работаю над запросом и использую exec_query привязки, чтобы избежать потенциальной инъекции SQL. Однако я сталкиваюсь с проблемой при попытке проверить, находится ли идентификатор в массиве.

 SELECT JSON_AGG(agg_date)
FROM (
 SELECT t1.col1, t1.col2, t2.col1, t2.col2, t3.col3, t3.col4, t4.col7, t4.col8, t5.col5, t5.col6
 FROM t1
 JOIN t2 ON t1.id = t2.t1_id
 JOIN t3 ON t1.id = t3.t3_id
 JOIN t4 ON t2.is = t4.t2_id
 JOIN t5 ON t3.id = t5.t3_id
  
 WHERE t2.id IN ($1) AND t4.id = $2
) agg_data
 

это дает ошибку invalid input syntax for integer: '1,2,3,4,5'

И SELECT ... WHERE t.id = ANY($1) дает ERROR: malformed array literal: "1,2,3,4,5,6,7" DETAIL: Array value must start with "{" or dimension information.

Если я добавлю фигурные скобки вокруг переменной привязки, я получу invalid input syntax for integer: "$1"

Вот как я использую exec_query

 connection.exec_query(<<~EOQ, "-- CUSTOM SQL --", [[nil, array_of_ids], [nil, model_id]], prepare: true)
  SELECT ... WHERE t.id IN ($1)
EOQ
 

Я пробовал использовать обычную интерполяцию, но это приводит к ошибкам тормозного оператора при внедрении sql, поэтому я не могу использовать этот способ 🙁

Мы высоко ценим любую помощь в проведении этой проверки. И если exec_query это неправильный способ сделать это, я определенно готов попробовать другие вещи 😀

В моем классе я использую внутреннюю защиту от инъекций sql AR для поиска первых идентификаторов переменных привязки, затем выбираю идентификаторы и объединяю их в строку для sql-запроса. Я делаю то же самое для другой переменной привязки, нахожу объект и использую этот идентификатор. Просто в качестве дополнительной меры предосторожности. Таким образом, к тому времени, когда вводимые пользователем данные используются для запроса, они уже прошли AR. Это сканирование тормозника, которое выдает ошибку. У меня в понедельник встреча с нашей службой безопасности по этому поводу, но я хотел бы также проверить здесь 😀

Позвольте Rails провести дезинфекцию за вас:

 ar = [1,2,8,9,100,800]

MyModel.where(id: ar)
 

ваша забота о внедрении sql предполагает, что ar является производным от пользовательского ввода. Это излишне, но, возможно, вы захотите убедиться, что это список целых чисел. ar = user_ar.map(amp;:to_i) .

 # with just Rails sanitization
ar = "; drop table users;" # sql injection

MyModel.where(id: ar)

# query is:
# SELECT `my_models`.* from `my_models` WHERE `my_models`.`id` = NULL;

# or
ar = [1,2,8,100,"; drop table users;"]

MyModel.where(id: ar)

# query is
# SELECT `my_models`.* from `my_models` WHERE `my_models`.`id` in (1,2,8,100);
 

Рельсы тебя накрыли!

С помощью Arel вы можете составить этот запрос следующим образом:

 class Aggregator

  def initialize(connection: ActiveRecord::Base.connection)
    @connection = connection
    @t1 = Arel::Table.new('t1')
    @t2 = Arel::Table.new('t2')
    @t3 = Arel::Table.new('t3')
    @t4 = Arel::Table.new('t4')
    @t5 = Arel::Table.new('t5')
    @columns = [
      :col1, 
      :col2, 
      @t2[:col1], 
      @t2[:col2], 
      @t3[:col3], 
      @t3[:col4], 
      @t4[:col7], 
      @t4[:col8], 
      @t5[:col5], 
      @t5[:col6]
    ]
  end
  
  def query(t2_ids:, t4_id:)
    agg_data = t1.project(*columns)
                 .where(
                   t2[:id].in(t2_ids)
                   .and(t4[:id].eq(t4_id))
                 ) 
                 .join(t2).on(t1[:id].eq(t2[:t1_id]))
                 .join(t3).on(t1[:id].eq(t3[:t1_id]))
                 .join(t4).on(t1[:id].eq(t4[:t1_id]))
                 .join(t5).on(t1[:id].eq(t5[:t1_id])) 
                 .as('agg_data')

    yield agg_data if block_given?
    t1.project('JSON_AGG(agg_data)')
      .from(agg_data)
  end

  def exec_query(t2_ids:, t4_id:)
    connection.exec_query(
      query(t2_ids: t2_ids, t4_id: t4_id),
      "-- CUSTOM SQL --"
    )
  end

  private

  attr_reader :connection, :t1, :t2, :t3, :t4, :t5, :columns

end
 

Конечно, было бы намного чище просто настроить некоторые модели так, чтобы вы могли это сделать t1.joins(:t2, :t3, :t4, ...) . Ваши опасения по поводу производительности довольно необоснованны, поскольку у ActiveRecord есть довольно много методов для запроса и получения необработанных результатов вместо экземпляров модели.

Использование переменных привязки для WHERE IN () условия несколько проблематично, так как вы должны использовать количество переменных привязки, соответствующее количеству элементов в списке:

 irb(main):118:0> T1.where(id: [1, 2, 3])
  T1 Load (0.2ms)  SELECT "t1s".* FROM "t1s" WHERE "t1s"."id" IN (?, ?, ?) /* loading for inspect */ LIMIT ?
 

Это означает, что вы должны заранее знать количество переменных привязки при подготовке запроса. В качестве хитрого обходного пути вы можете использовать некоторую творческую типизацию, чтобы заставить Postgres разделить строку, разделенную запятыми, на массив:

 class Aggregator

  # ...
  
  def query
    agg_data = t1.project(*columns)
                 .where(
                   t2[:id].eq('any (string_to_array(?)::int[])')
                   .and(t4[:id].eq(Arel::Nodes::BindParam.new('$2')))
                 ) 
                 .join(t2).on(t1[:id].eq(t2[:t1_id]))
                 .join(t3).on(t1[:id].eq(t3[:t1_id]))
                 .join(t4).on(t1[:id].eq(t4[:t1_id]))
                 .join(t5).on(t1[:id].eq(t5[:t1_id])) 
                 .as('agg_data')

    yield agg_data if block_given?
    t1.project('JSON_AGG(agg_data)')
      .from(agg_data)
  end

  def exec_query(t2_ids:, t4_id:)
    connection.exec_query(
      query,
      "-- CUSTOM SQL --"
      [
        [t2_ids.map {|id| Arel::Nodes.build_quoted(id) }.join(',')],
        [t4_id]
      ]
    )
  end

  # ...
end