How to sanitize Arel SQL?

Question

I have the following Arel SQL:

Arel.sql("(users.last_donated_at IS NOT NULL AND users.last_donated_at < '#{User::ACTIVE_DONOR_WITHIN_DAYS.days.ago}')")

I get SQL Injection warning when I run brakeman. I tried the following:

Arel.sql("(users.last_donated_at IS NOT NULL AND users.last_donated_at < ?)", User::ACTIVE_DONOR_WITHIN_DAYS.days.ago)

However, I get the following error:

ArgumentError:
       wrong number of arguments (given 2, expected 1)

How do I sanitize sql statement with Arel?

Answer 1

I am answering my own question. I am using Arel following the Github wiki for Ransack gem. I was doing something very similar to point # 2.2 mentioned on doc: https://github.com/activerecord-hackery/ransack/wiki/Using-Ransackers. In order to sanitize the params and avoid brakeman sql injection warning, I ended up doing the following:

Arel.sql(sanitize_sql_array("(users.last_donated_at IS NOT NULL AND users.last_donated_at < '#{User::ACTIVE_DONOR_WITHIN_DAYS.days.ago}')"))

Answer 2

Using Arel.sql is generally not the best way to handle querying. In my opinion you don't need to sanitize this query you need to refactor it.

You can build conditions for a rails where clause (and most other query methods order, select, etc.) in Arel using the convenience method ModelName.arel_attribute(:attribute_name) this will allow you to build query conditions beyond the high level support offered by rails' native where Hash.

This is identical to

table_name = ModelName.arel_table
table_name[:attribute_name]

So let's apply this to your query:

Based on your query the IS NOT NULL condition means nothing since you are also using less than so we can change the condition to just use less than e.g.

User.arel_attribute(:last_donated_at).lt(User::ACTIVE_DONOR_WITHIN_DAYS.days.ago)

This works because NULL is not less than (or greater than, or even equal to) anything so these results will not show up either way.

If you insist on the IS NOT NULL condition we can still produce the desired SQL using Arel Atrributes via:

User.arel_attribute(:last_donated_at).not_eq(nil).and(
  User.arel_attribute(:last_donated_at).lt(User::ACTIVE_DONOR_WITHIN_DAYS.days.ago)
)