Skip to content

ARGUS A14

Jump to bottom
will2469 edited this page Aug 29, 2026 · 1 revision

ARGUS-A14: Forbidden Wildcard SELECT * Queries

Rule Code: ARGUS-A14 Identifier: FORBIDDEN_SELECT_STAR Severity: HIGH (TOAST Table Bloat, Buffer Cache Pollution & PII Leak) Category: Performance, Memory Bounds & Security (Anti-Overfetching) Target Standards: CWE-200 (Exposure of Sensitive Information to an Unauthorized Actor), OWASP ASVS v4.0.3/v5.0 §V8.3.2, PostgreSQL Performance Guidelines

1. Overview & Core Invariant

All SQL queries executed in production application code must define explicit column projections. The use of wildcard expressions (SELECT * or SELECT alias.*) is strictly forbidden.

Every data retrieval query must list required column identifiers specifically (SELECT id, name, status, created_at). Legitimate exemptions are limited to:

  1. Row counting aggregate functions: COUNT(*) or COUNT(DISTINCT *).
  2. Boolean existence subqueries: EXISTS (SELECT 1 ... / SELECT * ...) where the PostgreSQL query planner ignores projection cost.

2. Technical Grounding & PostgreSQL Engine Realities

2.1. TOAST Attribute Dereferencing & Buffer Cache Pollution

PostgreSQL stores oversized attributes (TEXT, JSONB, BYTEA, arrays) out-of-line in separate TOAST tables.

  • Executing SELECT * forces PostgreSQL to perform random disk I/O seeks to dereference each TOAST pointer, even if the application never reads those fields.
  • TOAST data floods shared_buffers, evicting hot cache pages from RAM.

2.2. Nullification of Index-Only Scans

When a query requests only columns contained in an index (e.g. SELECT id, status FROM users WHERE status = 'active'), PostgreSQL executes an Index-Only Scan without touching table heap pages (zero table disk I/O). Wildcard SELECT * breaks Index-Only Scans completely, forcing physical heap lookups for every matching row.

2.3. Sensitive Information Exposure (CWE-200 & OWASP §V8.3.2)

When new private columns (e.g. password_hash, totp_secret, ssn) are added to database tables, API handlers querying via SELECT * automatically map and expose these fields into public JSON responses.

flowchart TD
    subgraph OVERFETCH ["Wildcard SELECT * (HAZARDOUS)"]
        direction TB
        Q1["SELECT * FROM users WHERE id = $1"] --> TOAST["Dereferences 2MB JSONB/BYTEA from TOAST Table"]
        Q1 --> Heap["Forces Physical Table Heap Scan (No Index-Only Scan)"]
        Q1 --> PII["Accidentally Pulls Private Columns (password_hash, totp_secret)"]
        PII --> Leak["PII Leaked into Public API JSON Response (CWE-200)"]
    end

    subgraph EXPLICIT ["Explicit Column Projection (COMPLIANT)"]
        direction TB
        Q2["SELECT id, name, email FROM users WHERE id = $1"] --> FastScan["Enables Fast Index-Only Scan (Zero Heap I/O)"]
        Q2 --> Lean["Bypasses Unnecessary TOAST Table Seeks (95% Bandwidth Saved)"]
        Q2 --> Secure["Strictly Confines Data to Intended DTO Fields (Zero PII Leak)"]
    end
Loading

3. How Argus Detects Violations (Static Analysis Architecture)

Argus evaluates SQL queries across all database call sites using AST inspection:

flowchart LR
    Scan["Scan DB Query Calls<br/>(Exclude _test.go)"] --> Parse["ast_visitor.go:<br/>pg_query_go AST Inspection"]
    Parse --> TargetList{"ResTarget Node Contains<br/>ColumnRef AStar (* or alias.*)?"}
    TargetList -->|Yes| ExceptionCheck{"exceptions.go:<br/>Inside COUNT(*) or<br/>EXISTS(...) SubLink?"}
    TargetList -->|No| Subqueries{"Inspect CTEs, FromClause Subqueries,<br/>and UNION/INTERSECT"}
    Subqueries -->|Contains Star| ExceptionCheck
    ExceptionCheck -->|Yes| Pass["Pass (Legitimate Exception)"]
    ExceptionCheck -->|No| Report["Report HIGH Violation:<br/>Forbidden SELECT * Wildcard"]
    Subqueries -->|No Star| Pass
Loading
  1. AST TargetList Inspection (ast_visitor.go): Identifies Node_AStar within ResTarget and ColumnRef nodes.
  2. Subquery & CTE Traversal (ast_visitor.go): Recursively inspects Common Table Expressions (WithClause), subselects (FromClause), and Set Operations (Larg/Rarg).
  3. Exemption Filtering (exceptions.go): Allows COUNT(*) and boolean probe subqueries inside EXISTS(...).

4. Vulnerability & Risk Taxonomy

Failure Mode Technical Impact Risk Severity
Accidental PII Exposure Newly added sensitive columns are automatically mapped and exposed via API responses. CRITICAL
TOAST Table Flooding Heavy JSONB/BYTEA attributes saturate memory buffers and increase query latency. HIGH
Index-Only Scan Invalidation Forces costly physical table heap access on queries that could run entirely in index memory. HIGH
Positional Scan Panics Application runtimes crash if migration alters column sequence order. HIGH

5. Non-Compliant Code Patterns (Bad Examples)

Example 1: Basic Wildcard Query

// VIOLATION: Selects all columns including potential TOAST attributes and private fields
func GetUser(ctx context.Context, pool *pgxpool.Pool, userID int) (*User, error) {
    // Flagged: Forbidden 'SELECT *' or wildcard column selection
    const query = "SELECT * FROM users WHERE id = $1"
    var u User
    err := pool.QueryRow(ctx, query, userID).Scan(&u.ID, &u.Name, &u.Email)
    return &u, err
}

Example 2: Table Alias Wildcard in JOIN

// VIOLATION: Alias wildcard over-fetches entire table
func GetUserOrders(ctx context.Context, pool *pgxpool.Pool, userID int) ([]Order, error) {
    // Flagged: Forbidden 'SELECT *' or wildcard column selection
    const query = "SELECT u.*, o.id FROM users u JOIN orders o ON u.id = o.user_id"
    rows, err := pool.Query(ctx, query)
    return parseOrders(rows), err
}

Example 3: Wildcard in Common Table Expression (CTE)

// VIOLATION: CTE wildcard over-fetches into temp working memory
func GetActiveOrders(ctx context.Context, pool *pgxpool.Pool) error {
    // Flagged: Forbidden 'SELECT *' or wildcard column selection
    const query = `WITH active_users AS (
        SELECT * FROM users WHERE status = 'active'
    ) SELECT id FROM active_users`
    _, err := pool.Exec(ctx, query)
    return err
}

6. Compliant Implementation Patterns (Good Examples)

Solution 1: Explicit Column Projection

// COMPLIANT: Fetches only the required columns
func GetUser(ctx context.Context, pool *pgxpool.Pool, userID int) (*User, error) {
    const query = "SELECT id, name, email, status FROM users WHERE id = $1"
    var u User
    err := pool.QueryRow(ctx, query, userID).Scan(&u.ID, &u.Name, &u.Email, &u.Status)
    return &u, err
}

Solution 2: Aggregate Function COUNT(*) 

// COMPLIANT: COUNT(*) aggregate row counting is explicitly permitted
func CountActiveUsers(ctx context.Context, pool *pgxpool.Pool) (int64, error) {
    const query = "SELECT COUNT(*) FROM users WHERE status = 'active'"
    var count int64
    err := pool.QueryRow(ctx, query).Scan(&count)
    return count, err
}

Solution 3: Boolean EXISTS Subquery

// COMPLIANT: EXISTS subquery projection is optimized by PostgreSQL planner
func UserHasOrders(ctx context.Context, pool *pgxpool.Pool, userID int) (bool, error) {
    const query = "SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = $1)"
    var exists bool
    err := pool.QueryRow(ctx, query, userID).Scan(&exists)
    return exists, err
}

7. How to Suppress (Ignore Directives)

For low-level disaster recovery database dump utilities or administrative schema introspection tools:

// argus:ignore ARGUS-A14 low-level database disaster recovery full row export
rows, err := pool.Query(ctx, "SELECT * FROM audit_archive")

Alternatively, use the canonical identifier alias:

// argus:ignore FORBIDDEN_SELECT_STAR administrative schema exporter
rows, err := pool.Query(ctx, dumpQuery)

8. Configuration Reference (.argus.yaml)

Enable or configure this rule in .argus.yaml:

rules:
  ARGUS-A14:
    enabled: true

Clone this wiki locally