Excessive Execution of Sequential Searches of Data Resource

Description

Excessive Execution of Sequential Searches of Data Resource occurs when a product contains a data query against an SQL table or view that is configured in a way that does not utilize an index and may cause sequential searches (full table scans) to be performed. When queries cannot use indexes, the database must examine every row in the table, causing O(n) performance instead of O(log n) with proper indexing. This creates significant performance degradation that scales poorly as data grows.

Risk

While primarily a performance issue, sequential searches have security implications. Attackers can exploit slow queries as denial-of-service vectors by triggering expensive table scans. Database connection pool exhaustion can occur when slow queries hold connections. The predictable performance degradation allows attackers to estimate data volumes. CPU and I/O exhaustion affects overall system availability. Time-based attacks become easier with predictable slow queries. Queries that worked fine in development may become unusable in production with larger datasets, creating availability incidents.

Solution

Analyze query execution plans using EXPLAIN (MySQL) or EXPLAIN ANALYZE (PostgreSQL). Create indexes on columns used in WHERE clauses, JOIN conditions, and ORDER BY clauses. Avoid functions on indexed columns in WHERE clauses (e.g., WHERE YEAR(date_column) = 2024). Use covering indexes for frequently executed queries. Monitor slow query logs and set up alerting. Implement query timeouts to prevent runaway queries. Use database query analyzers and optimization tools. Consider partitioning for very large tables. Avoid SELECT * and only retrieve needed columns. Use parameterized queries to enable query plan caching.

Common Consequences

Impact	Details
Availability	Scope: Availability DoS: Resource Consumption - Full table scans consume excessive CPU, memory, and I/O resources.
Availability	Scope: Availability Reduce Performance - Query performance degrades linearly with table size without proper indexing.
Other	Scope: Other Quality Degradation - System becomes slower and less responsive as data grows.

Example Code

Vulnerable Code

-- Vulnerable: Query without index usage
-- Table: orders (10 million rows)
-- No index on customer_email column

-- Vulnerable: Full table scan to find orders
SELECT * FROM orders
WHERE customer_email = '[email protected]';
-- Scans all 10 million rows!

-- Vulnerable: Function on indexed column prevents index usage
-- Even if created_at has an index:
SELECT * FROM orders
WHERE YEAR(created_at) = 2024;
-- Index on created_at cannot be used!

-- Vulnerable: LIKE with leading wildcard
SELECT * FROM products
WHERE name LIKE '%widget%';
-- Cannot use index with leading wildcard

-- Vulnerable: OR conditions preventing index usage
SELECT * FROM customers
WHERE phone = '555-1234' OR email = '[email protected]';
-- May not use indexes efficiently

-- Vulnerable: Implicit type conversion
SELECT * FROM users
WHERE user_id = '12345';  -- user_id is INT, comparing to string
-- Type conversion prevents index usage

// Vulnerable: JPA/Hibernate queries without index consideration
@Repository
public class VulnerableOrderRepository {

    @PersistenceContext
    private EntityManager em;

    // Vulnerable: Query on non-indexed field
    public List<Order> findByCustomerEmail(String email) {
        // customer_email may not be indexed
        return em.createQuery(
            "SELECT o FROM Order o WHERE o.customerEmail = :email",
            Order.class
        ).setParameter("email", email).getResultList();
    }

    // Vulnerable: Function in WHERE clause
    public List<Order> findOrdersForYear(int year) {
        // YEAR() function prevents index usage
        return em.createQuery(
            "SELECT o FROM Order o WHERE YEAR(o.createdAt) = :year",
            Order.class
        ).setParameter("year", year).getResultList();
    }

    // Vulnerable: Fetching all data with no limit
    public List<Order> searchOrders(String searchTerm) {
        // LIKE with wildcards on both sides = full table scan
        return em.createQuery(
            "SELECT o FROM Order o WHERE o.description LIKE :term",
            Order.class
        ).setParameter("term", "%" + searchTerm + "%").getResultList();
    }
}

# Vulnerable: Django ORM queries without index awareness
from django.db import models

class Order(models.Model):
    customer_email = models.CharField(max_length=255)  # No db_index=True!
    status = models.CharField(max_length=50)
    created_at = models.DateTimeField()
    description = models.TextField()

    class Meta:
        # No indexes defined!
        pass


# Vulnerable queries
def vulnerable_search_orders(email):
    # Full table scan - customer_email not indexed
    return Order.objects.filter(customer_email=email)


def vulnerable_search_by_year(year):
    # Function prevents index usage
    return Order.objects.filter(created_at__year=year)


def vulnerable_text_search(term):
    # icontains = LIKE '%term%' = full table scan
    return Order.objects.filter(description__icontains=term)


def vulnerable_complex_filter(email, status):
    # Multiple unindexed conditions
    return Order.objects.filter(
        customer_email=email,
        status=status,
        description__contains='urgent'
    )

// Vulnerable: MongoDB queries without index
const mongoose = require('mongoose');

const orderSchema = new mongoose.Schema({
    customerEmail: String,  // No index!
    status: String,
    createdAt: Date,
    items: [{
        productId: String,
        quantity: Number
    }]
});

// No indexes defined on the schema

// Vulnerable: Collection scan
async function findOrdersByEmail(email) {
    // Full collection scan without index
    return await Order.find({ customerEmail: email });
}

// Vulnerable: Regex query without index
async function searchOrders(term) {
    // Regex cannot use index effectively
    return await Order.find({
        'items.productId': { $regex: term, $options: 'i' }
    });
}

// Vulnerable: Query on nested field without index
async function findOrdersWithProduct(productId) {
    // Nested field query without compound index
    return await Order.find({ 'items.productId': productId });
}

Fixed Code

-- Fixed: Create appropriate indexes
CREATE INDEX idx_orders_customer_email ON orders(customer_email);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_status ON orders(status);

-- Fixed: Composite index for common query patterns
CREATE INDEX idx_orders_email_status ON orders(customer_email, status);

-- Fixed: Query now uses index
SELECT * FROM orders
WHERE customer_email = '[email protected]';
-- Uses idx_orders_customer_email

-- Fixed: Range query instead of function
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- Uses idx_orders_created_at

-- Fixed: For text search, use full-text index
CREATE FULLTEXT INDEX idx_products_name ON products(name);

SELECT * FROM products
WHERE MATCH(name) AGAINST('widget' IN NATURAL LANGUAGE MODE);
-- Uses full-text index

-- Fixed: Use UNION for OR conditions
SELECT * FROM customers WHERE phone = '555-1234'
UNION
SELECT * FROM customers WHERE email = '[email protected]';
-- Each query can use its respective index

-- Fixed: Proper type matching
SELECT * FROM users
WHERE user_id = 12345;  -- INT compared to INT
-- Index used correctly

// Fixed: JPA/Hibernate with proper indexing and query optimization
@Entity
@Table(name = "orders", indexes = {
    @Index(name = "idx_customer_email", columnList = "customerEmail"),
    @Index(name = "idx_created_at", columnList = "createdAt"),
    @Index(name = "idx_email_status", columnList = "customerEmail, status")
})
public class Order {
    @Id
    private Long id;

    @Column(name = "customer_email")
    private String customerEmail;

    @Column(name = "status")
    private String status;

    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

@Repository
public class FixedOrderRepository {

    @PersistenceContext
    private EntityManager em;

    // Fixed: Query uses indexed column
    public List<Order> findByCustomerEmail(String email) {
        return em.createQuery(
            "SELECT o FROM Order o WHERE o.customerEmail = :email",
            Order.class
        ).setParameter("email", email).getResultList();
    }

    // Fixed: Date range instead of YEAR() function
    public List<Order> findOrdersForYear(int year) {
        LocalDateTime startOfYear = LocalDateTime.of(year, 1, 1, 0, 0);
        LocalDateTime endOfYear = startOfYear.plusYears(1);

        return em.createQuery(
            "SELECT o FROM Order o WHERE o.createdAt >= :start AND o.createdAt < :end",
            Order.class
        ).setParameter("start", startOfYear)
         .setParameter("end", endOfYear)
         .getResultList();
    }

    // Fixed: Use pagination and consider full-text search
    public List<Order> searchOrders(String searchTerm, int page, int size) {
        // For substring search, consider Elasticsearch or full-text search
        // If SQL is required, at least paginate results
        return em.createQuery(
            "SELECT o FROM Order o WHERE o.description LIKE :term ORDER BY o.id",
            Order.class
        ).setParameter("term", "%" + searchTerm + "%")
         .setFirstResult(page * size)
         .setMaxResults(size)
         .getResultList();
    }
}

# Fixed: Django ORM with proper indexes
from django.db import models

class Order(models.Model):
    customer_email = models.CharField(max_length=255, db_index=True)  # Indexed!
    status = models.CharField(max_length=50, db_index=True)
    created_at = models.DateTimeField(db_index=True)
    description = models.TextField()

    class Meta:
        indexes = [
            # Composite index for common queries
            models.Index(fields=['customer_email', 'status']),
            models.Index(fields=['created_at', 'status']),
        ]


# Fixed queries
def fixed_search_orders(email):
    # Uses index on customer_email
    return Order.objects.filter(customer_email=email)


def fixed_search_by_year(year):
    # Fixed: Date range instead of year extraction
    from datetime import datetime
    start = datetime(year, 1, 1)
    end = datetime(year + 1, 1, 1)
    return Order.objects.filter(created_at__gte=start, created_at__lt=end)


def fixed_text_search(term):
    # Fixed: Use PostgreSQL full-text search or Elasticsearch
    # For simple prefix search (uses index):
    return Order.objects.filter(description__istartswith=term)

    # Or use django.contrib.postgres SearchVector:
    # from django.contrib.postgres.search import SearchVector
    # return Order.objects.annotate(
    #     search=SearchVector('description')
    # ).filter(search=term)


def fixed_complex_filter(email, status):
    # Uses composite index idx_email_status
    return Order.objects.filter(
        customer_email=email,
        status=status
    ).select_related()[:100]  # Limit results

// Fixed: MongoDB with proper indexes
const mongoose = require('mongoose');

const orderSchema = new mongoose.Schema({
    customerEmail: {
        type: String,
        index: true  // Single field index
    },
    status: String,
    createdAt: {
        type: Date,
        index: true
    },
    items: [{
        productId: {
            type: String,
            index: true  // Index on nested field
        },
        quantity: Number
    }]
});

// Compound indexes for common queries
orderSchema.index({ customerEmail: 1, status: 1 });
orderSchema.index({ createdAt: -1 });
orderSchema.index({ 'items.productId': 1 });

// Text index for search
orderSchema.index({ description: 'text' });

const Order = mongoose.model('Order', orderSchema);

// Fixed: Query uses index
async function findOrdersByEmail(email) {
    // Uses customerEmail index
    return await Order.find({ customerEmail: email }).lean();
}

// Fixed: Text search using text index
async function searchOrders(term) {
    // Uses text index
    return await Order.find({ $text: { $search: term } }).lean();
}

// Fixed: Efficient query with index
async function findOrdersWithProduct(productId) {
    // Uses items.productId index
    return await Order.find({ 'items.productId': productId })
        .limit(100)  // Always limit results
        .lean();
}

// Fixed: Analyze query performance
async function analyzeQuery(email) {
    const explanation = await Order.find({ customerEmail: email })
        .explain('executionStats');

    console.log('Index used:', explanation.queryPlanner.winningPlan.inputStage);
    console.log('Documents examined:', explanation.executionStats.totalDocsExamined);
}

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.

CWE-1176: Inefficient CPU Computation (parent)
CWE-1060: Excessive Number of Inefficient Server-Side Data Accesses (related)
CWE-400: Uncontrolled Resource Consumption (can lead to)

References

MITRE Corporation. "CWE-1067: Excessive Execution of Sequential Searches of Data Resource." https://cwe.mitre.org/data/definitions/1067.html
Use The Index, Luke. "SQL Indexing Tutorial." https://use-the-index-luke.com/
MongoDB. "Indexing Strategies." https://docs.mongodb.com/manual/applications/indexes/

Excessive Execution of Sequential Searches of Data Resource

Description

Risk

Solution

Common Consequences

Example Code

Vulnerable Code

Fixed Code

CVE Examples

References

Platform

Resources

Company

Latest Articles

Excessive Execution of Sequential Searches of Data Resource

Description

Risk

Solution

Common Consequences

Example Code

Vulnerable Code

Fixed Code

CVE Examples

Related CWEs

References