0

So I have SHA1 varchar sting like 'LQSVPLQSVPSYKLMTY7P5SVPSYN54CMGU' = 32 bytes in database. How do I can convert it to binary sting? 

SELECT BINARY('LQSVPLQSVPSYKLMTY7P5SVPSYN54CMGU');

Is not what I looking for. It will be same 32-byte string, but in BLOB. I need get value from sha1 first. Is there any way in Mysql\Postgresql to do it? I also interested in similar MD5 sting conversion. Is there universal way?

This question related to Storing hexadecimal values as binary in MySQL.

Community
  • 1
  • 1
FirstTimePoster
  • 251
  • 2
  • 9
  • 1
    Something is VERY wrong here. SHA1 is always exactly 20 bytes long in binary form, 40 bytes in hex form. MD5 is, respectively, 16 bytes and 32 in hex, but the pasted string cannot be a hex-encoded binary. Also, those hashes have a vanishingly low chance of producing a purely ASCII string like the one presented. – Seva Alekseyev Sep 14 '11 at 02:59
  • Actually my method works for MD5. Looks like my sha1 string is the only problem. Does anybody knows why/how SHA1 can contain any of [^0-9A-F] characters? – FirstTimePoster Sep 14 '11 at 03:01
  • Probably because it's hex-encoded. Use UNHEX() to produce a binary blob. – Seva Alekseyev Sep 14 '11 at 03:02
  • UNHEX return NULL for every of my "SHA1" sting. It won't work because hex-sting is only [0-9A-F]+ and my string is [0-9A-Z]+ for some reason. – FirstTimePoster Sep 14 '11 at 03:07
  • Which is exactly why I'm saying your post cannot be entirely correct. The suggested string cannot be a SHA1 hash (either in hex or raw) 'cause the length is wrong; the length matches an MD5 in hex, except it's not proper hex. Please check your assumptions. Where did you get this string? Any chance of charset-related corruption on the way? – Seva Alekseyev Sep 14 '11 at 03:14
  • I don't know how it was generated. And why it's called "sha1". I will post more information as soon as I resolve this issue. Thank you for participation. – FirstTimePoster Sep 14 '11 at 03:35
  • Maybe it is just SHA-256 (SHA-2) digest in string form. – Grzegorz Szpetkowski Sep 14 '11 at 22:48

1 Answers1

1

As noted in the comments, basic SHA1 and MD5 values are hex escaped. This can easily be converted into a bytea by:

 select ('\x' || md5('test'))::bytea;

This requires a version of PostgreSQL new enough to support hex encoding of byteas.

MySQL likely has a very different way to do this and given the inconsistencies between the types you can't expect a cross-db solution here.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182