How to properly parameterize my postgresql query

Question

I'm trying to parameterize my postgresql query in order to prevent SQL injection in my ruby on rails application. The SQL query will sum a different value in my table depending on the input.

Here is a simplified version of my function:

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

If I call calculate_value("retail"), it will execute the query like this:

select location, CAST('COALESCE(sum(amount * price), 0)' AS DOUBLE PRECISION) as 'retail' from table1 group by location

This results in an error. I want it to execute without the quotes like this:

select location, CAST(COALESCE(sum(amount * price), 0) AS DOUBLE PRECISION) as retail from table1 group by location

I understand that the addition of quotations is what prevents the sql injection but how would I prevent it in this case? What is the best way to handle this scenario?

EDIT: I added an extra column to be fetched from the table to highlight that I can't use pick to get one value.

Answer 1

find_by_sql is used when you want to populate objects with a single line of literal SQL. But we can use ActiveRecord for most of this, we just need one single column. To make objects, use select. If you just want results use pluck.

As you're picking from a fixed set of strings there's no risk of SQL injection in this code. Use Arel.sql to pass along a SQL literal you know is safe.

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