Rule Definition
Running SQL queries inside a loop is considered a bad practice due to performance inefficiencies and increased strain on the database server. Each iteration of the loop initiates a separate query execution, leading to a high volume of database requests that can degrade system performance, increase network traffic, and potentially cause resource contention. This approach significantly impacts the overall efficiency and scalability of the application, especially when dealing with large datasets or frequent iterations.

Remediation
It is generally possible to remediate this issue, using more complex SQL queries, like using JOIN or using subqueries to retrieve some information that are used in the global SQL query.

Violation Code Sample

using System;
using System.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        string[] userIds = { "user1", "user2", "user3" };
        string connectionString = "Your_DB_Connection_String";

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();

            foreach (var userId in userIds)
            {
                string query = $"SELECT * FROM Users WHERE UserID = '{userId}'";
                SqlCommand command = new SqlCommand(query, connection);
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    // Process the retrieved data
                    Console.WriteLine($"User ID: {reader["UserID"]}, Name: {reader["Name"]}");
                }

                reader.Close();
            }

            connection.Close();
        }
    }
}

Fixed Code Sample

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main(string[] args)
    {
        string[] userIds = { "user1", "user2", "user3" };
        string connectionString = "Your_DB_Connection_String";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string query = $"SELECT * FROM Users WHERE UserID IN ('{string.Join("','", userIds)}')";
            SqlCommand command = new SqlCommand(query, connection);
            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                // Process the retrieved data
                Console.WriteLine($"User ID: {reader["UserID"]}, Name: {reader["Name"]}");
            }

            reader.Close();
            connection.Close();
        }
    }
}

CWE-1050: Excessive Platform Resource Consumption within a Loop https://cwe.mitre.org/data/definitions/1050.html OMG ASCPEM ASCPEM-PRF-8 https://www.omg.org/spec/ASCPEM/1.0/PDF page 31