Api Key Exposure in Grape with Mssql

Grape is a REST-like API micro-framework for Ruby projects, commonly used to build endpoints that return resources or perform actions. When a Grape API interacts with Microsoft SQL Server via the tiny_tds or activerecord-sqlserver-adapter, developers often embed connection strings or API keys directly in route handlers or helper methods. This pattern becomes a critical exposure when error handling is insufficient or when debug endpoints are left in production.

Consider a Grape endpoint that authenticates a request and then queries a Microsoft SQL Server instance using a connection string that contains an API key or database credential. If the endpoint does not properly sanitize inputs or handle exceptions, an attacker can trigger verbose error messages that reveal the connection string, including the embedded API key. For example, a malformed query or an intentional SQL injection attempt can cause TinyTds::Error or ActiveRecord::StatementInvalid to surface the full connection string in the response body.

Additionally, if the Grape API exposes administrative or diagnostic routes (for example, a route that runs SELECT @@VERSION or returns table metadata), these endpoints can unintentionally leak sensitive configuration details. When these routes are combined with weak authentication or missing authorization checks, an unauthenticated attacker can harvest API keys or connection details that grant access to backend systems.

Another vector specific to the Grape + Mssql combination involves logging. If the application logs full SQL statements or connection details at debug level and those logs are accessible via an endpoint or through log aggregation tools, an API key embedded in the connection string can be exposed through log disclosure. This often occurs when developers use dynamic SQL construction with string interpolation instead of parameterized queries, inadvertently including secrets in log entries that may be surfaced by debugging endpoints.

During a middleBrick scan, which tests the unauthenticated attack surface using parallel checks including Input Validation, Data Exposure, and Unsafe Consumption, such exposures are identified when responses reveal connection strings, stack traces with sensitive data, or endpoints that return configuration details. The LLM/AI Security checks further ensure that no system prompt leakage or output containing API keys occurs in any generated responses, which is especially relevant if the API interacts with language models using keys stored in the same environment.

Remediation focuses on removing hard-coded keys from Grape route logic, using secure configuration management, and ensuring SQL interactions do not expose sensitive data. Below are concrete, Mssql-specific examples that you can apply in a Grape API.

1. Use environment variables and secure configuration

Never embed API keys or connection strings in route files. Load them from environment variables at runtime.

# config/initializers/db.rb
DB_CONFIG = {
  adapter: 'sqlserver',
  host: ENV['MSSQL_HOST'],
  port: ENV['MSSQL_PORT'] || 1433,
  database: ENV['MSSQL_DATABASE'],
  username: ENV['MSSQL_USERNAME'],
  password: ENV['MSSQL_PASSWORD'],
  encryption: { encrypt: true, trust_server_certificate: false }
}

# Use ActiveSupport::Cache::MemoryStore or a secure vault in production
require 'active_support/core_ext/hash/indifferent_access'
DB_CONFIG = ActiveSupport::HashWithIndifferentAccess.new(DB_CONFIG)

2. Parameterized queries to prevent injection and avoid logging sensitive data

Always use parameterized queries instead of string interpolation. This prevents SQL injection and avoids accidentally logging sensitive values.

# app/api/v1/users.rb
require 'json'
require 'tiny_tds'

class UsersAPI < Grape::API
  format :json

  helpers do
    def db_client
      @db_client ||= TinyTds::Client.new(
        host: ENV['MSSQL_HOST'],
        port: ENV['MSSQL_PORT'] || 1433,
        database: ENV['MSSQL_DATABASE'],
        username: ENV['MSSQL_USERNAME'],
        password: ENV['MSSQL_PASSWORD'],
        encryption: { encrypt: true, trust_server_certificate: false }
      )
    end
  end

  get '/users/:user_id' do
    user_id = params[:user_id].to_i
    # Parameterized query: values are passed separately, not interpolated
    result = db_client.execute(
      'SELECT id, email, role FROM users WHERE id = @id',
      id: user_id
    )
    row = result.first
    error!('Not found', 404) unless row
    { id: row['id'], email: row['email'], role: row['role'] }
  rescue TinyTds::Error => e
    # Log generic errors only; do not include connection details or SQL
    Rails.logger.error("Database error: #{e.message}")
    error!('Internal server error', 500)
  end
end

3. Secure error handling to prevent verbose responses

Ensure Grape does not return stack traces or database details. Use a generic error handler and avoid exposing Mssql internal messages.

# app/api/base.rb
class BaseAPI < Grape::API
  rescue_from ::StandardError do |e|
    # Log full error for internal monitoring, but return generic message
    Rails.logger.error("Unhandled exception: #{e.class} - #{e.message}")
    Rails.logger.error(e.backtrace.join("\n"))
    error!({ error: 'Internal server error' }, 500)
  end

  rescue_from TinyTds::Error do |e
    Rails.logger.error("Mssql error: #{e.message}")
    error!({ error: 'Database error' }, 500)
  end
end

4. Disable debug endpoints and restrict diagnostic routes

Remove or protect routes that expose server or configuration details. If you must keep them, require authentication and scope them to internal networks.

# app/api/v1/internal.rb
class InternalAPI < Grape::API
  before { authenticated_only! } # your own auth helper

  # Keep diagnostic routes behind auth and never return sensitive config
  get '/health' do
    { status: 'ok' }
  end

  # Avoid returning SQL version or schema details in production
  # get '/debug/version' do
  #   client = TinyTds::Client.new(ENV['MSSQL_CONNECTION'])
  #   client.execute('SELECT @@VERSION').first
  # end
end

5. Secure logging practices

Ensure logs do not contain connection strings or API keys. Filter sensitive parameters and avoid logging full SQL statements with values.

# config/initializers/filter_parameter_logging.rb
Rails.application.config.filter_parameters += [:password, :connection_string, :api_key]

# In your Grape rescue blocks, avoid logging raw SQL with values:
# BAD: Rails.logger.info("Query: SELECT * FROM users WHERE api_key = '#{ENV['API_KEY']}'")
# GOOD: Rails.logger.info("Query executed with parameterized inputs")