SQL Injection Prevention Techniques for Ruby on Rails Web Applications

What is an SQL injection vulnerability?

SQL injection is a web application vulnerability that occurs when untrusted data is inserted in a SQL query without any sanitization or escaping. Since databases play a core role in today’s web applications, being able to manipulate SQL queries and therefore control the database usually result with the compromise of the target web application’s data, or even a complete takeover of the application itself via elevation of the SQL injection to some sort of remote code execution vulnerability.

Read What is SQL injection and refer to the SQL injection cheat sheet for more detailed technical information and examples of the SQL injection vulnerability.

Preventing SQL Injections When Developing Web Applications with Ruby on Rails

Active Record is a Ruby on Rails library that helps developers interact with databases without actually writing any SQL queries. It provides an ORM layer which maps objects to the tables of the database system.

There are many methods available to read and manipulate the data. Even though most of those Active Record methods are already using parameterized queries and can be used without worrying about SQL Injection vulnerabilities, there are still some methods available which are accepting raw SQL and will introduce SQL injection vulnerabilities if not used with caution.

Use Dynamic Attribute-Based Finders When Possible

When finding/retrieving information from the database, dynamic attribute-based finders should be used to avoid SQL injection vulnerabilities. These work as parameterized queries and take care of the passed argument. Let’s illustrate the point with an example:

User.find_by(name: params[:name]) # Traditional

User.find_by_name(name) # dynamic finder

The find_by method used in the first line can be vulnerable to SQL injection if not used carefully. In our example, we made sure this is not the case by passing the column name and value explicitly. But on the second line, which uses dynamic finders, didn’t really required anything rather than calling the method itself. Active Record knows that the argument is not a column or a table name and added to the SQL query with proper escaping.

Only Accept and Construct Values from Inputs, not Commands

SQL Commands such as INSERT, DELETE and SELECT and other core parts of SQL queries such as column and table names should never be constructed from untrusted inputs. This can be easily achieved by not sending untrusted inputs to those methods in the Active Record library that accept column and table names, or raw SQL query.

Never Pass a String as an Argument Unless It Is a Dynamic Finder

First of all, it is really important to know exactly what the AR method expects as arguments. Some methods accept arguments in multiple forms and accept different formats. This works fine if the input is an integer, but the same method will be vulnerable to SQL Injection when called with a string.

There are many methods that will usually require external input to work with in the Active Record, especially under ActiveRecord::FinderMethods, and ActiveRecord::QueryMethods. User input should not be passed as it is, but rather should be encapsulated in a form that prevents modifying the SQL query. If a string is passed, that would generally be interpreted as Active Record, as an intent to add a fragment to the SQL query.

Let’s look at the ActiveRecord::QueryMethods::WhereChain::where method, which does the filtering based on the conditions specified in the arguments. This method accepts conditions in several different formats such as string, array, or hash, and goes to the WHERE-part of an SQL statement. If we simply pass user_input directly to this method, this would create a very basic type of SQL injection because when a string is passed, it will be added to the query as a SQL fragment and providing such opportunity will make attackers really happy! Let’s illustrate the point with an example:

User.where("name = '#{params[:name]'") # SQL Injection!

If this particular line of code invoked with name = ‘fff’, the resulting query will be:

SELECT "users".* FROM "users" WHERE (name = 'fff')
=> #<ActiveRecord::Relation []>

But if it is set to “‘ OR 1=’1“:

SELECT "users".* FROM "users" WHERE (name = ' ' OR '1'='1')
=> #<ActiveRecord::Relation [#<User id: 1, name:'jack', …….>]>

As seen above, the attack is the successful inclusion of an OR operator which helped us return all the records from database. More advanced queries could easily be crafted from this point onward. Let’s take a look at the injection-proof version.

User.where(["name = ?", "#{params[:name]}"])

SQL Injection is not possible when using the above, because the first element of the array is a template and the latters are parameters to that template.

User.where({ name: params[:name] })

Again, this is free from SQL injection because column name is set explicitly to the ‘name’ and the external input set to the value of it.

Vulnerability Classification and Severity Table