Rails 5.2 stops some raw SQL, prevents SQL injections

We sometimes use raw SQL in Active Record methods. This can lead to SQL injection vulnerabilities when we unknowingly pass unsanitized user input to the Active Record method.

class UsersController < ApplicationController
  def index
    User.order("#{params[:order]} ASC")
  end
end

Although this code is looking fine on the surface, we can see the issues looking at the example from rails-sqli.

pry(main)> params[:order] = "(CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END)"

pry(main)> User.order("#{params[:order]} ASC")
User Load (1.0ms)  SELECT "users".* FROM "users" ORDER BY (CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END) ASC
=> [#<User:0x00007fdb7968b508
  id: 1,
  email: "[email protected]",
  authentication_token: "Vkn5jpV_zxhqkNesyKSG">]

There are many Active Record methods which are vulnerable to SQL injection and some of these can be found here.

However, in Rails 5.2 these APIs are changed and they allow only attribute arguments and Rails does not allow raw SQL. With Rails 5.2 it is not mandatory but the developer would see a deprecation warning to remind about this.

irb(main):004:0> params[:order] = "email"
=> "email"
irb(main):005:0> User.order(params[:order])
  User Load (1.0ms)  SELECT  "users".* FROM "users" ORDER BY email LIMIT $1  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<User id: 1, email: "[email protected]", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>

irb(main):008:0> params[:order] = "(CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END)"
irb(main):008:0> User.order("#{params[:order]} ASC")
DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "(CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END)". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (called from irb_binding at (irb):8)
  User Load (1.2ms)  SELECT  "users".* FROM "users" ORDER BY (CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END) ASC
=> #<ActiveRecord::Relation [#<User id: 1, email: "[email protected]", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>

In Rails 6, this will result into an error.

In Rails 5.2, if we want to run raw SQL without getting the above warning, we have to change raw SQL string literals to an Arel::Nodes::SqlLiteral object.

irb(main):003:0> Arel.sql('title')
=> "title"
irb(main):004:0> Arel.sql('title').class
=> Arel::Nodes::SqlLiteral

irb(main):006:0> User.order(Arel.sql("#{params[:order]} ASC"))
  User Load (1.2ms)  SELECT  "users".* FROM "users" ORDER BY (CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END) ASC
=> #<ActiveRecord::Relation [#<User id: 1, email: "[email protected]", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>

This should be done with care and should not be done with user input.

Here is relevant commit and discussion.