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.

1class UsersController < ApplicationController
2  def index
3    User.order("#{params[:order]} ASC")
4  end
5end
Copy

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

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

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.

1irb(main):004:0> params[:order] = "email"
2=> "email"
3irb(main):005:0> User.order(params[:order])
4  User Load (1.0ms)  SELECT  "users".* FROM "users" ORDER BY email LIMIT $1  [["LIMIT", 11]]
5=> #<ActiveRecord::Relation [#<User id: 1, email: "[email protected]", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>
6
7irb(main):008:0> params[:order] = "(CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END)"
8irb(main):008:0> User.order("#{params[:order]} ASC")
9DEPRECATION 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)
10  User Load (1.2ms)  SELECT  "users".* FROM "users" ORDER BY (CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END) ASC
11=> #<ActiveRecord::Relation [#<User id: 1, email: "[email protected]", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>
Copy

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.

1irb(main):003:0> Arel.sql('title')
2=> "title"
3irb(main):004:0> Arel.sql('title').class
4=> Arel::Nodes::SqlLiteral
5
6irb(main):006:0> User.order(Arel.sql("#{params[:order]} ASC"))
7  User Load (1.2ms)  SELECT  "users".* FROM "users" ORDER BY (CASE SUBSTR(authentication_token, 1, 1) WHEN 'k' THEN 0 else 1 END) ASC
8=> #<ActiveRecord::Relation [#<User id: 1, email: "[email protected]", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>
Copy

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

Here is relevant commit and discussion.