Xml External Entities in Grape with Cockroachdb

XML External Entity (XXE) injection occurs when an application processes XML input containing references to external entities and does not properly restrict them. In a Grape API that uses CockroachDB as the backend, an endpoint that parses XML payloads can inadvertently expose the server filesystem, trigger SSRF, or leak internal data if entity expansion is not disabled.

Grape is a REST-like API micro-framework for Ruby, and CockroachDB is a distributed SQL database. When a Grape route accepts XML (for example, via request.env['rack.input']), typical parsing using Nokogiri::XML without disabling DTD and entity resolution allows an attacker to define external entities pointing to local files (file:///etc/passwd), internal CockroachDB service endpoints, or other internal services reachable from the application host.

This combination is risky because CockroachDB drivers in Ruby (such as the cockroachdb gem) often rely on standard PostgreSQL libraries. If an attacker can cause the application to use data from an external entity in a database query — for example, by embedding a leaked file path or internal host into XML that is later used to construct SQL — this can lead to unauthorized data access or SSRF against CockroachDB nodes or admin UIs.

Consider an endpoint designed to import XML configuration or data. If the XML includes a parameter that is directly interpolated into a SQL string without sanitization, an externally defined general entity can inject unexpected content. Even if the query uses placeholders, an attacker might manipulate behavior by exfiltrating schema or configuration via entity expansion, especially when error messages reflect database responses that depend on injected entity content.

Because the scan reports include findings mapped to OWASP API Top 10 and common weaknesses, an unauthenticated XXE test against a Grape endpoint that parses XML and interacts with CockroachDB may surface inputs that allow reading arbitrary files or probing internal network services. Remediation focuses on disabling DTDs and external entity resolution in the XML parser, validating and sanitizing any data derived from XML before it reaches the database layer, and ensuring that database credentials and connection strings are not exposed through error handling or verbose responses.

Secure handling of XML in Grape APIs that use CockroachDB requires disabling external entity processing at the parser level and ensuring database interactions remain parameterized. Below are concrete code examples showing insecure and secure approaches.

Insecure parsing (vulnerable to XXE)

# DO NOT DO THIS — vulnerable to XXE
require 'grape'
require 'nokogiri'

class MyAPI < Grape::API
  format :xml

  post '/import' do
    xml_text = request.body.read
    doc = Nokogiri::XML(xml_text) # No DTD/entity restrictions
    data = doc.at_xpath('//data').text
    # Potentially using data in a CockroachDB query
    db = CockroachDB.connect(url: ENV['DB_URL'])
    result = db['SELECT * FROM items WHERE name = ?', data].all
    { result: result.map('to_json') }
  end
end

Secure parsing with XXE mitigation and safe CockroachDB usage

# Secure approach — disable external entities and use parameterized queries
require 'grape'
require 'nokogiri'

class MyAPI < Grape::API
  format :json

  post '/import' do
    xml_text = request.body.read

    # Configure Nokogiri to block DTDs and external entities
    doc = Nokogiri::XML(xml_text) do |config|
      config.nonet          # Disallow network access
      config.strict_load    # Raise errors on undefined entities
      config.options = Nokogiri::XML::ParseOptions::NOENT | Nokogiri::XML::ParseOptions::DTDLOAD | Nokogiri::XML::ParseOptions::DTDATTR
      # Remove external subset and entity resolution
      config.recover        # Be liberal with parsing, but safe
    end

    # Validate and extract only expected fields; avoid raw concatenation into SQL
    data_element = doc.at_xpath('//data')
    raise Grape::Exceptions::Validation, 'Missing data element' unless data_element
    data = data_element.text

    # Use parameterized CockroachDB queries; never interpolate XML content directly
    db = CockroachDB.connect(url: ENV['DB_URL'])
    rows = db['SELECT id, name FROM items WHERE name = $1', data].all
    # Explicitly map results to safe structures
    items = rows.map do |row|
      { id: row[:id], name: row[:name] }
    end
    { items: items }
  end
end

Additional hardening

• Set config.options |= Nokogiri::XML::ParseOptions::HUGE cautiously and avoid loading untrusted DTDs or schemas.
• Use environment variables for CockroachDB connection strings and ensure they are not exposed through logs or error responses.
• Apply principle of least privilege to the CockroachDB user used by the Grape service — restrict to necessary tables and operations.
• Validate XML against a strict schema (XSD) when feasible, and reject unexpected elements or attributes.

By combining parser hardening with parameterized SQL, the Grape+CockroachDB stack avoids common XXE pitfalls while maintaining safe data handling.