通过PDO的SQL和事务的使用将不会执行

Question

I have been using PDO throughout my site; however, when it comes to updating a key in the auditkey table, the following (simplified method) will not successfully execute on the DB:

 try {
      $dbh = new PDO(etc...);
      $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      $currentKey = getCurrentKey(); //returns a SHA1 key
      beginTransaction($dbh); // not a typo, call to a wrapper function!
      $stmt = $dbh->prepare("UPDATE auditkey SET key=:nextKey");
      $stmt->bindValue(':nextKey', sha1($currentKey), PDO::PARAM_STR);
      $success = $stmt->execute();
      $dbh->commit();
    }

The error I get is the following:

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 'key='f6aa38e947482563913c56c2c72a59ea623c7dfb'' at line 1

When the UPDATE is amended and entered manually, the SQL works fine:

UPDATE `auditkey` SET `key`='f6aa38e947482563913c56c2c72a59ea623c7dfb';

Answer 1

The update query you posted at the end of your question is not an equivalent to the one you used in your prepared statement : in the hand-made query, the fields' names are enclosed in backticks.

This shouldn't change much for the auditkey field ; but it should change a lot for the key field, as key is a reserved word.

Try changing your prepared statement, to enclose the name of the key field in backticks :

$stmt = $dbh->prepare("UPDATE auditkey SET `key`=:nextKey");

As a reference, here is the list of Reserved words for MySQL.