SQL injection from user input concatenated into pg SQL query in Rails

Critical Risk SQL Injection

rubyrailspostgresqlsql-injectionpg

What it is

SQL injection vulnerability in Ruby on Rails applications where query strings are built by concatenating user-controlled data and passed to PG.exec/exec_params without parameters or proper escaping.

Language:

# VULNERABLE: Rails with PG and concatenated SQL
require 'pg'

class TransactionService
  def initialize
    @conn = PG.connect(
      host: 'localhost',
      dbname: 'banking_app',
      user: 'app_user',
      password: 'password'
    )
  end

  # Vulnerable authentication
  def authenticate(username, password)
    query = "SELECT id, username, email, role FROM users " +
            "WHERE username = '#{username}' " +
            "AND password = crypt('#{password}', password)"

    result = @conn.exec(query)
    result.first
  end

  # Vulnerable transaction search
  def search_transactions(user_id, filters = {})
    query = "SELECT t.*, a.account_name, a.account_type " +
            "FROM transactions t " +
            "JOIN accounts a ON t.account_id = a.id " +
            "WHERE a.user_id = #{user_id}"

    # Dynamic filter building
    if filters[:date_from]
      query += " AND t.transaction_date >= '#{filters[:date_from]}'"
    end

    if filters[:date_to]
      query += " AND t.transaction_date <= '#{filters[:date_to]}'"
    end

    if filters[:amount_min]
      query += " AND t.amount >= #{filters[:amount_min]}"
    end

    if filters[:amount_max]
      query += " AND t.amount <= #{filters[:amount_max]}"
    end

    if filters[:description]
      query += " AND t.description ILIKE '%#{filters[:description]}%'"
    end

    query += " ORDER BY #{filters[:sort_by] || 'transaction_date'} DESC"

    @conn.exec(query).to_a
  end

  # Vulnerable balance update
  def transfer_funds(from_account, to_account, amount, description)
    # Check balance
    balance_query = "SELECT balance FROM accounts WHERE id = #{from_account}"
    balance_result = @conn.exec(balance_query)

    if balance_result.any? && balance_result[0]['balance'].to_f >= amount
      # Debit from account
      debit_query = "UPDATE accounts SET balance = balance - #{amount} " +
                   "WHERE id = #{from_account}"
      @conn.exec(debit_query)

      # Credit to account
      credit_query = "UPDATE accounts SET balance = balance + #{amount} " +
                    "WHERE id = #{to_account}"
      @conn.exec(credit_query)

      # Log transaction
      log_query = "INSERT INTO transaction_log (from_account, to_account, " +
                 "amount, description, timestamp) VALUES " +
                 "(#{from_account}, #{to_account}, #{amount}, " +
                 "'#{description}', NOW())"
      @conn.exec(log_query)

      return true
    end
    false
  end
end

# Rails controller using vulnerable service
class TransactionsController < ApplicationController
  def search
    service = TransactionService.new

    filters = {
      date_from: params[:date_from],
      date_to: params[:date_to],
      amount_min: params[:amount_min],
      amount_max: params[:amount_max],
      description: params[:description],
      sort_by: params[:sort_by]
    }

    @transactions = service.search_transactions(current_user.id, filters)
  end

  def transfer
    service = TransactionService.new

    success = service.transfer_funds(
      params[:from_account],
      params[:to_account],
      params[:amount],
      params[:description]
    )

    if success
      render json: { success: true }
    else
      render json: { error: 'Insufficient funds' }, status: 400
    end
  end
end

# Attack examples:
# username = "admin' --"
# description = "'; DROP TABLE accounts; --"
# sort_by = "transaction_date; DELETE FROM users; --"

# SECURE: Parameterized queries with validation
require 'pg'
require 'bcrypt'

class TransactionService
  VALID_SORT_FIELDS = %w[transaction_date amount description status].freeze
  VALID_SORT_DIRECTIONS = %w[ASC DESC].freeze

  def initialize
    @conn = PG.connect(
      host: ENV['DB_HOST'],
      dbname: ENV['DB_NAME'],
      user: ENV['DB_USER'],
      password: ENV['DB_PASSWORD']
    )
    prepare_statements
  end

  def prepare_statements
    # Prepare frequently used queries
    @conn.prepare('find_user_by_username',
                  'SELECT id, username, email, role, password_hash ' +
                  'FROM users WHERE username = $1')

    @conn.prepare('get_account_balance',
                  'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE')

    @conn.prepare('update_account_balance',
                  'UPDATE accounts SET balance = balance + $1, ' +
                  'last_transaction = NOW() WHERE id = $2')

    @conn.prepare('insert_transaction_log',
                  'INSERT INTO transaction_log (from_account, to_account, ' +
                  'amount, description, status, timestamp) ' +
                  'VALUES ($1, $2, $3, $4, $5, NOW())')
  end

  # Secure authentication
  def authenticate(username, password)
    # Validate username format
    return nil unless username =~ /A[a-zA-Z0-9_]{3,30}z/

    result = @conn.exec_prepared('find_user_by_username', [username])

    if result.any?
      user = result.first
      # Verify password with bcrypt
      if BCrypt::Password.new(user['password_hash']) == password
        user.delete('password_hash')
        return user
      end
    end
    nil
  end

  # Secure transaction search with parameterized queries
  def search_transactions(user_id, filters = {})
    # Validate user_id
    return [] unless user_id.to_i > 0

    query = "SELECT t.id, t.amount, t.description, t.transaction_date, " +
            "t.status, a.account_name, a.account_type " +
            "FROM transactions t " +
            "JOIN accounts a ON t.account_id = a.id " +
            "WHERE a.user_id = $1"

    params = [user_id.to_i]
    param_count = 1

    # Add filters with parameters
    if filters[:date_from].present?
      begin
        date = Date.parse(filters[:date_from])
        param_count += 1
        query += " AND t.transaction_date >= $#{param_count}"
        params << date
      rescue Date::Error
        # Invalid date, skip this filter
      end
    end

    if filters[:date_to].present?
      begin
        date = Date.parse(filters[:date_to])
        param_count += 1
        query += " AND t.transaction_date <= $#{param_count}"
        params << date
      rescue Date::Error
        # Invalid date, skip this filter
      end
    end

    if filters[:amount_min].present? && filters[:amount_min].to_f > 0
      param_count += 1
      query += " AND t.amount >= $#{param_count}"
      params << filters[:amount_min].to_f
    end

    if filters[:amount_max].present? && filters[:amount_max].to_f > 0
      param_count += 1
      query += " AND t.amount <= $#{param_count}"
      params << filters[:amount_max].to_f
    end

    if filters[:description].present?
      param_count += 1
      query += " AND t.description ILIKE $#{param_count}"
      params << "%#{filters[:description]}%"
    end

    # Validate and add sorting
    sort_by = VALID_SORT_FIELDS.include?(filters[:sort_by]) ?
              filters[:sort_by] : 'transaction_date'
    sort_direction = VALID_SORT_DIRECTIONS.include?(filters[:direction]) ?
                     filters[:direction] : 'DESC'

    query += " ORDER BY #{sort_by} #{sort_direction}"
    query += " LIMIT 1000" # Prevent excessive results

    @conn.exec_params(query, params).to_a
  end

  # Secure funds transfer with transaction
  def transfer_funds(from_account, to_account, amount, description)
    # Validate inputs
    from_account_id = from_account.to_i
    to_account_id = to_account.to_i
    transfer_amount = amount.to_f

    return false if from_account_id <= 0 || to_account_id <= 0
    return false if transfer_amount <= 0 || transfer_amount > 1000000
    return false if description.blank? || description.length > 500

    begin
      # Start transaction
      @conn.exec('BEGIN')

      # Check balance with row lock
      balance_result = @conn.exec_prepared('get_account_balance',
                                          [from_account_id])

      if balance_result.none?
        @conn.exec('ROLLBACK')
        return false
      end

      current_balance = balance_result[0]['balance'].to_f

      if current_balance < transfer_amount
        @conn.exec('ROLLBACK')
        return false
      end

      # Debit from account
      @conn.exec_prepared('update_account_balance',
                         [-transfer_amount, from_account_id])

      # Credit to account
      @conn.exec_prepared('update_account_balance',
                         [transfer_amount, to_account_id])

      # Log transaction
      @conn.exec_prepared('insert_transaction_log',
                         [from_account_id, to_account_id,
                          transfer_amount, description, 'completed'])

      # Commit transaction
      @conn.exec('COMMIT')
      true

    rescue PG::Error => e
      @conn.exec('ROLLBACK')
      Rails.logger.error "Transfer failed: #{e.message}"
      false
    end
  end

  def close
    @conn.close if @conn
  end
end

# Secure Rails controller
class TransactionsController < ApplicationController
  before_action :authenticate_user!

  def search
    service = TransactionService.new

    # Sanitize and validate filters
    filters = {
      date_from: params[:date_from],
      date_to: params[:date_to],
      amount_min: params[:amount_min],
      amount_max: params[:amount_max],
      description: sanitize_search_term(params[:description]),
      sort_by: params[:sort_by],
      direction: params[:direction]
    }.compact

    @transactions = service.search_transactions(current_user.id, filters)

    render json: @transactions
  ensure
    service&.close
  end

  def transfer
    service = TransactionService.new

    # Additional validation
    from_account = params[:from_account]
    to_account = params[:to_account]
    amount = params[:amount].to_f
    description = params[:description]

    # Verify user owns the from_account
    unless current_user.accounts.exists?(id: from_account)
      render json: { error: 'Unauthorized' }, status: 403
      return
    end

    success = service.transfer_funds(
      from_account,
      to_account,
      amount,
      description
    )

    if success
      render json: { success: true }
    else
      render json: { error: 'Transfer failed' }, status: 400
    end
  ensure
    service&.close
  end

  private

  def sanitize_search_term(term)
    return nil if term.blank?
    # Remove potential SQL meta-characters
    term.gsub(/[%_\]/, '')
  end
end

# Alternative: Use ActiveRecord when possible
class Transaction < ApplicationRecord
  belongs_to :account

  scope :for_user, ->(user_id) {
    joins(:account).where(accounts: { user_id: user_id })
  }

  scope :date_range, ->(from, to) {
    where(transaction_date: from..to) if from && to
  }

  scope :amount_range, ->(min, max) {
    scope = all
    scope = scope.where('amount >= ?', min) if min
    scope = scope.where('amount <= ?', max) if max
    scope
  }

  def self.search(user_id, filters = {})
    transactions = for_user(user_id)
    transactions = transactions.date_range(filters[:date_from],
                                          filters[:date_to])
    transactions = transactions.amount_range(filters[:amount_min],
                                            filters[:amount_max])

    if filters[:description].present?
      transactions = transactions.where('description ILIKE ?',
                                       "%#{filters[:description]}%")
    end

    transactions.order(transaction_date: :desc).limit(1000)
  end
end

💡 Why This Fix Works

The vulnerable code concatenates user input directly into PG SQL queries, allowing injection attacks. The fixed version uses exec_params with $1, $2 placeholders, prepared statements for frequent queries, comprehensive input validation, proper transaction handling, and shows both raw PG and ActiveRecord approaches.

Why it happens

Query strings are built by concatenating user-controlled data and passed to PG.exec without parameters, enabling SQL injection.

Root causes

String Concatenation in PG Queries

Query strings are built by concatenating user-controlled data and passed to PG.exec without parameters, enabling SQL injection.

Preview example – RUBY

# VULNERABLE: String concatenation in PG queries
require 'pg'

class UserService
  def find_by_email(email)
    conn = PG.connect(dbname: 'myapp')

    # String interpolation vulnerability
    query = "SELECT * FROM users WHERE email = '#{email}'"
    result = conn.exec(query)

    conn.close
    result.first
  end

  def search_products(name, category, max_price)
    conn = PG.connect(dbname: 'myapp')

    # String concatenation vulnerability
    query = "SELECT * FROM products WHERE 1=1"
    query += " AND name ILIKE '%#{name}%'" if name
    query += " AND category = '#{category}'" if category
    query += " AND price <= #{max_price}" if max_price

    result = conn.exec(query)
    conn.close
    result.to_a
  end
end

Dynamic Query Building with User Input

Building complex SQL queries by concatenating user input from Rails params directly into query strings.

Preview example – RUBY

# VULNERABLE: Dynamic query with Rails params
class ReportsController < ApplicationController
  def transactions
    conn = PG.connect(dbname: 'myapp')

    # Building query from params
    query = "SELECT * FROM transactions WHERE user_id = #{params[:user_id]}"

    if params[:filters].present?
      params[:filters].each do |field, value|
        query += " AND #{field} = '#{value}'"
      end
    end

    query += " ORDER BY #{params[:sort_by]} #{params[:direction]}"

    @results = conn.exec(query)
    conn.close
  end
end

Fixes

Use Parameterized Queries with exec_params

Use PG.exec_params with placeholders ($1, $2, ...) and pass values as a separate array. This ensures proper escaping and prevents injection.

View implementation – RUBY

# SECURE: Parameterized queries with PG
require 'pg'

class UserService
  def initialize
    @conn = PG.connect(dbname: 'myapp')
  end

  def find_by_email(email)
    # Parameterized query with $1 placeholder
    query = "SELECT * FROM users WHERE email = $1"
    result = @conn.exec_params(query, [email])
    result.first
  end

  def search_products(name, category, max_price)
    query = "SELECT * FROM products WHERE 1=1"
    params = []
    param_count = 0

    if name.present?
      param_count += 1
      query += " AND name ILIKE $#{param_count}"
      params << "%#{name}%"
    end

    if category.present?
      param_count += 1
      query += " AND category = $#{param_count}"
      params << category
    end

    if max_price.present?
      param_count += 1
      query += " AND price <= $#{param_count}"
      params << max_price
    end

    result = @conn.exec_params(query, params)
    result.to_a
  end
end

Use Prepared Statements for Repeated Queries

For queries executed multiple times, use conn.prepare and conn.exec_prepared for better performance and security.

View implementation – RUBY

# SECURE: Prepared statements with PG
class DatabaseService
  def initialize
    @conn = PG.connect(dbname: 'myapp')
    prepare_statements
  end

  def prepare_statements
    @conn.prepare('find_user', 'SELECT * FROM users WHERE id = $1')
    @conn.prepare('update_balance',
                  'UPDATE accounts SET balance = balance + $1 WHERE id = $2')
    @conn.prepare('log_activity',
                  'INSERT INTO activity_log (user_id, action, details, timestamp) ' +
                  'VALUES ($1, $2, $3, NOW())')
  end

  def find_user(user_id)
    result = @conn.exec_prepared('find_user', [user_id])
    result.first
  end

  def update_balance(account_id, amount)
    @conn.exec_prepared('update_balance', [amount, account_id])
  end

  def log_activity(user_id, action, details)
    @conn.exec_prepared('log_activity', [user_id, action, details])
  end
end

Detect This Vulnerability in Your Code

Sourcery automatically identifies sql injection from user input concatenated into pg sql query in rails and many other security issues in your codebase.

Scan Your Code for Free Explore More Vulnerabilities