Ruby on Rails SQL Injection

Critical Risk SQL Injection
sql-injectionrailsrubyactiverecord

What it is

SQL injection vulnerability in Ruby on Rails applications where user input is directly interpolated into SQL queries using string interpolation or concatenation instead of parameterized queries.

class UsersController < ApplicationController
  # VULNERABLE: Direct string interpolation
  def show
    user_id = params[:id]
    @user = User.find_by_sql("SELECT * FROM users WHERE id = \#{user_id}").first

    if @user
      render json: @user
    else
      render json: { error: 'User not found' }, status: 404
    end
  end

  # VULNERABLE: String interpolation in where clause
  def search
    name = params[:name]
    department = params[:department]
    status = params[:status]

    # String interpolation allows injection
    @users = User.where("name LIKE '%#{name}%' AND department = '#{department}' AND status = '#{status}'")

    render json: @users
  end

  # VULNERABLE: String concatenation
  def filter_by_salary
    min_salary = params[:min_salary]
    max_salary = params[:max_salary]

    sql = "SELECT * FROM users WHERE salary >= " + min_salary.to_s
    if max_salary.present?
      sql += " AND salary <= " + max_salary.to_s
    end

    @users = User.find_by_sql(sql)
    render json: @users
  end

  # VULNERABLE: Dynamic order by
  def index
    sort_column = params[:sort] || 'name'
    sort_direction = params[:direction] || 'ASC'

    # Direct interpolation of column names
    @users = User.order("#{sort_column} #{sort_direction}")
    render json: @users
  end
end

# Malicious inputs:
# GET /users/1' OR '1'='1' --
# GET /search?name='; DROP TABLE users; --
# GET /users?sort=name; DROP TABLE users; --
class UsersController < ApplicationController
  before_action :validate_user_id, only: [:show]
  before_action :validate_search_params, only: [:search]
  before_action :validate_sort_params, only: [:index]

  # SECURE: Parameterized query
  def show
    user_id = params[:id].to_i
    @user = User.where('id = ?', user_id).first

    if @user
      render json: @user.as_json(except: [:password_digest, :reset_token])
    else
      render json: { error: 'User not found' }, status: 404
    end
  end

  # SECURE: Hash conditions and parameterized queries
  def search
    name = params[:name]&.strip
    department = params[:department]&.strip
    status = params[:status]&.strip

    # Start with all users
    users = User.all

    # Apply filters safely using hash conditions
    if name.present?
      users = users.where('name ILIKE ?', "%#{name}%")
    end

    if department.present?
      users = users.where(department: department)
    end

    if status.present?
      users = users.where(status: status)
    end

    # Limit results for performance
    @users = users.limit(100)
    render json: @users
  end

  # SECURE: Validated parameters with safe ActiveRecord methods
  def filter_by_salary
    min_salary = params[:min_salary]&.to_f || 0
    max_salary = params[:max_salary]&.to_f

    # Validate salary ranges
    if min_salary < 0
      return render json: { error: 'Invalid minimum salary' }, status: 400
    end

    users = User.where('salary >= ?', min_salary)

    if max_salary.present? && max_salary > min_salary
      users = users.where('salary <= ?', max_salary)
    end

    @users = users.limit(100)
    render json: @users
  end

  # SECURE: Whitelisted sort columns
  def index
    sort_column = params[:sort] || 'name'
    sort_direction = params[:direction]&.upcase || 'ASC'

    # Input validation already done in before_action
    @users = User.order("#{sort_column} #{sort_direction}").limit(100)
    render json: @users
  end

  private

  def validate_user_id
    unless params[:id]&.match?(/\A\d+\z/)
      render json: { error: 'Invalid user ID format' }, status: 400
    end
  end

  def validate_search_params
    # Validate name length
    if params[:name].present? && params[:name].length > 100
      render json: { error: 'Name search term too long' }, status: 400
      return
    end

    # Validate department
    if params[:department].present?
      valid_departments = %w[IT HR Finance Marketing Sales]
      unless valid_departments.include?(params[:department])
        render json: { error: 'Invalid department' }, status: 400
        return
      end
    end

    # Validate status
    if params[:status].present?
      valid_statuses = %w[active inactive suspended pending]
      unless valid_statuses.include?(params[:status])
        render json: { error: 'Invalid status' }, status: 400
        return
      end
    end
  end

  def validate_sort_params
    # Whitelist allowed sort columns
    allowed_columns = %w[name email department created_at updated_at salary]
    sort_column = params[:sort] || 'name'

    unless allowed_columns.include?(sort_column)
      render json: { error: 'Invalid sort column' }, status: 400
      return
    end

    # Validate sort direction
    sort_direction = params[:direction]&.upcase || 'ASC'
    unless %w[ASC DESC].include?(sort_direction)
      render json: { error: 'Invalid sort direction' }, status: 400
      return
    end

    # Store validated params
    params[:sort] = sort_column
    params[:direction] = sort_direction
  end
end

💡 Why This Fix Works

The vulnerable code uses string interpolation, concatenation, and sprintf to build SQL queries with user input, allowing injection attacks. The fixed version uses ActiveRecord's built-in parameterized queries, hash conditions, input validation, whitelisting for dynamic columns, and proper error handling.

Why it happens

Using #{} string interpolation to embed user input directly into SQL queries bypasses Rails' built-in SQL injection protection.

Root causes

String Interpolation in Rails SQL Queries

Using #{} string interpolation to embed user input directly into SQL queries bypasses Rails' built-in SQL injection protection.

Preview example – RUBY 
# Vulnerable
User.find_by_sql("SELECT * FROM users WHERE id = #{user_id}")
User.where("name LIKE '%#{name}%'")

Direct String Concatenation in ActiveRecord

Building SQL queries using string concatenation with user input creates injection vulnerabilities.

Preview example – RUBY 
# Vulnerable
sql = "SELECT * FROM users WHERE salary >= " + min_salary.to_s
User.find_by_sql(sql)

Fixes

1

Use ActiveRecord Parameterized Queries

Always use parameterized queries with ? placeholders when working with ActiveRecord in Rails applications.

View implementation – RUBY 
# Secure
User.where('id = ?', user_id)
User.where('name ILIKE ?', "%#{name}%")
2

Use Hash Conditions and ActiveRecord Methods

Leverage ActiveRecord's built-in methods and hash conditions for safe database queries.

View implementation – RUBY 
# Secure
User.where(department: department, status: status)
User.where('salary >= ? AND salary <= ?', min_salary, max_salary)

Detect This Vulnerability in Your Code

Sourcery automatically identifies ruby on rails sql injection and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities