CODEWITHSUNDEEP
ruby-on-railsactiverecord
Phil
Phil

Reputation: 2807

Rails ActiveRecord sanitize_sql replaces ? in string

I have a plain SQL query written by a trusted administrator that is to be run in a Rails (4.2) app. I am sanitizing it with ActiveRecord::Base.send(:sanitize_sql, ...) to allow user inputs to act as conditions, using the ? character for bind variables. The code has to allow arbitrary SQL, so I'm not interested in the arguments about why this is not the Rails way, etc.

The problem is that I can not include ? in a result field in the SQL without the underlying replace_bind_variables method replacing an intended literal ? in the result.

A simple query for example would be:

select 'http://www.google.com?q=' || res from some_table where a = ?;

To sanitize:

ActiveRecord::Base.send(:sanitize_sql, [sql, 'not me'], :some_table)

The sanitization fails because the ? in the URL gets replaced with the data intended for the condition, leading to the exception:

ActiveRecord::PreparedStatementInvalid: wrong number of bind variables (1 for 2)

The question is, does sanitize_sql or some variant allow literal ? characters to be included in a query so that they are not replaced? Is there some way of escaping them?

Upvotes: 0

Views: 1543

Answers (1)

Phil
Phil

Reputation: 2807

In the end I read through the ActiveRecord source and couldn't identify a way to handle this situation without a lot of code changes. There doesn't appear to be a way to escape the ? characters.

To resolve it for this one query I ended up using the SQL chr() function to generate a character that would pass the santization step untouched:

select 'http://www.google.com' || chr(63) || 'q=' || res from some_table where a = ?;

ASCII character 63 is ?.

Although not a perfect solution, I could at least get this one SQL query into the system without having to make massive code changes.

Upvotes: 1

Related Questions