Problem with binding NULL value to named placeholders with associative array in execute function in PDO

I am trying to construct and execute a delete query when the table_name and the conditional_clauses are passed as parameters in OOP fashion. I am using PDO by wrapping it in a custom wrapper. I am using prepared statements, with named placeholders. In every case, I am passing an associative array inside PDO->execute() function, where the array_keys are the name of the placeholders used and the array_value are the corresponding values to be substituted. I am facing issues only in one case when I want to specify an IS NULL condition with WHERE clause.

Basically, if I want to search for something like:

SELECT * FROM EMPLOYEES WHERE salary > 10000 AND skill IS NULL

I am able to dynamically construct a prepared statement which looks like:

$sql = SELECT * FROM employees WHERE salary > :salary AND skill IS :skill

And then execute the prepared SQL as:

$stmt->execute(["salary" => 10000,
                "skill" => null])

This is where I am facing the issue. I am getting a fatal error here only when a value is null. And, I want to include checking for IS NULL functionality in my wrapper.

Please note –

I want to achieve the purpose without using bindValue() or bindParam() functions.
I have turned emulation off (as MySQL can sort all placeholders out
properly).
Using ? as placeholders isn’t an option for me. I’ll have to
re-design my entire codebase otherwise.

Here’s the code snippet for reference:

<?php
class DeleteQuery {
        protected function where(array $whereCondition, array &$values): string{
        $whereClause = ' WHERE ';
        $i = 0;
        $j = 0;

        $hasComparators = array_key_exists("comparators", $whereCondition);
        $hasConjunctions = array_key_exists("conjunctions", $whereCondition);

        $comparatorCount = $hasComparators ? count($whereCondition["comparators"]) : 0;
        $conjunctionCount = $hasConjunctions ? count($whereCondition["conjunctions"]) : 0;

        foreach ($whereCondition["predicates"] as $predicate_key => &$predicate_value) {
            $whereClause .= $predicate_key;

            $whereClause .= ($hasComparators and ($i < $comparatorCount)) ?
            ' ' . $whereCondition["comparators"][$i++] . ' ' : ' = ';

            if (is_array($predicate_value)) {
                $whereClause .= "('" . implode("', '", $predicate_value) . "')";
                unset($whereCondition['predicates'][$predicate_key]);
            } else {
                $whereClause .= ':' . $predicate_key;
            }

            $whereClause .= !($hasConjunctions and ($j < $conjunctionCount)) ?
            '' : ' ' . $whereCondition["conjunctions"][$j++] . ' ';
        }

        $values = array_merge($values, $whereCondition['predicates']);

        return $whereClause;
    }

    public function delete($tblName, $conditions) {
        $sql = "DELETE FROM " . $tblName;
        $values = [];

        if (!empty($conditions) && is_array($conditions)) {
            /* If the stmt has WHERE clause */
            if (array_key_exists("where", $conditions)) {
                $sql .= $this->where($conditions['where'], $values);
            }

            /* If the stmt has ORDER BY clause */
            if (array_key_exists("order_by", $conditions)) {
                $sql .= $this->order_by($conditions['order_by']);
            }

            /* If the stmt has LIMIT clause */
            if (array_key_exists("limit", $conditions)) {
                $sql .= $this->limit($conditions['limit'], $values);
            }
        }

        echo $sql . PHP_EOL;
        print_r($values);
    }
}

$deleteConditions = [
    "where" => array(
        "predicates" => ["skill" => null],
        "comparators" => ["IS"],
    ),

    /* other conditional clauses */

];
$obj = new DeleteQuery();
$obj->delete("employees", $deleteConditions);

Answer

The IS operator can’t be used with an expression. IS NULL and IS NOT NULL are keywords.

You need a test that works with both null and non-null values of :skill. You can use the null-safe equality operator, <=>

$sql = 'SELECT * 
        FROM employees 
        WHERE salary > :salary 
        AND skill <=> :skill';

Advertisement

Answer