Non-attribute arguments - normally worked around with Arel.sql but with param substitution in "where"

rubyonrails-talk
1

Non-attribute arguments will be disallowed in Rails 6.1. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql().

  • Most of my cases are like this: order("ifnull(to_date,2099-12-31) desc")
  • And it’s pretty easy to fix them: order(Arel.sql("ifnull(to_date,2099-12-31) desc"))

However, how would I fix this one?

where('ifnull(to_date,2099-12-31) >= ?', date).

Arel.sql does not perform param substitution. Would the following be correct?

where(Arel.sql('ifnull(to_date,2099-12-31) >= ?'), date).

(I don’t seek tips on rewriting queries. I have tons of legacy code and I won’t be hand-fixing all of these, when a good regexp find-and-replace can resolve thousands of issues for me. Please focus on the question asked. Thank you in advance.)

2

Here’s what you want – note the ticks added around the date literal in order for it to be evaluated correctly in MySQL:

where(ActiveRecord::Base.sanitize_sql_array(
        ["isnull(to_date, '2099-12-31') >= ?", date]
     )).

You may want to consider changing out the SQL part to be this instead so that the same exact code works for either MySQL or Postgres:

"cast(coalesce(to_date, '2099-12-31') AS date) >= ?"
1 Like