0

I've been trying to figure out how to dynamically insert an index to use on an ActiveRecord Query. From this StackOverflow post, initially I tried the following:

class StoryAllocationDay < ApplicationRecord
  def self.use_index(index)
    from("#{self.table_name} USE INDEX(#{index})")
  end
end

For the above ActiveRecord model in my app, this results in the following working SQL:

$ StoryAllocationDay.use_index('index_story_allocation_days_on_date').to_sql

=> "SELECT `story_allocation_days`.* FROM story_allocation_days USE INDEX(index_story_allocation_days_on_date)"

However, my CI alerted me that Brakeman caught a potential SQL injection vulnerability here. So I looked for ways to sanitize this SQL, and came up with the following:

  def self.use_index(index)
    sql = ActiveRecord::Base.sanitize_sql_array([":table_name USE INDEX(:index_name)", {
      table_name: self.table_name,
      index_name: index
    }])

    from(sql)
  end

However, the sanitization necessarily introduces quote marks around the string-interpolated values (that's how it sanitizes):

$ StoryAllocationDay.use_index('index_story_allocation_days_on_date').to_sql

=> "SELECT `story_allocation_days`.* FROM 'story_allocation_days' USE INDEX('index_story_allocation_days_on_date')"

As you can see, the table and index names have single quotemarks around them. This results in a MySQL error:

$ StoryAllocationDay.use_index('index_story_allocation_days_on_date')

=> ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''story_allocation_days' USE INDEX('index_story_allocation_days_on_date') LIMIT 1' at line 1: SELECT  `story_allocation_days`.* FROM 'story_allocation_days' USE INDEX('index_story_allocation_days_on_date') LIMIT 11

I'm kind of surprised that introducing those quote marks would break the query, to be honest. Is there a way to get the advantages of SQL sanitization without breaking the SQL query itself?

Richie Thomas
  • 2,549
  • 4
  • 27
  • 48

0 Answers0