SQL Query with Binary Data (PHP and MySQL)

SQL Query with binary data (PHP and MySQL)

Note: This addresses binary data, but not encrypted data. See this answer for searching on encrypted data.

Try adding X, x or 0x in front of binary data used for search:

SELECT id FROM test WHERE pid = '0xÞFÈ>ZPÎ×jRZ{æ×';

EDIT: try also this:

SELECT id FROM test WHERE BINARY pid = 'ÞFÈ>ZPÎ×jRZ{æ×';

OR

SELECT id FROM test WHERE HEX(pid) = BIN2HEX('0xÞFÈ>ZPÎ×jRZ{æ×');

as supposed here: How to select with a binary field ? (php,mysql)

IF NOTHING FROM ABOVE WORKS: Try obtaining the pid in HEX format, like

SELECT id, HEX(pid) pid, test FROM test

and then when searching try only:

SELECT id, test FROM test WHERE HEX(pid) = '{$my_pid}'

But I'm not sure how do You obtain the pid data to PHP or even whether You pass the binary data into Your select - where query... Just guessing due to the php tag...

Convert binary(16) data in MySQL field

Solved: I had to convert that number using a MySQL function (INET6_NTOA).

Mysql using where clause with Binary data type

The issue is that you're comparing a string to a binary object. Change the type of your field to CHAR(64) and it should work.

How to query BINARY fields in MySQL?

Hexadecimal literals in MySQL look like this: X'01AF' or this: 0x01AF (case insensitive in both cases.

One option would be SELECT ... WHERE uuid = X'76572de1aa8c435bafe48e260e19466b'

Unequal binary char comparison when MySQL string fetched from PHP

char(255) CHARACTER SET latin1 COLLATE latin1_bin

will read/write bytes unchanged. It would be better to say BINARY(255), or perhaps something else.

If you tell the server that your client wants to talk in "utf8", and you SELECT that column, then MySQL will translate from latin1 (the charset of the data) to utf8 (the encoding you say the client wants). This leads to the longer hex string.

You say that phpmyadmin says "utf8" somewhere; that is probably the cause of the confusion.

If it had been stored as base64, there would be no confusion because base64 uses very few different characters, and they are encoded identically in latin1 and utf8. Furthermore, latin1_bin would have been appropriate. So, another explanation of what went wrong is the unwanted reconversion from base64 to binary.

MySQL's implementation of latin1_bin is simple and permissive -- all 256 bit values are simply stored and loaded, unchecked. This makes it virtually identical to BLOB and BINARY.

This is probably the base64_encode that should have been stored:

MDhGRDc5RTBBNjZGQkIzNzVCNjM3MEQ5RUNGQTdCQjhCMjAzREQ3MA==

Datatypes starting with VAR or ending with BLOB or TEXT are implemented via a 'length' field plus the bytes needed to represent the value.

On the other hand, CHAR and BINARY are fixed length, and padded by spaces (CHAR) or \0 (BINARY).

So, writing binary info to CHAR(255) actually may modify the data due to spaces appended.

PHP- inserting binary data in mysql using prepared statements

PHP's sha1 function returns a string representation of a hex number.

What that means is that if you print it to screen, it'll display a hex number. But in memory, it is an bunch of ASCII characters.

So, take the hex number 1A2F. As ASCII in memory that would be 0x31413246, instead of 0x1A2F

MySQL's normal interface sends all arguments as strings. When using the normal interface, MySQL will convert the ASCII string to a binary value.

The new prepared statement method sends everything as binary. So your nice value of "1A2F" will now be sent as 0x31413246 and inserted into the column. - source: dev.mysql.com - Prepared statements

Instead, convert your Hex string by packing it into a binary string using:

$binId = pack("H*", $id); // this string is not ASCII, don't print it to the screen! That will be ugly.

and then pass $binId to the MySQLi prepared statement instead of $id.

---

Related Topics