Jump to content
Larry Ullman's Book Forums

Saving Binary Hash_Hmac In Mysql


Recommended Posts

Great book, very helpful basis to writing php and mysql database. I've based my login on hash_hmac (page 78 chapter 4). Works fine for some passwords, but then a password attempt was rejected and I traced it back to the binary save.

 

Below is echo from various versions of Password, Crypt, Hash false so not saving binary, Hash true but then base64_encoded, saved to varbinary(100) and saved to tinyblob.

 

First line is echo of the variable saved, second line is the value as read back from the database.

 

Why does a password '11Waldron' not read back as saved in the last two cases when save as varbinary or tinyblob? Any thoughts?

 

Coding is basically -

$UsePassTBHash= mysql_real_escape_string(hash_hmac('sha256',$Password,'c#rAz01',true));

 

Results are -

 

 

11Waldron(Password)

 

juxBFM8KEpQoo(UsePassCrypt)

 

juxBFM8KEpQoo

 

00d93c9fb500fa4805b957c8c763392d099c5c67ab59f178722ce73018d17169(UsePassHashFalse)

 

00d93c9fb500fa4805b957c8c763392d099c5c67ab59f178722ce73018d17169

 

ANk8n7UA+kgFuVfIx2M5LQmcXGerWfF4ciznMBjRcWk=(UsePassB64Hash)

 

ANk8n7UA+kgFuVfIx2M5LQmcXGerWfF4ciznMBjRcWk=

 

\0�<��\0�H�W��c9- �\\g�Y�xr,�0�qi(UsePassVBHash)

 

�<���H�W��c9- �\g�Y�xr,�0�qi

 

\0�<��\0�H�W��c9- �\\g�Y�xr,�0�qi(UsePassTBHash)

 

�<���H�W��c9- �\g�Y�xr,�0�qi

Link to comment
Share on other sites

Thanks for the nice words on the book. It is appreciated. I'll be glad to help, but I've read your post three times now and I'm really not following you. Perhaps instead of focusing on all these permutations, it'd be more useful to focus on what your Web site is actually doing?

Link to comment
Share on other sites

Sorry not to have been more specific.

The various conbinations show -

line 1 PassCrypt as saved, line 2 PassCrypt as reloaded, both the same from a simple Crypt of the password

line 3 hash_hmac as saved, line 4 hash_hmac as reloaded, both the same with false as the last hash_hmac element, i.e. not binary

line 5 base64_encode(hash_hmac.......true) as saved, line 6 as reloaded, both the same when binary saved through base64

line 7 hash_hmac.......true saved to a varbinary(100) field, line 8 as then reloaded - but different

line 9 hash_hmac.......true saved to a tinyblob field, line 8 as then reloaded - again different

 

Some other passwords are that I started with worked fine including saving as binary fieleds, but 11Waldron, 211Waldron, Waldron11 do not seem to save correctly in MYSQL (version 5.4) binary fields. Is there some requirement not to use numeric characters or something similar? I would have thought any binary data which is saved would reload as exactly the same binary data. However when I try to compare the hash of the password that the user enters against the saved value, because the saved value is not reloading as originally computed/hashed, a password entered correctly gets rejected.

 

When I save $UsePassTBHash from the following line

$UsePassTBHash= mysql_real_escape_string(hash_hmac('sha256',$Password,'c#rAz01',true));

into a tinyblob or varbinary(100) field, Select UsePassTBHash from... etc. does not reload the same value

 

If there is really a problem saving and reloading a binary field, would I be better to end hash_hmac as false to create a text value, or to base64_encode the binary value from hash_hmac last field true?

Link to comment
Share on other sites

Thanks. First, to be clear, the output from various ways of hashing a password isn't meaningful or useful. I appreciate the attempt, but... what we really ought to be talking about is what you are trying to do and the specific code.

 

Second, there's absolutely no requirement on what the password is whatsoever. Get that out of your head. I know that may seem like part of the problem, but it's not.

 

If you just want to move on without really knowing what's going wrong here, then change to a text column type and omit the third argument to hash_hmac(). Otherwise some serious debugging is required.

Link to comment
Share on other sites

I would like to get to the bottom of this. I have extracted the relevant code and having set $dbc as the database connection the following code -

 

$q = "CREATE TABLE bintest(TestSave tinyblob NOT NULL) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci";

$r = mysqli_query($dbc, $q) or die(mysqli_error($dbc));

function Insert($dbc, $TestSave) {

$q = sprintf("INSERT INTO bintest(TestSave)

VALUES ('%s');", $TestSave);

echo "<p>Insert query SQL<br>$q</p>";

$r = mysqli_query($dbc, $q) or die(mysqli_error($dbc));

}

$Password = "11Waldron";

$TestSave= mysqli_real_escape_string($dbc, hash_hmac('sha256',$Password,'c#rAz01',true));

Insert($dbc, $TestSave);

$q = "SELECT TestSave FROM bintest";

$r = mysqli_query($dbc, $q) or die(mysqli_error($dbc));

while ($row = mysqli_fetch_array($r)){

echo "<p>$TestSave - the value as saved<br>

{$row['TestSave']} - the value as loaded has characters missing</p>";

}

 

gets the following output -

Insert query SQL

INSERT INTO bintest(TestSave) VALUES ('\0Ù<Ÿµ\0úH¹WÈÇc9- œ\\g«Yñxr,ç0Ñqi');

\0Ù<Ÿµ\0úH¹WÈÇc9- œ\\g«Yñxr,ç0Ñqi - the value as saved

Ù<ŸµúH¹WÈÇc9- œ\g«Yñxr,ç0Ñqi - the value as loaded has characters missing

 

I would really like to understand why there is this difference in what the insert query saves and the select query retrieves for some values of $password including '11Waldron', but for example 'Channon2012' retieves exactly as saved.

Link to comment
Share on other sites

Seems like a encoding problem. Try applying something like mysqli_set_charset('utf8'). I would also not run mysqli_real_escape_string on the hashed result. After hashing, that won't really be a problem anymore. Remove it.

 

I don't really understand why you don't simply change to a char or varchar type instead. I understand the potensial save in data, but is it really worth it if it gives you all this trouble?

 

One last suggestion: Sure your input is not truncated? Try generating a hash with the function, echo it to screen, then save it using something like phpMyAdmin. Then you can make sure that is not the problem at least.

 

http://php.net/manua...set-charset.php

Link to comment
Share on other sites

Thanks for your input. It is not a case of being worth the trouble, the workaround is easy, but that would not help my long term understanding. There will be times I need to read binary data and get the data I expect.

 

Anyway, problem now resolved - it relates to using mysqli_real_escape_string. The second line below has stripped out each '\' and the following character so was not what I wanted to make a valid comparison.

 

\0Ù<Ÿµ\0úH¹WÈÇc9- œ\\g«Yñxr,ç0Ñqi - the value as saved

Ù<ŸµúH¹WÈÇc9- œ\g«Yñxr,ç0Ñqi - the value as loaded has characters missing

\0Ù<Ÿµ\0úH¹WÈÇc9- œ\\g«Yñxr,ç0Ñqi

 

I need to use mysqli_real_escape_string on the retrieved data which generates the third (expected) line as in the following code which generates the three lines above -

 

while ($row = mysqli_fetch_array($r)){

$TestLoad = mysqli_real_escape_string($dbc, $row['TestSave']);

echo "<p>$TestSave - the value as saved<br>

{$row['TestSave']} - the value as loaded has characters missing<br>

$TestLoad</p>";

}

 

Larry, great to see you doing a book on Yii. I looked at the Zend framework and could not really understand it. I'll be buying your Yii book today.

Link to comment
Share on other sites

 Share

×
×
  • Create New...