Ruby on Rails: How to sanitize a string for SQL when not using find and other built-in methods?
- by williamjones
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. 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?
