Sql Queries In Loop

ID

php.sql_queries_in_loop

Severity

high

Remediation Complexity

trivial

Remediation Risk

low

Remediation Effort

low

Resource

Efficiency

Language

Php

Tags

efficiency, performance

Description

Reports a database query executed inside a loop body. The rule recognises the common procedural query functions (mysqli_query, mysql_query, pg_query, sqlite_query, odbc_exec, oci_execute, db2_exec) and the conventional query methods (query, exec, execute, prepare) called on a receiver that looks like a database handle (its variable or property name contains db, conn, pdo, stmt, wpdb, etc.). The method-call form only fires on a database-looking receiver, so unrelated objects such as $xpath→query() on a DOMXPath or a command-bus $handler→execute() are not reported.

Rationale

Issuing a query on every iteration creates the classic N+1 pattern: one round-trip per loop pass instead of one for the whole operation. Each round-trip carries network latency, query parsing and connection overhead, so a loop over a few hundred rows can turn a single fast request into hundreds of slow ones. Replacing the per-iteration query with one set-based statement — a join, an IN (…​) clause, or a single batched insert — collapses the cost back to a single round-trip.

<?php
function loadNames(array $ids, mysqli $conn): array
{
    $names = [];
    foreach ($ids as $id) {
        $res = mysqli_query($conn, "SELECT name FROM users WHERE id = $id"); // FLAW - one query per id
        $names[] = mysqli_fetch_assoc($res)['name'];
    }

    // OK - a single query for the whole set
    $list = implode(',', array_map('intval', $ids));
    $res = mysqli_query($conn, "SELECT name FROM users WHERE id IN ($list)");
    return $names;
}

Remediation

Pull the query out of the loop. Fetch every row the loop needs with a single set-based query (a join or an IN (…​) list) and iterate over the in-memory result, or collect the values and run one batched statement. When a prepared statement is unavoidable, prepare it once before the loop and only call execute() inside, rather than preparing on each pass.

References