Rails exec_query bindings ignored

Question

I'm trying to use exec_query to run an arbitrary query, with values brought in through bindings, and am getting unexpected errors.

Running this in the console

sql = 'SELECT * FROM foobars WHERE id IN (?)'
name = 'query_name_placeholder'
binds = [FooBar.first]
ActiveRecord::Base.connection.exec_query sql, name, binds

Yields this error:

Account Load (7.9ms)  SELECT  "foobars".* FROM "foobars"  ORDER BY "foobars"."id" ASC LIMIT 1
PG::SyntaxError: ERROR:  syntax error at or near ")"
LINE 1: SELECT * FROM foobars WHERE id IN (?)
                                             ^
: SELECT * FROM foobars WHERE id IN (?)
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near ")"
LINE 1: SELECT * FROM foobars WHERE id IN (?)
                                             ^
: SELECT * FROM accounts WHERE id IN (?)
from /Users/foo_user/.rvm/gems/ruby-2.2.4@foo_project/gems/activerecord-4.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:641:in `prepare'

It appears the binding syntax is being ignored? I've tried ... WHERE id = ? as well, but to no avail.

Answer 1

mu is too short got you part of the way there. For reference, here is the method's documentation: https://apidock.com/rails/ActiveRecord/ConnectionAdapters/DatabaseStatements/exec_query

He's right in that you will need to use the underlying database's binds syntax to set bind variables in the SQL string. For Oracle this is :1, :2 for PostgreSQL this is $1, $2... so that's step one.

Step two is you need to build bind objects, which are QueryAttribute objects, not just values to be passed in. This is a bit clunky, but here's an example:

binds = [ ActiveRecord::Relation::QueryAttribute.new(
    "id", 6, ActiveRecord::Type::Integer.new
)]
ApplicationRecord.connection.exec_query(
    'SELECT * FROM users WHERE id = $1', 'sql', binds
)

I just spent a whole day going through unit tests and source code trying to figure that out.