# VNX-1049: Excessive Data Query Operations in a Large Data Table

Reports a single SQL SELECT written in Python, Java or JavaScript/TypeScript that combines five or more JOIN clauses.

| Field | Value |
|---|---|
| Rule ID | `VNX-1049` |
| Severity | Low |
| SARIF level | `note` |
| Kind | `sast` |
| Languages | Python, Java, JavaScript / TypeScript |
| CWE | [CWE-1049](https://cwe.mitre.org/data/definitions/1049.html) |
| CAPEC | [CAPEC-66](https://capec.mitre.org/data/definitions/66.html) |
| ATT&CK | [T1190](https://attack.mitre.org/techniques/T1190/) |
| CWSS vector | `CWSS:1.0/TI:L/AP:RU/AL:S/IC:N/FC:LT/RP:RU/RL:S/AV:L/AS:N/IN:A/SC:A/BI:L/DI:H/EX:L/EC:N/P:L` |
| Tags | `excessive-data-query` `sql` `performance` `cwe-1049` |
| Source | [`internal/sast/rules/vnx-1049.rego`](https://github.com/Vulnesis/cli/blob/main/internal/sast/rules/vnx-1049.rego) |

## Overview

VNX-1049 maps to [CWE-1049: Excessive Data Query Operations in a Large Data Table](https://cwe.mitre.org/data/definitions/1049.html). It reports one SQL `SELECT` statement, written as a string literal in Python, Java or JavaScript/TypeScript, that combines five or more join and sub-query operations (`JOIN` clauses of any kind plus parenthesised `SELECT` sub-queries). The statement is read up to the end of its literal: a triple-quoted Python string, a Java text block, a JavaScript template literal, or adjacent and concatenated literals up to the end of the statement. The finding is on the line where the query starts.

## Why this matters

A query whose cost grows with every table it touches degrades badly once those tables are large. Five or more joins and sub-queries in one statement is the shape CWE-1049 describes. The rule cannot know how large the tables are, so treat a finding as a prompt to read the query plan.

Not reported: queries with four or fewer joins and sub-queries, several small queries in a row (each statement is counted on its own), `str.join`/`path.join` calls, SQL in comments, and query builders or ORM chains whose joins are not written as SQL text.

## What gets flagged

```python
query = text(
    '''SELECT c.id, c.name
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    JOIN payments p ON p.order_id = o.id
    WHERE c.id IN (SELECT customer_id FROM loyalty WHERE tier = 'gold')
      AND o.total > (SELECT AVG(total) FROM orders)
      AND p.status IN (SELECT code FROM payment_status WHERE settled = 1)
    '''
)
```

Not flagged:

```python
cur.execute("SELECT u.id FROM users u JOIN teams t ON t.id = u.team_id JOIN orgs o ON o.id = t.org_id")
```

## Remediation

1. Check the query plan (`EXPLAIN`) against production-sized data.
2. Add indexes on the join and filter columns, or replace repeated sub-queries with a join or a materialised view.
3. Split the query when parts of it are only needed some of the time.

## References

- [CWE-1049: Excessive Data Query Operations in a Large Data Table](https://cwe.mitre.org/data/definitions/1049.html)

## Suppressing this rule

Put a `nosec` comment on the line, or on line 1 to cover the whole file. Use the comment marker of the file's language (`#`, `//`, `--` or `;`):

```text
# nosec vnx-1049
// nosec vnx-1049
```

Or record an ignore rule: `vulnetix ignore add --rule vnx-1049 --reason "..."`. See [Suppressing findings](/sast/guides/suppressing-findings.md).
