How to use ActiveRecord where method with an array argument and nil condition

Question

Product.where(['color = ?',nil])

gives SQL

SELECT "products".* FROM "products" WHERE (color = NULL)

rather than what I want, which is:

SELECT "products".* FROM "products" WHERE (color IS NULL)

(color IS NULL, not color = NULL)

Using the hash syntax:

Product.where(color: nil)

gives what I want

SELECT "products".* FROM "products" WHERE "products"."color" IS NULL

But using the hash syntax I believe loses the benefit of anti-SQL injection that the array syntax offers.

EDIT - I omit to say that the condition will be a variable and will sometimes, but not always be nil:

Product.where(['color IS ?',nil]) does what I ask, but

Product.where(['color IS ?','red']) for example will of course then fail

Answer 1

You're mistaken, it provides the same SQL injection protection as the array syntax.

main> puts Foo.where( name: "' OR 1=1; --").to_sql
SELECT "foos".* FROM "foos" WHERE "foos"."name" = ''' OR 1=1; --'

Rails is even smart enough to handle a list containing a nil:

main> puts Foo.where(name: [ 'bar', nil ]).to_sql;
SELECT "foos".* FROM "foos" WHERE ("foos"."name" = 'bar' OR "foos"."name" IS NULL)
main> puts Foo.where(name: [ 'foo', 'bar', nil ]).to_sql;
SELECT "foos".* FROM "foos" WHERE ("foos"."name" IN ('foo', 'bar') OR "foos"."name" IS NULL)

The hash syntax is superior to the "array syntax" in every way except flexibility when you need something it just doesn't support, so most devs prefer it unless they're unable to use it.