Documentation

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

params[:column] = "age) FROM users WHERE name = 'Bob';"
Order.calculate(:sum, params[:column])

Documentation

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.

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

Documentation

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.

params[:admin] = "') OR 1=1--'"
User.destroy_by(["id = ? AND admin = '#{params[:admin]}", params[:id]])

Documentation

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?.

params[:user] = "') or (SELECT 1 AS one FROM 'orders' WHERE total > 100 AND ''='"
User.exists? ["name = '#{params[:user]}'"]

Documentation

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.

params[:id] = "admin = '1'"
User.find_by params[:id]

Documentation

The from method accepts arbitrary SQL.

params[:from] = "users WHERE admin = '1' OR ''=?;"
User.from(params[:from]).where(admin: false).all

Documentation

The group method accepts arbitrary SQL strings.

params[:group] = "name UNION SELECT * FROM users"
User.where(:admin => false).group(params[:group])

Documentation

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.

params[:total] = "1) UNION SELECT * FROM orders--"
Order.where(:user_id => 1).group(:user_id).having("total > #{params[:total]}")

Documentation

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

params[:table] = "--"
Order.joins(params[:table]).where("total > 1000").all

Documentation

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

params[:lock] = "?"
User.where('id > 1').lock(params[:lock])

Documentation

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

params[:excluded] = "?)) OR 1=1 --"
User.where.not("admin = 1 OR id IN (#{params[:excluded]})").all

Documentation

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

params[:column] = "* FROM users WHERE admin = '1' ;"
User.select(params[:column])

Documentation

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

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

Documentation

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.

params[:name] = "') OR 1--"
User.where("name = '#{params[:name]}' AND password = '#{params[:password]}'")

Documentation

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.

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

Documentation

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.

params[:name] = "' OR 1=1;"
User.update_all("admin = 1 WHERE name LIKE '%#{params[:name]}%'")