safer_raw_sql.rb

class SaferRawSQL
  TEXT_FORMAT = 0
  BINARY_FORMAT = 1

  ##
  # You must be using the PostgreSQL database to use this method because it
  # relies on PostgreSQL features.
  #
  # This method returns the same results as `ActiveRecord::Base.execute` but it
  # helps you avoid SQL injection attacks by using the PostgreSQL feature which
  # allows you to send the SQL code and values separately to the database.
  #
  # This means you don't have to rely on stanitization to avoide SQL
  # injections. Santization is helpful but limited in many cases e.g. it is
  # difficult to effectively sanitize strings representing human names.
  #
  # If you need to tell Postgres what type a particular parameter should be
  # treated as, you should tell it in the SQL e.g.
  #
  #     ... WHERE template = $1::string
  #
  # Example:
  #
  #     sql = "SELECT * FROM pages WHERE template = $1"
  #     # sql = "SELECT * FROM pages WHERE template = $1::string" # same query with explict type cast
  #
  #     params = ["topics"]
  #     results = SaferRawSQL.safer_execute(sql, params)
  #     p results
  #     => [{"id"=>13,
  #          "title"=>"Topics",
  #          "slug"=>"topics",
  #          "label"=>"Topics",
  #          "order"=>2,
  #          "template"=>"topics",
  #          "show_in_nav"=>true,
  #          "created_at"=>"2017-05-04 12:00:00.251916",
  #          "updated_at"=>"2017-05-04 12:00:00.331536"}]
  #
  # @param sql [String] SQL query which references parameters as $1, $2 etc.
  # @param param_values [Array<Any>] list of parameter values. $1 in the SQL
  #   references the first element, $2 the second etc.
  #
  # @return [Array<Hash<String, Any>>] Array of Hash results from the query
  def self.safer_execute(sql, param_values)
    values = param_values.map do |param|
      {
        value: param,
        format: TEXT_FORMAT
      }
    end

    ActiveRecord::Base
      .connection
      .raw_connection
      .exec_params(sql, values)
      .to_a
  end
end

sql = "SELECT * FROM pages where template = $1"
params = ["topics"]
p result = SafeRawSQL.safer_execute(sql, params)