Cross-site scripting (XSS) in a FastAPI application using CockroachDB typically arises when user-controlled data is reflected into HTML/JavaScript contexts without proper validation or escaping. CockroachDB, being a PostgreSQL-compatible distributed database, stores the data exactly as provided; it does not perform output encoding. Therefore, if FastAPI inserts or retrieves untrusted input (e.g., query parameters, form fields, JSON payloads) and embeds it directly into HTML templates or JSON responses that are later rendered in a browser, XSS becomes possible.

Attack patterns include reflected XSS via query parameters, stored XSS when user content is persisted in CockroachDB and later displayed, and DOM-based XSS when client-side JavaScript constructs HTML using data from the database. Common OWASP API Top 10 risks related to this include insufficient input validation and improper output encoding. For example, an attacker might supply <script>stealCookies()</script> as a username or comment; if FastAPI returns that value into an HTML page without escaping, the browser executes it. Even JSON endpoints can lead to XSS if the frontend dangerously interpolates data into the DOM.

With the LLM/AI Security checks unique to middleBrick, system prompt leakage detection and active prompt injection testing help identify whether attacker-supplied inputs can manipulate application behavior or data flow that eventually reaches the API surface. While middleBrick does not fix the issue, its findings include prioritized guidance and remediation steps that help developers address the root cause in the API design and data handling.

Remediation centers on never trusting data from CockroachDB and encoding output based on context. Use HTML escaping for dynamic content inserted into templates, strict input validation for all request parameters, and safe serialization for JSON responses. Below are concrete code examples for FastAPI with CockroachDB using asyncpg.

1. Parameter validation and HTML escaping in templates

Use an HTML template engine that auto-escapes variables (e.g., Jinja2) and validate inputs with Pydantic. Never mark content as safe unless you explicitly sanitize it.

from fastapi import FastAPI, Request
from fastapi.responses import HTMLResponse
from pydantic import BaseModel, Field
import re

app = FastAPI()

class Comment(BaseModel):
    username: str = Field(..., min_length=1, max_length=100)
    text: str = Field(..., min_length=1, max_length=1000)

# Basic alphanumeric + limited punctuation allowlist for usernames
USERNAME_RE = re.compile(r'^[\w\-\s]{1,100}$')

def is_valid_username(value: str) -> bool:
    return bool(USERNAME_RE.match(value))

@app.get("/comment", response_class=HTMLResponse)
async def show_comment(request: Request):
    username = request.query_params.get("username", "")
    if not is_valid_username(username):
        return HTMLResponse(content="
Invalid username
", status_code=400)
    # Assume fetch_from_cockroachdb returns a plain dict with text already stored verbatim
    comment_text = await fetch_from_cockroachdb(username)
    # Templates like Jinja2 auto-escape variables; here we demonstrate manual escaping for clarity
    from html import escape
    safe_text = escape(comment_text)
    return {"username": escape(username), "comment": safe_text}

async def fetch_from_cockroachdb(username: str) -> str:
    import asyncpg
    conn = await asyncpg.connect(
        host="your-cockroachdb-host",
        port=26257,
        user="your_user",
        password="your_password",
        database="your_db",
    )
    row = await conn.fetchrow(
        "SELECT text FROM user_comments WHERE username = $1 LIMIT 1",
        username
    )
    await conn.close()
    return row["text"] if row else ""

2. Safe storage and retrieval with parameterized queries

Always use parameterized queries to prevent SQL injection, which is orthogonal but often co-occurs with XSS when data integrity is weak. CockroachDB’s PostgreSQL wire protocol ensures placeholders are not interpolated into SQL strings.

import asyncpg

async def store_comment_safe(username: str, text: str):
    conn = await asyncpg.connect(
        host="your-cockroachdb-host",
        port=26257,
        user="your_user",
        password="your_password",
        database="your_db",
    )
    await conn.execute(
        "INSERT INTO user_comments (username, text) VALUES ($1, $2)",
        username,
        text,
    )
    await conn.close()

3. JSON responses: context-aware encoding on the frontend

When serving JSON, ensure the frontend does not dangerously inject data as HTML. If you must embed JSON into HTML (e.g., initial state), use proper escaping or a templating engine. middleBrick’s findings highlight the importance of output scanning for PII and code in LLM responses, but for XSS the guidance is to treat all dynamic data as untrusted and encode per context.

from fastapi import FastAPI
from fastapi.responses import JSONResponse

app = FastAPI()

@app.get("/api/comments")
async def api_comments(username: str):
    text = await fetch_from_cockroachdb(username)
    # Return plain data; let the consumer encode appropriately
    return JSONResponse(content={"username": username, "comment": text})