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?

tommasop · Accepted Answer · 2017-03-07 15:58:23Z

17

Add this method to your model:

class Media < ActiveRecord::Base
  def self.execute_sql(*sql_array)     
    connection.execute(send(:sanitize_sql_array, sql_array))
  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

edited Mar 7, 2017 at 15:58

tommasop

18.8k2 gold badges43 silver badges51 bronze badges

answered Mar 23, 2010 at 6:28

Harish Shetty

64.4k21 gold badges154 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

William Jones Over a year ago

Thanks for the tip, worked exactly like you said. Unfortunately, however, it seems that Rails does not sanitize colons (:) for SQL actually, and I'm still getting errors on that line. Perhaps requiring colons to be sanitized is unique to tsvectors. Looks like I will have to go to the creaky regex route.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related