Rails SQL query with safe interpolation for table names

user_1420684 0
Viewed 90

I'm writing a method that joins the tables of a 1-to-many relationship, where records from table A join to the latest record from table B.

Here's my code:

class ApplicationRecord < ActiveRecord::Base
  # If A has_many B, then A.joins_latest(:b) will create an inner join of A and its most recent B record
  def self.joins_latest(assoc_table, created_at: 'created_at')
    assoc_table = assoc_table.to_s

    latest_date_per_assocation = <<~SQL
      SELECT MAX(#{assoc_table}.#{created_at})
      FROM   #{assoc_table}
      WHERE  #{assoc_table}.#{reflections[assoc_table].foreign_key} = #{table_name}.id
    SQL
    joins(assoc_table.to_sym).where("#{assoc_table}.#{created_at} = (#{latest_date_per_assocation})")
  end
end

This code works great, the problem is that tools like Brakeman raise flags about SQL injection risk with the interpolation. I've tried using variables in the sql, but the resulting SQL was wrapped in single quotes like 'User'.'created_at', which postgres reads as strings instead of table/column names.

Is there any way to write SQL interpolation for table and column names in a way that is safe?

ruby-on-rails sql
1 Answers

Use ActiveRecord#maximum and ActiveSupport::Inflector methods to get an associated model

  def self.joins_latest(assoc_table, created_at: "created_at")
    associated_model = assoc_table.to_s.camelcase.singularize.constantize
    
    joins(assoc_table.to_sym).
      where(assoc_table.to_sym => { created_at: associated_model.maximum(created_at) })
  end
user_124913040
Related