php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #60515 PDO_MYSQL: "Invalid parameter number" although it is correct
Submitted: 2011-12-13 20:57 UTC Modified: 2013-10-15 11:54 UTC
Votes:25
Avg. Score:4.4 ± 1.0
Reproduced:22 of 22 (100.0%)
Same Version:9 (40.9%)
Same OS:1 (4.5%)
From: phoenixseve at freenet dot de Assigned:
Status: No Feedback Package: PDO related
PHP Version: 5.3.8 OS: archlinux x86_64
Private report: No CVE-ID: None
View Developer Edit
 [2011-12-13 20:57 UTC] phoenixseve at freenet dot de 
Description:
------------
When I execute the attached test script an exception is thrown with the message:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

The exception is raised in the execute() line.

If you change the WHERE clause to `id`=24 there is no error message.
The same is true for this query: UPDATE `edtable` SET `id`=:p0 WHERE `id`='24'

The generated error message is obviously not correct. I don't even see why an error message is generated as the request seems valid (although strange) to me.

Test script:
---------------
$createTableSql = <<<'EOT'
DROP TABLE IF EXISTS `edtable`;
CREATE TABLE IF NOT EXISTS `edtable` (
  `id` bigint(20) NOT NULL,
  `coun't()` varchar(20) NOT NULL
);
EOT;

  $pdo = new PDO('mysql:host=localhost;dbname=aynte','aynte','aynte');
  $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

  $result = $pdo->query($createTableSql);
  $result->closeCursor();

  $stmt = $pdo->prepare("UPDATE `edtable` SET `id`=:p0, `coun't()`= :p1 WHERE `id`='24'");
  $stmt->execute(array(':p0'=>'2', ':p1'=>'b2' ));

Expected result:
----------------
No error message.

Actual result:
--------------
PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in /srv/http/test.php:19\nStack trace:\n#0 /srv/http/test.php(19): PDOStatement->execute(Array)\n#1 {main}\n  thrown in /srv/http/test.php on line 19

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-03-06 02:35 UTC] jeffvanb at u dot washington dot edu 
This misleading error message is also thrown when you include a single-line 
comment that contains a single-quote. 

Example:

SELECT something
-- This valid SQL syntax shouldn't be a problem
FROM somewhere
WHERE value = :v;

PDO will tell you the parameter count is mismatched and you will pull your hair 
out wondering what is wrong.
 [2012-05-02 11:21 UTC] uw@php.net 
$stmt = $pdo->prepare("UPDATE `edtable` SET `id`=:p0, `coun't()`= :p1 WHERE `id`='24'");

Your SQL is invalid. 

Whatever is behind, parsing is done by the PDO core. Thus, this is most unlikely to be MySQL specific. Parsing needs to be done as a syntax is used that is not supported by MySQL. Replace MySQL here with any other DB that does not support named parameters or do the same with questionmarks and a DB that does support named parameters only but no questionmarks.
 [2012-09-17 20:12 UTC] willfitch@php.net 
After removing the invalid quote from your SQL statement, there is no issue here. 
I tried this with MySQL and PostgreSQL drivers.  Please confirm.
 [2012-09-17 20:55 UTC] phoenixseve at freenet dot de 
You're right, using WHERE `id`=24 works.

But the following query works on the command line client of MySQL:
mysql> UPDATE `edtable` SET`id`=1 WHERE `id`='24';
Query OK, 1 row affected (0.19 sec)
Rows matched: 1  Changed: 1  Warnings: 0


Same is true for Postgres 9.1:
postgres=# UPDATE "edtable" SET "id"=1 WHERE "id"='24';
UPDATE 1


As I can read from http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_equal `id`='24' is actually valid, at least in MySQL.

Who imposes the limitation that quotes are not valid here when using PDO?
 [2013-06-12 02:14 UTC] ssufficool@php.net
-Summary: "Invalid parameter number" although it is correct +Summary: PDO_MYSQL: "Invalid parameter number" although it is correct -Status: Open +Status: Feedback
 [2013-06-12 02:14 UTC] ssufficool@php.net 
Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.php.net/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to "Open".

Thank you for your interest in PHP.


I don't think the assoc array is supposed to include the ":"

try:
  $stmt->execute(array('p0'=>'2', 'p1'=>'b2' ));
 [2013-10-15 11:54 UTC] php-bugs at lists dot php dot net 
No feedback was provided. The bug is being suspended because
we assume that you are no longer experiencing the problem.
If this is not the case and you are able to provide the
information that was requested earlier, please do so and
change the status of the bug back to "Re-Opened". Thank you.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Thu Nov 28 08:01:28 2024 UTC