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!