Binding Parameters with Rails ActiveRecord::Base.connection.exec_query in mysql

I am attempting to utilize binding parameters in Rails using ActiveRecord::Base.connection.exec_query for a mysql database, but I'm doing something wrong.

Here is the code:

query = "Select * from accounts where name = ? AND description = ?"
ActiveRecord::Base.connection.exec_query(query,name = "SQL",[ [nil, "Some account"], [nil, "Some Desc"] ])

When run it gives this error:

SQL (68.7ms) Select * from accounts where name = ? AND description = ?

ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AND description = ?' at line 1

Here is the documentation for exec_query.

I understand that there are alternatives:

Account.where("name=? AND description=?", "Some account", "Some Desc")
Account.where(name: "Some account", description: "Some Desc")

However, I'm trying to figure out how to using binding parameters within the context of exec_query.

For context, in the test environment I am using mysql2 (0.5.6) and mysql 8.0.

Solution

Probably your bindings are done incorrectly. Try something like this:

query = "SELECT * FROM accounts WHERE name = $1 AND description = $2"
ActiveRecord::Base.connection.exec_query(query, "SQL", [[nil, "Some account"], [nil, "Some Desc"]])

Also, you can consider the sanitize_sql_array method as an alternative:

query = ActiveRecord::Base.sanitize_sql_array(
  [
    'SELECT * FROM accounts WHERE name = :name AND description = :description',
    { name: 'Some account', description: 'Some Desc' }
  ]
)
ActiveRecord::Base.connection.exec_query(query, 'SQL')