SQL and PHP – Crafting a Programmatic Query that Safely Binds an Array for usage with an IN statement

<?php

class ExampleClass
{
	
    /**
     * Get a list of messages that have not been processed yet optionally passing in values we want to ignore
     *
     * @throws Exception
     * @return array
     */
    public function getMessagesToProcess(?array $idValuesToIgnore) : array
    {
        // Optionally we can provide id values to ignore so we don't get them again.
        //
        // If there are no values in the array then a blank space is the only thing added to the where clause.
        $sqlInStatement = "";

        // SQL doesn't have native support for IN statements, so we have to do it manually 
        // but in a way that doesn't expose us to injection attacks.
        // 
        // Reference:
        // - https://stackoverflow.com/questions/37209686/doctrine-how-to-bind-array-to-the-sql
        if(!empty($idValuesToIgnore) && is_array($idValuesToIgnore) && count($idValuesToIgnore) != 0)
        {
            $sqlInStatementPrefix = "AND id NOT IN (";
            $sqlInStatementSuffix = ")";
            $sqlInStatementContent = null;
            
            $currentPosition = 1;
            $totalParametersToGenerate = count($idValuesToIgnore);

            while($currentPosition <= $totalParametersToGenerate)
            {
                $statementToAppend = ":id_" . $currentPosition;

                if(empty($sqlInStatementContent))
                {
                    $sqlInStatementContent = $statementToAppend;
                }
                else
                {
                    $sqlInStatementContent = "$sqlInStatementContent, $statementToAppend";
                }

                $currentPosition++;
            }

            $sqlInStatement = $sqlInStatementPrefix . $sqlInStatementContent . $sqlInStatementSuffix;
        }

        $sql = "SELECT
                    `id`
                FROM `Schema`.`Table`
                WHERE
                    `Table`.`processed_at` IS NULL
                    $sqlInStatement";

        $query = $this->db->prepare($sql);
        
        // Bind the values if there were any passed in as part of the constructed IN SQL earlier in the function
        if(!empty($idValuesToIgnore) && is_array($idValuesToIgnore) && count($idValuesToIgnore) != 0)
        {
            $currentPosition = 1;
        
            foreach($idValuesToIgnore as $id)
            {
                $parameterToBind = ":id_" . $currentPosition;
                $query->bindValue($parameterToBind, $id, PDO::PARAM_INT);
                $currentPosition++;
            }
        }
        
        try {
            $query->execute();

            $rowCount = $query->rowCount();

            if($rowCount != 0) {
                return $query->fetchAll();
            }
        } catch (\Exception|\Throwable $e) {
            $this->logger->error($e);
        }

        return [];
    }
}