PHP PDOException: "SQLSTATE[HY093]: Invalid parameter number"

Question

I'm getting the error "SQLSTATE[HY093]: Invalid parameter number" when I try to run the below function:

function add_persist($db, $user_id) {
    $hash = md5("per11".$user_id."sist11".time());
    $future = time()+(60*60*24*14);
    $sql = "INSERT INTO persist (user_id, hash, expire) VALUES (:user_id, :hash, :expire) ON DUPLICATE KEY UPDATE hash=:hash";
    $stm = $db->prepare($sql);
    $stm->execute(array(":user_id" => $user_id, ":hash" => $hash, ":expire" => $future));
    return $hash;
}

I feel like it's something simple that I'm just not catching. Any ideas?

Answer 1

Try:

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

and

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

Excerpt from the documentation (http://php.net/manual/en/pdo.prepare.php):

You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name twice in a prepared statement. You cannot bind multiple values to a single named parameter in, for example, the IN() clause of an SQL statement.

Answer 2

This is one limitation to using PDO. PDO simply acknowledges the number of parameters in the query and the execution and throws an error on any mismatch. If you need to use parameter repetition in your queries, you have to go about it using a workaround

$sql = "insert into persist(user_id, hash, expire) values
    (:user_id, :hash, :value) on duplicate key update
    hash = :hash2";
$stm->execute(array(':user_id' => $user_id, ':hash' => $hash, ':hash2' => $hash,
    ':expire' => $expire));

You can refer to this for a more elaborate workaround - https://stackoverflow.com/a/7604080/1957346

Answer 3

I know this is an old question, however I think it's worth noting that a more appropriate solution would be to avoid clunky workarounds in PHP by leveraging SQL appropriately:

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

This way, you only need to send the value once.