如何修复SQLSTATE [42000]错误; 用准备好的陈述

Question

I'm trying to create an email confirmation script.

Here is my PHP code:

...
$q = $dbh->prepare("INSERT INTO `email_confirm` (UserID,token,tokenDate) VALUES(:id, :token, UTC_TIMESTAMP()) ON DUPLICATE KEY UPDATE token = VALUES(:token), tokenDate = UTC_TIMESTAMP();");
$result = $q -> execute( array( ":id" => $this->id, ":token" => $token ) );
...

When this runs, I receive the following error:

 Caught exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?), tokenDate = UTC_TIMESTAMP()' at line 1

I'm no expert in MySQL, but I couldn't find any syntax errors in my code, and I would love some help.

Answer 1

As documented under PDO::prepare:

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 more than once in a prepared statement, unless emulation mode is on.

Whilst you could add a :token2 placeholder or similar that happens to be bound to the same value, actually MySQL's VALUES() function in the ON DUPLICATE KEY UPDATE clause takes a column name not a literal. Therefore this will do the trick:

$q = $dbh->prepare('
  INSERT INTO email_confirm
    (UserID, token, tokenDate)
  VALUES
    (:id, :token, UTC_TIMESTAMP())
  ON DUPLICATE KEY UPDATE
    token = VALUES(token),
    tokenDate = UTC_TIMESTAMP()
');

However, you may want to look into Automatic Initialization and Updating for TIMESTAMP and DATETIME, rather than trying to reimplement the wheel.