16

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)
the Tin Man
  • 158,662
  • 42
  • 215
  • 303
Colton Voege
  • 409
  • 2
  • 5
  • 12

3 Answers3

19

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)
gabrielhilal
  • 10,660
  • 6
  • 54
  • 81
1

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:

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
NickGnd
  • 5,107
  • 1
  • 20
  • 26
  • 4
    Hey Nick, thanks for the response, but I was looking for an answer that would work for raw SQL queries which don't go through the ActiveRecord models. I've updated my question to make this a bit more clear. – Colton Voege Dec 21 '15 at 19:51
  • @ColtonVoege Sorry, I misunderstood the question. As @gabrielhilal said, `quote` is the right way to sanitize a raw sql query without use ActiveRecord Model – NickGnd Dec 21 '15 at 20:05
  • This answer is so so wrong, using `'#{user_name}'` is venerable to SQL injection. – Hardik Jul 13 '16 at 18:11
  • @Hardik yep, you're right. That's why there is a comment written in capital letters with: `# UNSAFE - susceptible to SQL-injection attacks` on the top, maybe you miss it :) – NickGnd Jul 13 '16 at 19:53
  • @Hardik can you explain how just using quotes isn't secure? I'm really struggling with this right now and every single answer contradicts each other (take for example the answer directly below says quoting is enough). Here was what I was trying to solve: http://stackoverflow.com/questions/41410752/rails-5-sql-injection – Tallboy Dec 31 '16 at 19:04
  • This is a great idea, but how do you build queries for things like CTEs and such? – tibbon Dec 01 '17 at 16:17
1

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

clemens
  • 16,716
  • 11
  • 50
  • 65
lbaeyens
  • 13
  • 5