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.
Join the DZone community and get the full member experience.
Join For FreeWhat 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 | |
Topics:
sql injection prevention,
security,
web application security,
ruby on rails security,
sql injection vulnerability
Published at DZone with permission of Ferruh Mavituna, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments