DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Injection Prevention Techniques for Ruby on Rails Web Applications

SQL Injection Prevention Techniques for Ruby on Rails Web Applications

SQLi is one of the most dangerous, and common, attacks. Learn how to prevent such cyberattacks in your Ruby on Rails based applicaiton.

Ferruh Mavituna user avatar by
Ferruh Mavituna
·
Feb. 21, 18 · Tutorial
Like (2)
Save
Tweet
Share
12.03K Views

Join the DZone community and get the full member experience.

Join For Free

What Is a SQL Injection Vulnerability?

SQL Injection is a web application vulnerability that occurs when untrusted data is inserted in a SQL query without any sanitization or escaping. Since databases play a core role in today's web applications, being able to manipulate SQL queries and therefore control the database usually result with the compromise of the target web application's data or even a complete takeover of the application itself via the elevation of the SQL Injection to some sort of remote code execution vulnerability.

Read What is SQL Injection and refer to the SQL Injection cheat sheet for more detailed technical information and examples of the SQL Injection vulnerability.

Preventing SQL Injections When Developing Web Applications With Ruby on Rails


Active Record is a Ruby on Rails library that helps developers interact with databases without actually writing any SQL queries. It provides an ORM layer which maps objects to the tables of the database system.

There are many methods available to read and manipulate the data. Even though most of those Active Record methods are already using parameterized queries and can be used without worrying about SQL Injection vulnerabilities, there are still some methods available which are accepting raw SQL and will introduce SQL Injection vulnerabilities if not used with caution.

Use Dynamic Attribute-Based Finders When Possible

When finding/retrieving information from the database, dynamic attribute-based finders should be used to avoid SQL Injection vulnerabilities. These work as parameterized queries and take care of the passed argument. Let's illustrate the point with an example:

User.find_by(name: params[:name]) # Traditional
User.find_by_name(name) # dynamic finder 

The find_by method used in the first line can be vulnerable to SQL Injection if not used carefully. In our example, we made sure this is not the case by passing the column name and value explicitly. But the second line, which uses dynamic finders, didn't really require anything rather than calling the method itself. Active Record knows that the argument is not a column or a table name and added to the SQL query with proper escaping.

Only Accept and Construct Values From Inputs, not Commands

SQL Commands such as INSERT, DELETE, and SELECT and other core parts of SQL queries such as column and table names should never be constructed from untrusted inputs. This can be easily achieved by not sending untrusted inputs to those methods in the Active Record library that accept column and table names or raw SQL queries.

Never Pass a String as an Argument Unless It Is a Dynamic Finder

First of all, it is really important to know exactly what the AR method expects as arguments. Some methods accept arguments in multiple forms and accept different formats. This works fine if the input is an integer, but the same method will be vulnerable to SQL Injection when called with a string.

There are many methods that will usually require external input to work within the Active Record, especially under ActiveRecord::FinderMethods and ActiveRecord::QueryMethods. User input should not be passed as it is, but rather should be encapsulated in a form that prevents modifying the SQL query. If a string is passed, that would generally be interpreted as Active Record, as an intent to add a fragment to the SQL query.

Let's look at the ActiveRecord::QueryMethods::WhereChain::where method which does the filtering based on the conditions specified in the arguments. This method accepts conditions in several different formats such as string, array, or hash, and goes to the WHERE-part of a SQL statement. If we simply pass user_input directly to this method, this would create a very basic type of SQL Injection because when a string is passed, it will be added to the query as a SQL fragment and providing such an opportunity will make attackers really happy! Let's illustrate the point with an example:

User.where("name = '#{params[:name]'") # SQL Injection!

If this particular line of code invoked with name = 'fff', the resulting query will be:

SELECT "users".* FROM "users" WHERE (name = 'fff')
=> #<ActiveRecord::Relation []>

But if it is set to "' OR 1='1":

SELECT "users".* FROM "users" WHERE (name = ' ' OR '1'='1')
=> #<ActiveRecord::Relation [#<User id: 1, name:'jack', .......>]>

As seen above, the attack is the successful inclusion of an OR operator which helped us return all the records from the database. More advanced queries could easily be crafted from this point onward. Let's take a look at the injection-proof version.

User.where(["name = ?", "#{params[:name]}"])

SQL Injection is not possible when using the above because the first element of the array is a template and the latter are parameters to that template.

User.where({ name: params[:name] })

Again, this is free from SQL Injection, because the column name is set explicitly to the 'name' and the external input set to the value of it.

Vulnerability Classification and Severity Table

Classification ID / Severity
PCI v3.1 6.5.1
PCI v3.2 6.5.1
OWASP 2013 A1
CWE 89
CAPEC 66
WASC 19
HIPAA 164.306(a), 164.308(a)
CVSS 3.0 Score
Base 10 (Critical)
Temporal 10 (Critical)
Environmental 10 (Critical)
CVSS Vector String
CVSS:3.0/AV:N/AC:L/PR:N/UI:N/S:C/C:H/I:H/A:H
sql Database Injection Web application

Published at DZone with permission of Ferruh Mavituna, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • A Brief Overview of the Spring Cloud Framework
  • How To Check Docker Images for Vulnerabilities
  • Event Driven 2.0
  • Bye-Bye, Regular Dev [Comic]

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: