---
title: "Rails 5.2 stops some raw SQL, prevents SQL injections"
description:
  "Rails 5.2 disallows raw SQL in dangerous Active Record methods preventing SQL
  injections"
canonical_url: "https://www.bigbinary.com/blog/rails-5-2-disallows-raw-sql-in-active-record"
markdown_url: "https://www.bigbinary.com/blog/rails-5-2-disallows-raw-sql-in-active-record.md"
---

# Rails 5.2 stops some raw SQL, prevents SQL injections

Rails 5.2 disallows raw SQL in dangerous Active Record methods preventing SQL
injections

- Author: Piyush Tiwari
- Published: October 16, 2018
- Categories: Rails 5.2, Rails

We sometimes use raw SQL in Active Record methods. This can lead to
[SQL injection](https://en.wikipedia.org/wiki/SQL_injection) vulnerabilities
when we unknowingly pass unsanitized user input to the Active Record method.

```ruby
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](http://rails-sqli.org/).

```ruby
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: "piyush@example.com",
  authentication_token: "Vkn5jpV_zxhqkNesyKSG">]
```

There are many Active Record methods which are vulnerable to SQL injection and
some of these can be found [`here`](http://rails-sqli.org/).

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.

```ruby
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: "piyush@example.com", 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: "piyush@example.com", 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.

```ruby
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: "piyush@example.com", authentication_token: "Vkn5jpV_zxhqkNesyKSG">]>
```

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

Here is relevant [commit](https://github.com/rails/rails/pull/27947/files) and
[discussion](https://github.com/rails/rails/pull/27947).

## Links

- [Human page](https://www.bigbinary.com/blog/rails-5-2-disallows-raw-sql-in-active-record)