Updates for SQL Injection in Rails 6.1

Since early 2013, I have been maintaining rails-sqli.org, a collection of Rails ActiveRecord methods that can be vulnerable to SQL injection.

Rails 6 has been out since December 2019, but sadly the site has been missing information about changes and new methods in Rails 6.

As that deficiency has recently been rectified, let's walk through what has changed since Rails 5!

delete_all, destroy_all

In earlier versions of Rails, delete_all and destroy_all could be passed a string of raw SQL.

In Rails 6, these two methods no longer accept any arguments.

Instead, you can use...

delete_by, destroy_by

New in Rails 6, delete_by and destroy_by accept the same type of arguments as where: a Hash, an Array, or a raw SQL String.

This means they are vulnerable to the same kind of SQL injection.

For example:

params[:id] = "1) OR 1=1--"User.delete_by("id = #{params[:id]}")

Resulting query that deletes all users:

DELETE FROM "users" WHERE (id = 1) OR 1=1--)

order, reorder

Prior to Rails 6, it was possible to pass arbitrary SQL to the order and reorder methods.

Since Rails did not offer an easy way of setting sort direction, this kind of code was common:

User.order("name #{params[:direction]}")

In Rails 6.0, injection attempts would raise a deprecation warning:

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): "--". Non-attribute arguments will be disallowed in Rails 6.1. 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().

Starting with Rails 6.1, some logic to check the arguments to order. If the arguments do not appear to be column names or sort order, they will be rejected:

> User.order("name ARGLBARGHL")Traceback (most recent call last):        1: from (irb):12ActiveRecord::UnknownAttributeReference (Query method called with non-attribute argument(s): "name ARGLBARGHL")

It is still possible to inject additional columns to extract some information from the table, such as number of columns or names of the columns:

params[:direction] = ", 8"User.order("name #{params[:direction]}")

Resulting exception:

ActiveRecord::StatementInvalid (SQLite3::SQLException: 2nd ORDER BY term out of range - should be between 1 and 7)

pluck

pluck pulls out specified columns from a query, instead of loading whole records.

In previous versions of Rails, pluck (somewhat surprisingly!) accepted arbitrary SQL strings if they were given in an array.

Like order/reorder, Rails 6.0 started warning about this:

 > User.pluck(["1"])DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with non-attribute argument(s): ["1"]. Non-attribute arguments will be disallowed in Rails 6.1. 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().

In Rails 6.1, pluck now only accepts attribute names!

reselect

Rails 6 introduced reselect, which allows one to completely replace the SELECT clause of a query. Like select, it accepts any SQL string. Since SELECT is at the very beginning of the SQL query, it makes it a great target for SQL injection.

params[:column] = "* FROM orders -- "User.select(:name).reselect(params[:column])

Note this selects all columns from a different table:

SELECT * FROM orders -- FROM "users"

rewhere

rewhere is analogous to reselect but it replaces the WHERE clause.

Like where, it is very easy to open up rewhere to SQL injection.

params[:age] = "1=1) OR 1=1--"User.where(name: "Bob").rewhere("age > #{params[:age]}")

Resulting query:

SELECT "users".* FROM "users" WHERE "users"."name" = ? AND (age > 1=1) OR 1=1--)

Wrapping Up

Any other new methods that allow SQL injection? Let me know!

Want to find out more?

• https://rails-sqli.org/ for the complete list.
• Brakeman to help find vulnerable queries in your code.
• OWASP SQL Injection Cheat Sheet to learn more about preventing SQL injection.