The Dangerous Query Method Deprecation

Have you ever tried to update a Rails app from 5.2 to 6.0 opens a new window , or from 6.0 to 6.1 opens a new window ? If so, you might have seen this deprecation:

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "random()". 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 ...)

Note: While this deprecation message mentions Rails 6.0 as the version where the behavior is disallowed, that change was postponed, so the deprecation also shows in Rails 6.0. The behavior is actually disallowed in Rails 6.1.

What does this deprecation mean exactly? In this article, I will explain that in plain English, what issue it is trying to prevent, and how to fix the problem if you come across it in your codebase.

First, the plain English version: This deprecation addresses a change in query methods opens a new window . These are methods like order, pluck, etc.

Before Rails 6.1, you used to be able to pass raw SQL as strings into these methods. Since Rails 6.1, you are not able to pass raw SQL without wrapping the query with Arel.sql(). This reduces the chances that your application will fall victim to a security hole known as SQL injections opens a new window .

What are SQL injections?

A SQL injection is a type of cyber attack where a malicious user can inject, or insert, SQL code into a web application, to manipulate the application’s database.

If a user enters SQL code into a web application, the web application could be tricked into executing SQL commands that would give the user unauthorized access to the application’s database.

Once a user gains access to the database, they could potentially steal sensitive data, modify or delete existing data, or even take control of the entire web application.

Some example situations where SQL injections can happen are:

• When a query is entered into a login or a search field
• When a query appended to a valid input as a query string in a URL
• When a query is passed into a method that doesn’t sanitize its input

To prevent SQL injection attacks, web developers need to ensure that user input is properly sanitized and validated before being used in SQL queries.

In this context, sanitized means that the input escapes special characters that would allow for the SQL to be interpreted and executed. This can be achieved by using prepared statements (also called parameterized queries), which ensure that user input is treated as data rather than executable code.

I’m sure you have heard the tale of little Bobby Tables:

Courtesy of xkcd.com

SQL injections in Rails

SQL injection attacks can occur in Ruby on Rails applications if input from the user is not properly sanitized or validated.

In Rails, developers can use ActiveRecord to interact with the database. ActiveRecord allows developers to write queries in a safe and secure manner opens a new window , by escaping and quoting special characters, so they are not interpreted as SQL code.

However, if a developer writes a query manually using string concatenation or interpolation, without properly sanitizing or validating user input, it can leave the application vulnerable to SQL injection attacks.

For example, consider the following code snippet:

User.where("username = '#{params[:username]}'")

If a hacker enters a special string of characters in the username parameter, it could trick the web application into executing arbitrary SQL commands, like dropping the database or extracting sensitive information.

To prevent SQL injection attacks in Rails, developers should use parameterized queries with ActiveRecord, which automatically escapes and quotes special characters, ensuring that user input is treated as data rather than executable code.

For example:

User.where("username = ?", params[:username])

or

User.where(username: params[:username])

ActiveRecord will escape and quote the value of params[:username], ensuring that it is treated as data and not as executable SQL code.

Methods that are affected

The three methods that are affected by this deprecation are: order, reorder, and pluck.

Not all raw SQL passed into these methods is affected by the deprecation. The string parameter is matched against a regular expression that determines whether or not the input is valid.

Valid SQL strings contain a column name, a table name and a column name, or a function with zero or one argument. For order and reorder, optional direction and position of nulls is allowed.

Examples of valid raw strings are:

pluck("name") # column
pluck("users.name") # table.column
pluck("UPPER(title)") # function(column)
pluck("name, lastname") # column, column
order("name") # column, default direction
order("name ASC") # column direction
order("users.name DESC") # table.column direction
order("deleted_at DESC NULLS LAST") # column direction nulls-order
order("ABS(age) ASC") # function(column) direction

Examples of invalid raw strings:

pluck("CONCAT(name, ' ', lastname)") # function with more than 1 argument
pluck("COUNT(*)") # * is not considered 1 argument
pluck("DISTINCT email") # argument should be wrapped in parentheses, e.g. pluck("DISTINCT(email)")
order("CONCAT(name, ' ', lastname) ASC") # function with more than 1 argument
order("COALESCE(col1, col2, fallback)") # function with more than 1 argument
order("deleted_at IS NULL") # fails because `is null` doesn't match the regexp
order("some_column != -1") # fails because `!= -1` doesn't match the regexp

To learn more about what strings are considered valid SQL, you can visit the Rails source code and view the regular expressions used to filter the strings opens a new window .

How to fix this deprecation

The simplest way you can fix this deprecation, and avoid the possibility of SQL injection in general, is by removing strings that contain concatenation or interpolation that are not properly sanitized or validated.

One way you can do that is by replacing string input to ActiveRecord methods with hashes or arrays, if possible.

If replacing the string is not possible, you have to first ensure that the generated string is safe (how to do that will depend on each case), and, once that is done, you can wrap the string with Arel.sql before passing it to the method.

So, if you had a line that looked like this:

Post.order("length(title)")

You could tweak it to look like this:

Post.order(Arel.sql("length(title)"))

While it’s easy to simply wrap all strings with Arel.sql, it is important to do it only once the generated string was reviewed and properly sanitized, as Arel.sql expects ‘known-safe’ SQL strings opens a new window .

In many cases, the strings are hardcoded. This means they are safe, but since they don’t match the regular expression mentioned earlier, you have to tell ActiveRecord they are safe by wrapping them in an Arel.sql call even if they don’t include user input.

The deprecation is there to help you identify calls with raw strings in order for you to sanitize them. Arel.sql doesn’t actually sanitize them. Once you add the wrapper around a string or variable, ActiveRecord will assume the value is safe and will not warn you anymore.

If this deprecation is not fixed, an ActiveRecord::UnknownAttributeReference error will be raised in Rails 6.1.

Conclusion

In this article, you learned about what the “Dangerous query method” deprecation means, what issue the deprecation is trying to solve, and how to fix it.

Happy hacking!

PS: Need a code security audit? Contact us! opens a new window