Data Resource Access without Use of Connection Pooling

Description

Data Resource Access without Use of Connection Pooling occurs when a product accesses a data resource through a database without implementing connection pooling capability. Creating a new database connection for each request involves significant overhead: TCP handshake, authentication, session initialization, and resource allocation. Connection pools maintain a cache of reusable connections, dramatically reducing this overhead. Without pooling, applications waste resources repeatedly creating and destroying connections, leading to performance degradation and potential resource exhaustion.

Risk

While primarily a performance issue, lack of connection pooling has security implications. Resource exhaustion from creating too many connections can lead to denial of service. Connection limits may be hit, preventing legitimate users from accessing the system. The overhead of creating connections increases latency, making the system more vulnerable to slowloris-style attacks. Under load, connection creation failures may trigger error paths that leak information or fail insecurely. Abandoned connections due to errors can cause connection leaks, gradually exhausting the connection limit.

Solution

Use connection pooling libraries appropriate for your platform (HikariCP, c3p0 for Java; pg-pool for Node.js; SQLAlchemy pool for Python). Configure appropriate pool sizes based on expected load. Implement connection validation to detect stale connections. Set proper connection timeouts. Use connection pool monitoring to detect leaks and issues. Consider using container-managed connection pools in application servers. Configure maximum connection age to prevent connection staleness. Implement proper connection cleanup in error handlers.

Common Consequences

Impact	Details
Availability	Scope: Availability DoS: Resource Consumption - Repeated connection creation/destruction wastes resources and can exhaust limits.
Availability	Scope: Availability Reduce Performance - Connection creation overhead significantly degrades throughput and latency.
Other	Scope: Other Quality Degradation - Application scalability is severely limited without connection pooling.

Example Code

Vulnerable Code

// Vulnerable: Creating new connection for each request
public class VulnerableUserRepository {

    private static final String URL = "jdbc:mysql://localhost:3306/users";
    private static final String USER = "app_user";
    private static final String PASSWORD = "secret";

    // Vulnerable: New connection created for every method call
    public User findById(Long id) {
        Connection conn = null;
        try {
            // Vulnerable: Creates new connection each time
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            PreparedStatement stmt = conn.prepareStatement(
                "SELECT * FROM users WHERE id = ?"
            );
            stmt.setLong(1, id);

            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return mapUser(rs);
            }
            return null;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // Connection is closed, not returned to pool
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // Connection leak if close fails
                }
            }
        }
    }

    // Same pattern repeated for every method - very inefficient!
    public List<User> findAll() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            // ...
        } finally {
            if (conn != null) {
                try { conn.close(); } catch (SQLException e) {}
            }
        }
        return null;
    }
}

# Vulnerable: Creating new connection per request
import psycopg2

class VulnerableOrderService:

    def __init__(self):
        self.db_config = {
            'host': 'localhost',
            'database': 'orders',
            'user': 'app_user',
            'password': 'secret'
        }

    def get_order(self, order_id):
        # Vulnerable: New connection for each call
        conn = psycopg2.connect(**self.db_config)
        try:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT * FROM orders WHERE id = %s",
                (order_id,)
            )
            return cursor.fetchone()
        finally:
            conn.close()  # Connection destroyed, not pooled

    def get_all_orders(self, customer_id):
        # Vulnerable: Another new connection
        conn = psycopg2.connect(**self.db_config)
        try:
            cursor = conn.cursor()
            cursor.execute(
                "SELECT * FROM orders WHERE customer_id = %s",
                (customer_id,)
            )
            return cursor.fetchall()
        finally:
            conn.close()

    def create_order(self, order_data):
        # Vulnerable: Yet another connection
        conn = psycopg2.connect(**self.db_config)
        try:
            cursor = conn.cursor()
            # ... insert order
            conn.commit()
        finally:
            conn.close()

// Vulnerable: No connection pooling in Node.js
const mysql = require('mysql');

class VulnerableProductService {

    async getProduct(productId) {
        // Vulnerable: Creates new connection each time
        const connection = mysql.createConnection({
            host: 'localhost',
            user: 'app_user',
            password: 'secret',
            database: 'products'
        });

        return new Promise((resolve, reject) => {
            connection.connect((err) => {
                if (err) reject(err);

                connection.query(
                    'SELECT * FROM products WHERE id = ?',
                    [productId],
                    (error, results) => {
                        connection.end();  // Connection destroyed
                        if (error) reject(error);
                        resolve(results[0]);
                    }
                );
            });
        });
    }

    // Each method creates its own connection
    async getAllProducts() {
        const connection = mysql.createConnection({/*...*/});
        // ... same pattern
    }
}

// Vulnerable: No connection pooling in C#
public class VulnerableCustomerRepository
{
    private readonly string _connectionString =
        "Server=localhost;Database=customers;User Id=app;Password=secret;";

    // Vulnerable: Disabling connection pooling
    public Customer GetById(int id)
    {
        // Vulnerable: Pooling=false disables pooling
        using (var conn = new SqlConnection(_connectionString + "Pooling=false;"))
        {
            conn.Open();

            using (var cmd = new SqlCommand("SELECT * FROM Customers WHERE Id = @id", conn))
            {
                cmd.Parameters.AddWithValue("@id", id);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return MapCustomer(reader);
                    }
                }
            }
        }
        return null;
    }

    // Vulnerable: Creating connection without pool configuration
    public void SaveCustomer(Customer customer)
    {
        var conn = new SqlConnection(_connectionString);
        try
        {
            conn.Open();
            // ... save logic
        }
        finally
        {
            conn.Dispose();  // Not returning to pool
        }
    }
}

Fixed Code

// Fixed: Using HikariCP connection pool
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class FixedUserRepository {

    private static final HikariDataSource dataSource;

    static {
        // Fixed: Configure connection pool once
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/users");
        config.setUsername("app_user");
        config.setPassword("secret");

        // Pool configuration
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setIdleTimeout(300000);       // 5 minutes
        config.setConnectionTimeout(10000);   // 10 seconds
        config.setMaxLifetime(1800000);       // 30 minutes

        // Connection validation
        config.setConnectionTestQuery("SELECT 1");
        config.setValidationTimeout(5000);

        dataSource = new HikariDataSource(config);
    }

    // Fixed: Get connection from pool, return automatically via try-with-resources
    public User findById(Long id) {
        // Connection borrowed from pool, returned when closed
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "SELECT * FROM users WHERE id = ?")) {

            stmt.setLong(1, id);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return mapUser(rs);
                }
            }
            return null;

        } catch (SQLException e) {
            throw new RepositoryException("Failed to find user: " + id, e);
        }
    }

    public List<User> findAll() {
        // Fixed: Reuses pooled connection
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {

            List<User> users = new ArrayList<>();
            while (rs.next()) {
                users.add(mapUser(rs));
            }
            return users;

        } catch (SQLException e) {
            throw new RepositoryException("Failed to retrieve users", e);
        }
    }

    // Shutdown pool when application terminates
    public static void shutdown() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
        }
    }
}

# Fixed: Using SQLAlchemy with connection pooling
from sqlalchemy import create_engine, text
from sqlalchemy.pool import QueuePool
from contextlib import contextmanager

class FixedOrderService:

    def __init__(self):
        # Fixed: Engine manages connection pool
        self.engine = create_engine(
            'postgresql://app_user:secret@localhost/orders',
            poolclass=QueuePool,
            pool_size=10,           # Number of persistent connections
            max_overflow=20,        # Extra connections when pool exhausted
            pool_timeout=30,        # Seconds to wait for connection
            pool_recycle=1800,      # Recycle connections after 30 min
            pool_pre_ping=True      # Verify connection before use
        )

    @contextmanager
    def get_connection(self):
        """Context manager for connection handling"""
        conn = self.engine.connect()
        try:
            yield conn
        finally:
            conn.close()  # Returns to pool, doesn't destroy

    def get_order(self, order_id):
        # Fixed: Connection from pool
        with self.get_connection() as conn:
            result = conn.execute(
                text("SELECT * FROM orders WHERE id = :id"),
                {"id": order_id}
            )
            return result.fetchone()

    def get_all_orders(self, customer_id):
        # Fixed: Reuses pooled connection
        with self.get_connection() as conn:
            result = conn.execute(
                text("SELECT * FROM orders WHERE customer_id = :id"),
                {"id": customer_id}
            )
            return result.fetchall()

    def create_order(self, order_data):
        # Fixed: Transaction with pooled connection
        with self.engine.begin() as conn:  # Auto-commit/rollback
            conn.execute(
                text("INSERT INTO orders (customer_id, total) VALUES (:cid, :total)"),
                {"cid": order_data['customer_id'], "total": order_data['total']}
            )


# Alternative: Using psycopg2 pool directly
from psycopg2 import pool

class FixedOrderServicePsycopg:

    def __init__(self):
        # Fixed: Connection pool
        self.pool = pool.ThreadedConnectionPool(
            minconn=5,
            maxconn=20,
            host='localhost',
            database='orders',
            user='app_user',
            password='secret'
        )

    def get_order(self, order_id):
        conn = self.pool.getconn()
        try:
            with conn.cursor() as cursor:
                cursor.execute(
                    "SELECT * FROM orders WHERE id = %s",
                    (order_id,)
                )
                return cursor.fetchone()
        finally:
            self.pool.putconn(conn)  # Return to pool

// Fixed: Using mysql2 pool in Node.js
const mysql = require('mysql2/promise');

class FixedProductService {

    constructor() {
        // Fixed: Create connection pool
        this.pool = mysql.createPool({
            host: 'localhost',
            user: 'app_user',
            password: 'secret',
            database: 'products',
            waitForConnections: true,
            connectionLimit: 20,
            maxIdle: 10,
            idleTimeout: 60000,
            queueLimit: 0,
            enableKeepAlive: true,
            keepAliveInitialDelay: 0
        });
    }

    async getProduct(productId) {
        // Fixed: Get connection from pool
        const connection = await this.pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM products WHERE id = ?',
                [productId]
            );
            return rows[0];
        } finally {
            connection.release();  // Return to pool
        }
    }

    // Fixed: Even simpler - pool.execute handles connection automatically
    async getAllProducts() {
        const [rows] = await this.pool.execute(
            'SELECT * FROM products'
        );
        return rows;
    }

    async createProduct(product) {
        const connection = await this.pool.getConnection();
        try {
            await connection.beginTransaction();

            await connection.execute(
                'INSERT INTO products (name, price) VALUES (?, ?)',
                [product.name, product.price]
            );

            await connection.commit();
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }

    async shutdown() {
        await this.pool.end();
    }
}

// Fixed: Using connection pooling in C#
public class FixedCustomerRepository
{
    // Fixed: Connection string with pooling enabled (default)
    private readonly string _connectionString =
        "Server=localhost;Database=customers;User Id=app;Password=secret;" +
        "Min Pool Size=5;Max Pool Size=100;Connection Timeout=30;";

    public Customer GetById(int id)
    {
        // Fixed: Connection automatically uses pool
        // 'using' statement returns connection to pool
        using (var conn = new SqlConnection(_connectionString))
        {
            conn.Open();  // Gets connection from pool

            using (var cmd = new SqlCommand(
                "SELECT * FROM Customers WHERE Id = @id", conn))
            {
                cmd.Parameters.AddWithValue("@id", id);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return MapCustomer(reader);
                    }
                }
            }
        }  // Connection returned to pool here
        return null;
    }

    // Fixed: Async with pooling
    public async Task<Customer> GetByIdAsync(int id)
    {
        await using var conn = new SqlConnection(_connectionString);
        await conn.OpenAsync();

        await using var cmd = new SqlCommand(
            "SELECT * FROM Customers WHERE Id = @id", conn);
        cmd.Parameters.AddWithValue("@id", id);

        await using var reader = await cmd.ExecuteReaderAsync();
        if (await reader.ReadAsync())
        {
            return MapCustomer(reader);
        }
        return null;
    }
}

// Fixed: Using Entity Framework with built-in pooling
public class FixedDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            "Server=localhost;Database=customers;...",
            options => options
                .EnableRetryOnFailure(maxRetryCount: 3)
                .CommandTimeout(30)
        );

        // EF Core 2.0+: DbContext pooling
        // Configured in Startup.cs:
        // services.AddDbContextPool<FixedDbContext>(options => ...);
    }
}

---

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-400: Uncontrolled Resource Consumption (can lead to)
• CWE-410: Insufficient Resource Pool (related)

---

References

1. MITRE Corporation. "CWE-1072: Data Resource Access without Use of Connection Pooling." https://cwe.mitre.org/data/definitions/1072.html
2. HikariCP. "Fast, Simple, Reliable Connection Pool." https://github.com/brettwooldridge/HikariCP
3. Microsoft. "SQL Server Connection Pooling." https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling