Api Key Exposure in Hanami with Mssql

API key exposure in a Hanami application using Microsoft SQL Server (Mssql) typically occurs when sensitive credentials are embedded in SQL strings, connection strings, or passed through query interfaces that may be logged or exposed via error messages. Hanami, a Ruby web framework, often interacts with Mssql through adapters that construct dynamic SQL or accept configuration hashes. If API keys are stored in environment variables but referenced insecurely in Hanami models or SQL fragments, they risk being captured in logs or through error responses.

Mssql-specific risks arise when API keys are interpolated into T-SQL queries, such as dynamic WHERE clauses or linked server calls. For example, concatenating a key into a string that is then executed via DB[:some_table].where("api_key = '#{ENV['API_KEY']}'") can expose the key through SQL Server logs, query profiling tools, or verbose error messages returned to the client. Additionally, if the Hanami app uses Mssql’s OPENJSON or dynamic SQL features (e.g., EXEC or sp_executesql) with unfiltered input, an attacker who can inject SQL may be able to read configuration tables or trace execution paths that reveal keys.

Another vector involves connection pooling and Mssql’s application name or client settings. If the Hanami app sets APP or CLIENT properties with the API key (e.g., SET APP 'mykey=abc123'), these values may appear in Mssql system views like sys.dm_exec_sessions or extended event traces. Without proper input validation and least-privilege database permissions, an authenticated low-privilege user or an injected query can harvest these values, leading to broader compromise.

The 12 security checks in middleBrick’s black-box scans help detect such exposure by analyzing the unauthenticated attack surface of your endpoint. For Hanami + Mssql integrations, findings may include unsafe string interpolation in SQL, missing input validation on parameters used in dynamic queries, and excessive data exposure in error responses. These findings align with OWASP API Top 10 A03:2023 (Injection) and A05:2023 (Security Misconfiguration), highlighting the need to treat SQL execution paths as potential leakage channels.

Using middleBrick’s CLI (middlebrick scan <url>) or GitHub Action can integrate detection into your workflow, flagging risky patterns before they reach production. The dashboard further tracks these findings over time, helping teams correlate SQL configuration issues with API security posture.

To prevent API key exposure when Hanami interacts with Mssql, avoid embedding secrets in SQL text and enforce parameterized patterns. Below are concrete, safe approaches with working Mssql code examples tailored for Hanami’s dataset semantics.

1. Use parameterized queries instead of string interpolation

Never interpolate API keys into SQL strings. Use Hanami’s dataset API with bound parameters so values are sent separately from the command text.

require 'hanami/repo'

class ApiKeyRepo
  def initialize
    @repo = Hanami::Repo.new
  end

  # Safe: parameterize the API key lookup
  def find_by_key(key)
    @repo['services'].where(api_key: key).limit(1).one
  end
end

Corresponding Mssql behavior: Hanami’s adapter will send the WHERE clause as a prepared statement, ensuring the key does not appear in the SQL text visible to Mssql logs or monitoring tools.

2. Avoid dynamic SQL with EXEC or sp_executesql unless strictly necessary

If dynamic SQL is required, use parameterized sp_executesql rather than string concatenation. This prevents the key from being persisted in procedure cache or logs.

-- Parameterized dynamic SQL in Mssql (safe pattern)
DECLARE @sql NVARCHAR(MAX);
DECLARE @api_key NVARCHAR(255) = @p_api_key;

SET @sql = N'SELECT * FROM services WHERE api_key = @key';

EXEC sp_executesql @sql, N'@key NVARCHAR(255)', @key = @api_key;

Never do this:

-- UNSAFE: key appears in SQL text and logs
EXEC('SELECT * FROM services WHERE api_key = ''' + @api_key + '''');

3. Restrict Mssql permissions and avoid logging sensitive contexts

Ensure the Mssql login used by Hanami has minimal privileges. Do not grant VIEW DEFINITION or server-level visibility to secrets. If you must set contextual info, avoid placing keys in application name or session properties.

-- UNSAFE: key exposed in session context
SET APP 'api_key=supersecret';

-- Safer: use app name only, keep secrets out
SET APP 'HanamiService';

Audit Mssql system views (e.g., sys.dm_exec_sessions) access and limit who can query them. Combine this with Hanami environment safeguards: keep API keys in vault-backed environment variables and rotate them regularly.

4. Validate and sanitize all inputs that feed SQL

Even when using parameters, validate length, format, and type to reduce accidental leakage via crafted payloads that may trigger verbose errors or logging.

# Hanami input validation example
class Services::Create
  include Hanami::Validations::Form

  validations do
    required(:api_key).filled(:str?, format?: /^[A-Za-z0-9\-_]{20,128}$/)
  end
end

With Mssql, ensure that error handling does not return detailed system messages to callers, which might include fragments of SQL or configuration.