#47731 [W&A-Insight] Offer Listings N+1 Query Performance Vulnerability

Submitted on Jun 19th 2025 at 10:31:18 UTC by @Opzteam for IOP | Zano Trade

• Report ID: #47731

• Report Type: Websites & Apps

• Report severity: Insight

• Target: https://github.com/PRavaga/zano-p2p/tree/master/src/pages/dex/orders

• Impacts:

  • Taking down the application/website

Description

• The offers listing functionality contains a critical N+1 query performance vulnerability where individual database queries are executed inside loops for each offer, resulting in exponential query growth under load. The OffersModel.getPage method performs 4 separate database queries per offer (user lookup and 3 currency lookups), meaning a single page request with 15 offers triggers 61 database queries instead of 1-2 optimized queries. Under heavy traffic conditions typical of trading platforms, this inefficiency could cause severe performance degradation, database connection pool exhaustion, and potential service outages affecting the entire P2P trading marketplace.

• The performance vulnerability exists in the core offers listing functionality in api/models/Offers.ts, specifically in the getPage method:

// Single query to fetch offers (efficient)
const offers = (await Offer.findAll({
    where: OffersWhereConditions,
    order: [orderCondition],
    limit: 15,
    offset: (pageData.page - 1) * 15
}));

let offersWithUsers = [];

// N+1 query anti-pattern - loops through each offer
for (let offer of offers) {
    // Query 1: Individual user lookup per offer
    const user = await User.findOne({
        where: { id: offer.user_id }
    });

    // Query 2: Individual input currency lookup per offer  
    const inputCurrency = await configModel.getCurrencyRow(offer.input_currency_id);
    
    // Query 3: Individual target currency lookup per offer
    const targetCurrency = await configModel.getCurrencyRow(offer.target_currency_id);
    
    // Query 4: Individual deposit currency lookup per offer
    const depositCurrency = await configModel.getCurrencyRow(offer.deposit_currency_id);
    
    // Data assembly continues...
}

• Exponential Query Growth Pattern

Query Analysis per Request:

Base Query: 1 query to fetch offers (limit 15)
User Queries: 15 queries (1 per offer) = 15 queries
Currency Queries: 45 queries (3 per offer × 15 offers) = 45 queries
Total: 61 queries per page request

Offers per page: 15 → 61 total queries
If pagination shows 30 offers: 121 total queries  
If pagination shows 50 offers: 201 total queries

• The configModel.getCurrencyRow method performs individual database lookups:

// api/models/Config.ts
async getCurrencyRow(id: number) {
    const currency = await Currency.findOne({
        where: { id }
    });
    return currency;
}

• This method is called 3 times per offer, creating substantial redundancy since the same currencies are likely reused across multiple offers.

• The vulnerable method is exposed via a public API endpoint: This endpoint is accessed via /api/offers/get-page and is used throughout the frontend for displaying trading offers, making it a critical performance bottleneck.

// api/controllers/offers.controller.ts  
async getPage(req: Request, res: Response) {
    const result = await offersModel.getPage((req.body as GetPageBody).data);
    //  61 queries executed per request
}

Proof of Concept

1. Each page request consumes 61 database connections

2. Under moderate load (100 concurrent users), this could require 6,100 simultaneous database connections

3. Most database configurations have connection limits of 100-1000 connections

Database CPU usage increases exponentially with concurrent requests

Proof of Concept

# Simulate moderate load on offers endpoint
for i in {1..50}; do
  curl -X POST http://localhost:3000/api/offers/get-page \
    -H "Content-Type: application/json" \
    -d '{"data":{"page":1,"type":"buy"}}' &
done

# This would trigger: 50 requests × 61 queries = 3,050 database queries
# Likely causing significant performance degradation or timeouts

Remediation

• Immediate Fix: Implement Sequelize Associations and Includes

// api/schemes/Offer.ts - Add missing associations
import User from './User';
import Currency from './Currency';

// Add to Offer model definition
Offer.belongsTo(User, {
    foreignKey: 'user_id',
    as: 'user',
    onDelete: 'CASCADE',
    onUpdate: 'CASCADE'
});

Offer.belongsTo(Currency, {
    foreignKey: 'input_currency_id',
    as: 'input_currency',
    onDelete: 'RESTRICT',
    onUpdate: 'CASCADE'
});

Offer.belongsTo(Currency, {
    foreignKey: 'target_currency_id', 
    as: 'target_currency',
    onDelete: 'RESTRICT',
    onUpdate: 'CASCADE'
});

Offer.belongsTo(Currency, {
    foreignKey: 'deposit_currency_id',
    as: 'deposit_currency', 
    onDelete: 'RESTRICT',
    onUpdate: 'CASCADE'
});

• Optimize getPage Method with Single Query.

• Implement Query Result Caching