SQL Injection (SQLi): How To Fix It In Ruby On Rails

What is SQL Injection Vulnerability?

SQL injection is vulnerability where an attacker can manipulate some value used in an unsafe way inside a SQL query. The bug allows SQL injection through dynamic finder methods, leading to data leaks, data loss & other unpleasant outcomes.

Let’s consider the following code to get customer information by email:

Customer.where("email = #{user_data}").first

Since the attacker has full control over ‘user_data’, they can insert whatever they like in the ‘where query’. For example:

user_data = “[email protected]; DROP TABLE customers;”

The above ‘user_data’ with ‘where query’ will be executed separately as two SQL commands in the database, like this:

SELECT * FORM customers WHERE email='[email protected]; DROP TABLE customers;--'

This results in complete data loss from customers table. Apart from data loss, the attackers can get useful information from your database using SQL injection.

Here is a sample code where User is being searched by the username:

User.where("username = #{user_data}").first

The attacker inserts the following text as ‘user_data’:

user_data = "'' or admin='t'--"

The above ‘user_data’ with ‘where query’ works like this:

• The first part of the ‘user_data’ ‘# returns empty result set as the username is blank.
• The second part, admin=’t’ fetches admin information from the table.
• The last part — is a SQL comment to cancel all further command execution.

With this, all information about the admin is now in the attacker’s hands which might lead to serious problems.

Preventing SQL Injection Vulnerability

The best way to find out if an application is vulnerable to injection is to check whether the entire use of interpreters clearly segregates not-to-be trusted data from the command/query. In SQL calls, all the variables should bind with the prepared statements and stored procedures, whereas the dynamic queries should be avoided to prevent SQL vulnerabilities.

ActiveRecord & some other ORMs have all the facilities for parameterising queries. Here are some of the frequently used unsafe queries and safer ways to fix them:

Single Parameter Queries

# Unsafe Query

Post.where("post_title = '#{post_title}'")
Post.where("post_title = '%{post_title}'" % { post_title: post_title })

# Safe Query

Post.where(post_title: post_title)
Post.where("post_title = ?", post_title)
Post.where("post_title = :post_title", post_title: post_title)

Compounding Queries

# Unsafe Query

def unsafe_query
  query = []
  query << "post_title = #{post_title}" if condition1
  query << "author = #{author}"   if condition2
  Post.where(query.join(' and '))
end

# Safe Query

def safe_query
  Post.all.tap do |query|
    query.where(post_title: post_title) if condition1
    query.where(author: author)   if condition2
  end
end

Like Query

# Unsafe Query

Post.where("post_title LIKE '%#{post_title}%'")

# Safe Query

Post.where("post_title LIKE ?", "%#{post_title}%")

Conclusion

From the above mentioned Unsafe vs Safe illustrations, it’s clear that if there is a surrounding quote to the query, it’s vulnerable to SQL Injection. Thanks to clever methods, this is hardly a problem in most Rails applications now-a-days. However, this is a very common but devastating attack in the world of web apps.

Hence, it’s important to understand the problem & fix it as described above.

If you’re worried about the security of your Ruby on Rails App, we would be happy to help you. If you’re planning to start something new on RoR, get in touch with us. We’ll convert your ideas into app.