Rails ActiveRecord sanitize_sql replaces ? in string

Question

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?

try this apidock.com/rails/ActiveRecord/Base/sanitize_sql_for_conditions/… — wasipeer
– wasipeer, Commented Jan 14, 2019 at 12:00
@wasipeer that is a reasonable enough answer, unfortunately it requires a lot of existing SQL to be reworked to replace arrays for use with a hash of conditions. Just using an existing array won't work, as the method sanitize_sql_array checks for ? in the statement and relies on ? as a bind character before using the %s approach. — Phil
– Phil, Commented Jan 14, 2019 at 13:23
Do you need to go through ActiveRecord for this or could you drop down a level and use real prepared statements where '?' would be just a string rather than a placeholder inside a string literal. — mu is too short
– mu is too short, Commented Jan 14, 2019 at 23:15
@muistooshort I don't follow what you mean. As long as the SQL can be written exactly as it is now (like the example above), then whatever handles the sanitization of supplied conditions and generates subsequent SQL is possibly fine. Can you provide some more details on what you are suggesting? — Phil
– Phil, Commented Jan 15, 2019 at 14:57

Phil · Accepted Answer · 2019-01-18 10:06:56Z

1

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.

answered Jan 18, 2019 at 10:06

Phil

2,8171 gold badge26 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

Rails ActiveRecord sanitize_sql replaces ? in string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related