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?