What is the difference between using ? and % when sanitizing fields in ActiveRecord?

Question

I've been puzzled for a while over the difference between using a question mark, e.g.

Foo.find(:all, :conditions => ['bar IN (?)', @dangerous])

and using sprintf style field types, e.g.

Bar.find(:all, :conditions => ['qux IN (%s)', @dangerous])

in sanitizing inputs. Is there any security advantage whatsoever, if you know you're looking for a number - like an ID - and not a string, in using %d over ?, or are you just asking for a Big Nasty Error when a string comes along instead?

Does this change at all with the newer .where syntax in Rails 3 and 4?

Answer 1

%s is intended for strings. The main difference is that %s doesn't add quotes. From ActiveRecord::QueryMethods.where:

Lastly, you can use sprintf-style % escapes in the template. This works slightly differently than the previous methods; you are responsible for ensuring that the values in the template are properly quoted. The values are passed to the connector for quoting, but the caller is responsible for ensuring they are enclosed in quotes in the resulting SQL. After quoting, the values are inserted using the same escapes as the Ruby core method Kernel::sprintf.

Examples:

User.where(["name = ? and email = ?", "Joe", "[email protected]"])
# SELECT * FROM users WHERE name = 'Joe' AND email = '[email protected]';

User.where(["name = '%s' and email = '%s'", "Joe", "[email protected]"])
# SELECT * FROM users WHERE name = 'Joe' AND email = '[email protected]';

Update:

You are passing an array. %s seems to calls .to_s on the argument so this might not works as expected:

User.where("name IN (%s)", ["foo", "bar"])
# SELECT * FROM users WHERE (name IN ([\"foo\", \"bar\"]))

User.where("name IN (?)", ["foo", "bar"])
# SELECT * FROM users WHERE (name IN ('foo','bar'))

For simple queries you can use the hash notation:

User.where(name: ["foo", "bar"])
# SELECT * FROM users WHERE name IN ('foo', 'bar')