0

I have a table in a MySQL database with a BIT(64) row, which is a bitmask of 64 flags.

Using PHP and mysqli, I have an integer $n, which is in the range [0, 64). I have already written a function (see Appendix I) to set the nth bit of a byte array (i.e. string in PHP).

When passing the abovementioned byte-array (string) to MySQL, it seems that bitmask & ?, where ? is mysqli::bind_paramed as the byte array (param type is "s"), does not compare the bits as expected.

For example, using this query:

SELECT id FROM my_table WHERE (bitmask & ?) > 0

upon this table:

CREATE TABLE my_table (id INT PRIMARY KEY, bitmask BIT(64));

How can this be fixed?

I thought of passing a bin2hex and UNHEX() it, but this doesn't seem to fix the problem.

Appendix I

public static function setNthBit(int $n, int $bytes = 8) : string{
    $offset = $n >> 3;
    $byteArray = str_repeat("\0", $bytes);
    $byteArray{$bytes - 1 - $offset} = chr(1 << ($n & 7));
    return $byteArray;
}
SOFe
  • 7,867
  • 4
  • 33
  • 61
  • Just to clarify, if you have the 4th bit set in your `bitmask` for an `id` and you do something like `SELECT id FROM my_table WHERE bitmask & 8 = 8;` you don't get that `id` back? – JNevill May 19 '17 at 15:18
  • @JNevill what do you mean? The `id` has nothing to do with the bitwise comparison as far as I understand. – SOFe May 19 '17 at 15:21
  • @PEMapModder JNevill is asking if the `select` query in the question returns the id you are fetching, when you run it from mysql console with correct parameters. It will help to localise the problem whether it is on the db side or in the app. Also, example of php code where you are querying db is essential. – Alex Blex May 19 '17 at 15:34
  • I'm just wanting to clarify that if you have a record `id|bitmask` like `1|15` and you do `SELECT id FROM my_table WHERE bitmask & 8 > 0` that you do or do not get `id` 1 back from that query which would indicate that the 4th bit is set. – JNevill May 19 '17 at 15:35
  • It is just an example. My real query is something more complicated. – SOFe May 19 '17 at 15:39
  • I created a table with `id | bitmask` I inserted `1 | 15` into that table. Then to see if the 4th bit was set, ran `SELECT id FROM my_table WHERE bitmask & 8 > 0;` and it returned `id` of `1` as expected. I suspect that either your bitmask is not set with the value you think it is, or that you are testing for the wrong bit in your WHERE clause. But without seeing your data, An example SQL statement with values instead of `?` and your desired results, it's difficult to help much more. – JNevill May 19 '17 at 15:42
  • That's how it work in MySQL http://sqlfiddle.com/#!9/2caa7/1/2. There are chances the problem is in php, where you bind parameters. – Alex Blex May 19 '17 at 15:48
  • I got `"receptors" => "18446744073709551615", "xr" => string(16) "FFFFFFFFFFFFFFFF", "v" => string(8) "(empty, probably terminal problem)", "xv" => string(16) "0000000000000004", "ba" => int(0)` for the query `SELECT receptors, HEX(receptors) xr, ? v, HEX(?) xv, receptors & ? ba FROM hormones_blood` where all the `?`s are the same value. – SOFe May 19 '17 at 15:50
  • @AlexBlex you use `4` in the select query, which is an integer. My PHP program supports 32-bit machines, so if I were to pass a 64-bit bitmask, I have to either pass a 8-byte string or 16 bytes of hex characters to UNHEX in the query. – SOFe May 20 '17 at 07:54
  • I am thinking of fragmentating the bits to two fragments and compare them respectively. – SOFe May 20 '17 at 08:11

1 Answers1

0

It appears that the problem originates from that MySQL does not support bitwise comparison of strings. When I pass the bitmask as the parameter directly, MySQL would try to interpret as an integer, e.g. trying to interpret bitmask & '123' as bitmask & 123. I'm unsure how MySQL interprets a bunch of binary characters like \xFF or \x00 -- it just won't work, probably interpreted as 0.

I solved this by passing the bin2hex of the bitmask in the input and then CONV(?, 16, 10) in the query. CONV() will express the data in decimal, which MySQL will interpret as, hopefully, something like a BIGINT, which can be successfully bitwise-compared with a BIT(64) row.

SOFe
  • 7,867
  • 4
  • 33
  • 61