PHP :: Bug #60515 :: PDO_MYSQL: "Invalid parameter number" although it is correct

[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

[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.

Patches

Pull Requests

History

	php.net \| support \| documentation \| report a bug \| advanced search \| search howto \| statistics \| random bug \| login
go to bug id or search bugs for


Copyright © 2001-2024 The PHP Group All rights reserved.	Last updated: Sat Apr 27 00:01:30 2024 UTC