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

Field	Value
Rule ID	VNX-1049
Severity	Low
SARIF level	note
Kind	sast
Languages	Python, Java, JavaScript / TypeScript
CWE	CWE-1049
CAPEC	CAPEC-66
ATT&CK	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

Overview

VNX-1049 maps to CWE-1049: Excessive Data Query Operations in a Large Data Table. 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

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:

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

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 ;):

# nosec vnx-1049
// nosec vnx-1049

Or record an ignore rule: vulnetix ignore add --rule vnx-1049 --reason "...". See Suppressing findings.