The Deadly Sins in RoR security - SQL Injection

Ruby on Rails provides tools and security measures that are foolproof in many use cases. A regular developer writing code does not have to worry about whether a form field is vulnerable or someone could bypass authentication by typing a simple semicolon on the login screen. However, as the application becomes more complex and requirements become more complicated, the demand for custom solution rises. Foolproof mechanisms may not always align with the business logic, so developers sometimes need to get creative. And that is exactly where the trouble begins…

The Sin

Software development is not merely about creating and maintaining applications. A significant aspect of a developer's role involves working with data - extracting, presenting, and safeguarding it. Nowadays, people entrust their most valuable information to various applications - online shops, banks or offices. Any leakage of personal data can result in multiple individual tragedies and financial losses. Hence, developers must prioritize security as paramount.

One of the most common vulnerabilities is known as the SQL injection. This exploit specifically targets the database to read or modify the information it contains. Consequently, sensitive information can be stolen, and valuable data may be irreversibly damaged, causing serious harm. To execute an injection one sends data containing code in SQL language as input. If the server lacks adequate security measures, the code will be executed.

Sloth

Using Ruby on Rails’ ORM for standard use cases is very secure. However, when there is a need to extract complex data with intricate associations, developers quickly realize that deep knowledge of the framework can never completely replace plain SQL writing skills. Often, it is easier and faster to just write a query. The issue is the conveniences provided by RoR have made us developers quite lazy. We tend to forget that when implementing custom solutions, the built-in RoR mechanisms are no longer our guardian angels. It then becomes our responsibility to ensure protection against vulnerabilities.

User.find_by_sql(%Q(
  SELECT *
    FROM users
    WHERE email = '#{params[:email]}'
      AND password = '#{params[:password]}'))
))

A big no-no is using string interpolation or concatenation in SQL queries when the values inserted into the query are provided by users. Assuming that the example above is used for authentication, anyone can access any account. For example, if we provide parameters containing an existing e-mail and, instead of a password, enter a value that is always true: params[:password] = "password' OR '1' = '1". As a result, the following query is being executed in the database:

SELECT *
  FROM users
  WHERE email='user@example.com
    AND password='password' OR '1'='1' 

The database will search for a user with the provided e-mail. If it exists, no matter what the password was, the AND password='password' OR '1' = '1' part will be translated to AND false OR true, which will always result in true value. Poof, the authentication is no more.

pry(main)> email = "'; DROP DATABASE application"
pry(main)> User.find_by_sql(%Q(SELECT * FROM users WHERE email = '#{email}'))

User Load (3.4ms)  SELECT * FROM users WHERE email = ''; DROP DATABASE application;'

ActiveRecord::StatementInvalid: PG::SyntaxError:
ERROR:  cannot insert multiple commands into a prepared statement

Thankfully, database engines usually do not allow us to commit ye good ol’ semicolon trick to provide another command and destroy all the records. For example, PostgreSQL will end an attempt with PG::SyntaxError. But it does not mean that all the databases have such behavior implemented, so do not let your guard down.

Conscience examination

The example with authentication and providing credentials is quite extreme. Rails projects typically use Devise to handle it anyway, but this example clearly shows the potential impact. I hope no one commits such obvious sins in commercial projects. However, let us examine our conscience and check for any less obvious mistakes that we all did at some point.

Record.delete_all("id = #{params[:id]}")

Product.joins(%Q(
  INNER JOIN categories
    ON categories.id = products.category_id
    AND categories.name = '#{params[:category]}'
))

Using user parameters anywhere in the database query is always risky. Depending on the original intention of the query, different kinds of damage can occur - from serious data leaks to actual records deletion.

class ProductsController < ApplicationController
  def index
    filters = []
    filters << "created_at >= '#{params[:start_date]}'" if params[:start_date]
    filters << "created_at <= '#{params[:end_date]}'" if params[:end_date]
    filters << "status = '#{params[:status]}'" if params[:status]

    @products = Product.where(filters.join(' AND '))
  end
end

Complex dynamic queries are also something developers usually forget to stay alert about, especially when they involve filtering based on user input, such as date ranges. Even if the frontend uses selectors that do not allow any custom values, they still cannot be fully trusted, as user can manually modify HTTP arguments or the entire payload and send them directly to the backend. Furthermore, even if the application is designed only for trained professionals for internal company use, users still cannot be fully trusted. You never know if they might become victims of phishing or if someone steals their identity to gain access to the data you protect.

Repentance

It is impossible to develop a fully functional application that completely avoids using user-provided parameters. Concatenating values directly from the outside world with our own code carries inherent risks. However, there are different syntax constructs that allow us to use any user-provided parameters without leading to SQL injection vulnerability. Simply, as long as we do not overcomplicate the tools that Ruby on Rails provides, nothing bad will happen.

@users = User.where("email = ?", params[:email])
@users = User.where(email: params[:email])

@user = User.find_by("email = ?", params[:email])
@user = User.find_by(email: params[:email])
@user = User.find_by_email(params[:email])

Conclusion

The conclusion about SQL Injection in Ruby on Rails is quite simple. Whenever we develop custom features, we need to stay vigilant. Even very experienced developers can forget about the basics, such as avoiding the use of raw user parameters in queries. That is why teamwork and code reviews are crucial - if one person overlooks a rule, someone else can catch it.