SQL Injection Prevention

Overview

SQL injection represents one of the most critical security vulnerabilities in web applications. This attack vector occurs when untrusted data enters a SQL query without proper sanitization or parameterization, allowing attackers to manipulate the query structure and execute arbitrary SQL commands. The vulnerability exists at the intersection of application code and database interaction, where string concatenation or interpolation creates opportunities for malicious input to alter query semantics.

The fundamental problem stems from mixing code and data within SQL statements. When applications construct queries by concatenating user input directly into SQL strings, the database cannot distinguish between legitimate query structure and attacker-controlled data. An attacker can inject SQL syntax that closes the intended query, appends new commands, or modifies query logic to access unauthorized data, bypass authentication, or delete records.

Consider a simple authentication query constructed through string concatenation:

# Vulnerable approach
username = params[:username]
query = "SELECT * FROM users WHERE username = '#{username}'"
results = db.execute(query)

An attacker submitting admin' OR '1'='1 as the username transforms the query into:

SELECT * FROM users WHERE username = 'admin' OR '1'='1'

The injected OR '1'='1' condition always evaluates to true, returning all user records and potentially granting unauthorized access. More sophisticated attacks can extract entire databases, modify data, or execute operating system commands depending on database permissions and configuration.

Ruby applications interact with databases through various abstraction layers including raw database drivers, ActiveRecord ORM, Sequel, and other libraries. Each layer provides mechanisms for preventing SQL injection, but developers must understand and apply these protections consistently. The severity of SQL injection vulnerabilities and their prevalence in OWASP Top 10 lists underscore the critical importance of defensive programming practices.

Key Principles

SQL injection prevention operates on the principle of separating query structure from data values. The database must receive explicit instructions about which parts of a SQL statement represent code and which represent data. This separation prevents user input from altering query semantics regardless of the characters or SQL syntax the input contains.

Parameterized queries, also called prepared statements, implement this separation by sending the query structure and data values to the database in distinct phases. The database first parses and compiles the query with placeholders for data values, establishing the query structure. Subsequently, the application sends the actual data values, which the database treats exclusively as data parameters rather than executable code. This two-phase approach ensures that user input cannot escape the data context to become part of the query structure.

# Parameterized query structure
query = "SELECT * FROM users WHERE username = ?"
statement = db.prepare(query)
results = statement.execute(username)

The placeholder ? indicates a parameter position. When the database receives this query, it parses the structure with the parameter location marked but not filled. The subsequent execute call provides the actual username value, which the database inserts at the parameter position without parsing it as SQL syntax.

Input validation represents a complementary defense layer but cannot substitute for parameterized queries. Validation examines user input against expected patterns and rejects values that contain suspicious characters or patterns. However, validation approaches face fundamental challenges including the difficulty of creating comprehensive blacklists, the potential for encoding bypasses, and the legitimate need for special characters in some contexts. A username might legitimately contain an apostrophe, making it impossible to blanket-reject such characters.

Object-Relational Mappers provide higher-level abstractions that automatically implement parameterization when used correctly. ORMs translate method calls into parameterized SQL queries, handling the low-level database interaction details. However, ORMs also provide escape hatches for raw SQL queries, and developers must understand when they transition from safe ORM methods to potentially vulnerable raw query construction.

The principle of least privilege applies at the database level by restricting application database accounts to only the permissions required for legitimate operations. An application that only reads and writes specific tables should not have permissions to drop tables or execute administrative commands. This defense-in-depth approach limits the damage from a successful SQL injection attack by constraining what commands the attacker can execute through the compromised application.

Defense in depth acknowledges that no single protection mechanism provides complete security. Applications should combine parameterized queries with input validation, database permission restrictions, web application firewalls, and security monitoring. Each layer provides additional protection if another layer fails or contains a flaw.

Ruby Implementation

Ruby's database ecosystem provides multiple approaches to SQL injection prevention across different abstraction levels. The sqlite3 gem demonstrates low-level parameterized query interfaces:

require 'sqlite3'

db = SQLite3::Database.new('application.db')

# Positional parameters with ?
username = params[:username]
rows = db.execute("SELECT * FROM users WHERE username = ?", username)

# Named parameters with :name syntax
email = params[:email]
rows = db.execute(
  "SELECT * FROM users WHERE username = :user AND email = :email",
  user: username,
  email: email
)

# Prepared statement for repeated execution
stmt = db.prepare("INSERT INTO logs (user_id, action) VALUES (?, ?)")
user_actions.each do |action|
  stmt.execute(current_user_id, action)
end
stmt.close

The pg gem for PostgreSQL uses numbered parameters with similar semantics:

require 'pg'

conn = PG.connect(dbname: 'production')

# Numbered parameters $1, $2, etc.
result = conn.exec_params(
  "SELECT * FROM orders WHERE user_id = $1 AND status = $2",
  [user_id, 'pending']
)

# Prepared statement with name
conn.prepare('find_user', "SELECT * FROM users WHERE id = $1")
result = conn.exec_prepared('find_user', [user_id])

ActiveRecord provides automatic parameterization through its query interface. Methods like where, find_by, and find generate parameterized queries:

# Safe ActiveRecord queries
User.where(username: params[:username])
User.where("age > ?", minimum_age)
User.where("created_at BETWEEN ? AND ?", start_date, end_date)
User.find_by(email: params[:email])

# Multiple conditions with hash syntax
Order.where(user_id: current_user.id, status: 'shipped')

# Combining conditions safely
User.where("age > ? AND city = ?", min_age, city)
  .where(active: true)

ActiveRecord's where method accepts hash arguments that automatically parameterize values. String conditions with ? placeholders accept additional arguments as parameter values. The framework converts these method calls into parameterized SQL queries without developer intervention.

The Sequel gem provides similar protections with a more flexible syntax:

require 'sequel'

DB = Sequel.connect('sqlite://application.db')

# Symbol-based conditions
users = DB[:users].where(username: username)

# Placeholder conditions
users = DB[:users].where("age > ?", min_age)

# String interpolation with literal method (use cautiously)
users = DB[:users].where("status = ?", status)

# Filtering with multiple conditions
orders = DB[:orders]
  .where(user_id: user_id)
  .where("total > ?", minimum_total)

Ruby's sanitization helpers provide additional protection layers when constructing dynamic queries. ActiveRecord includes sanitize_sql_array and quote methods:

# Sanitize array-style conditions
condition = ActiveRecord::Base.sanitize_sql_array(
  ["username = ? AND status = ?", username, status]
)
User.where(condition)

# Quote individual values for interpolation contexts
quoted_city = ActiveRecord::Base.connection.quote(city)
# Still prefer parameterization over quoted interpolation

Database connection adapters provide identifier quoting for dynamic table or column names, though dynamic identifiers create additional security considerations:

# Quote table or column identifiers
table_name = params[:table]
quoted_table = ActiveRecord::Base.connection.quote_table_name(table_name)

# Whitelist approach for dynamic identifiers is safer
allowed_tables = %w[users orders products]
if allowed_tables.include?(table_name)
  results = ActiveRecord::Base.connection.execute(
    "SELECT * FROM #{quoted_table} WHERE id = ?", id
  )
end

Raw SQL queries through ActiveRecord require explicit parameterization:

# Vulnerable raw SQL
User.find_by_sql("SELECT * FROM users WHERE city = '#{city}'")

# Safe raw SQL with parameters
User.find_by_sql(["SELECT * FROM users WHERE city = ?", city])

# Execute with parameters
sql = "UPDATE users SET last_login = ? WHERE id = ?"
ActiveRecord::Base.connection.execute(
  ActiveRecord::Base.sanitize_sql_array([sql, Time.now, user_id])
)

Security Implications

SQL injection vulnerabilities enable attackers to bypass authentication, access unauthorized data, modify database records, delete information, and potentially execute operating system commands. The severity depends on database permissions, configuration, and the specific database system in use.

Authentication bypass represents the most common exploitation scenario. Attackers inject logic that makes authentication queries always return true or return administrative accounts:

# Vulnerable authentication
username = params[:username]
password = params[:password]
query = "SELECT * FROM users WHERE username = '#{username}' 
         AND password = '#{password}'"
user = db.execute(query).first

# Attack input: username = admin' --
# Resulting query:
# SELECT * FROM users WHERE username = 'admin' --' AND password = ''
# The -- comments out the password check

Data exfiltration attacks extract sensitive information from databases. Attackers use UNION queries to append results from other tables:

# Vulnerable search query
search = params[:query]
query = "SELECT title, content FROM articles WHERE title LIKE '%#{search}%'"

# Attack input: query = %' UNION SELECT username, password FROM users --
# Resulting query returns article data mixed with user credentials

Database modification attacks insert, update, or delete records. Stacked queries in databases that support multiple statements enable arbitrary command execution:

# Vulnerable update query
comment = params[:comment]
query = "UPDATE posts SET comment = '#{comment}' WHERE id = #{post_id}"

# Attack input: comment = '; DROP TABLE users; --
# If stacked queries are enabled, executes DROP TABLE command

Some database systems support extended stored procedures or features that enable operating system command execution. SQL Server's xp_cmdshell and similar features in other databases can execute shell commands when enabled and accessible to the application database account:

-- Potential OS command execution through SQL injection
'; EXEC xp_cmdshell 'net user attacker password /ADD'; --

Second-order SQL injection occurs when the application stores attacker input safely but later retrieves and uses it in an unsafe query context. The initial storage uses parameterization, but subsequent retrieval and use in string concatenation creates the vulnerability:

# Initial safe storage
User.create(username: params[:username])

# Later vulnerable usage
user = User.find(id)
query = "SELECT * FROM logs WHERE username = '#{user.username}'"
# If username contains injection payload, vulnerability triggers here

Blind SQL injection techniques extract information when the application does not display query results directly. Boolean-based blind injection infers information by observing application behavior differences based on true/false conditions:

# Application shows different behavior based on query success
search = params[:id]
user = User.find_by_sql("SELECT * FROM users WHERE id = #{search}").first

# Attack tests conditions character by character:
# id = 1 AND SUBSTRING(password, 1, 1) = 'a'
# Observe response time or error messages to infer password characters

Time-based blind injection uses database functions that introduce delays to extract information bit by bit:

-- Attack payload introducing time delay on true condition
1 AND IF(SUBSTRING(password,1,1)='a', SLEEP(5), 0)
-- Response delay indicates the condition evaluated true

Error-based SQL injection extracts information through detailed database error messages. When applications display full error messages including query details, attackers craft inputs that generate errors revealing database structure:

# Application displays database errors
begin
  User.find_by_sql("SELECT * FROM users WHERE id = #{params[:id]}")
rescue => e
  render plain: "Error: #{e.message}"
end

# Attack input generates error revealing table structure
# id = 1 AND (SELECT * FROM users) 
# Error message may reveal column names and data types

NoSQL injection affects document databases and key-value stores when query construction concatenates user input. MongoDB queries constructed from user input can suffer similar injection vulnerabilities:

# Vulnerable MongoDB query construction
username = params[:username]
password = params[:password]
user = User.where("this.username == '#{username}' && 
                   this.password == '#{password}'").first

# Attack input: username = ' || '1'=='1
# Bypasses authentication through JavaScript injection

Practical Examples

Web application login forms represent a common SQL injection target. A vulnerable implementation constructs authentication queries through string interpolation:

class SessionsController < ApplicationController
  def create
    # VULNERABLE IMPLEMENTATION
    username = params[:username]
    password = params[:password]
    
    query = "SELECT * FROM users WHERE username = '#{username}' 
             AND password_hash = '#{Digest::SHA256.hexdigest(password)}'"
    
    user = ActiveRecord::Base.connection.execute(query).first
    
    if user
      session[:user_id] = user['id']
      redirect_to dashboard_path
    else
      flash[:error] = "Invalid credentials"
      render :new
    end
  end
end

The secure implementation uses ActiveRecord's parameterized query methods:

class SessionsController < ApplicationController
  def create
    # SECURE IMPLEMENTATION
    username = params[:username]
    password = params[:password]
    
    user = User.find_by(username: username)
    
    if user&.authenticate(password)
      session[:user_id] = user.id
      redirect_to dashboard_path
    else
      flash[:error] = "Invalid credentials"
      render :new
    end
  end
end

Search functionality across multiple fields requires combining conditions safely:

class ProductsController < ApplicationController
  def search
    # VULNERABLE SEARCH
    query = params[:q]
    category = params[:category]
    
    sql = "SELECT * FROM products WHERE name LIKE '%#{query}%'"
    sql += " AND category = '#{category}'" if category.present?
    
    @products = Product.find_by_sql(sql)
  end
end

The secure approach builds queries through ActiveRecord's query interface:

class ProductsController < ApplicationController
  def search
    # SECURE SEARCH
    @products = Product.all
    
    if params[:q].present?
      @products = @products.where("name LIKE ?", "%#{params[:q]}%")
    end
    
    if params[:category].present?
      @products = @products.where(category: params[:category])
    end
    
    if params[:min_price].present?
      @products = @products.where("price >= ?", params[:min_price])
    end
    
    @products = @products.order(created_at: :desc)
  end
end

Dynamic sorting and filtering based on user parameters requires careful handling. Column names cannot be parameterized as values, requiring whitelist validation:

class UsersController < ApplicationController
  ALLOWED_SORT_COLUMNS = %w[name email created_at last_login].freeze
  ALLOWED_SORT_DIRECTIONS = %w[asc desc].freeze
  
  def index
    @users = User.all
    
    # Validate sort parameters against whitelist
    sort_column = params[:sort].presence_in(ALLOWED_SORT_COLUMNS) || 'created_at'
    sort_direction = params[:direction].presence_in(ALLOWED_SORT_DIRECTIONS) || 'desc'
    
    # Safe to interpolate after whitelist validation
    @users = @users.order("#{sort_column} #{sort_direction}")
    
    # Filter conditions still use parameterization
    if params[:status].present?
      @users = @users.where(status: params[:status])
    end
  end
end

Report generation with date ranges and aggregations demonstrates complex query construction:

class ReportsController < ApplicationController
  def sales_summary
    start_date = Date.parse(params[:start_date])
    end_date = Date.parse(params[:end_date])
    region = params[:region]
    
    # Secure aggregation query
    @summary = Order
      .select('DATE(created_at) as date, SUM(total) as daily_total, COUNT(*) as order_count')
      .where('created_at BETWEEN ? AND ?', start_date, end_date)
      .where(region: region)
      .group('DATE(created_at)')
      .order('date DESC')
  end
end

Batch operations processing multiple records require parameterization for each record:

class BulkUpdateService
  def update_prices(price_updates)
    # price_updates is array of {id: 1, price: 29.99}
    
    ActiveRecord::Base.transaction do
      price_updates.each do |update|
        # Each update uses parameterized query
        Product.where(id: update[:id])
          .update_all(price: update[:price], updated_at: Time.current)
      end
    end
  end
end

Common Pitfalls

String interpolation in SQL queries represents the most common vulnerability. Developers often use Ruby's string interpolation without recognizing the security implications:

# VULNERABLE: String interpolation
def find_user(email)
  User.find_by_sql("SELECT * FROM users WHERE email = '#{email}'")
end

# SECURE: Parameterized query
def find_user(email)
  User.find_by_sql(["SELECT * FROM users WHERE email = ?", email])
end

The vulnerability persists even when the interpolated value comes from trusted sources if those sources originally came from user input:

# VULNERABLE: Trusting stored values without context
def recent_activity(user)
  # user.username came from user input originally
  query = "SELECT * FROM logs WHERE username = '#{user.username}'"
  ActiveRecord::Base.connection.execute(query)
end

Dynamic column or table names cannot use parameter placeholders, leading developers to interpolate identifiers incorrectly:

# VULNERABLE: Direct identifier interpolation
def sort_users(column)
  User.order("#{column} DESC")
end

# SECURE: Whitelist validation
ALLOWED_SORT_COLUMNS = %w[name email created_at].freeze

def sort_users(column)
  column = ALLOWED_SORT_COLUMNS.include?(column) ? column : 'created_at'
  User.order("#{column} DESC")
end

Partial parameterization where some parts of a query use parameters while other parts use interpolation creates vulnerabilities:

# VULNERABLE: Mixing parameterization and interpolation
def search_products(category, name)
  Product.where("category = '#{category}' AND name LIKE ?", "%#{name}%")
end

# SECURE: Full parameterization
def search_products(category, name)
  Product.where("category = ? AND name LIKE ?", category, "%#{name}%")
end

Like clause pattern construction requires attention to both SQL injection and LIKE metacharacter escaping:

# VULNERABLE: Unescaped LIKE pattern
def search(query)
  Product.where("name LIKE ?", "%#{query}%")
end
# Input containing % or _ has unintended behavior

# SECURE: Escaped LIKE pattern
def search(query)
  sanitized = query.gsub(/[%_]/) { |m| "\\#{m}" }
  Product.where("name LIKE ?", "%#{sanitized}%")
end

ORM finder methods that accept SQL fragments can introduce vulnerabilities:

# VULNERABLE: find_by with interpolated SQL
def find_admin(username)
  User.find_by("username = '#{username}' AND role = 'admin'")
end

# SECURE: Hash conditions or parameterized SQL
def find_admin(username)
  User.find_by(username: username, role: 'admin')
end

Raw SQL in migrations, seeds, or rake tasks receives less scrutiny than controller code but requires the same protections:

# VULNERABLE: Migration with interpolated values
class AddDefaultUsers < ActiveRecord::Migration[7.0]
  def up
    username = ENV['ADMIN_USERNAME']
    execute("INSERT INTO users (username, role) VALUES ('#{username}', 'admin')")
  end
end

# SECURE: Parameterized execution
class AddDefaultUsers < ActiveRecord::Migration[7.0]
  def up
    username = ENV['ADMIN_USERNAME']
    execute(sanitize_sql_array(["INSERT INTO users (username, role) VALUES (?, 'admin')", username]))
  end
end

Query fragments in scopes or class methods can hide vulnerabilities:

# VULNERABLE: Scope with interpolated value
class User < ApplicationRecord
  def self.in_city(city)
    where("city = '#{city}'")
  end
end

# SECURE: Parameterized scope
class User < ApplicationRecord
  def self.in_city(city)
    where(city: city)
  end
end

JSON or XML data parsed from user input and used in queries requires validation:

# VULNERABLE: Using parsed JSON directly
def bulk_update
  updates = JSON.parse(params[:data])
  updates.each do |update|
    sql = "UPDATE products SET price = #{update['price']} WHERE id = #{update['id']}"
    ActiveRecord::Base.connection.execute(sql)
  end
end

# SECURE: Parameterized updates with validation
def bulk_update
  updates = JSON.parse(params[:data])
  updates.each do |update|
    Product.where(id: update['id']).update_all(price: update['price'])
  end
end

Testing Approaches

SQL injection testing verifies that application code properly parameterizes queries and rejects malicious input. Unit tests exercise data access methods with injection payloads:

require 'test_helper'

class UserTest < ActiveSupport::TestCase
  test "find_by_username protects against SQL injection" do
    # Create test user
    user = User.create(username: 'testuser', email: 'test@example.com')
    
    # Attempt SQL injection through username
    injection_payload = "testuser' OR '1'='1"
    result = User.find_by(username: injection_payload)
    
    # Should return nil, not the user (payload should be treated as literal string)
    assert_nil result
    
    # Valid username should still work
    valid_result = User.find_by(username: 'testuser')
    assert_equal user, valid_result
  end
  
  test "search method parameterizes query correctly" do
    user = User.create(username: 'admin', email: 'admin@example.com')
    
    # Injection payload attempting to expose all users
    payload = "%' OR '1'='1"
    results = User.search_by_username(payload)
    
    # Should return empty result, not all users
    assert_empty results
  end
end

Integration tests validate end-to-end protection through controller and request specs:

require 'rails_helper'

RSpec.describe 'Authentication', type: :request do
  describe 'POST /login' do
    let!(:user) { User.create(username: 'validuser', password: 'secret123') }
    
    it 'protects against SQL injection in login' do
      # SQL injection payload attempting authentication bypass
      post '/login', params: {
        username: "admin' OR '1'='1' --",
        password: 'anything'
      }
      
      expect(response).to have_http_status(:unauthorized)
      expect(session[:user_id]).to be_nil
    end
    
    it 'allows legitimate authentication' do
      post '/login', params: {
        username: 'validuser',
        password: 'secret123'
      }
      
      expect(response).to have_http_status(:redirect)
      expect(session[:user_id]).to eq(user.id)
    end
  end
end

Security-focused tests verify that injection payloads fail to extract unauthorized data:

require 'rails_helper'

RSpec.describe ProductsController, type: :controller do
  describe 'GET #search' do
    let!(:product) { Product.create(name: 'Widget', price: 29.99) }
    let!(:user) { User.create(username: 'admin', password_hash: 'secret') }
    
    it 'prevents UNION-based data extraction' do
      # Attempt UNION injection to extract user table data
      get :search, params: {
        q: "' UNION SELECT username, password_hash FROM users --"
      }
      
      # Verify user data not in response
      expect(response.body).not_to include('admin')
      expect(response.body).not_to include('password_hash')
      expect(assigns(:products)).not_to include(user)
    end
    
    it 'handles special characters safely' do
      get :search, params: { q: "'; DROP TABLE products; --" }
      
      # Verify table still exists and query executed safely
      expect { Product.count }.not_to raise_error
      expect(Product.count).to eq(1)
    end
  end
end

Property-based testing generates diverse injection payloads to test defensive measures:

require 'rails_helper'
require 'rantly/rspec_extensions'

RSpec.describe User, type: :model do
  describe '.find_by_email' do
    it 'safely handles arbitrary input strings' do
      property_of {
        # Generate random strings including SQL metacharacters
        Rantly { sized(20) { string(:alnum) + ['\'', '"', ';', '--', '/*'].sample } }
      }.check(100) { |random_input|
        expect { User.find_by(email: random_input) }.not_to raise_error
      }
    end
  end
end

Query logging analysis during tests detects parameter usage:

require 'rails_helper'

RSpec.describe 'Query Parameterization', type: :request do
  it 'uses parameterized queries for user search' do
    queries = []
    
    subscriber = ActiveSupport::Notifications.subscribe('sql.active_record') do |_, _, _, _, payload|
      queries << payload[:sql] if payload[:name] != 'SCHEMA'
    end
    
    get '/users/search', params: { name: "O'Brien" }
    
    ActiveSupport::Notifications.unsubscribe(subscriber)
    
    # Verify queries contain placeholders, not interpolated values
    search_query = queries.find { |q| q.include?('WHERE') }
    expect(search_query).to match(/\?|\$\d+/)  # Contains parameter placeholder
    expect(search_query).not_to include("O'Brien")  # Doesn't contain literal value
  end
end

Database audit logs can be checked in test environments to verify no unauthorized queries execute:

require 'rails_helper'

RSpec.describe 'Security Audit', type: :request do
  before { DatabaseAuditor.start_recording }
  after { DatabaseAuditor.stop_recording }
  
  it 'prevents DROP TABLE commands through injection' do
    post '/products', params: {
      name: "Product'; DROP TABLE products; --"
    }
    
    audit_log = DatabaseAuditor.recorded_queries
    drop_commands = audit_log.select { |q| q.upcase.include?('DROP TABLE') }
    
    expect(drop_commands).to be_empty
  end
end

Reference

Parameter Placeholder Syntax

ActiveRecord Safe Query Methods

Input Validation Patterns

Common Injection Payloads for Testing

Security Configuration Checklist

Framework-Specific Methods

Sanitization Methods