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