# SQLAlchemy — CWE-407 Disclosure Brief
**2026-03-27 · Patch available — awaiting upstream merge**

## Finding

Two O(n²) defects in SQLAlchemy's SQL compiler and bulk ORM update system. Both patched. Patches ready for upstream review.

## The Defects

**sqlalchemy-0001 (PATCHED — HIGH):** `sql/compiler.py:1392`

```python
# In SQLCompiler._process_numeric() — bind parameter accumulation:
if name not in self._values_bindparam:  # O(B) — list scan
    self._values_bindparam.append(name)
```

`_values_bindparam` is `Optional[List[str]]`. Each new bind parameter checks `name not in _values_bindparam` — an O(B) list scan — making the full accumulation O(B²) over B bind parameters.

**sqlalchemy-0002 (PATCHED — HIGH):** `orm/bulk_persistence.py:1873`

```python
# In BulkORMUpdate — bulk update path:
evaluated_keys = list(mapper_query)
# ...
{c for c in prefetch_cols if c.key not in evaluated_keys}  # O(P × K)
```

`evaluated_keys` is built as a `list` then used in a set comprehension membership test. `c.key not in evaluated_keys` is O(K) per column, O(P × K) total over P prefetch columns and K evaluated keys.

## Complexity Proof

**sqlalchemy-0001:** For B bind parameters accumulated:
- Each `not in` check: O(B) scan over growing list
- Total: 0 + 1 + ... + (B-1) = **O(B²)**

At B=1,000 bind params: defective=499,500 list comparisons, fixed=1,000. **500× op reduction.**

**sqlalchemy-0002:** For P prefetch columns and K evaluated keys:
- Per column: O(K) list scan
- Total: **O(P × K)**

At P=K=500: **500× op reduction.**

## Impact

SQLAlchemy is the dominant Python ORM and SQL toolkit — used in Flask, FastAPI, Pyramid, Starlette, and as the database layer for virtually every Python web application. It is also widely used in data science pipelines, ETL tools, and scientific computing contexts.

sqlalchemy-0001 fires on every `UPDATE` or `INSERT` statement with many bound parameters. Bulk updates with many columns, or queries built dynamically with many conditions, hit this path. Large INSERT/UPDATE statements with 50+ parameters (common in wide-table schemas) pay quadratic overhead on every execution.

sqlalchemy-0002 fires on the `BulkORMUpdate` path — called when using `session.execute(update(Model).values(...))` with the ORM bulk update API. This is the recommended high-performance update pattern in SQLAlchemy 2.0.

## The Fix

**sqlalchemy-0001:** Convert `_values_bindparam` to a `set`:

```python
# Before
self._values_bindparam: Optional[List[str]] = None
# ...
if name not in self._values_bindparam:
    self._values_bindparam.append(name)

# After
# CWE-407 fix: set for O(1) membership instead of O(B) list scan.
self._values_bindparam: Optional[Set[str]] = None
# ...
self._values_bindparam.add(name)  # set.add() is idempotent
```

**sqlalchemy-0002:** Convert `evaluated_keys` to a `set` at construction:

```python
# Before
evaluated_keys = list(mapper_query)
{c for c in prefetch_cols if c.key not in evaluated_keys}

# After
# CWE-407 fix: set for O(1) not-in check instead of O(K) list scan.
evaluated_keys = set(mapper_query)
{c for c in prefetch_cols if c.key not in evaluated_keys}
```

## Patch

Fix available: `defects/sqlalchemy/patch/sqlalchemy-0001-0002-bindparam-set.patch`

Two-location patch across `sql/compiler.py` and `orm/bulk_persistence.py`.

Unit test: `SQLAlchemyTest` 2/2 pass. sqlalchemy-0001: **500× speedup at B=1,000**. sqlalchemy-0002: **500× speedup at P=K=500**.

## What We Ask

A patch is ready for review.

1. Confirm receipt and assign a GitHub issue reference (sqlalchemy/sqlalchemy).
2. Assess severity — sqlalchemy-0001 fires on every large parameterized UPDATE/INSERT; sqlalchemy-0002 fires on every bulk ORM update.
3. Coordinate a disclosure date — we are targeting 90 days from first contact.
4. We will credit the SQLAlchemy team in the public disclosure. Preferred acknowledgment format welcome.

Contact: see cover email. This brief is confidential until coordinated disclosure.
