Ruby Rails SQL Injection Vulnerability (Brakeman Check)

Critical Risk SQL Injection

RubyRailsSQL InjectionBrakemanDatabase SecurityActiveRecord

What it is

Application constructs SQL queries using string interpolation or concatenation with user input in Rails, creating SQL injection vulnerabilities that can compromise database security.

Language:

class UsersController < ApplicationController
  def search
    # Vulnerable: String interpolation in where clause
    query = params[:query]
    @users = User.where("name LIKE '%#{query}%'")
    
    render json: @users
  end
  
  def find_by_id
    # Vulnerable: Direct interpolation in SQL
    user_id = params[:id]
    @user = User.find_by_sql("SELECT * FROM users WHERE id = #{user_id}")
    
    render json: @user
  end
  
  def update_status
    # Vulnerable: Dynamic SQL with user input
    user_id = params[:user_id]
    status = params[:status]
    
    sql = "UPDATE users SET status = '#{status}' WHERE id = #{user_id}"
    ActiveRecord::Base.connection.execute(sql)
    
    render json: { message: 'Status updated' }
  end
  
  def advanced_search
    # Vulnerable: Complex query building
    conditions = []
    conditions << "name LIKE '%#{params[:name]}%'" if params[:name]
    conditions << "email LIKE '%#{params[:email]}%'" if params[:email]
    
    where_clause = conditions.join(' AND ')
    @users = User.where(where_clause)
    
    render json: @users
  end
end

class UsersController < ApplicationController
  before_action :validate_search_params, only: [:search, :advanced_search]
  
  def validate_search_params
    if params[:query] && params[:query].length > 100
      render json: { error: 'Query too long' }, status: 400
      return
    end
    
    if params[:name] && params[:name].length > 50
      render json: { error: 'Name query too long' }, status: 400
      return
    end
    
    if params[:email] && !params[:email].match?(/\A[\w+\-.]+@[a-z\d\-]+(\.[a-z\d\-]+)*\.[a-z]+\z/i)
      render json: { error: 'Invalid email format' }, status: 400
      return
    end
  end
  
  def search
    # Secure: Use parameterized query
    query = params[:query]
    
    if query.present?
      # Safe: ActiveRecord handles parameterization
      @users = User.where('name ILIKE ?', "%#{query}%")
    else
      @users = User.none
    end
    
    render json: @users
  end
  
  def find_by_id
    # Secure: Use ActiveRecord find methods
    user_id = params[:id]
    
    begin
      # Validate ID is numeric
      id = Integer(user_id)
      @user = User.find(id)
      
      render json: @user
    rescue ArgumentError
      render json: { error: 'Invalid user ID format' }, status: 400
    rescue ActiveRecord::RecordNotFound
      render json: { error: 'User not found' }, status: 404
    end
  end
  
  def update_status
    # Secure: Use ActiveRecord update methods
    user_id = params[:user_id]
    status = params[:status]
    
    # Validate status
    allowed_statuses = %w[active inactive pending suspended]
    unless allowed_statuses.include?(status)
      return render json: { error: 'Invalid status' }, status: 400
    end
    
    begin
      id = Integer(user_id)
      user = User.find(id)
      
      # Safe: ActiveRecord handles parameterization
      user.update!(status: status)
      
      render json: { message: 'Status updated', user: user }
    rescue ArgumentError
      render json: { error: 'Invalid user ID format' }, status: 400
    rescue ActiveRecord::RecordNotFound
      render json: { error: 'User not found' }, status: 404
    rescue ActiveRecord::RecordInvalid => e
      render json: { error: e.message }, status: 422
    end
  end
  
  def advanced_search
    # Secure: Build conditions safely
    conditions = {}
    
    if params[:name].present?
      conditions[:name] = params[:name]
    end
    
    if params[:email].present?
      conditions[:email] = params[:email]
    end
    
    if params[:status].present?
      allowed_statuses = %w[active inactive pending suspended]
      if allowed_statuses.include?(params[:status])
        conditions[:status] = params[:status]
      end
    end
    
    # Safe: Use hash conditions
    @users = User.where(conditions)
    
    # Alternative: Use scopes for complex queries
    @users = @users.active if params[:active_only] == 'true'
    @users = @users.recent if params[:recent_only] == 'true'
    
    render json: @users
  end
  
  # Secure alternative using scopes
  def scoped_search
    @users = User.all
    
    # Use safe scopes instead of raw SQL
    @users = @users.with_name(params[:name]) if params[:name].present?
    @users = @users.with_email_domain(params[:domain]) if params[:domain].present?
    @users = @users.created_after(params[:after]) if params[:after].present?
    
    render json: @users
  end
end

# Secure model with scopes
class User < ApplicationRecord
  # Safe scopes that handle parameterization
  scope :with_name, ->(name) { where('name ILIKE ?', "%#{sanitize_sql_like(name)}%") }
  scope :with_email_domain, ->(domain) { where('email ILIKE ?', "%@#{sanitize_sql_like(domain)}") }
  scope :created_after, ->(date) { where('created_at > ?', Date.parse(date)) }
  scope :active, -> { where(status: 'active') }
  scope :recent, -> { where('created_at > ?', 30.days.ago) }
  
  validates :status, inclusion: { in: %w[active inactive pending suspended] }
  validates :email, format: { with: URI::MailTo::EMAIL_REGEXP }
end

💡 Why This Fix Works

See fix suggestions for detailed explanation.

Why it happens

Rails query with interpolation: User.where("email = '#{params[:email]}'"). String interpolation doesn't parameterize. SQL injection through params[:email]. Quote escaping vulnerable. ActiveRecord where() requires parameterized queries for security. String interpolation creates injection vectors.

Root causes

Using String Interpolation in ActiveRecord where() Clauses

Using Raw SQL with User Input in find_by_sql

Raw SQL queries: User.find_by_sql("SELECT * FROM users WHERE name = '#{name}'"). find_by_sql executes raw SQL. String formatting bypasses ORM protection. User input in SQL strings. All raw SQL needs parameterization. ActiveRecord provides safe alternatives.

Concatenating User Input in SQL Conditions

String concatenation: condition = 'username = "' + params[:user] + '"'; User.where(condition). + operator builds SQL strings. No parameterization. User controls query logic. Should use: User.where(username: params[:user]). Hash syntax or placeholders required.

Using String Conditions Without Parameterization

Unparameterized conditions: User.where('role = ?', params[:role]) safe but User.where('role = ' + params[:role]) vulnerable. Question mark placeholders required. Array syntax for parameters. String building without placeholders creates SQL injection.

Building Dynamic ORDER BY or LIMIT Clauses

Dynamic sorting: User.order("#{params[:sort]} #{params[:direction]}"). ORDER BY clauses with user input. Column and direction from params. Cannot use parameters for identifiers. Requires allowlist validation. String interpolation in order() vulnerable.

Fixes

Use Hash Syntax for Simple WHERE Conditions

Safe hash conditions: User.where(email: params[:email]) or User.where(role: role, active: true). ActiveRecord handles parameterization. Hash syntax preferred. Type-safe. No SQL injection risk. Simple and readable. Use for equality checks.

Use Array Syntax with Placeholders for Complex Conditions

Parameterized queries: User.where(['email = ? AND active = ?', params[:email], true]) or User.where('age > :min_age', min_age: params[:age]). Question mark or named placeholders. Array passes parameters. ActiveRecord escapes values. Safe for all conditions.

Use ActiveRecord Query Interface Instead of Raw SQL

ORM methods: User.joins(:posts).where(posts: {published: true}).order(created_at: :desc). Chainable query interface. Automatic parameterization. Type safety. Database abstraction. Avoid find_by_sql when possible. ORM methods prevent SQL injection.

Validate Dynamic Identifiers with Allowlists

Allowlist for ORDER BY: ALLOWED_SORT = ['name', 'email', 'created_at']; ALLOWED_DIR = ['asc', 'desc']; raise unless ALLOWED_SORT.include?(params[:sort]) && ALLOWED_DIR.include?(params[:dir]); User.order("#{params[:sort]} #{params[:dir]}"). Validate identifiers. Can't parameterize column names. Allowlist essential.

Use Arel for Complex Dynamic Queries

Arel query builder: users = User.arel_table; query = users[:email].eq(params[:email]).and(users[:active].eq(true)); User.where(query). Programmatic query construction. Type-safe. Prevents injection through structure. Advanced query building without raw SQL.

Run Brakeman to Detect SQL Injection Vulnerabilities

Use Brakeman scanner: brakeman -A detects SQL injection. Review SQL Injection warnings. Fix flagged queries. Automated detection in CI/CD. Regular scans prevent unsafe queries. Brakeman specifically checks ActiveRecord with user input.

Detect This Vulnerability in Your Code

Sourcery automatically identifies ruby rails sql injection vulnerability (brakeman check) and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities

Ruby Rails SQL Injection Vulnerability (Brakeman Check)

What it is

💡 Why This Fix Works

Why it happens

Root causes

Using String Interpolation in ActiveRecord where() Clauses

Using Raw SQL with User Input in find_by_sql

Concatenating User Input in SQL Conditions

Using String Conditions Without Parameterization

Building Dynamic ORDER BY or LIMIT Clauses

Fixes

Use Hash Syntax for Simple WHERE Conditions

Use Array Syntax with Placeholders for Complex Conditions

Use ActiveRecord Query Interface Instead of Raw SQL

Validate Dynamic Identifiers with Allowlists

Use Arel for Complex Dynamic Queries

Run Brakeman to Detect SQL Injection Vulnerabilities

SQL injection from non-literal string concatenation in database/sql query

SQL injection from formatted SQL string in JDBC Statement

SQL injection from HTTP request data in Django raw() query

Python SQLAlchemy Raw Query SQL Injection Vulnerability

Detect This Vulnerability in Your Code

Ruby Rails SQL Injection Vulnerability (Brakeman Check)

What it is

💡 Why This Fix Works

Why it happens

Root causes

Using String Interpolation in ActiveRecord where() Clauses

Using Raw SQL with User Input in find_by_sql

Concatenating User Input in SQL Conditions

Using String Conditions Without Parameterization

Building Dynamic ORDER BY or LIMIT Clauses

Fixes

Use Hash Syntax for Simple WHERE Conditions

Use Array Syntax with Placeholders for Complex Conditions

Use ActiveRecord Query Interface Instead of Raw SQL

Validate Dynamic Identifiers with Allowlists

Use Arel for Complex Dynamic Queries

Run Brakeman to Detect SQL Injection Vulnerabilities

Related Vulnerabilities

SQL injection from non-literal string concatenation in database/sql query

SQL injection from formatted SQL string in JDBC Statement

SQL injection from HTTP request data in Django raw() query

Python SQLAlchemy Raw Query SQL Injection Vulnerability

Detect This Vulnerability in Your Code