Fixing SQL Injection Vulnerabilities in Ruby/Rails

In a previous post on Fixing Command Injection Vulnerabilities you saw the damage that can be caused when an attacker gets access to your system. It’s basically Game Over!

Nelson from the simpsons laughing at a Game Over screen

The same is true of SQL Injection also known as SQLi. The dangers of SQL Injection have been talked about for a long time, but for many developers they’ve never seen it in practice. This post is going to explore what a SQL Injection is, why you need to fix it, and how to fix it!

What is a SQL Injection Vulnerability?

SQL Injection falls into the Injection category of vulnerabilities detailed in the OWASP Top 10. SQL Injection is easy to exploit, occurs commonly, and the impact is severe. As a professional software developer it is your job to recognize and fix these vulnerabilities!

This is what SQL Injection looks like:

User.where("email = #{payload}").first

That’s all that’s required for an attacker to gain access to your entire database. Don’t believe me? Let’s see how an attacker could own your database.

Basic Exploitation

Since an attacker has full control of payload (for example sake let’s say via params[:email]) they can insert whatever they’d like into your where query. Here’s an example:

# http://domain.com/query?email=') or 1=1--
payload = "') or 1=1--"

@user = User.where("email = #{payload}").first
render @user

#=> #<User id: 1, email: "a@a.com", name: "A", admin: false, created_at: "2015-10-02 13:14:38", updated_at: "2015-10-02 13:14:38">

Above the attacker is sending a payload of ') or 1=1--. It works like this:

The first part of the payload ') sets the query to return zero results; email is blank: email=''.
The second part, 1=1 always equals true, which results in the first entry in the users table being returned.
The final part, -- is a SQL comment. This is a technique to cancel out any further query modifications that could occur server side. Essentially, this reduces the fine tuning to make a payload work.

Simplified, most SQL Injections will follow this type of payload format:

Close the query
Insert the attack
Prevent server modifications

While this seems trivial, an attacker can now manipulate payloads to get access to juicier information. Let’s see another example:

# http://domain.com/query?email=') or admin='t'--
payload = "') or admin='t'--"

@user = User.where("email = #{payload}").first
render @user

#=> #<User id: 193, email: "admin1@email.com", name: "Admin1", admin: true, created_at: "2015-09-28 01:33:39", updated_at: "2015-09-28 01:58:35">

Using the payload ') or admin='t'-- the attacker has gotten the system to return an admin user. They now have knowledge about an admin in your database.

Enumerating

In order to get a full dump of admin accounts the attacker needs to be able to enumerate through your admin table. It turns out that this is trivial to accomplish using an id filter:

# http://domain.com/query?email=') or admin='t' and id > 193--
payload = "') or admin='t' and id > 193--"

@user = User.where("email = #{payload}").first
render @user

#=> #<User id: 291, email: "admin2@email.com", name: "Admin2", admin: true, created_at: "2015-09-28 01:33:39", updated_at: "2015-09-28 01:58:35">

Here the attacker adds and id > 193 to get the next admin user. At this point, they keep incrementing id until they dump every admin out of your database.

In the back of your mind maybe you’re thinking:

“My user table gets owned, but I encrypt my passwords so at least the damage is just limited to a single table. Big deal if someone gets access to all my user’s … that’s not too bad … right?”

Now you’re smart so I’m sure you didn’t say that to yourself. Because this is bad. And an attacker can do worse!

Discovering Other Tables

The first step in accessing more than just the users table is finding out what other tables exist. This step is going to be slightly different based upon the underlying database that you’re using. Every database has some type of functionality similar to what is demonstrated here with varying syntax. For the purposes of this example, a sqlite3 database is used.

How can an attacker find out what other tables exist in the application? Via the sqlite_master table. This table lists the entire database’s schema including tables and indexes.

In order to access this information a couple of new techniques will be required. Let’s see the payload first and then look at the techniques:

# http://domain.com/query?email=') union select 1,name,1,1,1,1 from sqlite_master--
payload = "') union select 1,name,1,1,1,1 from sqlite_master--"

@user = User.where("email = #{payload}").first
render @user

#=> #<User id: 1, email: "schema_migrations", name: "1", admin: true, created_at: 1, updated_at: 1>

The first new technique is the addition of the union operator. This is a SQL operator (not limited to sqlite3) that combines the result of two select statements.

This payload also introduces a new technique of querying a system table:

select 1,name,1,1,1,1 from sqlite_master--

What’s happening here is that the attacker is selecting the name column from the sqlite_master table, and then inserting 1’s to fill out the remaining columns. Without those 1’s the database would throw an exception:

SELECTs to the left and right of UNION do not have the same number of result columns:
SELECT "users".* FROM "users" WHERE (email = '') union select
name, 1, 1, 1, 1 FROM sqlite_master--') ORDER BY "users"."id" ASC LIMIT 1

The end query that gets sent to the database looks like this:

SELECT "users".* FROM "users" WHERE (email = '')
  UNION
SELECT 1,name,1,1,1,1 FROM sqlite_master--')  ORDER BY "users"."id" ASC LIMIT 1

Remember that the first query to users doesn’t return a result so the result of the second query is interpreted as a User and fills a User object with the sqlite_master information. Specifically, the payload is crafted so that the name field corresponds with the email field in User.

In this particular example the result was email: "schema_migration" which isn’t helpful. Of course an attacker could use the enumeratation technique from eariler to traverse the entires in the sqlite_master table, but that’s slow. Instead the payload can be modified to use a function and get all the tables in the database at once!

# http://domain.com/query?email=') union select 1,group_concat(name, ','),1,1,1,1 from sqlite_master--
payload = "') union select 1,group_concat(name, ','),1,1,1,1 from sqlite_master--"

@user = User.where("email = #{payload}").first
render @user

#=> #<User id: 1, email: "users,credit_cards,schema_migrations,unique_schema_migrations,sqlite_sequence", name: "1", admin: true, created_at: 1, updated_at: 1>

Above the payload is using the group_concat function provided by sqlite3 to pull together all of the tables into a single value: users,credit_cards,schema_migrations,unique_schema_migrations,sqlite_sequence And viola, the attacker now has knowledge of every table in your database, including the credit_cards table!

Accessing Other Tables

Now that the attacker has discovered the credit_cards table in the application, they’re going to pull as much out of it as they can. Using the same union technique from above:

# http://domain.com/query?email=') union select 1,number, 1, 1, 1, 1 FROM credit_cards--
payload = "') union select 1,number,1,1,1,1 FROM credit_cards--"

@user = User.where("email = #{payload}").first
render @user

#=> #<User id: 1, email: "4242 4242 4242 4242", name: "1", admin: true, created_at: 1, updated_at: 1>

The output of User ought to scare you! An attacker has managed to populate the email field with a credit card number.

For our attacker this is where the party really starts. They have a toe hold into your system, and it’s a matter of time and a simple script to dump all your database. allthethings

How to fix SQL Injection Vulnerabilities

By now it should be crystal clear why you must fix SQL Injection vulnerabilities. In order to fix your SQL queries you’ll need to use parameterization. Parameterization, in a nutshell, is the safest way to handle unsafe user input. And whether you’re using ActiveRecord, Sequel, ROM, or some other ORM they’re all going to have facilities for parameterizing queries.

Let’s look at some common unsafe queries that frequently occur and how to fix them (these examples are ActiveRecord based.)

Single Parameter Queries

The most common use case for Ruby queries is a single parameter.

# Unsafe
User.where("email = '#{email}'")
User.where("email = '%{email}'" % { email: email })

# Safe
User.where(email: email)
User.where("email = ?", email)
User.where("email = :email", email: email)

While line 3 above looks very similar to line 8, they are different in that line 3 uses string formatting instead of parameterization which is unsafe for protecting against SQL injection.

Looking at the Unsafe vs Safe examples above you can extrapolate a rule of thumb: If you have to add surrounding quotes to your query, you’re vulnerable to SQL Injection.

Compounding Queries

Sometimes you need to chain together a series of queries, usually that’s with an AND statement:

# Unsafe
def unsafe_query
  sql = []
  sql << "email = #{email}" if condition1?
  sql << "name = #{name}"   if condition2?
  # ... etc

  User.where(sql.join(' and '))
end

# Safe
def safe_query
  User.all.tap do |query|
    query.where(email: email) if condition1?
    query.where(name: name)   if condition2?
    # ... etc
  end
end

ActiveRecord is great because it allows you to easily chain together multiple pieces of a query and because they’re evaluated lazily.

One of the real tricky places I’ve seen people struggle with is OR statements. This is in the process of changing but right now the common pattern is:

# Unsafe
def unsafe_query
  sql = []
  sql << "email = #{email}" if condition1?
  sql << "name = #{name}"   if condition2?
  # ... etc

  User.where(sql.join(' OR '))
end

# Safe
def safe_query
  sql   = []
  param = []

  if condition1?
    sql << "email = ?"
    param << email
  end

  if condition2?
    sql << "name = ?"
    param << name
  end

  User.where(sql.join(' OR '), *param)
end

While not very pretty, notice that the parameters are passed in separately from the query. This way parameterization can still occur keeping you safe from SQL Injection. There are ways to pretty this up which I’d encourage you to use if this type of code is in your code base.

LIKE Query

Another common scenario is doing a starts with/ends with filter using LIKE. This query is more apt to introduce SQL injection because many people don’t understand how it works!

# Unsafe
User.where("email LIKE '%#{partial_email}%'")

# Safe
User.where("email LIKE ?", "%#{partial_email}%")

Notice that with both queries, you’re going to have to do some string interpolation to insert the % signs. You’ll want to make sure that this occurs inside the value that will be parameterized.

Raw Queries

The final common scenario is raw queries. These are queries where you need to get right into the SQL itself without using ActiveRecord or any other type of framework.

# Unsafe
st = ActiveRecord::Base.connection.raw_connection.prepare(
  "select * from users where email = '#{email}'")
results = st.execute
st.close

# Safe
st = ActiveRecord::Base.connection.raw_connection.prepare(
  "select * from users where email = ?")
results = st.execute(email)
st.close

The above query is too simple for a raw query, you’d normally be doing a complex query, but at least now you can see proper parameterization. Raw queries follow in the same footsteps as previous examples of query parameterization.

That wraps up this post on SQL Injection. I hope that you learned something new. If there’s a Ruby or Rails security topic that you’d like me to touch on send me a tweet or an email.