Bug #60515 [Opn->Fbk]: PDO_MYSQL: "Invalid parameter number" although it is correct
Edit report at https://bugs.php.net/bug.php?id=60515&edit=1
ID: 60515
Updated by: ssufficool@php.net
Reported by: phoenixseve at freenet dot de
-Summary: "Invalid parameter number" although it is correct
+Summary: PDO_MYSQL: "Invalid parameter number" although it is
correct
-Status: Open
+Status: Feedback
Type: Bug
Package: PDO related
Operating System: archlinux x86_64
PHP Version: 5.3.8
Block user comment: N
Private report: N
New Comment:
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' ));
Previous Comments:
------------------------------------------------------------------------
[2012-09-17 20:55:02] 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 SETid=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?
------------------------------------------------------------------------
[2012-09-17 20:12:42] 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-05-02 11:21:11] 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-03-06 02:35:34] 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.
------------------------------------------------------------------------
[2011-12-13 20:57:47] 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
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=60515&edit=1
Thread (7 messages)