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?

You cannot use a named parameter marker of the same name twice in a prepared statement. http://php.net/manual/en/pdo.prepare.php — bitWorking
– bitWorking, Commented Aug 3, 2013 at 2:42
I understand that that post has an answer that answers my question as well, but my question definitely isn't a possible duplicate. — vijrox
– vijrox, Commented Aug 3, 2013 at 2:50
In the other question, fetchAll() returns nothing because the query failed just as yours. The other author just did not notice it. This is essentially the same problem, so it is worth linking these two. — RandomSeed
– RandomSeed, Commented Aug 3, 2013 at 2:58
yes they are linked in that the answers are the same, but there is no way of knowing that based on the questions — vijrox
– vijrox, Commented Aug 3, 2013 at 7:32

Tony · Accepted Answer · 2020-08-11 11:34:21Z

57

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.

edited Aug 11, 2020 at 11:34

Tony

10.4k3 gold badges51 silver badges78 bronze badges

answered Aug 3, 2013 at 2:40

vee

38.7k7 gold badges76 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

vijrox Over a year ago

it worked! I'll accept your answer as soon as stack lets me. Why doesn't the original method work?

vee Over a year ago

@VijayRamamurthy, please read through this: php.net/manual/en/pdo.prepare.php

Shafizadeh Over a year ago

What is the difference between your answer and OP's question?

Community · Accepted Answer · 2017-05-23 12:10:45Z

18

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

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered Aug 3, 2013 at 2:45

Achrome

7,82114 gold badges38 silver badges45 bronze badges

1 Comment

Juha Untinen Over a year ago

Awesome! I spent two days debugging code that worked on everything else except one particular query. The answer was (as mentioned by "vijrox" above in a comment) that you cannot reuse the same named parameter in PDO. I had UPDATE users SET username = :username, caption = :caption WHERE username = :username, which will not work because :username appears twice. The fix was to rename the second one, ending up with this: UPDATE users SET username = :username, caption = :caption WHERE username = :user (the last :user is different).

Duncan · Accepted Answer · 2017-04-04 05:54:54Z

6

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.

answered Apr 4, 2017 at 5:54

Duncan

2,09413 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Related