MySQLi Prepared Statements Not Working Correctly With UPDATEs

[m00dawg]

Binary package hint: php5-mysql

When using MySQLi and prepared statements, it seems as though some
write-based queries (such as UPDATE) end up getting bad values.
Specifically, items after a WHERE clause are getting values of 0 or
things like \0\0 where they should be getting a proper ID (such as
123).

This issue was encountered on Ubuntu 9.10 with PHP 5.2.10. This issue
does not occur on Ubuntu 9.04 with PHP 5.2.6. Same version of MySQL.
Same php.ini and my.cnf.

To reproduce:

1. Grab files from: http://www.moocowproductions.org/tmp/test_cases/
2. Run .SQL script through MySQL
3. Enable gereral query log in MySQL
3a. For MySQL 5.0, edit /etc/my.cnf and add 'log' and restart
3b. For MySQL 5.1, run "SET GLOBAL general_log=1"
4. Edit mysqli_bug.php and modify $dbPassword
5. Run mysqli_bug.php
6. Check General Log (See Expected and Actual results)

The expected output of the MySQL general log should be something like:

The MySQL general log should look like this:

  61649 Query SET sql_mode='TRADITIONAL'
  61649 Query set autocommit=0
  61649 Prepare UPDATE t1 SET
 itemID = ? WHERE num1 = ?
  61649 Execute UPDATE t1 SET
 itemID = 4 WHERE num1 = 5
  61649 Close stmt
  61649 Query commit
  61649 Quit

The actual result is:

The log, instead, looks like this:

    124 Query SET sql_mode='TRADITIONAL'
    124 Query set autocommit=0
    124 Prepare UPDATE t1 SET
 itemID = ? WHERE num1 = ?
    124 Execute UPDATE t1 SET
 itemID = 4 WHERE num1 = 0
    124 Close stmt
    124 Query commit
    124 Quit

(Note that num1 = 0 and it should be 5)

So far, this only appears to be happening with UPDATE queries. SELECTs do not have this problem, and neither do INSERTs. I did not run into an issue with DELETEs, though I did not test this thoroughly.

[m00dawg (tim-moocowproductions)]

Added test-cases files (to avoid having to get them externally)

[Chuck Short (zulcss)]

Thanks for the bug report, can you attach our php.ini as well?

Thanks
chuck

[m00dawg (tim-moocowproductions)]

Sure can! Attached!

Keep in mind, this my.cnf is the same used for 9.04 and 9.10. I am also using MariaDB (via ourdelta.org). Same version on both 9.04 and 9.10.

[Chuck Short (zulcss)]

Thanks