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

One of the more useful parts of SQL is the usage of the IN statement in the WHERE clause to create a query that is easy to read and execute.

Unfortunately with prepared statements we can’t exactly bind anything to an IN statement ahead of time if the size of a data structure (array) is undetermined at the time of execution unless those values are listed one at a time.

So what I did today in PHP was essentially the same syntax to accomplish this as I’ve done previously in Java/C# – details below.

<?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 [];
    }
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s