Inserting BIT(M) Column Values to MySQL5.7 with PHP7 MySQLi Prepared Statements

Question

When trying to insert values into a BIT(64) column using MySQLi prepared statements, I am receiving the error (on execute):

Data too long for column 'bits64' at row 1

Here's my code:

if ($Segments[0] == 'yes') {$bitmask = '1';}else{$bitmask = '0';}
if ($Segments[1] == 'yes') {$bitmask = $bitmask.'1';}else{$bitmask = $bitmask.'0';}
...
if ($Segments[63] == 'yes') {$bitmask = $bitmask.'1';}else{$bitmask = $bitmask.'0';}

$Upload = $conn->prepare("INSERT INTO `testtable` (`bits64`) VALUES (?)");
$Upload->bind_param("s", $bitmask);
$Upload->execute();

The code provides a 64 character string. If I use PHPMyAdmin to insert the same output, it works successfully and looks like this:

INSERT INTO `testtable` (`bits64`) VALUES (b'0000000000000000000000000000000000000000000000000000000000000000');
INSERT INTO `testtable` (`bits64`) VALUES (b'1111111111111111111111111111111111111111111111111111111111111111');

The only thing that has shown any "success" (*not really) is changing the bind type to integer. But it appears that it is treated like an integer that should be converted to binary, since it gives the same error with the bit flags set to 1.

Other things I have tried include formatting $bitmask as:

$bitmask = "b'".$bitmask."'";
$bitmask = "0b".$bitmask;
// And others

As far as I am aware, the only bind_param types are integer, string, double, and blob. I assume it's sending the values like '101010' (code sample), 101010 (as integer), 'b'101010'', and '0b101010', respectfully; however, what is needed is b'101010'.

For speed and security reasons (of other fields), I have to use prepared statements.

I initially used BIGINT for this, but it appeared that rounding errors were occurring when most of the flags were set.

I think I have MySQL strict mode disabled.

My fallback is either a lot of TINYINT columns or CHAR(64), but I would prefer more efficient storage.

There are some related questions on this website, but most relate to single bit fields, where an integer 0 or 1 is more easily correlated to the single bit. I should be most appreciative of any advice or solutions.

EDIT

I realized that I did not have SQL-Mode completely blank. Once SQL Mode is an empty string, there are no errors but I get 0111111111111111111111111111111111111111111111111111111111111111 no matter what I try.

EDIT 2

Inserting via phpMyAdmin with all 1 flags set also gives 0111111111111111111111111111111111111111111111111111111111111111, so there must be other problems with the way I have this column configured. I am using Ubuntu 16.04, Server version: 5.7.19, PHP version: 7.0.22.

score 0 · Answer 1 · answered Mar 18 '20 at 01:23

This is one of the examples where the binding type matters...but not really helps in your case.

You need to understand what data types you are really working with. You have create a string of digits in PHP. It's not a binary number! It's just a string. PHP can juggle it and cast it, but you need to be explicit with PHP that what you want is a number, not a string.

One way to do it would be:

$bitmask = bindec($bitmask); // convert binary string to decimal number

$Upload = $conn->prepare("INSERT INTO `testtable` (`bits64`) VALUES (?)");
$Upload->bind_param("i", $bitmask); // bind as integer
$Upload->execute();

If you want to avoid binding as an integer, you can cast it in SQL using VALUES(CAST(? AS UNSIGNED)) or simply by adding 0 e.g. VALUES(? + 0)

To avoid using bindec() you can use bit-wise operations in PHP. The value will then always be an integer.

score -1 · Answer 2 · answered Oct 02 '17 at 18:54

maybe try

$Upload = $conn->prepare("INSERT INTO `testtable` (`bits64`) VALUES (b?)");

but it wouldn't surprise me if prepared statements doesn't really support bitfields at all..

For speed and security reasons (of other fields), I have to use prepared statements.

no you don't. this would be just as secure:

if(strlen(rtrim($bitmask,'01'))!==0){throw new \RuntimeException('illegal characters found in bitmask!');}
$conn->query('INSERT INTO `testtable` (`bits64`) VALUES (b\''.$bitmask.'\')');

and i assure you the performance impact will be absolutely minimal, benchmark it.

Hanshenrik... thanks for the fast answer. I forgot to mention, I tried that initially but got a fatal error at bind_param (i.e. the prepare line was bad). I might be doing something wrong, there, however. — Justin, Oct 02 '17 at 18:59

Inserting BIT(M) Column Values to MySQL5.7 with PHP7 MySQLi Prepared Statements

2 Answers2