Databases
StackOverflow2013
Postsdbo
PORuby on Rails: How to sanitize a string for SQL when not using find?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORuby on Rails: How to sanitize a string for SQL when not using find?
    text
    copied!<p>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? </p> <p>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.</p> <p>For example, when I do model.name='::', where name is a string, it works fine. When I do model.vector='::' it errors out:</p> <pre><code>ActiveRecord::StatementInvalid: PGError: ERROR: syntax error in tsvector: "::" "vectors" = E'::' WHERE "id" = 1 </code></pre> <p>This seems to be a problem caused by lack of escaping of the semicolons, and I can manually set the vector='::' fine.</p> <p>I also had the bright idea, maybe I can just call something like:</p> <pre><code>ActiveRecord::Base.connection.execute "UPDATE medias SET vectors = ? WHERE id = 1", "::" </code></pre> <p>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.</p> <p>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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload