Format String in Fastapi with Cockroachdb

A format string vulnerability occurs when user-controlled input is passed directly into a string formatting function without proper sanitization. In a Fastapi application using Cockroachdb, this typically arises when constructing SQL queries by interpolating request data into format strings, for example with Python’s % operator or .format(). Cockroachdb, like other SQL databases, does not inherently protect against malformed format strings; if an attacker supplies format specifiers such as %s, %x, or %n, the database driver may interpret them unexpectedly, potentially leading to information disclosure or unstable query behavior.

Consider a Fastapi endpoint that builds a query using string formatting to filter users by username:

query = "SELECT id, email FROM users WHERE username = '%s'" % username
cursor.execute(query)

If username contains format specifiers, the resulting string can cause the Cockroachdb SQL parser to misinterpret the query. Even when using an ORM, format string risks can surface if raw queries are constructed with user input. The vulnerability is not Cockroachdb-specific, but the database’s strict SQL compliance means that unexpected format sequences can trigger errors or expose metadata rather than executing safely. In black-box scanning, middleBrick tests for input validation weaknesses by probing endpoints with payloads containing format specifiers and observing responses for anomalies such as malformed results or error messages that reveal internal query structure.

When format strings are improperly handled in Fastapi routes backed by Cockroachdb, the application may leak database schema details or trigger inconsistent execution paths. For instance, a payload like ' OR 1=1; --%n' could cause logging or error handling to output unexpected data, aiding further exploitation. Because Fastapi often serializes database responses directly into JSON, improperly escaped or interpreted data can propagate to clients, compounding the exposure. middleBrick’s unauthenticated scans include input validation checks that send format-related payloads to detect such behaviors, focusing on how the API handles unusual or malicious input before it reaches the database layer.

To mitigate format string risks in Fastapi with Cockroachdb, always use parameterized queries or an ORM that enforces separation between SQL code and data. This prevents user input from being interpreted as format specifiers or executable SQL. Below are concrete, working examples demonstrating safe practices.

Using Psycopg3 with parameterized queries

Psycopg3, the recommended driver for Cockroachdb, supports parameterized queries that eliminate string interpolation. Never construct SQL by formatting strings with user input.

from fastapi import FastAPI, Depends, HTTPException
import psycopg

app = FastAPI()

def get_db():
    conn = psycopg.connect("postgresql://user:password@host:26257/dbname")
    try:
        yield conn
    finally:
        conn.close()

@app.get("/users/{username}")
def read_user(username: str, conn: psycopg.Connection = Depends(get_db)):
    with conn.cursor() as cur:
        # Safe: parameter passed separately, not part of the SQL string
        cur.execute("SELECT id, email FROM users WHERE username = $1", (username,))
        row = cur.fetchone()
        if row is None:
            raise HTTPException(status_code=404, detail="User not found")
        return {"id": row[0], "email": row[1]}

Using SQLAlchemy with ORM models

SQLAlchemy provides an additional layer of abstraction, ensuring that queries are built safely. Define models and query using the ORM rather than raw SQL where possible.

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative
declarative_base
from sqlalchemy.orm import sessionmaker, Session

DATABASE_URL = "cockroachdb://user:password@host:26257/dbname?sslmode=require"
engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String)
    username = Column(String)

app = FastAPI()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/users/{username}")
def read_user(username: str, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.username == username).first()
    if user is None:
        raise HTTPException(status_code=404, detail="User not found")
    return {"id": user.id, "email": user.email}

Both approaches ensure that user input is never concatenated into SQL strings, preventing format string manipulation. middleBrick’s OpenAPI/Swagger analysis, including full $ref resolution, can validate that your endpoints rely on parameterized patterns and do not expose raw query construction in operation descriptions or examples.

Additionally, review logging and error handling to ensure that database errors do not echo user input verbatim. Configure Fastapi exception handlers to return generic messages while logging detailed errors server-side. With these fixes, the combination of Fastapi and Cockroachdb remains robust against format string exploits, and continuous monitoring via the Pro plan helps detect regressions early.