Excessive Index Range Scan for a Data Resource

Description

Excessive Index Range Scan for a Data Resource occurs when a product performs an index range scan on a large data table that can cover a large number of rows. According to CISQ recommendations, tables exceeding 1,000,000 rows are considered "large," and an index range scan covering 10 or more rows is considered problematic. While index scans are faster than full table scans, poorly designed queries that scan large portions of an index can still cause significant performance issues, especially when the scan must then fetch many rows from the main table.

Risk

Excessive index range scans have security implications. Slow queries can be exploited for denial-of-service attacks. Resource-intensive scans exhaust database CPU and I/O capacity. Lock contention during long-running scans can block other operations. Attackers can craft input that triggers worst-case query performance. Memory consumption increases when processing large result sets. Query timeouts can cause application failures. The predictable performance degradation enables amplification attacks.

Solution

Use appropriate indexes that support point lookups rather than range scans when possible. Add additional filter criteria to narrow index ranges. Use covering indexes to avoid table lookups for indexed data. Consider query rewrites to improve selectivity. Implement query result limits to prevent unbounded scans. Use database query analysis tools to identify expensive index scans. Apply database partitioning to reduce scan scope. Cache frequently accessed query results. Implement query timeouts to prevent runaway scans. Monitor query performance and optimize slow queries proactively.

Common Consequences

Impact	Details
Availability	Scope: Availability DoS: Resource Consumption - Large index scans consume significant CPU, I/O, and memory resources.
Availability	Scope: Availability Reduce Performance - Queries take excessively long to complete, affecting user experience.
Other	Scope: Other Lock Contention - Long-running scans may hold locks that block other operations.

Example Code

Vulnerable Code

-- Vulnerable: Index range scan covering too many rows
-- Table has 10 million rows with index on created_at

-- Vulnerable: Date range covers 6 months of data
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2024-07-01';
-- Index scan might cover 5 million rows!

-- Vulnerable: Leading wildcard forces scan of entire index
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';
-- Cannot use index efficiently - scans all rows

-- Vulnerable: Low-cardinality column in range
SELECT * FROM users
WHERE status = 'active'
  AND country IN ('US', 'CA', 'UK', 'AU', 'DE');
-- If 90% of users are 'active', this scans most of the table

-- Vulnerable: OR conditions preventing index optimization
SELECT * FROM products
WHERE category_id = 5
   OR price BETWEEN 10 AND 1000
   OR name LIKE 'Widget%';
-- May result in multiple index scans or full table scan

-- Vulnerable: Function on indexed column
SELECT * FROM events
WHERE YEAR(event_date) = 2024
  AND MONTH(event_date) = 6;
-- Function prevents direct index use, forces full scan

// Vulnerable: JPA query with excessive range scan
@Repository
public class VulnerableOrderRepository {

    @PersistenceContext
    private EntityManager em;

    // Vulnerable: Unbounded date range query
    public List<Order> findOrdersByDateRange(LocalDate start, LocalDate end) {
        // This could return millions of rows!
        return em.createQuery(
            "SELECT o FROM Order o WHERE o.createdAt BETWEEN :start AND :end",
            Order.class)
            .setParameter("start", start)
            .setParameter("end", end)
            .getResultList();  // No limit!
    }

    // Vulnerable: Low selectivity query
    public List<Order> findPendingOrders() {
        // If most orders are 'PENDING', this scans most of the table
        return em.createQuery(
            "SELECT o FROM Order o WHERE o.status = 'PENDING'",
            Order.class)
            .getResultList();
    }

    // Vulnerable: Text search without full-text index
    public List<Product> searchProducts(String searchTerm) {
        // LIKE with leading wildcard - always full scan
        return em.createQuery(
            "SELECT p FROM Product p WHERE p.description LIKE :term",
            Product.class)
            .setParameter("term", "%" + searchTerm + "%")
            .getResultList();
    }
}

# Vulnerable: Django ORM with inefficient queries
from django.db import models
from datetime import datetime, timedelta


class VulnerableAnalyticsService:

    def get_user_activity(self, start_date, end_date):
        # Vulnerable: Could return millions of records
        # Index on timestamp, but range is too wide
        return UserActivity.objects.filter(
            timestamp__gte=start_date,
            timestamp__lt=end_date
        )  # No limit, no pagination

    def search_logs(self, search_term):
        # Vulnerable: Case-insensitive LIKE with wildcards
        # Forces full table scan regardless of indexes
        return LogEntry.objects.filter(
            message__icontains=search_term
        )

    def get_orders_by_status(self, statuses):
        # Vulnerable: IN clause with many values on low-cardinality column
        # Might scan large portions of table
        return Order.objects.filter(
            status__in=statuses,
            is_deleted=False
        ).order_by('-created_at')  # Still no limit!

    def get_recent_high_value_orders(self):
        # Vulnerable: OR conditions prevent efficient index use
        return Order.objects.filter(
            models.Q(total__gte=1000) |
            models.Q(is_priority=True) |
            models.Q(customer__is_vip=True)
        ).filter(
            created_at__gte=datetime.now() - timedelta(days=90)
        )

Fixed Code

-- Fixed: Optimized queries with controlled index scans

-- Fixed: Narrow date range with pagination
SELECT * FROM orders
WHERE created_at >= '2024-06-01'
  AND created_at < '2024-06-08'  -- Just one week
ORDER BY created_at
LIMIT 100 OFFSET 0;  -- Pagination

-- Fixed: Covering index for email domain lookup
-- Create index: CREATE INDEX idx_email_domain ON customers(email_domain);
-- Add column: ALTER TABLE customers ADD email_domain VARCHAR(100)
--             GENERATED ALWAYS AS (SUBSTRING_INDEX(email, '@', -1));
SELECT * FROM customers
WHERE email_domain = 'gmail.com'
LIMIT 100;

-- Fixed: Composite index with high-cardinality column first
-- Create index: CREATE INDEX idx_country_status ON users(country, status);
SELECT * FROM users
WHERE country = 'US'
  AND status = 'active'
LIMIT 100;

-- Fixed: UNION ALL instead of OR for separate index usage
SELECT * FROM products WHERE category_id = 5 LIMIT 100
UNION ALL
SELECT * FROM products WHERE price BETWEEN 10 AND 100 LIMIT 100
UNION ALL
SELECT * FROM products WHERE name LIKE 'Widget%' LIMIT 100;

-- Fixed: Use date range that allows index usage
SELECT * FROM events
WHERE event_date >= '2024-06-01'
  AND event_date < '2024-07-01'
LIMIT 100;

-- Fixed: Partitioned table for date-based queries
-- Table partitioned by month - query only scans relevant partition
SELECT * FROM orders_partitioned
WHERE created_at >= '2024-06-01'
  AND created_at < '2024-07-01'
LIMIT 100;

// Fixed: JPA repository with optimized queries
@Repository
public class FixedOrderRepository {

    @PersistenceContext
    private EntityManager em;

    // Fixed: Paginated results with reasonable page size
    public Page<Order> findOrdersByDateRange(
            LocalDate start, LocalDate end, Pageable pageable) {

        // Enforce maximum date range
        if (ChronoUnit.DAYS.between(start, end) > 30) {
            throw new IllegalArgumentException(
                "Date range cannot exceed 30 days");
        }

        String countQuery = "SELECT COUNT(o) FROM Order o " +
                           "WHERE o.createdAt BETWEEN :start AND :end";

        String dataQuery = "SELECT o FROM Order o " +
                          "WHERE o.createdAt BETWEEN :start AND :end " +
                          "ORDER BY o.createdAt DESC";

        Long count = em.createQuery(countQuery, Long.class)
            .setParameter("start", start)
            .setParameter("end", end)
            .getSingleResult();

        List<Order> orders = em.createQuery(dataQuery, Order.class)
            .setParameter("start", start)
            .setParameter("end", end)
            .setFirstResult((int) pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();

        return new PageImpl<>(orders, pageable, count);
    }

    // Fixed: Use composite index and limit results
    public List<Order> findRecentPendingOrders(int limit) {
        // Index: (status, created_at DESC)
        // Adds time constraint to limit scan
        LocalDate cutoff = LocalDate.now().minusDays(7);

        return em.createQuery(
            "SELECT o FROM Order o " +
            "WHERE o.status = 'PENDING' " +
            "AND o.createdAt >= :cutoff " +
            "ORDER BY o.createdAt DESC",
            Order.class)
            .setParameter("cutoff", cutoff)
            .setMaxResults(limit)
            .getResultList();
    }

    // Fixed: Use full-text search instead of LIKE
    public List<Product> searchProducts(String searchTerm, int limit) {
        // Using database full-text search (MySQL example)
        return em.createNativeQuery(
            "SELECT * FROM products " +
            "WHERE MATCH(name, description) AGAINST(:term IN NATURAL LANGUAGE MODE) " +
            "LIMIT :limit",
            Product.class)
            .setParameter("term", searchTerm)
            .setParameter("limit", limit)
            .getResultList();
    }
}

# Fixed: Django ORM with optimized queries
from django.db import models
from django.core.paginator import Paginator
from datetime import datetime, timedelta
from django.contrib.postgres.search import SearchVector, SearchQuery


class FixedAnalyticsService:

    MAX_DAYS_RANGE = 7
    DEFAULT_PAGE_SIZE = 100

    def get_user_activity(self, start_date, end_date, page=1):
        # Fixed: Enforce maximum date range
        if (end_date - start_date).days > self.MAX_DAYS_RANGE:
            raise ValueError(
                f"Date range cannot exceed {self.MAX_DAYS_RANGE} days"
            )

        # Fixed: Paginated results
        queryset = UserActivity.objects.filter(
            timestamp__gte=start_date,
            timestamp__lt=end_date
        ).order_by('-timestamp')

        paginator = Paginator(queryset, self.DEFAULT_PAGE_SIZE)
        return paginator.get_page(page)

    def search_logs(self, search_term, limit=100):
        # Fixed: Use PostgreSQL full-text search
        # Requires: CREATE INDEX idx_log_search ON log_entry
        #           USING gin(to_tsvector('english', message));
        search_query = SearchQuery(search_term)

        return LogEntry.objects.annotate(
            search=SearchVector('message')
        ).filter(
            search=search_query
        ).order_by('-timestamp')[:limit]

    def get_orders_by_status(self, statuses, days_back=7, limit=100):
        # Fixed: Add time constraint and limit
        cutoff = datetime.now() - timedelta(days=days_back)

        return Order.objects.filter(
            status__in=statuses,
            is_deleted=False,
            created_at__gte=cutoff  # Narrow the range
        ).order_by('-created_at')[:limit]

    def get_recent_high_value_orders(self, limit=100):
        # Fixed: Separate queries with UNION for better index usage
        # Or use a computed/denormalized column

        # Option 1: Add a 'requires_attention' boolean column
        # that's updated by triggers/signals
        return Order.objects.filter(
            requires_attention=True,
            created_at__gte=datetime.now() - timedelta(days=7)
        ).order_by('-created_at')[:limit]

    def get_orders_with_cursor_pagination(self, cursor=None, page_size=100):
        """
        Fixed: Use cursor-based pagination for large datasets.
        More efficient than offset pagination for large offsets.
        """
        queryset = Order.objects.order_by('-created_at', '-id')

        if cursor:
            # cursor is (created_at, id) tuple
            created_at, order_id = cursor
            queryset = queryset.filter(
                models.Q(created_at__lt=created_at) |
                models.Q(created_at=created_at, id__lt=order_id)
            )

        orders = list(queryset[:page_size + 1])

        has_next = len(orders) > page_size
        if has_next:
            orders = orders[:page_size]

        next_cursor = None
        if has_next and orders:
            last = orders[-1]
            next_cursor = (last.created_at, last.id)

        return {
            'orders': orders,
            'next_cursor': next_cursor,
            'has_next': has_next
        }

---

CVE Examples

This CWE is marked as PROHIBITED for direct CVE mapping as it represents a performance/quality concern rather than a direct security vulnerability.

---

Related CWEs

• CWE-405: Asymmetric Resource Consumption (Amplification) (parent)
• CWE-1067: Excessive Execution of Sequential Searches of Data Resource (related)
• CWE-400: Uncontrolled Resource Consumption (can lead to)

---

References

1. MITRE Corporation. "CWE-1094: Excessive Index Range Scan for a Data Resource." https://cwe.mitre.org/data/definitions/1094.html
2. OMG ASCPEM-PRF-7. "Automated Source Code Performance Efficiency Measure."
3. Use The Index, Luke. "Indexing for Range Queries." https://use-the-index-luke.com/