Blog #12
SQL Injection in Ruby
Ruby On Rails
Ruby on Rails provides an interface called Active Record, an object-relational mapping (ORM) abstraction that facilitates database access. The following snippet of code displays the User model performing email and password validation, as well as conducting some authenticated actions:
if User.where("email = '#{address}' and password = '#{password}'").exists?
# Do something as authenticated in user
end
Since the SQL query is built by concatenating address and password user inputs, an attacker could manipulate the query to bypass the authentication check.
For example, by injecting mail@attacker.com') or ('1'='1 and an incorrect password in the address field, the query becomes:
SELECT `users`.* FROM `users` WHERE (email = 'mail@attacker.com') or ('1'='1' and password = 'wrongpassword')
The manipulated query returns the user record whose email equals mail@attacker.com or if 1 equals 1 and whose password equals wrongpassword. Since the password is not relevant anymore but the WHERE clause is true, an attacker could log in without having a valid password simply with a valid e-mail.
Vulnerable examples from rails-sqli.org
The list from rails-sqli.org below shows many query methods and options in Active Record which do not sanitize raw SQL arguments, and are not intended to be called with unsafe user input. These examples were tested with Rails 5.0.0.1 and SQLite 3. See other Rails versions and examples on rails-sqli.org.
Calculate Methods
There are several methods based on 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
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])
Delete All Method
Any methods which delete records should be used with care! The delete_all method adopts 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.
This example bypasses conditions and deletes all users:
params[:id] = "1) OR 1=1--"
User.delete_all("id = #{params[:id]}")
Destroy All Method
The destroy_all method uses 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.
params[:admin] = "') OR 1=1--'"
User.destroy_all(["id = ? AND admin = '#{params[:admin]}", params[:id]])
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 is next example is more obvious than the one above; it deomnstrates checking a table for a given value:
params[:user] = "') or (SELECT 1 AS one FROM 'orders' WHERE total > 100 AND ''='"
User.exists? ["name = '#{params[:user]}'"]
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.
The safest (and most common) use of these methods is to pass in a hash table.
This will find users who are admins:
params[:id] = "admin = 't'"
User.find_by params[:id]
From Method
The from method accepts arbitrary SQL.
Instead of returning all non-admin users, we return all admin users:
params[:from] = "users WHERE admin = 't' OR 1=?;"
User.from(params[:from]).where(admin: false)
Group Method
The group method accepts arbitrary SQL strings.
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])
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.
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]}")
Joins Method
The joins method can take an array of associations or straight SQL strings.
Skip WHERE clause and return all orders instead of just the orders for the specified user:
params[:table] = "--"
Order.joins(params[:table])
Lock Method and Option
The lock method and the :lock option for find and related methods accept a SQL fragment.
Not a real example: SQLite does not support this option:
params[:lock] = "?"
User.where('id > 1').lock(params[:lock])
Order Method
The order method accepts any SQL string.
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 to 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")
Pluck Method
The pluck method is intended to select a specific column from a table. Instead, it accepts any SQL statement its been given. 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.
Output the passwords from the users table:
params[:column] = "password FROM users--"
Order.pluck(params[:column])
Reorder Method
The reorder method is not very common, but it accepts any SQL fragment just like the order method.
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]}")
Select Method
The :select option allows complete control over the SELECT clause of the query.
Since the SELECT clause resides at the beginning of the query, nearly any SQL can be injected.
params[:column] = "* FROM users WHERE admin = 't' ;"
User.select(params[:column])
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.
The example below uses a classic SQL Injection to bypass authentication:
params[:name] = "') OR 1--"
User.where("name = '#{params[:name]}' AND password = '#{params[:password]}'")
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.
Update every user to be an admin:
params[:name] = "' OR 1=1;"
User.update_all("admin = 1 WHERE name LIKE '%#{params[:name]}%'")
Prevention
Active Record has a built-in filter for special SQL characters, which will escape ', ", NULL character and line breaks. Using Model.find(id) or Model.find_by_some thing(something) automatically applies this countermeasure. But in SQL fragments, especially in conditions fragments where(), the connection.execute() or Model.find_by_sql() methods, it has to be applied manually.
Instead of concatenating the user-provided variables to the condition string, you can pass an array to sanitize tainted strings like this:
User.where("email = ? AND password = ?", address, password).exists?
You can also pass a hash for the same result:
User.where(email: address, password: password).exists?
The array or hash form is only available in model instances. You can try sanitize_sql() elsewhere.
References
CWE - CWE-89: Improper Neutralization of Special Elements used in an SQL Command
OWASP - SQL Injection Prevention Cheat Sheet
OWASP - Ruby On Rails Cheat Sheet
Ruby On Rails - Securing Rails Applications
https://knowledge-base.secureflag.com/vulnerabilities/sql_injection/sql_injection_ruby.html