Bug #67983 [Opn->Csd]: mysqlnd with MYSQLI_OPT_INT_AND_FLOAT_NATIVE fails to interpret bit columns
Edit report at https://bugs.php.net/bug.php?id=67983&edit=1
ID: 67983
Updated by: nikic@php.net
Reported by: tenzzor at gmail dot com
Summary: mysqlnd with MYSQLI_OPT_INT_AND_FLOAT_NATIVE fails
to interpret bit columns
-Status: Open
+Status: Closed
Type: Bug
Package: MySQLi related
Operating System: Linux Ubuntu
PHP Version: 5.6.0
Block user comment: N
Private report: N
New Comment:
Automatic comment on behalf of nikita.ppv@gmail.com
Revision: http://git.php.net/?p=php-src.git;a=commit;h=315f3f8dc9b3ba3639c9284301933286fc99a825
Log: Fixed bug #67983
Previous Comments:
------------------------------------------------------------------------
[2018-07-04 14:23:06] 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'.
(https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/rtref/itol.htm)
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;
to
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.)
------------------------------------------------------------------------
[2014-09-08 17:57:45] tenzzor at gmail dot com
Description:
------------
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 https://github.com/php/php-src/blob/master/ext/mysqli/tests/mysqli_options_int_and_float_native.phpt
but it's only checking for value=0.
Test script:
---------------
<?php
$connection = mysqli_init();
mysqli_options($connection, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
mysqli_real_connect($connection, '127.0.0.1', '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);
var_dump($result['id']);
Expected result:
----------------
int(1) or string(1)
Actual result:
--------------
int(0)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=67983&edit=1
Thread (7 messages)