Rails find_by_sql - Use "?" literally rather than as bind variable?

Question

In the below, I want the first ? to be used literally, only the second ? should be used as the bind variable marker:

Foo.find_by_sql ["select IFNULL(col,'?') from foos where id = ?",1]

This errors:

wrong number of bind variables (1 for 2)

How would I escape the first ? so it is treated literally?

Answer 1

ActiveRecord isn't smart enough to ignore placeholders in string literals so it thinks that the ? in '?' is a placeholder rather than part of a string. The easiest way around this is to use named placeholders:

Foo.find_by_sql ["select IFNULL(col, '?') from foos where id = :id", :id => 1]

When ActiveRecord sees a Hash in the array's second element it will look for named placeholders (which use Ruby symbol notation) rather than positional ? placeholders. I tend to lean towards named placeholders period as they're more readable and more robust.