Insert fails in phpMyAdmin, but works when using MySQL directly

TeckFudge_3213788 0
Viewed 420

I'm trying to insert a hash into MySQL, so I can test user logins. My first instinct was to use phpMyAdmin to make quickly updating passwords easier (default hash is 60 0's), until I get around to making a page for doing that using my website. The hash field is a binary (60) data type. My error is Please enter a valid HEX input.

To generate the hash, I am using the following: Code Credit

$pass = "password";
$salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');
$cost = 10;
$salt = sprintf("$2y$%02d$", $cost) . $salt;
$hash = crypt($pass, $salt);
echo $hash;//Result: $2y$10$XFo5biY5UvZfaRjhDraOFO26yocvwiAwidtGNIvoEezj5zitb2Xem

I already realize I am halfway to making the website do the inserts for me, but that isn't the question. I was wanting to know why phpMyAdmin > myDB > myTable > browse > edit then copy pasting the result from above does not work, however phpMyAdmin > myDB > myTable > SQL does work when using the following:

UPDATE `myTable` SET `hash`="$2y$10$XFo5biY5UvZfaRjhDraOFO26yocvwiAwidtGNIvoEezj5zitb2Xem" WHERE username = "myusername"

Going back to phpMyAdmin > myDB > myTable > browse I find that my ASCII hash has changed to the HEX hash:

2432792431302458466f356269593555765a6661526a684472614f464f3236796f637677694177696474474e49766f45657a6a357a6974623258656d

This HEX string remains the same when I do a select in phpMyAdmin, however, using the MySQL console, I never see HEX, it's always ASCII. Does anyone know why I can't insert the ASCII hash in phpMyAdmin, but I can in MySQL?

Edit: Dead link, but the WayBack Machine has a copy.

hash mysql php
2 Answers

I know this question is four years old and my answer does not solve the problem, but it explains what's happening. So for subsequent searchers' benefit:

The behaviour you describe is caused by the phpmyadmin UI. Updating/Inserting are ajax calls and the validation happens in the browser with javascript. The validation looks at the type of the field you are updating/inserting and allows you only what it thinks is valid content. I presume your hash field is a VARBINARY. I use that type to spare the server the resources to determine the codeset of a string, which it does for VARCHAR, when I know that only non-language 8bit character-strings like UUIDs will be entered. Now phpmyadmin/js/makegrid.js on lines 1403-1410 does

...
} else if ($this_field.hasClass('hex')) {
if ($(g.cEdit).find('.edit_box').val().match(/^[a-f0-9]*$/i) !== null) {
this_field_params[field_name] = $(g.cEdit).find('.edit_box').val();
} else {
var hexError = '<div class="error">' + PMA_messages.strEnterValidHex + '</div>';
PMA_ajaxShowMessage(hexError, false);
this_field_params[field_name] = PMA_getCellValue(g.currentEditCell);
}
...

and that is probably your problem, like it is mine. Unfortunately I do not see a way to disable this other than altering the source code, which is not a good idea because it will get overwritten by the next release update.

user_26103810

I have also found a lazy workaround.

If you double click to edit the BINARY field you can enter any valid string, "a" for example. This will be accepted and the table updated. Also phpMyAdmin will output the sql query (UPDATE table set field = 'a') on the top of the frame and offer the Button to [Edit inline] below it on the right. All one has to do then is paste the HEX String into the query.

user_26103810
Related