Null Pointer Dereference in Fastapi with Cockroachdb

A null pointer dereference in a Fastapi service that uses CockroachDB typically occurs when application code assumes a database query result is non-null but receives None. Because Fastapi is dynamically typed and relies on Pydantic models for request and response validation, missing or malformed data from CockroachDB can propagate as None values that downstream code does not guard. CockroachDB, being a distributed SQL database, may return NULL for columns that are nullable or when joins do not match; if the Fastapi layer does not explicitly handle these NULLs and instead passes them into attribute access or business logic, a runtime exception such as AttributeError or TypeError can occur.

With OpenAPI/Swagger analysis, middleBrick correlates the API schema (2.0/3.0/3.1) with runtime behavior: if the spec defines a response field as non-nullable but the CockroachDB query can legitimately return NULL (for example, a LEFT JOIN or an optional foreign key), the mismatch highlights a potential source of null pointer dereference. MiddleBrick’s checks for Input Validation and Property Authorization can surface cases where untrusted input leads to unexpected NULLs, while BFLA/Privilege Escalation and Data Exposure checks ensure that overly permissive query results do not leak sensitive information when errors expose stack traces.

Attackers can exploit this by crafting requests that trigger edge-case queries—such as referencing a non-existent record ID or omitting optional fields—causing the service to crash or behave inconsistently. In the context of the 12 security checks run in parallel, middleBrick flags these patterns as high-severity findings and provides remediation guidance tied to OWASP API Top 10 and common coding practices in Fastapi with CockroachDB.

To prevent null pointer dereference, explicitly handle None at the boundary between CockroachDB and business logic. Use Pydantic models with strict mode and optional fields, and validate query results before accessing attributes. Below are concrete, working examples for Fastapi with CockroachDB using asyncpg and SQLAlchemy (2.0 style) with placeholders for your connection string.

from typing import Optional
from fastapi import FastAPI, HTTPException, Depends
from sqlalchemy import text, select
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase

class Base(DeclarativeBase):
    pass

app = FastAPI()
engine = create_async_engine("cockroachdb+asyncpg://username:password@host:26257/dbname?sslmode=require")
AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_db() -> AsyncSession:
    async with AsyncSessionLocal() as session:
        yield session

class User(Base):
    __tablename__ = "users"
    id: int
    name: Optional[str] = None

@app.get("/users/{user_id}")
async def read_user(user_id: int, db: AsyncSession = Depends(get_db)):
    # Safe query with explicit NULL handling
    result = await db.execute(select(User).filter_by(id=user_id))
    user = result.scalar_one_or_none()
    if user is None:
        raise HTTPException(status_code=404, detail="User not found")
    # Pydantic model ensures non-null fields are validated
    return {"id": user.id, "name": user.name if user.name is not None else "anonymous"}

@app.get("/users/{user_id}/profile")
async def read_user_profile(user_id: int, db: AsyncSession = Depends(get_db)):
    # Handle potential NULL in joined columns
    stmt = text("""
        SELECT u.id, u.name, p.bio
        FROM users u
        LEFT JOIN profiles p ON u.id = p.user_id
        WHERE u.id = :uid
    """)
    row = await db.execute(stmt, {"uid": user_id})
    row = row.fetchone()
    if row is None:
        raise HTTPException(status_code=404, detail="Profile not found")
    # Explicitly check for NULLs before use
    bio = row["bio"] if row["bio"] is not None else "No bio provided"
    return {"id": row["id"], "name": row["name"], "bio": bio}

Key practices: always use scalar_one_or_none() or fetchone() checks, map optional columns to Optional types in Pydantic models, and raise HTTPException for missing resources rather than allowing None to propagate. middleBrick’s scans can highlight mismatches between your OpenAPI spec and runtime NULL behavior, helping you align definitions with actual CockroachDB semantics.

For teams using the CLI, you can integrate checks into development with: middlebrick scan <url>. The GitHub Action adds API security checks to your CI/CD pipeline, failing builds if risk scores drop below your threshold, while the MCP Server lets you scan APIs directly from AI coding assistants in your IDE.