How to sanitize raw SQL in Rails 4

Question

In Rails 3 I could use sanitize_sql_array to sanitize raw SQL for those occassional moments where a raw SQL query is needed. But this appears to have been removed in Rails 4, or not so much removed, but moved to ActiveRecord::Sanitization. However, I can not figure out how to call sanitize_sql_array now, so what's the best way to sanitize raw SQL in Rails 4?

I want to clarify that I am talking about a full raw SQL query here, not using Rail's models. I'm aware that this is not best practice, this is just what I have to do for this specific query since it can't be represented by Rails's nice ActiveRecord interface (Trust me, I've tried).

Here is a sample call, which is obviously simpler than what my query actually looks like:

query = "SELECT * FROM users 
LEFT OUTER JOIN posts ON users.id=posts.user_id
AND posts.topic_id = '#{topic.id}'" 
# ^- Obviously bad and very vulnerable, this is what we're trying to fix
ActiveRecord::Base.connection.select_all(query)

Answer 1

If you really need to write raw SQL you can use quote to sanitize it:

conn = ActiveRecord::Base.connection
name = conn.quote("John O'Neil")
title = conn.quote(nil)
query = "INSERT INTO users (name,title) VALUES (#{name}, #{title})"
conn.execute(query)

Answer 2

From the Active Record docs, the best way to sanitize a SQL query is to avoid to build our own conditions as pure strings, in other words, inserts the parameters directly into the query, like this:

User.find_by("user_name = '#{user_name}' AND password = '#{password}'")

and instead use array or hash conditions.

Array conditions:

Client.where("orders_count = ? AND locked = ?", params[:orders], false)

Hash conditions:

Client.where(is_active: true)

A clarifying example:

class User < ActiveRecord::Base
  # UNSAFE - susceptible to SQL-injection attacks
  def self.authenticate_unsafely(user_name, password)
    where("user_name = '#{user_name}' AND password = '#{password}'").first
  end

  # SAFE
  def self.authenticate_safely(user_name, password)
    where("user_name = ? AND password = ?", user_name, password).first
  end

  # SAFE
  def self.authenticate_safely_simply(user_name, password)
    where(user_name: user_name, password: password).first
  end
end

Here are some references:

• http://api.rubyonrails.org/classes/ActiveRecord/Base.html#method-c-sanitize_sql_array
• http://guides.rubyonrails.org/active_record_querying.html

Answer 3

The quote method and other ActiveRecord::Base sanitization methods have been deprecated, and were never part of the public API.

https://github.com/rails/rails/issues/28947

The official sanitization methods are

http://api.rubyonrails.org/classes/ActiveRecord/Sanitization/ClassMethods.html