php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #79084 mysqlnd may fetch wrong column indexes with MYSQLI_BOTH
Submitted: 2020-01-08 19:03 UTC Modified: 2020-01-13 16:54 UTC
From: rob-phpbugs at tigertech dot com Assigned: cmb (profile)
Status: Closed Package: MySQLi related
PHP Version: 7.3.13 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: rob-phpbugs at tigertech dot com
New email:
PHP Version: OS:

 

 [2020-01-08 19:03 UTC] rob-phpbugs at tigertech dot com
Description:
------------
If a query has column names that are all numeric, like this:

SELECT * FROM (SELECT 0 as `2007`, 0 as `2008`, 0 as `2020`) AS x;

And you use "fetch_array()" on it, PHP 7.2 gives this result:

Array
(
    [0] => 0
    [2007] => 0
    [1] => 0
    [2008] => 0
    [2] => 0
    [2020] => 0
)

But PHP 7.3 and 7.4 give this result:

Array
(
    [0] => 0
    [2007] => 0
    [2008] => 0
    [2009] => 0
    [2020] => 0
)

The latter is wrong: it should not contain "2009", and it is missing "[1]" and "[2]".

Test script:
---------------
<?php
$mysqli = mysqli_connect("localhost", "user", "password", "db");
$sql = "SELECT * FROM (SELECT 0 as `2007`, 0 as `2008`, 0 as `2020`) AS x";
$res = $mysqli->query($sql);
$row = $res->fetch_array();
echo print_r($row, 1);


Expected result:
----------------
Array
(
    [0] => 0
    [2007] => 0
    [1] => 0
    [2008] => 0
    [2] => 0
    [2020] => 0
)


Actual result:
--------------
Array
(
    [0] => 0
    [2007] => 0
    [2008] => 0
    [2009] => 0
    [2020] => 0
)


Patches

Add a Patch

Pull Requests

Pull requests:

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2020-01-08 19:30 UTC] rob-phpbugs at tigertech dot com
I realize I included a confusing double "SELECT" in the example script because of how I was narrowing down the bug originally. That's not necessary and unrelated; the same thing happens with the simpler query of:

<?php
$mysqli = mysqli_connect("localhost", "user", "password", "db");
$sql = "SELECT 0 as `2007`, 0 as `2008`, 0 as `2020`";
$res = $mysqli->query($sql);
$row = $res->fetch_array();
echo print_r($row, 1);
 [2020-01-09 08:54 UTC] cmb@php.net
What happens should be similar to

<?php
$a = [];
$a['2007'] = 0;
$a[] = 0;
?>

So I'm surprised that you're reporting that would have been
different with PHP 7.2.  I cannot reproduce that.
 [2020-01-09 17:23 UTC] rob-phpbugs at tigertech dot com
I'm not sure I understand what you said, for which I apologize.

Are you saying that when you run the test script on both PHP 7.2 and 7.3, it prints the same results? Could you post what the output of the script is in both cases? Here's the output of a test I just did:

$ cat test.php
<?php
$mysqli = mysqli_connect("localhost", "test", "[redacted]", "test");
$sql = "SELECT 0 as `2007`, 0 as `2008`, 0 as `2020`";
$res = $mysqli->query($sql);
$row = $res->fetch_array();
print "PHP version: " . phpversion() . "\n";
print_r($row);

$ php-7.2 test.php
PHP version: 7.2.26
Array
(
    [0] => 0
    [2007] => 0
    [1] => 0
    [2008] => 0
    [2] => 0
    [2020] => 0
)

$ php-7.3 test.php
PHP version: 7.3.13
Array
(
    [0] => 0
    [2007] => 0
    [2008] => 0
    [2009] => 0
    [2020] => 0
)
 [2020-01-09 17:45 UTC] nikic@php.net
I just tried your script and get the same results on 7.2 and 7.3:

$ ~/php-7.2/sapi/cli/php t042.php 
Array
(
    [0] => 0
    [2007] => 0
    [2008] => 0
    [2009] => 0
    [2020] => 0
)
$ ~/php-7.3/sapi/cli/php t042.php 
Array
(
    [0] => 0
    [2007] => 0
    [2008] => 0
    [2009] => 0
    [2020] => 0
)

Do both PHP versions use mysqlnd (rather than libmysql)?
 [2020-01-09 18:10 UTC] rob-phpbugs at tigertech dot com
Ah -- I believe that is exactly what's happening. My PHP 7.2 has:

MysqlI Support => enabled
Client API library version => 10.1.41-MariaDB

And my PHP 7.3 has:

MysqlI Support => enabled
Client API library version => mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $

So that must be the cause of this.

The mysqlnd version does not appear to match the documentation for mysqli fetch_array at <https://www.php.net/manual/en/mysqli-result.fetch-array.php>, which says that it defaults to MYSQLI_BOTH. If I'm understanding correctly, that means there should be 6 keys in the output of the previous test script, for [0], [1], [2], [2007], [2008] and [2020].

Here's a different test script that shows a different weird result with mysqlnd, apparently caused by the same underlying issue:

$ cat test.php
<?php
$mysqli = mysqli_connect("localhost", "test", "test", "test");
$sql = "SELECT 0 as `test`, 1 as `test2`, 2 as `2007`, 2 as `2008`, 3 as `2020`";
$res = $mysqli->query($sql);
$row = $res->fetch_array();
print "mysqli version: " . $mysqli->client_info . "\n";
print_r($row);

$ php-7.2 test.php
mysqli version: 10.1.41-MariaDB
Array
(
    [0] => 0
    [test] => 0
    [1] => 1
    [test2] => 1
    [2] => 2
    [2007] => 2
    [3] => 2
    [2008] => 2
    [4] => 3
    [2020] => 3
)

$ php-7.3 test.php
mysqli version: mysqlnd 5.0.12-dev - 20150407 - $Id: 7cc7cc96e675f6d72e5cf0f267f48e167c2abb23 $
Array
(
    [0] => 0
    [test] => 0
    [1] => 1
    [test2] => 1
    [2] => 2
    [2007] => 2
    [2008] => 2
    [2009] => 3
    [2020] => 3
)

The mysqlnd version seems wrong; it isn't filling in the expected [3] and [4] keys, and it's added a [2009] key that should not be there.
 [2020-01-12 14:14 UTC] cmb@php.net
-Summary: mysqli fetch_array returns different results on PHP 7.3+ with some column names +Summary: mysqlnd may fetch with wrong column indexes -Status: Open +Status: Verified
 [2020-01-12 14:14 UTC] cmb@php.net
Ah, indeed, if mysqli uses libmysql-client, the numeric indexes
are explicitly specified when inserting to the row, but if mysqlnd
is used, the values with numeric indexes are added to the row, and
as such may be wrong if column names are numeric strings.  This
likely affects PDO_MySQL as well.
 [2020-01-12 17:29 UTC] cmb@php.net
The following pull request has been associated:

Patch Name: Fix #79084: mysqlnd may fetch wrong column indexes with MYSQLI_BOTH
On GitHub:  https://github.com/php/php-src/pull/5080
Patch:      https://github.com/php/php-src/pull/5080.patch
 [2020-01-12 18:17 UTC] cmb@php.net
-Summary: mysqlnd may fetch with wrong column indexes +Summary: mysqlnd may fetch wrong column indexes with MYSQLI_BOTH
 [2020-01-12 18:17 UTC] cmb@php.net
That same issue affects PDO core (i.e. with any driver),
so I think it's better to track that as separate ticket,
so I have filed bug #79106.
 [2020-01-13 12:24 UTC] cmb@php.net
Automatic comment on behalf of cmbecker69@gmx.de
Revision: http://git.php.net/?p=php-src.git;a=commit;h=1752393bb4013ca94d45da5a4d43997a73ae9750
Log: Fix #79084: mysqlnd may fetch wrong column indexes with MYSQLI_BOTH
 [2020-01-13 12:24 UTC] cmb@php.net
-Status: Verified +Status: Closed
 [2020-01-13 12:26 UTC] cmb@php.net
-Assigned To: +Assigned To: cmb
 [2020-01-13 12:26 UTC] cmb@php.net
The fix for this bug has been committed.
If you are still experiencing this bug, try to check out latest source from https://github.com/php/php-src and re-test.
Thank you for the report, and for helping us make PHP better.
 [2020-01-13 16:46 UTC] bugreports at gmail dot com
this was also broken in 7.2

print_r diff with the new expected array result
'select field_1 as `10`, field_2 as `20`, field_3 as `30` from test_table'

 (
     [0] => zeile 1 - feld 1
     [10] => zeile 1 - feld 1
-    [1] => zeile 1 - feld 2
+    [11] => zeile 1 - feld 2
     [20] => zeile 1 - feld 2
-    [2] => zeile 1 - feld 3
+    [21] => zeile 1 - feld 3
     [30] => zeile 1 - feld 3
 )
 [2020-01-13 16:54 UTC] cmb@php.net
> this was also broken in 7.2

And most likely on previous versions as well; however, bug fixes
can only be applied to actively supported versions[1].

[1] <https://www.php.net/supported-versions.php>
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sun May 31 20:01:27 2020 UTC