#1628 need "display binary as hex/string" option in Settings UI

[permutations]

I'm on a shared server, so my ability to configure phpMyAdmin is limited to what's exposed in the Settings UI. My Web host recently upgraded from phpMyAdmin 4.2.10 to 4.3.10, and with that a critical setting has been removed from the UI: the ability to display blob data as text rather than hex.

In Drupal 7 and higher, ALL data is stored as blobs, but it's just serialized data. When I could change the display from hex to string, I could see what was being stored. Now it's all gibberish, and I can't check the values of any field.

If I wasn't on a shared server, I could add this line to the phpMyAdmin configuration file (but I'm not so I can't):

Show BLOB data as a string not hex.

$cfg['DisplayBinaryAsHex'] = false;

Please add this option back into the Settings UI so that people using Drupal 7 and higher on a shared server aren't completely screwed. There is a lot of sobbing about this on the internet. I'm trying to develop a module for Drupal 7, and not being able to look at the data with phpMyAdmin makes things much harder.

[Marc Delisle]

The setting was removed by this commit:
https://github.com/phpmyadmin/phpmyadmin/commit/85390a71693d33c2efabf1857b4f2120c104b735

To implement this feature request:
https://sourceforge.net/p/phpmyadmin/feature-requests/982/

[Chirayu Chiripal]

We removed that option considering the fact that BLOB are meant to store binary data which in most cases is not textual and showing them as text would show gibberish. When storing text data it is advisable to use TEXT instead of BLOB.

Do you just want to view the data or even edit it?

[permutations]

I mainly want to view the data, though I can't say I'd never want to edit it.

You say BLOBs are meant to store binary data. That was the original intent, but creative programmers have found other uses for it. Drupal 7 switched all data to BLOB so (as it was explained to me) "it could store arrays and not just strings". I'm not 100% certain what they mean by that so don't ask me to explain or defend it, but I think it has something to do with the much greater size of BLOBs, allowing more data to be stored. In any case, they did it, and the data stored is text not binary. Drupal is used in many thousands of Web sites, so your removal of this option from the user interface negatively impacts countless people.

It's one thing to recommend a best practice, but it's quite another to mandate a personal programming philosophy to the extent that people's options are removed. That's not right. There are legitimate uses for the BLOB with text data, as Drupal 7 and Drupal 8 prove. It's reasonable to make hex the default for BLOB data, but please restore the option to view BLOBs as text to the user interface. And not just configurable from the a line in the config file, but an option in the graphical UI. I can't access the config file because I'm on a shared server.

[permutations]

Is my post here sufficient, or do I need to post this perspective in the other thread as well?

[Marc Delisle]

@permutations normally you should not reply via the mailing list, but directly in the web interface on sourceforge.net.

[Isaac Bennetch]

I believe we're going to attempt to bring this checkbox back, although exact details still have to be worked out. As @chirayu45 points out, there are some issues with doing this that we have to be cautious about.

[Chirayu Chiripal]

I think inline editing should be disabled if we are displaying it in string form.

[permutations]

I'm very glad that you will bring this checkbox back.

It's reasonable to disable editing by default when displaying in string form, but please don't remove the capability completely. It's possible for a developer to have a legitimate reason for editing these fields.

[Marc Delisle]

Chirayu, please have a look at the 2015-05 team meeting log and tell us your feeling about this RFE here.

[Chirayu Chiripal]

I don't know why my reply is not getting posted, doing it for 3rd time. :(

I think we should bring this back given the use case by requester.

The commit which removed this feature also fixed some small issues related to behavior of displaying binary fields. Some of them are documented here (https://chirayuchiripal.wordpress.com/2014/05/25/gsoc-2014-week-2-support-for-editing-binary-fields-in-hexadecimal/).

[Anthony Cartmell]

Just posting another two votes from people who work with Drupal and would like to see BLOB contents rendered as text.

Or, perhaps, a browser display transformation from the "binary" data, which is in fact a serialised PHP array, into a PHP array output with print_r() or var_dump()...

That works nicely, in the rough it adds a browser transformation option with the code:

$buffer = "<pre>" . print_r(unserialize(hex2bin($buffer)), TRUE) . "</pre>";

Could do with some error checking, in case the BLOB data isn't actually a serialised PHP array, and perhaps some options, but that basically does what a Drupal person would love to see.

[Anthony Cartmell]

OK, with a bit more work this now works for most Drupal serialized-data fields. I've made the code a bit more robust and helpful too.

The idea is that the transformation takes the field data and displays in nicely using var_dump() to make it very readable. The var_dump() output is valid PHP code too, so it can be copied and pasted into a PHP interpreter or source file. The only slight downside is that serialized objects don't work with var_dump() if they contain other objects, so we revert to using print_r() in that specific case.

Transformation plugin (two files) attached. Tested on a variety of different field types and seems to work OK. I'm sure has some as-yet-undiscovered bugs.

Extract into [phpMyAdmin]/libraries/plugins/transformations and then use the new transformation for any field that contains serialized PHP data (whether binary BLOB or text LONGTEXT) to get the unserialized data displayed prettily in phpMyAdmin.

[Marc Delisle]

Thanks Anthony. I am testing the master branch in PHP 5.3.29 with a BLOB containing
a:2:{i:0;s:5:"abcde";i:1;i:123;}
and get an error code 500 (internal server error). Also tried with a more simple case like
s:5:"abcde";
and still get the error.

By the way, do you agree with our DCO ?
https://github.com/phpmyadmin/phpmyadmin/blob/master/DCO

[Anthony Cartmell]

My code runs hex2bin() on the $buffer, which I found was needed in my PMA for dealing with binary fields. But hex2bin is only available with PHP Version >= 5.4.0, so for earlier PHP versions you need the alternative function from here: http://php.net/manual/en/function.hex2bin.php

The DCO is fine, I wrote the code in my own time and am happy to contribute the bits that work (or just the idea!).

[Marc Delisle]

Tried again in PHP 5.5.20: I get:

s:5:"abcde";
Unserialize transformation failed.

[Anthony Cartmell]

I'm guessing:

• that text was contained in a binary BLOB field, and
• you used my code as-supplied, and
• your version of phpMyAdmin does not pass the $buffer as hex-encoded binary.

If you remove the hex2bin() part of my code, it should work fine if that's the case.

On my machine the $buffer for a binary field is hex-encoded text string, so I have to convert it back to binary to get the original serialised text string before running unserialize() on it. I don't know whether this is specific to my version of phpMyAdmin, or whether it has to do with the display settings (e.g. "Display binary as Hex").

[Marc Delisle]

Well, my data is not hex-encoded, so maybe this transformation should be named differently?

Also, could you attach here a file containing an example of a BLOB that is produced by Drupal 7?

[Marc Delisle]

With data produced by
<?php
$a = 'ABCDE';
echo bin2hex(serialize($a));
?>

Your transformation works ok for me; but again, maybe the transformation should not be named just "unserialized php".

[Anthony Cartmell]

I don't know why my version of phpMyAdmin is passing bin2hex() encoded binary data, instead of the actual contents of the field in the database. Maybe a display setting? In which case, the plugin needs to know whether the $buffer is plain or has been previously-encoded with bin2hex().

[Anthony Cartmell]

The transformation is called Unserializedphp. It simply aims to take serialized PHP from a database field and run unserialize() on it. The code isn't complicated, other than it proceeds carefully because this transformation won't always work.

As a bonus, and the reason this is attached to this particular feature request, the field also handles binary fields and converts the hexadecimal-encoded $buffer obtained from a BLOB field back into binary before running unserialize() on it. So this works nicely for Drupal where BLOB fields are used to store serialized PHP, but which are unreadable as hex data in phpMyAdmin.

I've attached a typical binary BLOB field contents from a Drupal site, where the contents are in fact just the textual serialization of a nested PHP array.

In one other case Drupal uses a LONGTEXT field instead of a BLOB, but mostly it uses BLOBs for this sort of thing.