Command Injection in Cockroachdb

Command injection in Cockroachdb occurs when untrusted input is passed to system-level commands or SQL queries without proper sanitization. Unlike traditional command injection in application code, Cockroachdb-specific injection often targets the database's interaction with the operating system and its SQL processing engine.

The most common Cockroachdb command injection vectors include:

• Unsafe dynamic SQL construction using string concatenation with user input
• PL/pgSQL functions that execute system commands via pg_sleep or other functions
• Improper handling of file paths in IMPORT/EXPORT operations
• Unsafe usage of the exttt{crdb_internal} schema functions
• Unsanitized input in exttt{EXECUTE IMMEDIATE} statements

Consider this vulnerable Cockroachdb code pattern:

CREATE OR REPLACE FUNCTION vulnerable_export(path TEXT, table_name TEXT) RETURNS VOID AS $$
DECLARE
    cmd TEXT;
BEGIN
    cmd := 'EXPORT INTO CSV ''file://' || path || '/' || table_name || '.csv'' FROM ' || table_name;
    EXECUTE IMMEDIATE cmd;
END;
$$ LANGUAGE plpgsql;

An attacker could call exttt{vulnerable_export('/tmp', 'users; DROP TABLE users; --', 'users')}, causing the function to execute malicious SQL commands. The function concatenates user input directly into the SQL command string without validation.

Another Cockroachdb-specific vector involves the exttt{crdb_internal} schema:

-- Vulnerable: Unvalidated input in crdb_internal function
SELECT crdb_internal.node_executable_path || '/../../../../etc/passwd';

This can leak sensitive system information if an attacker controls the input path. The exttt{crdb_internal} schema provides access to database internals and should be restricted to trusted users only.

IMPORT statements are also vulnerable to path traversal attacks:

-- Vulnerable: Path traversal in IMPORT
IMPORT TABLE users CSV DATA = 'file:///var/lib/cockroach/users.csv';
-- Malicious: Could be manipulated to access other files
IMPORT TABLE users CSV DATA = 'file:///var/lib/cockroach/../../../etc/shadow';

Cockroachdb's support for PostgreSQL-compatible functions creates additional injection surfaces. Functions like exttt{COPY}, exttt{pg_sleep}, and dynamic SQL execution can be exploited when combined with user-controlled input.

Detecting command injection in Cockroachdb requires both static code analysis and runtime scanning. middleBrick's black-box scanning approach is particularly effective for Cockroachdb because it tests the unauthenticated attack surface without requiring database credentials.

middleBrick scans Cockroachdb endpoints for several specific injection patterns:

• SQL injection in REST API endpoints that interact with Cockroachdb
• Authentication bypass attempts targeting Cockroachdb's built-in authentication
• Path traversal in IMPORT/EXPORT operations
• System command injection via unsafe function calls
• Privilege escalation attempts through role manipulation

The scanner tests Cockroachdb's JDBC and PostgreSQL-compatible endpoints with specialized payloads:

-- Example payloads middleBrick might test against Cockroachdb endpoints:
SELECT * FROM users WHERE id = '1' OR 1=1; --
DROP TABLE users; --
SELECT pg_sleep(10); -- timing attack
SELECT current_setting('is_superuser');

For Cockroachdb-specific detection, middleBrick examines:

middleBrick's runtime scanning identifies these issues by sending crafted requests to Cockroachdb's HTTP and SQL interfaces. The scanner detects command injection by observing error responses, timing differences, and unexpected data exposure.

For code-level detection, static analysis should look for:

-- Patterns to flag in code review:
EXECUTE IMMEDIATE 'SELECT * FROM ' || user_input;
'SELECT * FROM ' || unsafe_variable;
IMPORT ... DATA = 'file://' || user_path;

middleBrick's scoring system evaluates Cockroachdb endpoints on a 0-100 scale, with specific weight given to injection vulnerabilities. A Cockroachdb endpoint vulnerable to command injection would typically score in the 20-40 range, depending on the severity and exploitability of the issues found.

Remediating command injection in Cockroachdb requires a defense-in-depth approach combining secure coding practices, proper permissions, and runtime protections.

1. Use Parameterized Queries

Always use parameterized queries instead of string concatenation:

-- Vulnerable
EXECUTE IMMEDIATE 'SELECT * FROM users WHERE id = ' || user_id;

-- Secure
EXECUTE IMMEDIATE 'SELECT * FROM users WHERE id = $1' USING user_id;

2. Input Validation and Sanitization

Validate all user input before using it in SQL commands:

CREATE OR REPLACE FUNCTION safe_export(path TEXT, table_name TEXT) RETURNS VOID AS $$
DECLARE
    cmd TEXT;
BEGIN
    -- Validate table name against allowed pattern
    IF table_name !~ '^[a-zA-Z_][a-zA-Z0-9_]*$' THEN
        RAISE EXCEPTION 'Invalid table name';
    END IF;
    
    -- Validate path (prevent traversal)
    IF position('../' IN path) > 0 OR position('/..' IN path) > 0 THEN
        RAISE EXCEPTION 'Invalid path';
    END IF;
    
    cmd := 'EXPORT INTO CSV ''file://' || path || '/' || table_name || '.csv'' FROM ' || table_name;
    EXECUTE IMMEDIATE cmd;
END;
$$ LANGUAGE plpgsql;

3. Principle of Least Privilege

Restrict database roles and permissions:

-- Create limited role for application
CREATE ROLE app_user WITH PASSWORD 'secure_password';
REVOKE ALL ON DATABASE mydb FROM app_user;
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;

-- Only grant necessary permissions
GRANT SELECT ON users TO app_user;
GRANT INSERT ON users TO app_user;

-- Revoke dangerous permissions
REVOKE CREATE ON SCHEMA public FROM app_user;
REVOKE ALL ON crdb_internal FROM app_user;

4. Safe IMPORT/EXPORT Practices

Use absolute paths and validate file locations:

-- Secure EXPORT function
CREATE OR REPLACE FUNCTION secure_export(table_name TEXT, export_path TEXT) RETURNS VOID AS $$
DECLARE
    full_path TEXT;
BEGIN
    -- Only allow exports to /exports directory
    IF position('/exports/' IN export_path) != 1 THEN
        RAISE EXCEPTION 'Exports only allowed to /exports/ directory';
    END IF;
    
    -- Validate table exists
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = table_name) THEN
        RAISE EXCEPTION 'Table does not exist';
    END IF;
    
    EXECUTE 'EXPORT INTO CSV ''file://' || export_path || '/' || table_name || '.csv'' FROM ' || table_name;
END;
$$ LANGUAGE plpgsql;

5. Monitor and Audit

Enable Cockroachdb's audit logging for sensitive operations:

-- Enable audit logging for critical tables
ALTER TABLE users EXPERIMENTAL_AUDIT SET READ WRITE;

-- Monitor crdb_internal access
CREATE TABLE audit_log (log_time TIMESTAMP, user TEXT, operation TEXT, details JSONB);

CREATE OR REPLACE FUNCTION log_crdb_internal_access() RETURNS VOID AS $$
BEGIN
    INSERT INTO audit_log (log_time, user, operation, details)
    VALUES (NOW(), current_user, 'crdb_internal_access', row_to_json(current_query()));
END;
$$ LANGUAGE plpgsql;

6. Use middleBrick for Continuous Monitoring

Integrate middleBrick into your CI/CD pipeline to catch injection vulnerabilities before deployment:

# GitHub Action example
name: Security Scan
on: [push, pull_request]
jobs:
  security:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Run middleBrick scan
        run: |
          npm install -g middlebrick
          middlebrick scan http://localhost:26257 --fail-below B --output json
      - name: Fail on low score
        if: failure()
        run: echo