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:

[code language=”html”]
Customer.where("email = #{user_data}").first
[/code]

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

[code language=”html”]
user_data = “email@somedomain.com; DROP TABLE customers;”
[/code]

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

[code language=”html”]
SELECT * FORM customers WHERE email=’someone@example.com; DROP TABLE customers;–‘
[/code]

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:

[code language=”html”]
User.where("username = #{user_data}").first
[/code]

The attacker inserts the following text as ‘user_data’:

[code language=”html”]
user_data = "” or admin=’t’–"
[/code]

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

[code language=”html”]
Post.where("post_title = ‘#{post_title}’")
Post.where("post_title = ‘%{post_title}’" % { post_title: post_title })
[/code]

# Safe Query

[code language=”html”]
Post.where(post_title: post_title)
Post.where("post_title = ?", post_title)
Post.where("post_title = :post_title", post_title: post_title)
[/code]

Compounding Queries

# Unsafe Query

[code language=”html”]
def unsafe_query
query = []
query << "post_title = #{post_title}" if condition1
query << "author = #{author}" if condition2
Post.where(query.join(‘ and ‘))
end
[/code]

# Safe Query

[code language=”html”]
def safe_query
Post.all.tap do |query|
query.where(post_title: post_title) if condition1
query.where(author: author) if condition2
end
end
[/code]

Like Query

# Unsafe Query

[code language=”html”]
Post.where("post_title LIKE ‘%#{post_title}%’")
[/code]

# Safe Query

[code language=”html”]
Post.where("post_title LIKE ?", "%#{post_title}%")
[/code]

 

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.