SQL Injection in Rails: Understanding and Preventing CWE-89

SQL injection is a critical vulnerability that can expose sensitive data, compromise user accounts, and even lead to a full server takeover. The Common Weakness Enumeration (CWE) system identifies this vulnerability as CWE-89: Improper Neutralization of Special Elements used in an SQL Command (‘SQL Injection’). For Ruby on Rails developers, understanding how to prevent this attack is a fundamental aspect of building secure applications.

This article will explore what SQL injection is, how it manifests in Rails, and the best practices for preventing it.

What is SQL Injection?

SQL injection occurs when an attacker manipulates an application’s SQL queries by inserting malicious SQL code into user-provided input. If the application doesn’t properly sanitize this input, the malicious code is executed by the database, allowing the attacker to bypass security measures and access unauthorized data.

Consider a simple login form. A vulnerable application might construct a query like this:

SELECT * FROM users WHERE username = '#{params[:username]}' AND password = '#{params[:password]}';

An attacker could enter ' OR '1'='1 as the username and any password. The resulting query would be:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'password';

This query would return all users, effectively bypassing the login process.

SQL Injection in Rails

While Rails’ ActiveRecord provides built-in protection against SQL injection in many cases, vulnerabilities can still be introduced through improper use of its methods.

Vulnerable Code Examples

The most common source of SQL injection vulnerabilities in Rails is the use of raw strings in query conditions. Here are a few examples of vulnerable code:

1. Using string interpolation in where clauses:

# Vulnerable
User.where("name = '#{params[:name]}'")

2. Using raw strings in order clauses:

# Vulnerable
User.order("name #{params[:direction]}")

3. Using find_by_sql with user input:

# Vulnerable
User.find_by_sql("SELECT * FROM users WHERE name = '#{params[:name]}'")

Preventing SQL Injection in Rails

The key to preventing SQL injection is to never trust user input and to always use ActiveRecord’s built-in sanitization methods.

Use Parameterized Queries

ActiveRecord’s where method can take an array or a hash to safely pass user input to the database. This is the most common and effective way to prevent SQL injection.

Array Syntax:

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

In this example, ? is a placeholder that ActiveRecord replaces with the sanitized value of params[:name].

Hash Syntax:

# Safe
User.where(name: params[:name])

This is the preferred syntax in most cases as it’s more readable and less prone to errors.

Sanitize Raw SQL Fragments

For situations where you need to use raw SQL, such as complex ORDER BY clauses, you can use Arel.sql to mark the string as safe, or use sanitize_sql_for_order for order clauses.

Using Arel.sql:

# Safe for known directions
direction = %w[asc desc].include?(params[:direction]) ? params[:direction] : 'asc'
User.order(Arel.sql("name #{direction}"))

Using sanitize_sql_for_order:

# Safe
order_clause = User.sanitize_sql_for_order(["name #{params[:direction]}"])
User.order(order_clause)

Whitelist User Input for Order Clauses

A simple and effective way to handle dynamic order clauses is to whitelist the allowed values:

# Safe
direction = params[:direction] == 'desc' ? 'desc' : 'asc'
User.order(name: direction)

Use Scopes

For complex queries, encapsulate the logic in a scope on your model. This makes your code cleaner and less likely to contain vulnerabilities.

class User < ApplicationRecord
  scope :sorted_by_name, ->(direction) {
    safe_direction = %w[asc desc].include?(direction) ? direction : 'asc'
    order(name: safe_direction)
  }
end

# In your controller
User.sorted_by_name(params[:direction])

Tools for Detecting SQL Injection

In addition to writing secure code, you can use automated tools to scan your application for security vulnerabilities, including SQL injection.

Brakeman

Brakeman is a static analysis tool specifically designed for Ruby on Rails applications. It scans your source code for security vulnerabilities and can detect many common issues, including SQL injection.

To use Brakeman, add it to your Gemfile:

gem 'brakeman', group: :development

Then run bundle install and execute Brakeman from your terminal:

brakeman

Brakeman will generate a report with any vulnerabilities it finds, along with the file and line number where the issue was detected.

Other Static Analysis (SAST) Tools

Many other Static Application Security Testing (SAST) tools can be integrated into your development pipeline to automatically scan your code for vulnerabilities. These tools can help you catch issues early in the development process.

Conclusion

SQL injection remains a significant threat to web applications, but Ruby on Rails provides powerful tools to prevent it. By following these best practices, you can build secure and robust Rails applications:

• Always use parameterized queries (where(name: params[:name]) or where("name = ?", params[:name])).
• Never use string interpolation to build queries with user input.
• Whitelist user input for values that cannot be parameterized, like order clauses.
• Encapsulate complex queries in model scopes.
• Use static analysis tools like Brakeman to automatically detect vulnerabilities.

By being mindful of how you handle user input in your database queries and by using automated security tools, you can effectively eliminate the risk of SQL injection in your Rails applications.