db_query() PDOException (no parameters were bound)

Question

I have a relatively simple query that is executed via db_query()->execute; For some reason this causes a PDO Error, which I assume has to do with the way drupal handles the input. Any ideas on what causes this error and how to avoid it?

db_query('
    INSERT INTO {account_floodprotect} (ipaddress, soapcount, mailcount)
    VALUES (:ipaddress, :soapcount, :mailcount)
    ON DUPLICATE KEY UPDATE
    soapcount = soapcount + :soapcount,
    mailcount = mailcount + :mailcount',
  array(
    ':ipaddress' => $ip,
    ':soapcount' => $soapcount,
    ':mailcount' => $mailcount,
  ))->execute();

/*

ERROR CAUSED:

PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound

The table involved:

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ipaddress  | varchar(255)     | NO   | UNI |         |                |
| soapcount  | int(10) unsigned | YES  |     | 0       |                |
| mailcount  | int(10) unsigned | YES  |     | 0       |                |
| updatetime | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

Answer 1

Don't need execute()，

db_query('
    INSERT INTO {account_floodprotect} (ipaddress, soapcount, mailcount)
    VALUES (:ipaddress, :soapcount, :mailcount)
    ON DUPLICATE KEY UPDATE
    soapcount = soapcount + :soapcount,
    mailcount = mailcount + :mailcount',
  array(
    ':ipaddress' => $ip,
    ':soapcount' => $soapcount,
    ':mailcount' => $mailcount,
  ));

Answer 2

I think it'll be the parameters after ON DUPLICATE KEY UPDATE that are causing the problem; db_query() is fairly picky about which parts of the query string it will or won't do parameter replacements for.

The simplest way around it is to use the db_merge() function. Your query would look something like this:

db_merge('account_floodprotect')
  ->key('id')
  ->fields(array(
    'ipaddress' => $ipaddress,
    'soapcount' => $soapcount,
    'mailcount' => $mailcount
  ))
  ->expression('soapcount', 'soapcount + :soapcount', array(':soapcount' => $soapcount))
  ->expression('mailcount', 'mailcount + :mailcount', array(':mailcount' => $mailcount))
  ->execute();