Recently, we were trying to write a similarity(...) query in Postgres, and needed to pass in a parameter into a SELECT clause. Brakeman came back saying that we had a Possible SQL Injection. After looking at the code, we were directly interpolating the input into the SQL string, so it was absolutely right:
def self.with_similarity_to(cpu)
select("similarity(cpu, #{cpu}) as similarity", '*')
end
Typically to solve this kind of problem, you can use ?s in your query and Rails will replace them automatically:
Book.where("title = ?", params[:title])
EXCEPT! The select method doesn't allow you to do this. Additional parameters passed to the select method are just enumerated in the select clause:
select("blah", "dad").to_sql # => SELECT blah, dad FROM...
So how do you escape input here so as to be safe from SQL injection?
Use ActiveRecord#sanitize_sql_array:
sanitize_sql_array(["name=? and group_id=?", "foo'bar", 4])
# => "name='foo''bar' and group_id=4"
This will make our statement safe (or at least, much safer than before) and we can move on!
def self.with_similarity_to(cpu)
select(sanitize_sql_array(["similarity(cpu, ?) as similarity", cpu]), '*')
end
Any other pro tips on how to handle this? Let me know!