|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #67983 mysqlnd with MYSQLI_OPT_INT_AND_FLOAT_NATIVE fails to interpret bit columns
Submitted: 2014-09-08 17:57 UTC Modified: 2017-10-24 07:21 UTC
Avg. Score:5.0 ± 0.0
Reproduced:5 of 5 (100.0%)
Same Version:1 (20.0%)
Same OS:2 (40.0%)
From: tenzzor at gmail dot com Assigned:
Status: Closed Package: MySQLi related
PHP Version: 5.6.0 OS: Linux Ubuntu
Private report: No CVE-ID: None
 [2014-09-08 17:57 UTC] tenzzor at gmail dot com
mysqlnd with MYSQLI_OPT_INT_AND_FLOAT_NATIVE fails to interpret bit columns. It always returns int 0 regardless of actual column value.
I found a passing test but it's only checking for value=0.

Test script:
$connection = mysqli_init();
mysqli_options($connection, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
mysqli_real_connect($connection, '', 'root', '', 'test');

mysqli_set_charset($connection, 'utf8');
mysqli_query($connection, 'DROP TABLE IF EXISTS test');
mysqli_query($connection, 'CREATE TABLE test (id BIT(1))');
mysqli_query($connection, 'INSERT INTO test VALUES (1)');

$res = mysqli_query($connection, 'SELECT * FROM test');

$result = mysqli_fetch_assoc($res);


Expected result:
int(1) or string(1)

Actual result:


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2014-09-09 09:23 UTC]
-Status: Open +Status: Assigned
 [2014-09-09 09:23 UTC]
-Status: Open +Status: Assigned -Assigned To: +Assigned To: andrey
 [2017-10-24 07:21 UTC]
-Status: Assigned +Status: Open -Assigned To: andrey +Assigned To:
 [2018-07-04 14:23 UTC] oh at friedrich-heilmuth dot de
The bug in mysqlnd is in function:

    php_mysqlnd_rowp_read_text_protocol_aux (line 1634 in ext/mysqlnd/mysqlnd_wireprotocol.c)

I got curious and tested the bug by putting the 'special value' 0x3132333435363738 into a BIT(64) field and then ran mysqli with the 'native option' enabled. The result was - as expected - the integer 12345678.
The reason is, that the used atoll() function expects a string with decimal digits and the above value is the bit pattern for 12345678 in ASCII and UTF-8. (The value is actually wrong though.)

The problem is, that execution enters the branch

    if (as_int_or_float && perm_bind.php_type == IS_LONG) {

(line 1704) even for the BIT type and interprets data which is a binary variable length integer as a decimal string. Function atoll() can only convert bytes 0x30 through 0x39 to integer (and it skips isspace()s).

A quick google search for atoll() gave '... The return value is 0L if the function cannot convert the input to a value of that type'.
If e.g. a BIT(1) field contains the value 1, the string "\001" is fed to atoll() which it cannot convert to anything else but 0. This is the reason why BIT seems to always return 0. It does not always return 0 but it always returns a wrong integer value except when all bits are unset. (Although only by coincidence though, because atoll() 'sees' an empty string caused by the byte 0x00 in the first position of the string. It happens in this case that the 'could not convert' value and the actual value are the same.)

To solve the problem, the branch *must not* be entered for the BIT type, possibly by changing

    mysqlnd_ps_fetch_functions[MYSQL_TYPE_BIT].php_type     = IS_LONG;


    mysqlnd_ps_fetch_functions[MYSQL_TYPE_BIT].php_type     = IS_STRING;

in ext/mysqlnd/mysqlnd_ps_codec.c (line 471) to prevent entering the wrong branch (a quick search with find seemed to show that this change is safe and without side effects but I might be wrong) and adjust the

    if (fields_metadata[i].type == MYSQL_TYPE_BIT) {

branch to either convert the value to string or integer, depending on the flag.
(not with something like sprintf("%u") but with sprintf("%d") though.)

An alternative is to explicitely exclude the type MYSQL_TYPE_BIT from the execution branch but that still requires

I suggest full conversion to integer if requested, which means that a converted integer value can even become negative for BIT(64) (if it's above PHP_INT_MAX).
It's subject to discussion if the returned value should be an integer string if no conversion is requested or if it should be a raw binary value. An integer string makes little sense IMO for bit operations.
(It should be an integer string though as long as the value is converted wrongly because that makes it easier at least to do the conversion manually. I use intval(gmp_sub($value,'18446744073709551616'))) currently for conversion. The value subtracted here is UINT64_MAX+1. This calculation returns the integer -1 for UINT_MAX 18446744073709551615 which is the correct bit pattern with all bits set to 1.)

Note: the correponding function for the binary protocol (php_mysqlnd_rowp_read_binary_protocol) seems to return a binary string (via mysqlnd_ps_fetch_functions[type].func(current_field, &fields_metadata[i], 0, &p), line 1581). It might be necessary to adjust this function too for type BIT and native integers. It must also be 'symmetric' to the text proto case, i.e. convert to integer if requested and either binary string or integer string in both cases.

A hint in the documentation that using the type BIT(64) might require special handling (something like if($value<0) to check for the high bit) because it's not possible to mask the highest bit with bit ops in PHP since the required bit mask is beyond PHP_INT_MAX (even in 64 bit PHP environments).

mysqlnd already has the required handling for type BIGINT UNSIGNED which converts integers>PHP_INT_MAX to string which is fine.
(Using UNSIGNED types is a bad idea in the first place IMO but that's another question.)
 [2020-12-16 09:54 UTC]
Automatic comment on behalf of
Log: Fixed bug #67983
 [2020-12-16 09:54 UTC]
-Status: Open +Status: Closed
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Mon Jan 18 19:01:23 2021 UTC