WHY IS MYSQL REJECTING ASCII CHARACTER INSERTION INTO CHAR TYPE COLUMN
I'm getting a rejection error when installing a new record into mysql (from vfp). I suspect it might be mysql being somewhat stricter about data types that vfp.
The data in question is an SHA256 hash value in plain ascii (32 chrs) which I can insert into a VFP table till the cows come home.
Mysql rejects it with the following message - which I do not understand:
[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.19]Inco
That looks like a clunky way to express the hex values of the ascii string but that doesn't make sense because the hex version of the string is 0hBEEC0F153832825A8647806E
The initial data type I had set the mysql column to was Varchar (32)
after reading refs in the mysql tooltip text re Varchars, which refers to utf8 chars requiring up to 3 bytes per char, I switched to plain CHAR which reported no such strictures, but I get the same error with that.
To test my hypothesis that it was somehow related to data type, instead of creating an ascii string, I created the hashes in hex and just used the first 32 characters of the 64 byte hex string. That worked a treat. Of course that's no use in the real world and I have no intention of switching to 64 byte fields just so I can use the full hex string because one of my requirements is to keep the whole table as lean as possible and 32 chrs is as lean as we can get with 256 bit hash. So it looks as though the ascii strings are being rejected because they contain one or more non standard characters.
Is that down to an encoding parameter I haven't applied or am I barking up completely the wrong tree?
The data in question is an SHA256 hash value in plain ascii (32 chrs) which I can insert into a VFP table till the cows come home.
Mysql rejects it with the following message - which I do not understand:
[MySQL][ODBC 8.0(w) Driver][mysqld-8.0.19]Inco
That looks like a clunky way to express the hex values of the ascii string but that doesn't make sense because the hex version of the string is 0hBEEC0F153832825A8647806E
The initial data type I had set the mysql column to was Varchar (32)
after reading refs in the mysql tooltip text re Varchars, which refers to utf8 chars requiring up to 3 bytes per char, I switched to plain CHAR which reported no such strictures, but I get the same error with that.
To test my hypothesis that it was somehow related to data type, instead of creating an ascii string, I created the hashes in hex and just used the first 32 characters of the 64 byte hex string. That worked a treat. Of course that's no use in the real world and I have no intention of switching to 64 byte fields just so I can use the full hex string because one of my requirements is to keep the whole table as lean as possible and 32 chrs is as lean as we can get with 256 bit hash. So it looks as though the ascii strings are being rejected because they contain one or more non standard characters.
Is that down to an encoding parameter I haven't applied or am I barking up completely the wrong tree?
how are you generating the hash? and what is the output type?
It looks like it is returning a binary so you need to convert that to a string
$somefile = "D:\2019-04-09_21-44-12.gif"
$md5 = New-Object -TypeName System.Security.Cryptography.MD5CryptoServiceProvider
$hash = [System.BitConverter]::ToString($md5.ComputeHash([System.IO.File]::ReadAllBytes($somefile)))
$hash
$hash2 = Get-FileHash $somefile -Algorithm MD5
$hash2.Hash
$hash2.Hash[0]93-F4-FE-77-50-F7-24-67-A0-BC-21-8D-EB-99-79-A5
93F4FE7750F72467A0BC218DEB9979A5
9
ASKER
Greetings David
All my crypto operations are through the Chilkat crypto library. Interesting question as to whether its output is binary or string because even if binary, any viewer other than a hex viewer will "translate" it something viewable. So viewing the hashes in mysql, I see plain unreadable ascii. In the VFP debugger it will present as 0xh followed by the hex translation and so on. . I have the choice of any output I want but I'm guessing that the plain "chilcrypt.hashstring(stri
Ferinstance, the first time I tried this, many moons past, I thought that there was a problem with the algo in that some of the string lengths were apparently less than the expected 32 chars. But when I viewed them in hex, I realised that the final char was hex 00 (occassionally 0000) so when attempting to present that as a string, the viewer just assumes there is nothing in the final position or two!
I've now moved on with mysql and am happy that the varbinary is giving me exactly what I need, which suggests that both you and mysql are sensitive to a definition of strings which is currently beyond my comprehension.
All my crypto operations are through the Chilkat crypto library. Interesting question as to whether its output is binary or string because even if binary, any viewer other than a hex viewer will "translate" it something viewable. So viewing the hashes in mysql, I see plain unreadable ascii. In the VFP debugger it will present as 0xh followed by the hex translation and so on. . I have the choice of any output I want but I'm guessing that the plain "chilcrypt.hashstring(stri
Ferinstance, the first time I tried this, many moons past, I thought that there was a problem with the algo in that some of the string lengths were apparently less than the expected 32 chars. But when I viewed them in hex, I realised that the final char was hex 00 (occassionally 0000) so when attempting to present that as a string, the viewer just assumes there is nothing in the final position or two!
I've now moved on with mysql and am happy that the varbinary is giving me exactly what I need, which suggests that both you and mysql are sensitive to a definition of strings which is currently beyond my comprehension.
why are you not using the .NET crypto libraries instead?
with C and C++ a string is a group of characters terminating with a 0 byte
with C and C++ a string is a group of characters terminating with a 0 byte
ASKER
>>why are you not using the .NET crypto libraries instead?
Oh, largely because I trust Chilkat more than I trust Microsoft. Not least because I can put questions direct to the author and get an intelligent response back, usually within a few hours.
I'm testing that assertion as we speak. It hadn't occurred to me, prior to your question, to consider whether the output is binary or string. By coincidence, I hit another problem with decoding/recoding hex>string>hex this morning and it's probably related to this issue, so I've sent them a sample illustrating the problem and the line of code which reproduces it and asked what I'm doing wrong. I'll probably have the reply by this evening and it may kill both birds with one stone. If it does, I'll close this question and award you the points for nudging me in the right direction.
In addition, the Chilkat libraries are much lighter weight and multiplatform; and can be called in all the environments I need to use - which is vital for crosschecking that the hashing and encryption/decryption performed in one environment is identical in another. By sticking with the same library, if errors are found I can be certain that they must be in my code and not some arcane syntax/issue with the hashing or encryption engine.
Oh, largely because I trust Chilkat more than I trust Microsoft. Not least because I can put questions direct to the author and get an intelligent response back, usually within a few hours.
I'm testing that assertion as we speak. It hadn't occurred to me, prior to your question, to consider whether the output is binary or string. By coincidence, I hit another problem with decoding/recoding hex>string>hex this morning and it's probably related to this issue, so I've sent them a sample illustrating the problem and the line of code which reproduces it and asked what I'm doing wrong. I'll probably have the reply by this evening and it may kill both birds with one stone. If it does, I'll close this question and award you the points for nudging me in the right direction.
In addition, the Chilkat libraries are much lighter weight and multiplatform; and can be called in all the environments I need to use - which is vital for crosschecking that the hashing and encryption/decryption performed in one environment is identical in another. By sticking with the same library, if errors are found I can be certain that they must be in my code and not some arcane syntax/issue with the hashing or encryption engine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
only just spotted your comment Pcelba. That explanation and link fills in the blanks. Its the avoidance of a character set which has the relevant effect and, as soon as I read that I realised it should have been obvious
ASKER
I decided to try VARBINARY as the data type and that accepts my Ascii hashes without issue. However, I need to test whether the result is commutative and that, if I transfer the mysql version back to VFP, the result would match my original.
And even if that's a fix, I'm not inclined to mark it as "found my own solution" because I still don't understand why - if it is the fix - it's necessary; i.e. why doesn't the CHAR version work?