Ruby on Rails: How to sanitize a string for SQL when not using find?

Question

I'm trying to sanitize a string that involves user input without having to resort to manually crafting my own possibly buggy regex if possible, however, if that is the only way I would also appreciate if anyone can point me in the right direction to a regex that is unlikely to be missing anything. There are a number of methods in Rails that can allow you to enter in native SQL commands, how do people escape user input for those?

The question I'm asking is a broad one, but in my particular case, I'm working with a column in my Postgres database that Rails does not natively understand as far as I know, the tsvector, which holds plain text search information. Rails is able to write and read from it as if it's a string, however, unlike a string, it doesn't seem to be automatically escaping it when I do things like vector= inside the model.

For example, when I do model.name='::', where name is a string, it works fine. When I do model.vector='::' it errors out:

ActiveRecord::StatementInvalid: PGError: ERROR:  syntax error in tsvector: "::"
"vectors" = E'::' WHERE "id" = 1

This seems to be a problem caused by lack of escaping of the semicolons, and I can manually set the vector='\:\:' fine.

I also had the bright idea, maybe I can just call something like:

ActiveRecord::Base.connection.execute "UPDATE medias SET vectors = ? WHERE id = 1", "::"

However, this syntax doesn't work, because the raw SQL commands don't have access to find's method of escaping and inputting strings by using the ? mark.

This strikes me as the same problem as calling connection.execute with any type of user input, as it all boils down to sanitizing the strings, but I can't seem to find any way to manually call Rails' SQL string sanitization methods. Can anyone provide any advice?

Answer 1

Add this method to your model:

class Media < ActiveRecord::Base
  def self.execute_sql(array)     
    sql = self.send(:sanitize_sql_array, array)
    self.connection.execute(sql)
  end
end

Now you can execute any SQL such as:

Media.execute_sql(['UPDATE medias SET vectors = ? WHERE id = 1', '::'])

Reference

1) sanitize_sql_array