Advanced Patterns and Best Practices

Implement query builders for complex dynamic requirements:

# Ruby example with Sequel gem
require 'sequel'

class SafeQueryBuilder
  def initialize(db)
    @db = db
    @allowed_filters = [:name, :email, :created_at, :status]
  end
  
  def build_user_query(filters, sort_options)
    dataset = @db[:users]
    
    # Apply filters safely
    filters.each do |key, value|
      next unless @allowed_filters.include?(key)
      
      case key
      when :name, :email
        dataset = dataset.where(Sequel.like(key, "%#{value}%"))
      when :created_at
        dataset = dataset.where(key > value)
      when :status
        dataset = dataset.where(key => value)
      end
    end
    
    # Apply sorting safely
    if sort_options[:column] && @allowed_filters.include?(sort_options[:column].to_sym)
      order = sort_options[:direction] == 'desc' ? :desc : :asc
      dataset = dataset.order(Sequel.send(order, sort_options[:column].to_sym))
    end
    
    dataset
  end
end

# Usage
builder = SafeQueryBuilder.new(DB)
users = builder.build_user_query(
  {name: "John'; DROP TABLE users; --", status: 'active'},
  {column: 'created_at', direction: 'desc'}
).all
# Completely safe despite malicious input

Batch operations with parameterized queries:

// Go implementation with batch inserts
func batchInsertUsers(db *sql.DB, users []User) error {
    // Start transaction for atomicity
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback()
    
    // Prepare statement once
    stmt, err := tx.Prepare(`
        INSERT INTO users (username, email, password_hash, created_at) 
        VALUES ($1, $2, $3, $4)
    `)
    if err != nil {
        return err
    }
    defer stmt.Close()
    
    // Execute for each user
    for _, user := range users {
        _, err := stmt.Exec(
            user.Username,
            user.Email,
            user.PasswordHash,
            time.Now(),
        )
        if err != nil {
            return err
        }
    }
    
    return tx.Commit()
}

Remember that parameterized queries must be used consistently throughout your application. A single vulnerable query can compromise your entire database, making comprehensive implementation essential.