Excessive Number of Inefficient Server-Side Data Accesses

Description

Excessive Number of Inefficient Server-Side Data Accesses occurs when software performs too many data queries without using efficient data processing functionality such as stored procedures, batch operations, or optimized query patterns. CISQ recommends a default threshold of 5 data queries maximum for an inefficient function or procedure. When code makes numerous individual database calls where fewer, more efficient calls would suffice, it creates performance bottlenecks and increases latency. This is commonly known as the "N+1 query problem" or "chatty" data access.

Risk

While primarily a performance issue, excessive data accesses have security implications. Attackers can exploit slow endpoints for denial-of-service attacks by triggering paths with many database queries. Database connection pool exhaustion can occur under load. Increased latency may cause timeouts leading to inconsistent states. The additional round-trips increase the window for race conditions. High database load can impact overall system availability. Chatty data access patterns also increase the attack surface for SQL injection if queries aren't properly parameterized.

Solution

Use batch operations instead of individual queries in loops. Implement eager loading or JOIN queries to fetch related data in single queries. Use stored procedures for complex multi-step operations. Implement query result caching. Use pagination for large result sets. Employ ORM features like eager loading, batch fetching, and query optimization. Set database query count limits and alerts. Use query profiling to identify N+1 problems. Consider denormalization for read-heavy operations. Use database connection pooling efficiently.

Common Consequences

Impact	Details
Availability	Scope: Availability DoS: Resource Consumption - Multiple inefficient queries consume database resources and can exhaust connection pools.
Availability	Scope: Availability Reduce Performance - Latency increases significantly with each additional database round-trip.
Other	Scope: Other Quality Degradation - Inefficient data access patterns make the system harder to scale and maintain.

Example Code

Vulnerable Code

// Vulnerable: N+1 query problem - one query per order item
public class VulnerableOrderService {

    public OrderDetails getOrderDetails(Long orderId) {
        // Query 1: Get order
        Order order = orderRepository.findById(orderId);

        // N queries: One query per item to get product details
        List<ItemDetails> itemDetails = new ArrayList<>();
        for (OrderItem item : order.getItems()) {
            // Vulnerable: Query in a loop!
            Product product = productRepository.findById(item.getProductId());

            // Another query per item for inventory
            Inventory inventory = inventoryRepository.findByProductId(item.getProductId());

            // Another query per item for pricing
            Pricing pricing = pricingRepository.findByProductId(item.getProductId());

            itemDetails.add(new ItemDetails(item, product, inventory, pricing));
        }

        // If order has 100 items: 1 + (100 * 3) = 301 queries!
        return new OrderDetails(order, itemDetails);
    }
}

# Vulnerable: Multiple queries in loop
class VulnerableUserService:

    def get_users_with_details(self, user_ids):
        users = []

        for user_id in user_ids:
            # Query 1: Get user
            user = self.db.query(User).get(user_id)

            # Query 2: Get profile (per user)
            profile = self.db.query(Profile).filter(
                Profile.user_id == user_id
            ).first()

            # Query 3: Get roles (per user)
            roles = self.db.query(Role).join(UserRole).filter(
                UserRole.user_id == user_id
            ).all()

            # Query 4: Get preferences (per user)
            preferences = self.db.query(Preference).filter(
                Preference.user_id == user_id
            ).all()

            users.append({
                'user': user,
                'profile': profile,
                'roles': roles,
                'preferences': preferences
            })

        # For 50 users: 50 * 4 = 200 queries!
        return users

// Vulnerable: Entity Framework lazy loading causing N+1
public class VulnerableProductController : Controller
{
    public IActionResult GetCatalog()
    {
        // Query 1: Get all products
        var products = _context.Products.ToList();

        var catalog = new List<CatalogItem>();

        foreach (var product in products)
        {
            // Lazy loading triggers query for each product's category
            var category = product.Category;  // Query N

            // Another lazy load for supplier
            var supplier = product.Supplier;  // Query N

            // Another for reviews
            var reviews = product.Reviews;  // Query N

            catalog.Add(new CatalogItem(product, category, supplier, reviews));
        }

        // 1000 products = 1 + (1000 * 3) = 3001 queries!
        return View(catalog);
    }
}

// Vulnerable: Sequential async calls instead of batch
async function vulnerableGetOrdersWithCustomers(orderIds) {
    const results = [];

    for (const orderId of orderIds) {
        // Query 1: Get order
        const order = await db.query('SELECT * FROM orders WHERE id = $1', [orderId]);

        // Query 2: Get customer for each order
        const customer = await db.query(
            'SELECT * FROM customers WHERE id = $1',
            [order.customer_id]
        );

        // Query 3: Get shipping address
        const address = await db.query(
            'SELECT * FROM addresses WHERE id = $1',
            [order.shipping_address_id]
        );

        // Query 4: Get order items
        const items = await db.query(
            'SELECT * FROM order_items WHERE order_id = $1',
            [orderId]
        );

        results.push({ order, customer, address, items });
    }

    // 100 orders = 100 * 4 = 400 queries executed sequentially!
    return results;
}

Fixed Code

// Fixed: Batch queries and eager loading
public class FixedOrderService {

    public OrderDetails getOrderDetails(Long orderId) {
        // Single query with JOINs to fetch all related data
        Order order = orderRepository.findByIdWithDetails(orderId);

        // All data loaded in one query via JPQL with JOIN FETCH
        return mapToOrderDetails(order);
    }

    // Using batch fetch for multiple orders
    public List<OrderDetails> getMultipleOrderDetails(List<Long> orderIds) {
        // Single query for all orders with all relationships
        List<Order> orders = orderRepository.findAllByIdWithDetails(orderIds);
        return orders.stream().map(this::mapToOrderDetails).collect(toList());
    }
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    // Fixed: Eager load all relationships in single query
    @Query("SELECT DISTINCT o FROM Order o " +
           "JOIN FETCH o.items i " +
           "JOIN FETCH i.product p " +
           "JOIN FETCH p.inventory " +
           "JOIN FETCH p.pricing " +
           "WHERE o.id = :orderId")
    Order findByIdWithDetails(@Param("orderId") Long orderId);

    // Fixed: Batch fetch for multiple orders
    @Query("SELECT DISTINCT o FROM Order o " +
           "JOIN FETCH o.items i " +
           "JOIN FETCH i.product p " +
           "WHERE o.id IN :orderIds")
    List<Order> findAllByIdWithDetails(@Param("orderIds") List<Long> orderIds);
}

# Fixed: Eager loading with SQLAlchemy
from sqlalchemy.orm import joinedload, selectinload

class FixedUserService:

    def get_users_with_details(self, user_ids):
        # Fixed: Single query with eager loading
        users = self.db.query(User)\
            .options(
                joinedload(User.profile),           # Eager load profile
                selectinload(User.roles),           # Batch load roles
                selectinload(User.preferences)      # Batch load preferences
            )\
            .filter(User.id.in_(user_ids))\
            .all()

        # Returns all data with just 1-4 queries total
        # (main query + one for each selectinload)
        return [{
            'user': user,
            'profile': user.profile,
            'roles': user.roles,
            'preferences': user.preferences
        } for user in users]


# Alternative: Using raw SQL with JOINs
class FixedUserServiceSQL:

    def get_users_with_details(self, user_ids):
        # Single query with JOINs
        query = """
            SELECT u.*, p.*, r.name as role_name
            FROM users u
            LEFT JOIN profiles p ON p.user_id = u.id
            LEFT JOIN user_roles ur ON ur.user_id = u.id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE u.id = ANY(:user_ids)
        """
        results = self.db.execute(query, {'user_ids': user_ids}).fetchall()
        return self._group_results(results)

// Fixed: Entity Framework eager loading
public class FixedProductController : Controller
{
    public IActionResult GetCatalog()
    {
        // Fixed: Eager load all relationships in single query
        var products = _context.Products
            .Include(p => p.Category)
            .Include(p => p.Supplier)
            .Include(p => p.Reviews)
            .ToList();

        var catalog = products.Select(p => new CatalogItem(
            p,
            p.Category,    // Already loaded
            p.Supplier,    // Already loaded
            p.Reviews      // Already loaded
        )).ToList();

        // Now only 1 query (with JOINs) instead of 3001
        return View(catalog);
    }

    // Alternative: Projection to DTO for better performance
    public IActionResult GetCatalogOptimized()
    {
        // Fixed: Query only needed columns
        var catalog = _context.Products
            .Select(p => new CatalogItemDto
            {
                ProductId = p.Id,
                ProductName = p.Name,
                CategoryName = p.Category.Name,
                SupplierName = p.Supplier.Name,
                ReviewCount = p.Reviews.Count,
                AverageRating = p.Reviews.Average(r => r.Rating)
            })
            .ToList();

        return View(catalog);
    }
}

// Fixed: Batch queries with JOINs
async function fixedGetOrdersWithCustomers(orderIds) {
    if (orderIds.length === 0) return [];

    // Fixed: Single query with JOINs
    const results = await db.query(`
        SELECT
            o.*,
            c.id as customer_id, c.name as customer_name, c.email,
            a.id as address_id, a.street, a.city, a.country
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        LEFT JOIN addresses a ON a.id = o.shipping_address_id
        WHERE o.id = ANY($1)
    `, [orderIds]);

    // Fixed: Batch fetch items for all orders at once
    const allItems = await db.query(`
        SELECT * FROM order_items
        WHERE order_id = ANY($1)
    `, [orderIds]);

    // Group items by order_id in application
    const itemsByOrder = allItems.reduce((acc, item) => {
        if (!acc[item.order_id]) acc[item.order_id] = [];
        acc[item.order_id].push(item);
        return acc;
    }, {});

    // Combine results
    return results.map(row => ({
        order: extractOrder(row),
        customer: extractCustomer(row),
        address: extractAddress(row),
        items: itemsByOrder[row.id] || []
    }));

    // Now only 2 queries total instead of 400!
}

// Fixed: Using DataLoader pattern for GraphQL-style batching
class OrderLoader {
    constructor(db) {
        this.db = db;
        this.customerLoader = new DataLoader(customerIds =>
            this.batchLoadCustomers(customerIds)
        );
        this.itemsLoader = new DataLoader(orderIds =>
            this.batchLoadItems(orderIds)
        );
    }

    async batchLoadCustomers(customerIds) {
        const customers = await this.db.query(
            'SELECT * FROM customers WHERE id = ANY($1)',
            [customerIds]
        );
        // Return in same order as requested ids
        return customerIds.map(id =>
            customers.find(c => c.id === id)
        );
    }

    async batchLoadItems(orderIds) {
        const items = await this.db.query(
            'SELECT * FROM order_items WHERE order_id = ANY($1)',
            [orderIds]
        );
        // Group by order_id
        return orderIds.map(orderId =>
            items.filter(item => item.order_id === orderId)
        );
    }
}

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-1120: Excessive Code Complexity (parent)
CWE-1226: Complexity Issues (category member)
CWE-400: Uncontrolled Resource Consumption (can lead to)

References

MITRE Corporation. "CWE-1060: Excessive Number of Inefficient Server-Side Data Accesses." https://cwe.mitre.org/data/definitions/1060.html
CISQ. "Automated Source Code Quality Measures."
Fowler, Martin. "OrmHate" - discussing N+1 query problems.

Excessive Number of Inefficient Server-Side Data Accesses

Description

Risk

Solution

Common Consequences

Example Code

Vulnerable Code

Fixed Code

CVE Examples

References

Platform

Resources

Company

Latest Articles

Excessive Number of Inefficient Server-Side Data Accesses

Description

Risk

Solution

Common Consequences

Example Code

Vulnerable Code

Fixed Code

CVE Examples

Related CWEs

References