php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #54258 MySQL: Silent ignorance of binds inside comments causes other to be wrong bound
Submitted: 2011-03-15 16:30 UTC Modified: 2011-05-10 20:06 UTC
From: an0nym at narod dot ru Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.3.5 OS: Linux
Private report: No CVE-ID: None
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: an0nym at narod dot ru
New email:
PHP Version: OS:

 

 [2011-03-15 16:30 UTC] 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". 

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2011-05-10 09:56 UTC] uw@php.net
-Status: Open +Status: Feedback
 [2011-05-10 09:56 UTC] uw@php.net
Can't reproduce. Please, provide full example including connect, create table, error handling and so forth.
 [2011-05-10 12:55 UTC] an0nym at narod dot ru
-Status: Feedback +Status: Open
 [2011-05-10 12:55 UTC] 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
<?php
var_dump(function_exists("mysqli_fetch_all"));
$DB = new PDO("mysql:dbname=test;host=localhost", "root", "",
    array(PDO::ATTR_ERRMODE => 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 12:56 UTC] 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 17:43 UTC] uw@php.net
-Status: Open +Status: Bogus
 [2011-05-10 17:43 UTC] uw@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 17:44 UTC] uw@php.net
... uups mixed up 1 and 2 at the beginning. But still: bogus.
 [2011-05-10 20:06 UTC] an0nym at narod dot ru
> 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
<?php
var_dump(function_exists("mysqli_fetch_all"));
$DB = new PDO("mysql:dbname=test;host=localhost", "root", "",
    array(PDO::ATTR_ERRMODE => 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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Apr 26 03:01:32 2024 UTC