10

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.

Abhi
  • 4,123
  • 6
  • 45
  • 77
bosticko
  • 763
  • 9
  • 21
  • `exec_query` is part of the low level driver interface so you have to use the database's native placeholders, in PostgreSQL's case that would be `$1`, `$2`, ... Once you get past that you'll probably encounter a `NoMethodError` because `binds` isn't what is expected; I have no idea what `binds` is supposed to look like as none of this stuff is documented and the code behind it is the usual incomprehensible defusenss that you find inside Rails. – mu is too short Nov 03 '16 at 19:50

1 Answers1

32

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.

fqxp
  • 7,680
  • 3
  • 24
  • 41
Michael
  • 668
  • 5
  • 13
  • Thanks for going to all that trouble. Database access in Ruby is pretty awful when you step outside the usual boxes. – mu is too short Jan 23 '18 at 00:01
  • 1
    No problem, I had to figure it out for my own stuff too. I was just surprised at how hard it was to do something that should have been so simple. At least on oracle_enhanced, it's actually simpler to just use the underlying OCI8 adapter. – Michael Jan 23 '18 at 14:03
  • do we need to pass prepare: true ? and what is the difference between false and true? – Andrey Khataev Nov 20 '18 at 16:37
  • 1
    @AndreyKhataev where would you pass prepare: true? exec_query doesn't have that options hash. It's just the query, a name for caching, and a list of binds that match the binds in the query itself. – Michael Nov 20 '18 at 21:44
  • @Michael I was confused by this answer https://stackoverflow.com/a/51372969 and this documentation https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-exec_query - here we can see prepare parameter – Andrey Khataev Nov 21 '18 at 09:00
  • They seem to have essentially the same answer with the extra optional keyword argument. I wasn’t aware of it, and I’ve been using the binds no problem with Oracle without prepare. – Michael Nov 22 '18 at 15:02
  • @AndreyKhataev and Michael, prepared statements aren't about changing the results; they provide performance efficiency for queries that will be repeated often, and they provide a measure of security against sql injection attacks. You can get the general (database-agnostic) idea here: https://en.wikipedia.org/wiki/Prepared_statement and some relevant sample code here: https://stackoverflow.com/a/51372969 – Joel Fouse Jan 08 '19 at 21:04
  • @JoelFouse Doesn't Oracle do that anyway with bound variables? If you're using :1, :2... I was under the impression it already parsing and caching the query. – Michael Jan 09 '19 at 20:20