Bug #54258 [Bgs]: MySQL: Silent ignorance of binds inside comments causes other to be wrong bound
Edit report at http://bugs.php.net/bug.php?id=54258&edit=1 ID: 54258 User updated by:an0nym at narod dot ru Reported by:an0nym at narod dot ru Summary:MySQL: Silent ignorance of binds inside comments causes other to be wrong bound Status: Bogus Type: Bug Package:PDO related Operating System: Linux PHP Version:5.3.5 Block user comment: N Private report: N New Comment: > No error handling in your code. MySQL does what it is supposed to do according to http://www.php.net/manual/en/pdostatement.bindparam.php. It returns false for the second call to bindParam(), because there is only one parameter to bind. > > > UPDATE t SET /*f1 = ?, */f2 = ? >^ comment > ^ parameter to bind > > MySQL sets f2 = 1. And, that's exactly what you get. Yeap, MySQL does. Nope, PDO doesn't. >cat test.php PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false)); $DB->exec("CREATE TEMPORARY TABLE t(f1 VARCHAR(1), f2 VARCHAR(1)) SELECT 0 f1, 0 f2"); $stmt = $DB->prepare("UPDATE t SET /*f1 = :field1, */f2 = :field2"); $field1 = 1; $field2 = 2; var_dump($stmt->bindParam(":field1", $field1), $stmt->bindParam(":field2", $field2)); $stmt->execute(); foreach ($DB->query("SELECT * FROM t") as $row) var_dump($row); >php test.php bool(true) bool(true) bool(false) array(4) { ["f1"]=> string(1) "0" [0]=> string(1) "0" ["f2"]=> string(1) "1" [1]=> string(1) "1" } As you can see, PDO returned TRUE binding :field1 = $field1 and FALSE binding :field2 = $field2. However, actually it silently bound :field2 = $field1. I understand why - MySQL doesn't have named placeholders and doesn't tell us about placeholder inside comment it skipped. Nevertheless, PDO did the opposite to what it reported about. Obviously, it is a bug, maybe the one that won't be fixed based on objective reasons. Previous Comments: [2011-05-10 17:44:40] u...@php.net ... uups mixed up 1 and 2 at the beginning. But still: bogus. [2011-05-10 17:43:54] u...@php.net Thanks for explaining, but I still believe there is no error here. You are running: CREATE TEMPORARY TABLE t(f1 VARCHAR(1), f2 VARCHAR(1)) UPDATE t SET /*field1 = : 1, */field2 = 2 SELECT * FROM t You get: 1 row with field1 = 0, field2 = 2 That's pretty much what I expect. You are setting: PDO::ATTR_EMULATE_PREPARES => false)); But you are forcing parameter substitution on the client because you are using ":name" instead of "?" placeholder syntax. The MySQL server does not support use of ":name" for placeholders in prepared statements. Thus, PDO hooks in, does the string replacements and tells MySQL to prepare: UPDATE t SET /*f1 = ?, */f2 = ? MySQL prepares it for you. Then, you bind parameters: $stmt->bindParam(":field1", $field1, PDO::PARAM_INT); $stmt->bindParam(":field2", $field2, PDO::PARAM_INT); No error handling in your code. MySQL does what it is supposed to do according to http://www.php.net/manual/en/pdostatement.bindparam.php. It returns false for the second call to bindParam(), because there is only one parameter to bind. UPDATE t SET /*f1 = ?, */f2 = ? ^ comment ^ parameter to bind MySQL sets f2 = 1. And, that's exactly what you get. Please add proper error handling to your code. [2011-05-10 12:56:43] an0nym at narod dot ru As you can see f2 is silently updated to 1 instead of exception (at least) or right value 2. [2011-05-10 12:55:23] an0nym at narod dot ru root@test # uname -a Linux test 2.6.35.11-83.fc14.x86_64 #1 SMP Mon Feb 7 07:06:44 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux 06:53:51 ~ root@test # php -v PHP 5.3.6 (cli) (built: Mar 17 2011 20:56:13) Copyright (c) 1997-2011 The PHP Group Zend Engine v2.3.0, Copyright (c) 1998-2011 Zend Technologies 06:53:56 ~ root@test # find / | grep libmysql /usr/lib64/mysql/libmysqlclient.so.16.0.0 /usr/lib64/mysql/libmysqlclient_r.so.16.0.0 /usr/lib64/mysql/libmysqlclient_r.so.16 /usr/lib64/mysql/libmysqlclient.so.16 06:54:02 ~ root@test # cat test.php PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false)); $DB->exec("CREATE TEMPORARY TABLE t(f1 VARCHAR(1), f2 VARCHAR(1)) SELECT 0 f1, 0 f2"); $stmt = $DB->prepare("UPDATE t SET /*f1 = :field1, */f2 = :field2"); $field1 = 1; $field2 = 2; $stmt->bindParam(":field1", $field1, PDO::PARAM_INT); $stmt->bindParam(":field2", $field2, PDO::PARAM_INT); $stmt->execute(); foreach ($DB
Bug #54258 [Bgs]: MySQL: Silent ignorance of binds inside comments causes other to be wrong bound
Edit report at http://bugs.php.net/bug.php?id=54258&edit=1 ID: 54258 Updated by: u...@php.net Reported by:an0nym at narod dot ru Summary:MySQL: Silent ignorance of binds inside comments causes other to be wrong bound Status: Bogus Type: Bug Package:PDO related Operating System: Linux PHP Version:5.3.5 Block user comment: N Private report: N New Comment: ... uups mixed up 1 and 2 at the beginning. But still: bogus. Previous Comments: [2011-05-10 17:43:54] u...@php.net Thanks for explaining, but I still believe there is no error here. You are running: CREATE TEMPORARY TABLE t(f1 VARCHAR(1), f2 VARCHAR(1)) UPDATE t SET /*field1 = : 1, */field2 = 2 SELECT * FROM t You get: 1 row with field1 = 0, field2 = 2 That's pretty much what I expect. You are setting: PDO::ATTR_EMULATE_PREPARES => false)); But you are forcing parameter substitution on the client because you are using ":name" instead of "?" placeholder syntax. The MySQL server does not support use of ":name" for placeholders in prepared statements. Thus, PDO hooks in, does the string replacements and tells MySQL to prepare: UPDATE t SET /*f1 = ?, */f2 = ? MySQL prepares it for you. Then, you bind parameters: $stmt->bindParam(":field1", $field1, PDO::PARAM_INT); $stmt->bindParam(":field2", $field2, PDO::PARAM_INT); No error handling in your code. MySQL does what it is supposed to do according to http://www.php.net/manual/en/pdostatement.bindparam.php. It returns false for the second call to bindParam(), because there is only one parameter to bind. UPDATE t SET /*f1 = ?, */f2 = ? ^ comment ^ parameter to bind MySQL sets f2 = 1. And, that's exactly what you get. Please add proper error handling to your code. [2011-05-10 12:56:43] an0nym at narod dot ru As you can see f2 is silently updated to 1 instead of exception (at least) or right value 2. [2011-05-10 12:55:23] an0nym at narod dot ru root@test # uname -a Linux test 2.6.35.11-83.fc14.x86_64 #1 SMP Mon Feb 7 07:06:44 UTC 2011 x86_64 x86_64 x86_64 GNU/Linux 06:53:51 ~ root@test # php -v PHP 5.3.6 (cli) (built: Mar 17 2011 20:56:13) Copyright (c) 1997-2011 The PHP Group Zend Engine v2.3.0, Copyright (c) 1998-2011 Zend Technologies 06:53:56 ~ root@test # find / | grep libmysql /usr/lib64/mysql/libmysqlclient.so.16.0.0 /usr/lib64/mysql/libmysqlclient_r.so.16.0.0 /usr/lib64/mysql/libmysqlclient_r.so.16 /usr/lib64/mysql/libmysqlclient.so.16 06:54:02 ~ root@test # cat test.php PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false)); $DB->exec("CREATE TEMPORARY TABLE t(f1 VARCHAR(1), f2 VARCHAR(1)) SELECT 0 f1, 0 f2"); $stmt = $DB->prepare("UPDATE t SET /*f1 = :field1, */f2 = :field2"); $field1 = 1; $field2 = 2; $stmt->bindParam(":field1", $field1, PDO::PARAM_INT); $stmt->bindParam(":field2", $field2, PDO::PARAM_INT); $stmt->execute(); foreach ($DB->query("SELECT * FROM t") as $row) var_dump($row); 06:54:07 ~ root@test # php test.php bool(false) array(4) { ["f1"]=> string(1) "0" [0]=> string(1) "0" ["f2"]=> string(1) "1" [1]=> string(1) "1" } 06:54:11 ~ root@test # mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.56 Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit Bye 06:54:47 ~ root@test # [2011-05-10 09:56:54] u...@php.net Can't reproduce. Please, provide full example including connect, create table, error handling and so forth. [2011-03-15 16:30:52] an0nym at narod dot ru Description: See test script. Test script: --- $statement = $DB->prepare("UPDATE t SET /*field1 = :field1, */field2 = :field2"); $field1 = 1; $field2 = 2; $statement->bindParam(":field1", $field1, PDO::PARAM_INT); $statement->bindParam(":field2", $field2, PDO::PARAM_INT); $statement->execute(); Expected result: Query "UPDATE t SET /*field1 = 1, */field2 = 2" or error message like "wrong param count". Actual result: -- Silently running query "UPDATE t SET /*field1 = ?, */field2 = 1". -