Avoid sql injection with connection.execute

Question

If a query can't be efficiently expressed using ActiveRecord, how to safely use ActiveRecord::Base.connection.execute when interpolating passed params attributes?

connection.execute "... #{params[:search]} ..."

Answer 1

You can use the methods in ActiveRecord::Sanitization::ClassMethods.

You do have to be slightly careful as they are protected and therefore only readily available for ActiveRecord::Base subclasses.

Within a model class you could do something like:

class MyModel < ActiveRecord::Base

  def bespoke_query(params)
    query = sanitize_sql(['select * from somewhere where a = ?', params[:search]])
    connection.execute(query)
  end

end

You can send the method to try it out on the console too:

> MyModel.send(:sanitize_sql, ["Evening Officer ?", "'Dibble'"])
=> "Evening Officer '\\'Dibble\\''"

Answer 2

ActiveRecord has a sanitize method that allows you to clean the query first. Perhaps it's something you can look into: http://apidock.com/rails/v4.1.8/ActiveRecord/Sanitization/ClassMethods/sanitize

I'd be very careful inserting parameters directly like that though. What problem are you experiencing, that you cannot use ActiveRecord?