SQL Injection (SQLi); How to Fix it in Ruby on Rails by Jay June 11, 2016 2 Minutes ReadWhat 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. Related Posts: Ruby on Rails Integration Testing with Minitest and Capybara Ruby on Rails releases fixes for DoS, XSS Vulnerabilities Why Businesses Prefer Ruby on Rails and Tips to Hire Ruby on Rails Developer Tags: Code, Rails, RoR, Ruby, SQL Jayadev Das jayadev.das@andolasoft.com Do what you do best in – that’s what I’ve always believed in and that’s what I preach. Over the past 25+ years (yup that’s my expertise ‘n’ experience in the Information Technology domain), I’ve been consulting to small, medium and large companies ‘bout Web Technologies, Mobile Future as well as on the good-and-bad of tech. Blogger, International Business Advisor, Web Technology Expert, Sales Guru, Startup Mentor, Insurance Sales Portal Expert & a Tennis Player. And top of all – a complete family man!