Как правильно параметризовать мой запрос postgresql
Я пытаюсь параметризовать свой запрос postgresql, чтобы предотвратить внедрение SQL в моем приложении ruby on rails. SQL-запрос будет суммировать разные значения в моей таблице в зависимости от ввода.
Вот упрощенная версия моей функции:
def self.calculate_value(value)
calculated_value = ""
if value == "quantity"
calculated_value = "COALESCE(sum(amount), 0)"
elsif value == "retail"
calculated_value = "COALESCE(sum(amount * price), 0)"
elsif value == "wholesale"
calculated_value = "COALESCE(sum(amount * cost), 0)"
end
query = <<-SQL
select CAST(? AS DOUBLE PRECISION) as ? from table1
SQL
return Table1.find_by_sql([query, calculated_value, value])
end
Если я вызову calculate_value("retail"), он выполнит запрос следующим образом:
select location, CAST('COALESCE(sum(amount * price), 0)' AS DOUBLE PRECISION) as 'retail' from table1 group by location
Это приводит к ошибке. Я хочу, чтобы он выполнялся без кавычек, например:
select location, CAST(COALESCE(sum(amount * price), 0) AS DOUBLE PRECISION) as retail from table1 group by location
Я понимаю, что добавление кавычек предотвращает внедрение sql, но как мне предотвратить это в этом случае? Каков наилучший способ справиться с этим сценарием?
Обновлено: я добавил дополнительный столбец для извлечения из таблицы, чтобы подчеркнуть, что я не могу использовать pick для получения одного значения.
Ответы 1
find_by_sql используется, когда вы хотите заполнить объекты одной строкой литерала SQL. Но мы можем использовать ActiveRecord для большей части этого, нам нужен только один столбец. Чтобы создавать объекты, используйте выберите . Если вам просто нужен результат, используйте щипки.
Поскольку вы выбираете из фиксированного набора строк, в этом коде отсутствует риск внедрения SQL. Используйте Arel.sql, чтобы передать литерал SQL, который, как вы знаете, безопасен.
def self.calculate_value(result_name)
sum_sql = case result_name
when "quantity"
"sum(amount)"
when "retail"
"sum(amount * price)"
when "wholesale"
"sum(amount * cost)"
end
sum_sql = Arel.sql(
"coalesce(cast(#{sum_sql} as double precision), 0) as #{result_name}"
)
return Table1
.group(:location)
# replace pluck with select to get Table1 objects
.pluck(:location, sum_sql)
end
Я внес правку в ОП. В большинстве случаев я не хочу получать только одно значение. У меня просто так было, чтобы упростить запрос.
@tee Используй pluck. Применяется тот же принцип. Table1.group(:location).pluck(:location, Arel.sql("...")).
мой запрос упрощен. Я хочу использовать find_by_sql, поскольку я пишу более сложные запросы.
@tee Извините, вам нужно предоставить что-то, представляющее вашу проблему. Обычно find_by_sql можно избежать. Связывание предназначено для значений, вы добавляете идентификаторы. Используйте quote_column_name. Но так как это фиксированные строки, они безопасны и не нуждаются в кавычках.
Каков результат, который вы ищете?
Table1.find_by_sqlпопытается создатьTable1объекты, но вы подаете ему один столбец. Вам нужны только результаты расчета?