Command Injection in Fastapi with Cockroachdb

Command injection occurs when untrusted input is concatenated into system or shell commands. In a Fastapi application that interacts with Cockroachdb, this risk typically arises not from Cockroachdb itself, which is a SQL database, but from unsafe use of operating system commands that include data derived from database queries or user input. For example, if a Fastapi endpoint runs a subprocess to call Cockroachdb-specific tooling (such as cockroach node or cockroach sql) and includes unchecked input in the command string, an attacker can inject additional shell commands.

Consider a scenario where an endpoint retrieves a cluster node name from Cockroachdb and then passes that value to a shell command for diagnostics. If the node name contains shell metacharacters or an attacker influences the stored data through other means (such as a compromised record), the command can be altered to execute arbitrary shell commands. This is a classic command injection vector enabled by improper input validation and shell usage.

Another vector involves dynamic construction of Cockroachdb connection strings or flags based on user-supplied parameters. If these parameters are not strictly validated and are later used in shell commands (for example, to invoke a backup script), special characters such as semicolons, ampersands, or pipes can break out of the intended command context. The Fastapi route may appear to only interact with SQL, but the introduction of subprocess calls or external tooling creates a path for command injection.

Even when using an ORM or a driver to communicate with Cockroachdb, developers might log or expose query details to the shell for debugging. If those logs or debug commands are constructed unsafely, they can become a vector for command injection. The key takeaway is that command injection is not a database vulnerability per se; it is a misuse of shell commands where data from any source—including data originally retrieved from Cockroachdb—is concatenated without proper sanitization or use of safe APIs.

To prevent command injection in Fastapi when working with Cockroachdb, avoid constructing shell commands with external input. Use parameterized SQL queries for database operations and, if you must invoke Cockroachdb tooling, use language-native APIs or structured command arguments instead of shell strings.

Here is an example of a vulnerable Fastapi endpoint that demonstrates the risk:

import subprocess
from fastapi import FastAPI, Query

app = FastAPI()

@app.get("/debug-node")
def debug_node(node_name: str = Query(...)):
    # Vulnerable: direct shell command construction
    cmd = f"cockroach node status --node={node_name}"
    result = subprocess.run(cmd, shell=True, capture_output=True, text=True)
    return {"output": result.stdout}

An attacker can supply a node_name such as foo; rm -rf / and cause destructive command execution. The fix is to avoid shell=True and pass arguments as a list:

import subprocess
from fastapi import FastAPI, Query

app = FastAPI()

@app.get("/debug-node")
def debug_node(node_name: str = Query(...)):
    # Safe: arguments passed as a list, no shell interpretation
    cmd = ["cockroach", "node", "status", f"--node={node_name}"]
    result = subprocess.run(cmd, capture_output=True, text=True)
    return {"output": result.stdout}

For Cockroachdb SQL operations, always use parameterized queries with the appropriate driver. Here is a secure example using an async database session:

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import asyncpg

app = FastAPI()

class NodeQuery(BaseModel):
    node_id: int

@app.post("/query-node")
def query_node(payload: NodeQuery):
    # Assume pool is an asyncpg connection pool to Cockroachdb
    try:
        record = pool.execute(
            "SELECT * FROM node_diagnostics WHERE node_id = $1",
            payload.node_id
        )
        return {"node_data": record}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

In this example, the query uses placeholders and parameter binding, which prevents injection into SQL. Do not concatenate user input into SQL strings, even for administrative commands.

When you need to run administrative scripts, validate and sanitize all inputs against a strict allowlist. For example, if a node name must match a pattern, enforce it with regex before using it anywhere near a shell or a command builder. This approach reduces risk across Fastapi routes and any subprocess or external tooling interaction.