Invalid Parameter Number Error in PHP PDO

When attempting to execute a prepared statement using PDO, you may encounter the error "SQLSTATE[HY093]: Invalid parameter number." This issue arises due to the incorrect usage of parameter markers.

The provided function add_persist uses the following prepared statement:

INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash

When binding the parameter values using execute() method, the function uses the following values:

["user_id" => $user_id, "hash" => $hash, "expire" => $future]

However, the prepared statement contains a duplicate parameter marker for hash, which is not allowed by PDO. To resolve this issue, we need to assign a unique parameter marker for each value being passed.

The corrected code would be:

$sql = "INSERT INTO persist (user_id, hash, expire)
        VALUES (:user_id, :hash, :expire)
        ON DUPLICATE KEY UPDATE hash=:hash2";

$stm = $db->prepare($sql);

$stm->execute(
    array("user_id" => $user_id, 
          "hash" => $hash, 
          "expire" => $future,
          "hash2" => $hash)
);

The additional parameter :hash2 ensures that there are no duplicate parameter markers in the prepared statement, resolving the error.