Database Security

Overview

Database security protects stored data from malicious attacks, unauthorized access, and accidental exposure. Modern applications handle sensitive information including user credentials, financial records, personal data, and business intelligence. A single security breach can expose millions of records, resulting in financial losses, legal liability, and reputational damage.

Database security operates at multiple layers. Network security controls which systems can reach the database server. Authentication verifies user identity before granting access. Authorization determines which operations each authenticated user can perform. Input validation prevents malicious data from entering the system. Encryption protects data both in transit and at rest. Audit logging records all database activities for security analysis and compliance.

The threat landscape includes SQL injection attacks, which manipulate queries to access unauthorized data or execute malicious commands. Credential theft exposes database accounts to attackers. Privilege escalation allows limited users to gain administrative access. Data exfiltration extracts sensitive information without authorization. Insider threats involve authorized users misusing their access. Denial of service attacks overwhelm database resources to disrupt availability.

Ruby applications connect to databases through libraries like pg (PostgreSQL), mysql2 (MySQL), and sqlite3 (SQLite), or through Object-Relational Mapping frameworks like ActiveRecord and Sequel. These tools provide security features including parameterized queries, connection encryption, and credential management. Applications must configure and use these features correctly to maintain security.

Database security intersects with application security, network security, and compliance requirements. Applications act as the primary interface to database systems. Secure coding practices in application code prevent most database security vulnerabilities. Network configurations control traffic between application servers and database servers. Compliance frameworks like PCI DSS, HIPAA, and GDPR mandate specific security controls for protected data types.

Key Principles

Authentication and Authorization form the foundation of database access control. Authentication verifies the identity of users or applications attempting to connect. Authorization determines which database objects and operations each authenticated identity can access. Strong authentication requires complex passwords, key-based authentication, or certificate-based authentication rather than default or weak credentials.

Database management systems implement role-based access control (RBAC) to manage permissions. Roles group related permissions and assign them to users or applications. A read-only role grants SELECT privileges without INSERT, UPDATE, or DELETE capabilities. An application role provides only the minimum permissions needed for the application to function. A DBA role grants full administrative access for maintenance tasks.

Principle of Least Privilege mandates granting each user or application the minimum permissions required to perform legitimate tasks. Application database accounts should not have administrative privileges, DROP TABLE permissions, or access to tables they don't use. Limiting privileges reduces the potential damage from compromised accounts, SQL injection attacks, or application bugs.

Defense in Depth applies multiple layers of security controls rather than relying on a single mechanism. Even if attackers bypass one control, additional layers prevent full compromise. Defense in depth combines network firewalls, application authentication, database authentication, query parameterization, input validation, encryption, and monitoring.

Input Validation and Parameterization prevent injection attacks by treating user input as data rather than executable code. SQL injection occurs when applications concatenate user input directly into SQL statements. Attackers craft input containing SQL syntax that alters query behavior. Parameterized queries separate SQL logic from data values. The database treats parameter values as literal data regardless of content.

Encryption protects data confidentiality both in transit and at rest. Transport Layer Security (TLS) encrypts network connections between applications and databases, preventing eavesdropping and man-in-the-middle attacks. At-rest encryption protects data files, backups, and storage media from unauthorized access if physical security fails. Column-level encryption protects specific sensitive fields like credit card numbers or social security numbers.

Connection Security ensures database connections use encrypted channels and authenticate both endpoints. Applications should connect using TLS/SSL to encrypt all data transmitted over the network. Certificate validation confirms the database server identity and prevents impersonation. Connection strings must not contain plaintext credentials in source code or configuration files.

Audit Logging and Monitoring record database activities for security analysis, compliance, and incident response. Audit logs capture authentication attempts, privilege changes, data modifications, and query executions. Monitoring detects suspicious patterns like failed login attempts, unusual query volumes, or access to sensitive tables outside normal hours. Security Information and Event Management (SIEM) systems aggregate and analyze logs from multiple sources.

Credential Management handles database passwords and connection details securely. Applications should never hard-code credentials in source code. Environment variables, encrypted configuration files, or dedicated secret management systems provide better alternatives. Credentials should rotate regularly. Development, staging, and production environments must use different credentials.

Backup Security protects backup files from unauthorized access while ensuring data recovery capability. Encrypted backups prevent data exposure if backup media is lost or stolen. Access controls limit who can restore backups. Backup testing verifies both data integrity and restoration procedures. Off-site backup storage protects against site-wide disasters.

Ruby Implementation

Ruby database libraries implement security features that applications must activate and configure correctly. The pg gem for PostgreSQL, mysql2 for MySQL, and sqlite3 for SQLite provide low-level database access with built-in parameterization support. ActiveRecord and Sequel build higher-level abstractions with additional security features.

Parameterized Queries with ActiveRecord use placeholder syntax to separate SQL logic from data values. ActiveRecord automatically parameterizes queries when using its query interface:

# Secure: ActiveRecord parameterizes the value
User.where("email = ?", user_input)
# Generates: SELECT * FROM users WHERE email = $1
# Parameters: ["user@example.com"]

# Secure: Hash conditions are parameterized
User.where(email: user_input)

# Secure: Named placeholders
User.where("email = :email AND status = :status", 
           email: user_input, status: "active")

Dangerous String Interpolation creates SQL injection vulnerabilities. Never concatenate user input directly into SQL strings:

# VULNERABLE: String interpolation allows SQL injection
User.where("email = '#{user_input}'")
# If user_input = "' OR '1'='1"
# Generates: SELECT * FROM users WHERE email = '' OR '1'='1'
# Returns all users

# VULNERABLE: String concatenation
query = "SELECT * FROM users WHERE name = '" + params[:name] + "'"
ActiveRecord::Base.connection.execute(query)

Raw SQL with Parameterization handles cases requiring custom SQL. Use bind parameters even when writing raw queries:

# Secure: Bind parameters in raw SQL
sql = "SELECT * FROM users WHERE created_at > ? AND role = ?"
User.find_by_sql([sql, 30.days.ago, "admin"])

# Secure: Named parameters in raw SQL
sql = "SELECT * FROM users WHERE email LIKE :pattern"
User.find_by_sql([sql, {pattern: "%@example.com"}])

Sequel Security provides similar parameterization with slightly different syntax:

# Secure: Sequel placeholder syntax
DB[:users].where("email = ?", user_input)

# Secure: Hash conditions
DB[:users].where(email: user_input)

# Secure: Named placeholders
DB[:users].where("status = :status", status: user_input)

# Secure: Filter method with hash
DB[:users].filter(email: user_input, active: true)

Connection Encryption Configuration specifies TLS/SSL settings in database configuration files. For PostgreSQL with the pg gem:

# config/database.yml
production:
  adapter: postgresql
  host: db.example.com
  database: myapp_production
  sslmode: require
  sslrootcert: /path/to/ca-certificate.crt
  sslcert: /path/to/client-certificate.crt
  sslkey: /path/to/client-key.key

The sslmode parameter controls encryption requirements. The value require enforces encryption but doesn't verify server identity. The value verify-ca verifies the server certificate against a certificate authority. The value verify-full additionally verifies the server hostname matches the certificate.

Environment-Based Credential Management keeps sensitive data out of source control:

# config/database.yml
production:
  adapter: postgresql
  host: <%= ENV['DATABASE_HOST'] %>
  database: <%= ENV['DATABASE_NAME'] %>
  username: <%= ENV['DATABASE_USERNAME'] %>
  password: <%= ENV['DATABASE_PASSWORD'] %>
  sslmode: require

# .env file (not committed to version control)
DATABASE_HOST=db.production.com
DATABASE_NAME=myapp_production
DATABASE_USERNAME=myapp_user
DATABASE_PASSWORD=complex_secure_password_here

Encrypted Credentials in Rails provides built-in credential management:

# Edit encrypted credentials
$ EDITOR=vim rails credentials:edit

# config/credentials.yml.enc (encrypted)
database:
  password: secret_password_here
  
secret_key_base: long_random_string

# Access in application code
Rails.application.credentials.database[:password]

ActiveRecord Encryption protects sensitive attributes at the application level:

class User < ApplicationRecord
  encrypts :ssn, deterministic: false
  encrypts :email, deterministic: true
  encrypts :credit_card, deterministic: false
end

# Encrypted values stored in database
user = User.create(ssn: "123-45-6789")
# Database contains encrypted blob, not plaintext

# Decryption automatic on read
user.ssn # => "123-45-6789"

# Deterministic encryption allows querying
User.where(email: "user@example.com") # Works
User.where(ssn: "123-45-6789") # Doesn't work (non-deterministic)

Mass Assignment Protection prevents attackers from modifying unauthorized attributes:

class User < ApplicationRecord
  # Strong parameters in controller
  def user_params
    params.require(:user).permit(:email, :name)
    # Excludes :admin, :role, and other sensitive fields
  end
end

# Controller action
def create
  @user = User.new(user_params)
  # Attacker cannot set admin=true via HTTP parameters
end

SQL Sanitization Methods provide additional protection when needed:

# Sanitize single value
sanitized = ActiveRecord::Base.sanitize_sql(["name = ?", user_input])

# Sanitize array of values
values = ["value1", "value2", "value3"]
sanitized = ActiveRecord::Base.sanitize_sql_array(
  ["name IN (?)", values]
)

# Quote identifier (table/column name)
table = ActiveRecord::Base.connection.quote_table_name(user_table)
column = ActiveRecord::Base.connection.quote_column_name(user_column)

Practical Examples

SQL Injection Vulnerability and Fix demonstrates the most common database security flaw:

# VULNERABLE: User search feature with SQL injection
class UsersController < ApplicationController
  def search
    @users = User.where("name LIKE '%#{params[:query]}%'")
  end
end

# Attack scenario:
# User enters: '; DROP TABLE users; --
# Resulting SQL: SELECT * FROM users WHERE name LIKE '%'; DROP TABLE users; --%'
# Executes: DROP TABLE users

# SECURE: Parameterized query prevents injection
class UsersController < ApplicationController
  def search
    @users = User.where("name LIKE ?", "%#{params[:query]}%")
  end
end

# Same attack input treated as literal string
# SQL: SELECT * FROM users WHERE name LIKE $1
# Parameters: ["%'; DROP TABLE users; --%"]
# Returns: No matching users

Authentication with Multiple Security Layers implements defense in depth:

class DatabaseConnection
  def self.establish
    config = {
      adapter: 'postgresql',
      host: ENV['DB_HOST'],
      port: ENV['DB_PORT'] || 5432,
      database: ENV['DB_NAME'],
      username: ENV['DB_USERNAME'],
      password: ENV.fetch('DB_PASSWORD'),
      
      # Require TLS encryption
      sslmode: 'verify-full',
      sslrootcert: '/etc/ssl/certs/ca-certificate.crt',
      
      # Connection pool limits
      pool: 5,
      timeout: 5000,
      
      # Enable prepared statements
      prepared_statements: true,
      
      # Statement timeout prevents long-running queries
      variables: {
        statement_timeout: 30000 # 30 seconds
      }
    }
    
    ActiveRecord::Base.establish_connection(config)
  rescue KeyError => e
    raise "Database configuration incomplete: #{e.message}"
  end
end

# Verify connection security
connection = ActiveRecord::Base.connection
ssl_status = connection.execute("SHOW ssl").first
raise "SSL not enabled" unless ssl_status['ssl'] == 'on'

Secure Password Storage and Authentication never stores passwords in plaintext:

class User < ApplicationRecord
  # bcrypt automatically salts and hashes passwords
  has_secure_password
  
  # Password complexity requirements
  validates :password, 
    length: { minimum: 12 },
    format: { 
      with: /(?=.*[a-z])(?=.*[A-Z])(?=.*\d)(?=.*[@$!%*?&])/,
      message: "must include uppercase, lowercase, number, and symbol"
    },
    if: :password_digest_changed?
    
  # Prevent timing attacks on email lookup
  def self.find_by_email_constant_time(email)
    users = User.where(email: email.downcase).limit(2).to_a
    users.first if users.size == 1
  end
end

# Authentication controller
class SessionsController < ApplicationController
  def create
    # Constant-time lookup prevents user enumeration
    user = User.find_by_email_constant_time(params[:email])
    
    # Constant-time comparison prevents timing attacks
    if user&.authenticate(params[:password])
      # Rate limiting prevents brute force
      if RateLimiter.allowed?(user.id)
        session[:user_id] = user.id
        redirect_to dashboard_path
      else
        render json: { error: "Too many attempts" }, status: 429
      end
    else
      # Same response time whether user exists or not
      sleep 0.1
      render json: { error: "Invalid credentials" }, status: 401
    end
  end
end

Role-Based Access Control restricts data access by user role:

class User < ApplicationRecord
  enum role: { viewer: 0, editor: 1, admin: 2 }
  
  def can_modify?(record)
    case role
    when 'viewer'
      false
    when 'editor'
      record.user_id == id
    when 'admin'
      true
    end
  end
end

class RecordsController < ApplicationController
  before_action :require_authentication
  
  def update
    @record = Record.find(params[:id])
    
    unless current_user.can_modify?(@record)
      render json: { error: "Unauthorized" }, status: 403
      return
    end
    
    if @record.update(record_params)
      render json: @record
    else
      render json: @record.errors, status: 422
    end
  end
  
  private
  
  def record_params
    # Filter allowed attributes based on role
    allowed = [:title, :content]
    allowed << :published if current_user.admin?
    params.require(:record).permit(allowed)
  end
end

Sensitive Data Encryption protects confidential information:

class PaymentMethod < ApplicationRecord
  # Non-deterministic encryption prevents querying
  encrypts :card_number
  encrypts :cvv
  
  # Deterministic encryption allows lookups
  encrypts :card_fingerprint, deterministic: true
  
  # Store only last 4 digits in plaintext for display
  before_validation :extract_last_four
  
  def self.find_by_fingerprint(card_number)
    fingerprint = Digest::SHA256.hexdigest(card_number)
    find_by(card_fingerprint: fingerprint)
  end
  
  private
  
  def extract_last_four
    return unless card_number
    self.last_four = card_number.to_s[-4..]
  end
end

# Usage
payment = PaymentMethod.create(
  card_number: "4532123456789012"
)

# Database contains encrypted card_number
# Only last_four stored in plaintext: "9012"

# Retrieve by fingerprint
found = PaymentMethod.find_by_fingerprint("4532123456789012")
found.card_number # Decrypts automatically

Audit Logging for Compliance records all data access:

class AuditLog < ApplicationRecord
  belongs_to :user
  
  enum action: {
    read: 0, create: 1, update: 2, delete: 3,
    login: 4, logout: 5, failed_login: 6
  }
end

module Auditable
  extend ActiveSupport::Concern
  
  included do
    after_create :log_create
    after_update :log_update
    after_destroy :log_destroy
  end
  
  private
  
  def log_create
    create_audit_log(:create, changes: saved_changes)
  end
  
  def log_update
    create_audit_log(:update, changes: saved_changes)
  end
  
  def log_destroy
    create_audit_log(:delete, changes: attributes)
  end
  
  def create_audit_log(action, changes:)
    AuditLog.create(
      user: Current.user,
      auditable: self,
      action: action,
      changes: changes,
      ip_address: Current.ip_address,
      user_agent: Current.user_agent
    )
  end
end

# Include in sensitive models
class FinancialRecord < ApplicationRecord
  include Auditable
end

# Query audit trail
def audit_trail_for(user)
  AuditLog.where(user: user)
    .order(created_at: :desc)
    .includes(:auditable)
end

Common Pitfalls

String Interpolation in Queries remains the most frequent security mistake. Developers interpolate variables into SQL strings for convenience, creating injection vulnerabilities:

# VULNERABLE: Direct interpolation
def find_users_by_status(status)
  User.where("status = '#{status}'")
end

# Attack: status = "' OR '1'='1"
# Returns all users regardless of status

# SECURE: Always use placeholders
def find_users_by_status(status)
  User.where("status = ?", status)
end

Dynamic Column Names pose special challenges. Column and table names cannot use parameterization. Applications accepting user input for column names must whitelist valid values:

# VULNERABLE: User-controlled column name
def sort_users(sort_column)
  User.order(sort_column)
end

# Attack: sort_column = "email; DROP TABLE users; --"

# SECURE: Whitelist valid column names
ALLOWED_SORT_COLUMNS = ['email', 'created_at', 'name'].freeze

def sort_users(sort_column)
  column = ALLOWED_SORT_COLUMNS.include?(sort_column) ? 
           sort_column : 'created_at'
  User.order(column)
end

# Alternative: Use symbols which are safe
def sort_users(sort_column)
  User.order(sort_column.to_sym) if User.column_names.include?(sort_column)
end

Exposed Connection Strings occur when credentials appear in committed code, error messages, or logs:

# WRONG: Credentials in code
ActiveRecord::Base.establish_connection(
  adapter: 'postgresql',
  host: 'db.example.com',
  username: 'myapp',
  password: 'secretpassword123' # Committed to git!
)

# WRONG: Connection string in logs
Rails.logger.info("Connected to #{ActiveRecord::Base.connection_config}")
# Logs contain password

# CORRECT: Environment variables
ActiveRecord::Base.establish_connection(ENV['DATABASE_URL'])

# CORRECT: Filtered logging
Rails.application.config.filter_parameters += [
  :password, :database_url, :connection_string
]

Over-Privileged Database Accounts violate the principle of least privilege. Application accounts with excessive permissions amplify attack impact:

# WRONG: Application uses DBA account
# Database user has ALL PRIVILEGES
# SQL injection can DROP DATABASE

# CORRECT: Limited application privileges
# CREATE USER myapp_user WITH PASSWORD 'secure_password';
# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES 
#   IN SCHEMA public TO myapp_user;
# REVOKE CREATE, DROP, TRUNCATE ON DATABASE FROM myapp_user;

# Verify privileges in application startup
def verify_database_privileges
  conn = ActiveRecord::Base.connection
  
  # Check we cannot drop tables
  begin
    conn.execute("DROP TABLE nonexistent_table")
  rescue ActiveRecord::StatementInvalid => e
    unless e.message.include?("permission denied")
      raise "Database account has excessive DROP privileges"
    end
  end
end

Missing Connection Encryption transmits sensitive data in plaintext across networks:

# WRONG: No SSL configuration
# Database traffic unencrypted
production:
  adapter: postgresql
  host: db.example.com
  database: myapp_production

# CORRECT: Require SSL/TLS
production:
  adapter: postgresql
  host: db.example.com
  database: myapp_production
  sslmode: verify-full
  sslrootcert: /etc/ssl/certs/ca-bundle.crt
  
# Verify encryption at runtime
def verify_connection_security
  result = ActiveRecord::Base.connection.execute(
    "SELECT ssl_is_used()"
  ).first
  
  raise "Unencrypted database connection" unless result['ssl_is_used']
end

Timing Attacks on Authentication leak information through response timing variations:

# VULNERABLE: Different timing for valid/invalid users
def authenticate(email, password)
  user = User.find_by(email: email)
  return false unless user # Fast response if user doesn't exist
  
  user.authenticate(password) # Slow bcrypt comparison if user exists
end

# Attacker can enumerate valid email addresses by timing

# SECURE: Constant-time comparison
def authenticate(email, password)
  user = User.find_by(email: email)
  
  if user
    result = user.authenticate(password)
  else
    # Perform dummy bcrypt comparison to maintain constant time
    BCrypt::Password.create("dummy_password")
    result = false
  end
  
  result
end

Insufficient Validation Before Database Operations allows attackers to bypass security controls:

# VULNERABLE: No validation of referenced records
def transfer_funds(from_account_id, to_account_id, amount)
  from = Account.find(from_account_id)
  to = Account.find(to_account_id)
  
  from.balance -= amount
  to.balance += amount
  
  from.save!
  to.save!
end

# Attack: User references account they don't own

# SECURE: Verify ownership
def transfer_funds(from_account_id, to_account_id, amount)
  from = current_user.accounts.find(from_account_id)
  to = Account.find(to_account_id)
  
  raise "Insufficient funds" if from.balance < amount
  raise "Invalid amount" unless amount.positive?
  
  Account.transaction do
    from.update!(balance: from.balance - amount)
    to.update!(balance: to.balance + amount)
    
    TransferLog.create!(
      from_account: from,
      to_account: to,
      amount: amount,
      user: current_user
    )
  end
end

Testing Approaches

SQL Injection Testing verifies query parameterization prevents injection attacks:

require 'rails_helper'

RSpec.describe UsersController, type: :controller do
  describe "GET #search" do
    it "prevents SQL injection in search query" do
      # Attempt SQL injection
      malicious_input = "'; DROP TABLE users; --"
      
      get :search, params: { query: malicious_input }
      
      # Table still exists
      expect { User.count }.not_to raise_error
      
      # No users matched the literal string
      expect(assigns(:users)).to be_empty
    end
    
    it "treats special characters as literal values" do
      User.create!(name: "'; SELECT * FROM users; --")
      
      get :search, params: { query: "'; SELECT" }
      
      expect(assigns(:users).count).to eq(1)
    end
  end
end

Authorization Testing confirms access control enforcement:

RSpec.describe RecordsController, type: :controller do
  let(:admin) { create(:user, role: :admin) }
  let(:editor) { create(:user, role: :editor) }
  let(:viewer) { create(:user, role: :viewer) }
  let(:record) { create(:record, user: editor) }
  
  describe "PUT #update" do
    it "allows admin to update any record" do
      sign_in admin
      put :update, params: { 
        id: record.id, 
        record: { title: "Updated" } 
      }
      expect(response).to have_http_status(:success)
    end
    
    it "allows editor to update their own records" do
      sign_in editor
      put :update, params: { 
        id: record.id, 
        record: { title: "Updated" } 
      }
      expect(response).to have_http_status(:success)
    end
    
    it "prevents editor from updating others' records" do
      other_editor = create(:user, role: :editor)
      sign_in other_editor
      
      put :update, params: { 
        id: record.id, 
        record: { title: "Updated" } 
      }
      
      expect(response).to have_http_status(:forbidden)
      expect(record.reload.title).not_to eq("Updated")
    end
    
    it "prevents viewer from updating any record" do
      sign_in viewer
      put :update, params: { 
        id: record.id, 
        record: { title: "Updated" } 
      }
      expect(response).to have_http_status(:forbidden)
    end
  end
end

Encryption Testing validates data protection mechanisms:

RSpec.describe PaymentMethod, type: :model do
  describe "encryption" do
    it "encrypts card number in database" do
      payment = PaymentMethod.create!(
        card_number: "4532123456789012"
      )
      
      # Check encrypted value in database
      raw_value = ActiveRecord::Base.connection.execute(
        "SELECT card_number FROM payment_methods WHERE id = #{payment.id}"
      ).first['card_number']
      
      expect(raw_value).not_to eq("4532123456789012")
      expect(raw_value).not_to include("4532")
    end
    
    it "decrypts card number on read" do
      payment = PaymentMethod.create!(
        card_number: "4532123456789012"
      )
      
      reloaded = PaymentMethod.find(payment.id)
      expect(reloaded.card_number).to eq("4532123456789012")
    end
    
    it "allows deterministic encryption for lookups" do
      payment = PaymentMethod.create!(
        card_number: "4532123456789012"
      )
      
      found = PaymentMethod.find_by_fingerprint("4532123456789012")
      expect(found).to eq(payment)
    end
  end
end

Connection Security Testing verifies encryption configuration:

RSpec.describe "Database connection", type: :system do
  it "uses SSL/TLS encryption" do
    result = ActiveRecord::Base.connection.execute(
      "SHOW ssl"
    ).first
    
    expect(result['ssl']).to eq('on')
  end
  
  it "verifies server certificate" do
    config = ActiveRecord::Base.connection_config
    
    expect(config[:sslmode]).to eq('verify-full')
    expect(config[:sslrootcert]).to be_present
  end
  
  it "uses prepared statements" do
    config = ActiveRecord::Base.connection_config
    expect(config[:prepared_statements]).to be true
  end
end

Audit Log Testing ensures compliance with logging requirements:

RSpec.describe "Audit logging", type: :system do
  let(:user) { create(:user) }
  
  before do
    sign_in user
    Current.user = user
  end
  
  it "logs record creation" do
    expect {
      FinancialRecord.create!(
        amount: 1000,
        description: "Payment"
      )
    }.to change { AuditLog.count }.by(1)
    
    log = AuditLog.last
    expect(log.action).to eq("create")
    expect(log.user).to eq(user)
    expect(log.changes).to include("amount" => [nil, 1000])
  end
  
  it "logs record updates" do
    record = create(:financial_record, amount: 1000)
    
    expect {
      record.update!(amount: 2000)
    }.to change { AuditLog.count }.by(1)
    
    log = AuditLog.last
    expect(log.action).to eq("update")
    expect(log.changes).to include("amount" => [1000, 2000])
  end
  
  it "includes IP address and user agent" do
    record = create(:financial_record)
    
    Current.ip_address = "192.168.1.100"
    Current.user_agent = "Mozilla/5.0"
    
    record.update!(amount: 2000)
    
    log = AuditLog.last
    expect(log.ip_address).to eq("192.168.1.100")
    expect(log.user_agent).to eq("Mozilla/5.0")
  end
end

Security Scanner Integration automates vulnerability detection:

# spec/security/brakeman_spec.rb
require 'rails_helper'
require 'brakeman'

RSpec.describe "Security scanning" do
  it "passes Brakeman security checks" do
    tracker = Brakeman.run(
      app_path: Rails.root,
      print_report: true,
      pager: false
    )
    
    expect(tracker.filtered_warnings).to be_empty,
      "Brakeman found security issues:\n" +
      tracker.filtered_warnings.map(&:message).join("\n")
  end
end

# spec/security/bundler_audit_spec.rb
RSpec.describe "Dependency security" do
  it "has no known vulnerable dependencies" do
    output = `bundle audit check --update`
    
    expect($?.exitstatus).to eq(0),
      "Vulnerable dependencies detected:\n#{output}"
  end
end

Tools & Ecosystem

Brakeman performs static analysis to detect security vulnerabilities in Rails applications:

# Install
gem 'brakeman', group: :development

# Run scan
$ brakeman --ensure-latest

# Generate report
$ brakeman -o report.html

# Configuration file: config/brakeman.yml
ignore_file: config/brakeman.ignore
check_paths:
  - app/controllers
  - app/models
  - app/views
sql_injection_confidence_threshold: low

Bundler Audit checks Ruby gems against vulnerability databases:

# Install
gem 'bundler-audit', group: :development

# Check for vulnerable dependencies
$ bundle audit check --update

# Continuous monitoring
$ bundle audit check

# Integrate in CI pipeline
# .github/workflows/security.yml
- name: Security audit
  run: |
    bundle install
    bundle audit check --update

Database Security Gems provide additional protection layers:

# Strong migrations - prevent dangerous migrations
gem 'strong_migrations'

# Detects n+1 queries and unused eager loading
gem 'bullet', group: :development

# Database query performance monitoring
gem 'rack-mini-profiler', group: :development

# SQL query analysis
gem 'pg_query'

# Database credential encryption
gem 'lockbox'
gem 'blind_index' # For searchable encryption

PostgreSQL Security Extensions enhance database-level protection:

# Enable pgcrypto for encryption functions
CREATE EXTENSION pgcrypto;

# Enable pg_stat_statements for query monitoring
CREATE EXTENSION pg_stat_statements;

# Row-level security policies
CREATE POLICY user_isolation ON documents
  USING (user_id = current_setting('app.current_user_id')::int);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

MySQL Security Configuration hardens database server settings:

# /etc/mysql/my.cnf
[mysqld]
# Require SSL connections
require_secure_transport = ON

# Disable LOCAL INFILE to prevent file reading
local_infile = 0

# Set statement timeout
max_execution_time = 30000

# Enable audit logging
plugin-load-add = audit_log.so
audit_log_file = /var/log/mysql/audit.log

# Restrict network binding
bind-address = 127.0.0.1

Database Activity Monitoring tracks and alerts on suspicious behavior:

# Custom monitoring with Scout APM
gem 'scout_apm'

# config/scout_apm.yml
production:
  monitor: true
  key: <%= ENV['SCOUT_KEY'] %>
  log_level: warn
  
  # Monitor slow queries
  database_query_monitoring: true
  slow_query_threshold: 500

# Application Performance Monitoring
gem 'skylight'

# SQL query monitoring
gem 'active_record_query_trace', group: :development

Secret Management Tools secure credential storage and rotation:

# AWS Secrets Manager
gem 'aws-sdk-secretsmanager'

class SecretManager
  def self.database_credentials
    client = Aws::SecretsManager::Client.new(
      region: ENV['AWS_REGION']
    )
    
    secret = client.get_secret_value(
      secret_id: 'production/database'
    )
    
    JSON.parse(secret.secret_string)
  end
end

# HashiCorp Vault
gem 'vault'

Vault.configure do |config|
  config.address = ENV['VAULT_ADDR']
  config.token = ENV['VAULT_TOKEN']
  config.ssl_verify = true
end

credentials = Vault.logical.read('secret/data/database')

Intrusion Detection Systems identify attack patterns:

# Fail2ban configuration for database attacks
# /etc/fail2ban/jail.local
[postgresql]
enabled = true
port = 5432
filter = postgresql
logpath = /var/log/postgresql/postgresql-*.log
maxretry = 3
bantime = 3600

# Rack::Attack for application-level rate limiting
gem 'rack-attack'

# config/initializers/rack_attack.rb
Rack::Attack.throttle('req/ip', limit: 300, period: 5.minutes) do |req|
  req.ip
end

Rack::Attack.throttle('login/ip', limit: 5, period: 20.seconds) do |req|
  req.ip if req.path == '/login' && req.post?
end

Reference

SQL Injection Prevention

ActiveRecord Security Methods

PostgreSQL SSL Modes

Database Privilege Levels

Encryption Configuration

Security Headers for Database Responses

Audit Log Event Types

Security Testing Checklist

Common Attack Patterns

Database Connection Pool Settings

Emergency Response Procedures