Hacking Ruby on Rails - SQL injection

This week we would like to introduce the new ongoing series about hacking Ruby on Rails. Actually about preventing being hacked when writing Ruby on Rails application. Today we will focus on SQL injection a.k.a. SQLi attack.

Table of Contents:

1. What is SQL injection vulnerability?

2. SQL Injection prevention techniques.

What is SQL injection vulnerability?

Start with the fundamentals. In the begining you should comprehend the sql injection vulnerability and the mechanics of SQL injection in order to learn how use sql injection prevention techniques and how to defend against it.

So what does "inject some SQL" mean? To store data, almost all modern applications employ databases. Such a database contains all of the user accounts as well as their passwords. In order to read some records, save new ones, or alter existing ones, the program will regularly communicate with the database. As a result, it is common for the application to run several SQL queries.

SQL injection can be also translated as an attack that tries to manipulate SQL query being sent to the server by injecting malicious SQL statement. Injection can be done with everything that comes from user - parameters, modified form content etc.

SQL Injection prevention techniques

So, what is the important in terms of preventing sql injection?

The primary key is to never rely on user input and to never create a SQL query using it directly. Values from the user should always be serialized or parameterized.

Use arrays or hashes rather than strings when giving parameters to Active Records methods. You should transmit user input as a parameter rather than utilizing it directly. This strategy can be used with nearly all methods and will help you prevent SQL injection risks.

(source: xkcd.com)

Oh, now I remember! That's the part where we drop tables!

Well, more or less. So, let's get to it!

Let's attack one of usual Rails controllers:

class TasksController < ApplicationController
  def index
    @tasks = Task.where project_id: params[:project_id]
  end
end

What could go wrong right? Well, at this point not much - ActiveRecord saves us from SQLi with prepared statements. However - even hero like ActiveRecord has some sql injection vulnerabilities. Let's change one little thing in our controller:

@tasks = Task.where "project_id = #{params[:project_id]}"

Now, sending

params[:project_id] = " '0' OR 1=1"

will let us see all of tasks, whether they are in the project we belong to, or not.

Well, allright. Who would consider writing where condition as string anyways? Most of us won't (I hope so). Let's try another popular ActiveRecord method, order.

@agents = User.where(agent: true).order params[:sort]

Looks familiar? I hope not, because it is vulnerable to SQL injection too:

params[:sort] = "(CASE SUBSTR(email, 1, 1) WHEN 'a' THEN 0 else 1 END)"

Using params[:sort] set like that allows us to slowly, query by query, guess the emails of the agents.

But.. but I thought we'll be dropping tables, not listing stuff…

Do you think that hackers are just going around the web and drop tables all around? Well, no. They are not. They are going for information that can get them money. Information such as emails, passwords, bank data - everything they can sell. Dropping tables is cool, but it doesn't make a living unless you're security tester ;).

Summary

While Ruby on Rails is an awesome framework that protects us against dropping tables, it does not protect us against data leaks. And you can earn more money selling leaked data than dropping tables. Remember that no framework can replace cautious, experienced programmer and no automated checker can replace careful code review.

Further reading

We're making workshops on security in RoR!