DEV Community

loading...

Updates for SQL Injection in Rails 6.1

Justin
I work on Brakeman (security tool for Ruby on Rails) and do other stuff too.
Originally published at blog.presidentbeef.com ・3 min read

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]}")
Enter fullscreen mode Exit fullscreen mode

Resulting query that deletes all users:

DELETE FROM "users" WHERE (id = 1) OR 1=1--)
Enter fullscreen mode Exit fullscreen mode

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]}")
Enter fullscreen mode Exit fullscreen mode

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().
Enter fullscreen mode Exit fullscreen mode

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):12
ActiveRecord::UnknownAttributeReference (Query method called with non-attribute argument(s): "name ARGLBARGHL")
Enter fullscreen mode Exit fullscreen mode

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]}")
Enter fullscreen mode Exit fullscreen mode

Resulting exception:

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

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().
Enter fullscreen mode Exit fullscreen mode

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])
Enter fullscreen mode Exit fullscreen mode

Note this selects all columns from a different table:

SELECT * FROM orders -- FROM "users"
Enter fullscreen mode Exit fullscreen mode

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]}")
Enter fullscreen mode Exit fullscreen mode

Resulting query:

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

Wrapping Up

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

Want to find out more?

Discussion (0)