SQL, MD5, Base64 Encode problem

Databases
1

Okay. I have been snooping around the forums and even posting my questions about this. I got some good answers and even thought I was finished. However I ran into a snag and thought I would appeal to the power-users here who maybe can correct me.

Background: I am trying to add a field to a table that is an MD5/EncodeBase64 signature of a BLOB field. The BLOB field can be large.

Anyways, I first tried to use a loop and a recordset to evaluate and make this update for each record. (the table has a few thousand records, but will growth fast once this is implemented. But apparently Xojo has a known bug that prevents updating a recordset in a loop? Ugh.

But I was directed to doing all in the SQL statement. I had to add an EncodeBase64 function to the MySQL DB, but it seemed to work like magic.

However… I checked the data.

The hash I get from

data.Field("Signature").StringValue = EncodeBase64(Crypto.MD5(data.Field("sContents").StringValue))

is not the same as the hash I get from

"UPDATE tblmytable SET Signature = BASE64_ENCODE(MD5(sContents))”

So it seems Xojo does a different MD5 and/or Base64encode from SQL?? I thought these where standards?

TIA

2

I just read something that taught me something that my be applicable.

[quote]Decoding Base64 with padding
When decoding Base64 text, four characters are typically converted back to three bytes. The only exceptions are when padding characters exist. A single ‘=’ indicates that the four characters will decode to only two bytes, while ‘==’ indicates that the four characters will decode to only a single byte.[/quote]

Here is an example of two keys that are support to be the same:

[code]MjI1YjQyOWNlZjJlYjQ1NzY2NGJmNjJiMDU0Mjc2NWE=

IltCnO8utFdmS/YrBUJ2Wg==[/code]

The top one is from MySQL and the bottom from Xojo. So does this mean Xojo encodes to a 4 character to single byte decode? Can you tell Xojo to do a 4 to 2?

3

Just curious, why are you base64 encoding the md5 strings?

4

The Xojo crypt functions return the raw string. Other crypt functions tend to return a hex encoded string. Try

data.Field("Signature").StringValue = EncodeBase64(Hex(Crypto.MD5(data.Field("sContents").StringValue)))

5

That one. What the other Tim said.