Non-SQL Invokable Control Element with Excessive Number of Data Resource Accesses

Description

Non-SQL Invokable Control Element with Excessive Number of Data Resource Accesses occurs when a product contains a function or method that performs an excessive number of data accesses or queries through a data manager without using efficient database capabilities. CISQ recommends a maximum threshold of 2 data accesses per function/method as a baseline. When a single function makes numerous database calls, it indicates poor design that fails to leverage database capabilities like joins, stored procedures, or batch operations. This creates performance bottlenecks and resource consumption issues.

Risk

While primarily a performance issue, excessive data accesses have security implications. Functions with many database calls are susceptible to denial-of-service attacks that trigger the expensive code path. Database connection pool exhaustion can occur under load. Each database round-trip increases latency, making the system vulnerable to timeout-based attacks. The increased complexity makes security auditing more difficult. Poor performance under load may cause the system to fail to meet availability requirements. Race conditions become more likely with multiple non-atomic database operations.

Solution

Consolidate multiple queries into single queries using joins. Use database views for complex query patterns. Implement stored procedures for multi-step database operations. Use batch operations instead of individual queries in loops. Apply the repository pattern to encapsulate data access logic. Use caching for frequently accessed data. Implement pagination for large result sets. Profile database queries to identify inefficient patterns. Consider using ORMs with eager loading capabilities. Apply query optimization techniques.

Common Consequences

Impact	Details
Availability	Scope: Availability DoS: Resource Consumption - Multiple database calls per function consume excessive resources under load.
Availability	Scope: Availability Reduce Performance - Performance degrades significantly with each additional database round-trip.
Other	Scope: Other Quality Degradation - Excessive data accesses indicate poor architectural design.

Example Code

Vulnerable Code

// Vulnerable: Multiple database accesses in single method
public class VulnerableReportGenerator {

    // Vulnerable: More than 2 data accesses per method (CISQ threshold)
    public Report generateUserReport(Long userId) {
        // Access 1: Get user
        User user = userRepository.findById(userId);

        // Access 2: Get user profile
        UserProfile profile = profileRepository.findByUserId(userId);

        // Access 3: Get user settings
        Settings settings = settingsRepository.findByUserId(userId);

        // Access 4: Get user orders
        List<Order> orders = orderRepository.findByUserId(userId);

        // Access 5: Get payment methods
        List<PaymentMethod> payments = paymentRepository.findByUserId(userId);

        // Access 6: Get notifications
        List<Notification> notifications = notificationRepository.findByUserId(userId);

        // Access 7: Get activity log
        List<Activity> activities = activityRepository.findByUserId(userId);

        // Access 8: Get recommendations
        List<Recommendation> recommendations = recommendationRepository.findForUser(userId);

        // 8 database calls for one report!
        return new Report(user, profile, settings, orders,
                         payments, notifications, activities, recommendations);
    }
}

# Vulnerable: Excessive data accesses
class VulnerableOrderProcessor:

    def process_order(self, order_id):
        # Access 1: Get order
        order = self.db.query(Order).get(order_id)

        # Access 2: Get customer
        customer = self.db.query(Customer).get(order.customer_id)

        # Access 3: Verify customer status
        status = self.db.query(CustomerStatus).filter_by(
            customer_id=customer.id
        ).first()

        # Access 4: Get shipping address
        shipping = self.db.query(Address).get(order.shipping_address_id)

        # Access 5: Get billing address
        billing = self.db.query(Address).get(order.billing_address_id)

        # Access 6: Get payment method
        payment = self.db.query(PaymentMethod).get(order.payment_method_id)

        # Access 7: Check inventory for each item
        for item in order.items:
            # Access 8, 9, 10...: One query per item!
            inventory = self.db.query(Inventory).filter_by(
                product_id=item.product_id
            ).first()

        # Access N: Record transaction
        self.db.add(Transaction(order_id=order_id, status='processed'))

        # Access N+1: Update order status
        self.db.query(Order).filter_by(id=order_id).update({'status': 'processed'})

        # Potentially 10+ database accesses!
        return order

// Vulnerable: Many data accesses without efficient queries
public class VulnerableDashboardService
{
    public Dashboard GetUserDashboard(int userId)
    {
        // Access 1
        var user = _userRepo.GetById(userId);

        // Access 2
        var profile = _profileRepo.GetByUserId(userId);

        // Access 3
        var recentOrders = _orderRepo.GetRecentByUserId(userId);

        // Access 4
        var pendingTasks = _taskRepo.GetPendingByUserId(userId);

        // Access 5
        var notifications = _notificationRepo.GetUnreadByUserId(userId);

        // Access 6
        var favorites = _favoritesRepo.GetByUserId(userId);

        // Access 7
        var recommendations = _recommendationRepo.GetForUser(userId);

        // Access 8
        var statistics = _statsRepo.GetByUserId(userId);

        // Access 9, 10, 11... for each recent order
        foreach (var order in recentOrders)
        {
            order.Items = _orderItemRepo.GetByOrderId(order.Id);
        }

        // Many unnecessary round-trips to database!
        return new Dashboard(user, profile, recentOrders,
            pendingTasks, notifications, favorites, recommendations, statistics);
    }
}

// Vulnerable: Multiple sequential database calls
class VulnerableAnalyticsService {

    async generateAnalyticsReport(siteId) {
        // Access 1
        const site = await this.db.sites.findById(siteId);

        // Access 2
        const visitors = await this.db.visitors.count({ siteId });

        // Access 3
        const pageViews = await this.db.pageViews.count({ siteId });

        // Access 4
        const bounceRate = await this.db.sessions.aggregate([
            { $match: { siteId, bounced: true } },
            { $count: 'bounced' }
        ]);

        // Access 5
        const avgDuration = await this.db.sessions.aggregate([
            { $match: { siteId } },
            { $group: { _id: null, avg: { $avg: '$duration' } } }
        ]);

        // Access 6
        const topPages = await this.db.pageViews
            .find({ siteId })
            .sort({ views: -1 })
            .limit(10);

        // Access 7
        const referrers = await this.db.referrers
            .find({ siteId })
            .sort({ count: -1 })
            .limit(10);

        // Access 8
        const devices = await this.db.sessions.aggregate([
            { $match: { siteId } },
            { $group: { _id: '$device', count: { $sum: 1 } } }
        ]);

        // 8 separate database calls that could be combined
        return { site, visitors, pageViews, bounceRate, avgDuration,
                 topPages, referrers, devices };
    }
}

Fixed Code

// Fixed: Consolidated data access with joins and efficient queries
public class FixedReportGenerator {

    // Fixed: Single query using joins or optimized repository method
    public Report generateUserReport(Long userId) {
        // Fixed: One query with all necessary joins
        UserReportData data = userRepository.findUserWithReportData(userId);

        // OR: Use a view or stored procedure
        // UserReportData data = reportRepository.getUserReport(userId);

        return new Report(data);
    }
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    // Fixed: Single query with JOIN FETCH
    @Query("SELECT new com.example.UserReportData(" +
           "u, p, s, o, pm, n, a, r) " +
           "FROM User u " +
           "LEFT JOIN FETCH u.profile p " +
           "LEFT JOIN FETCH u.settings s " +
           "LEFT JOIN FETCH u.orders o " +
           "LEFT JOIN FETCH u.paymentMethods pm " +
           "LEFT JOIN FETCH u.notifications n " +
           "LEFT JOIN FETCH u.activities a " +
           "LEFT JOIN FETCH u.recommendations r " +
           "WHERE u.id = :userId")
    UserReportData findUserWithReportData(@Param("userId") Long userId);
}

// Alternative: Use stored procedure for complex reports
@Repository
public class ReportRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public UserReportData getUserReport(Long userId) {
        // Fixed: Single stored procedure call
        StoredProcedureQuery query = em.createStoredProcedureQuery(
            "sp_get_user_report", UserReportData.class);
        query.registerStoredProcedureParameter("userId", Long.class, ParameterMode.IN);
        query.setParameter("userId", userId);

        return (UserReportData) query.getSingleResult();
    }
}

# Fixed: Efficient data access with SQLAlchemy
from sqlalchemy.orm import joinedload, selectinload

class FixedOrderProcessor:

    def process_order(self, order_id):
        # Fixed: Single query with eager loading
        order = self.db.query(Order)\
            .options(
                joinedload(Order.customer).joinedload(Customer.status),
                joinedload(Order.shipping_address),
                joinedload(Order.billing_address),
                joinedload(Order.payment_method),
                selectinload(Order.items).joinedload(OrderItem.inventory)
            )\
            .get(order_id)

        # All related data loaded in 1-2 queries instead of 10+

        # Process with loaded data
        if not self._validate_order(order):
            raise ValidationError("Order validation failed")

        # Fixed: Batch update with single transaction
        with self.db.begin():
            # Update inventory in batch
            self._update_inventory_batch(order.items)

            # Record transaction and update order in same transaction
            self.db.add(Transaction(order_id=order_id, status='processed'))
            order.status = 'processed'

        return order

    def _update_inventory_batch(self, items):
        """Batch inventory update instead of per-item queries"""
        product_ids = [item.product_id for item in items]
        quantities = {item.product_id: item.quantity for item in items}

        # Fixed: Single update statement
        self.db.query(Inventory)\
            .filter(Inventory.product_id.in_(product_ids))\
            .update({
                Inventory.quantity: Inventory.quantity - case(
                    quantities,
                    value=Inventory.product_id
                )
            }, synchronize_session='fetch')

// Fixed: Efficient queries with Entity Framework
public class FixedDashboardService
{
    public async Task<Dashboard> GetUserDashboardAsync(int userId)
    {
        // Fixed: Single query with includes
        var user = await _context.Users
            .Include(u => u.Profile)
            .Include(u => u.Orders.OrderByDescending(o => o.CreatedAt).Take(10))
                .ThenInclude(o => o.Items)
            .Include(u => u.PendingTasks)
            .Include(u => u.Notifications.Where(n => !n.IsRead))
            .Include(u => u.Favorites)
            .Include(u => u.Statistics)
            .FirstOrDefaultAsync(u => u.Id == userId);

        // Fixed: Recommendations loaded separately if complex logic needed
        var recommendations = await _recommendationService.GetForUserAsync(userId);

        return new Dashboard
        {
            User = user,
            Profile = user.Profile,
            RecentOrders = user.Orders.ToList(),
            PendingTasks = user.PendingTasks.ToList(),
            Notifications = user.Notifications.ToList(),
            Favorites = user.Favorites.ToList(),
            Statistics = user.Statistics,
            Recommendations = recommendations
        };
    }
}

// Alternative: Use projection for read-only scenarios
public class FixedDashboardServiceProjection
{
    public async Task<DashboardDto> GetUserDashboardAsync(int userId)
    {
        // Fixed: Project only needed data in single query
        return await _context.Users
            .Where(u => u.Id == userId)
            .Select(u => new DashboardDto
            {
                UserName = u.Name,
                Email = u.Email,
                ProfileImage = u.Profile.ImageUrl,
                OrderCount = u.Orders.Count(),
                PendingTaskCount = u.PendingTasks.Count(),
                UnreadNotifications = u.Notifications.Count(n => !n.IsRead),
                RecentOrderSummaries = u.Orders
                    .OrderByDescending(o => o.CreatedAt)
                    .Take(5)
                    .Select(o => new OrderSummaryDto
                    {
                        Id = o.Id,
                        Total = o.Total,
                        Status = o.Status
                    }).ToList()
            })
            .FirstOrDefaultAsync();
    }
}

// Fixed: Aggregation pipeline for analytics
class FixedAnalyticsService {

    async generateAnalyticsReport(siteId) {
        // Fixed: Single aggregation pipeline
        const report = await this.db.sessions.aggregate([
            { $match: { siteId } },
            {
                $facet: {
                    // All metrics computed in single query
                    visitors: [
                        { $group: { _id: '$visitorId' } },
                        { $count: 'count' }
                    ],
                    pageViews: [
                        { $unwind: '$pageViews' },
                        { $count: 'count' }
                    ],
                    bounceRate: [
                        { $group: {
                            _id: null,
                            total: { $sum: 1 },
                            bounced: { $sum: { $cond: ['$bounced', 1, 0] } }
                        }},
                        { $project: {
                            rate: { $divide: ['$bounced', '$total'] }
                        }}
                    ],
                    avgDuration: [
                        { $group: { _id: null, avg: { $avg: '$duration' } } }
                    ],
                    devices: [
                        { $group: { _id: '$device', count: { $sum: 1 } } }
                    ]
                }
            }
        ]).toArray();

        // Fixed: Parallel queries for independent data
        const [topPages, referrers, site] = await Promise.all([
            this.db.pageViews
                .find({ siteId })
                .sort({ views: -1 })
                .limit(10)
                .toArray(),
            this.db.referrers
                .find({ siteId })
                .sort({ count: -1 })
                .limit(10)
                .toArray(),
            this.db.sites.findById(siteId)
        ]);

        // Reduced from 8 sequential calls to 2 parallel operations
        return this.formatReport(report[0], topPages, referrers, site);
    }
}

---

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 (parent)
• CWE-1060: Excessive Number of Inefficient Server-Side Data Accesses (related)
• CWE-1067: Excessive Execution of Sequential Searches (related)

---

References

1. MITRE Corporation. "CWE-1073: Non-SQL Invokable Control Element with Excessive Number of Data Resource Accesses." https://cwe.mitre.org/data/definitions/1073.html
2. CISQ. "Automated Source Code Quality Measures."
3. Fowler, Martin. "Patterns of Enterprise Application Architecture." - Repository Pattern