md5 in bigquery

Question

In BigQuery, I'm using md5 function as:

select md5('<<some string>>') as hashed

which always returns "==" in the last of the letter like:

R7zlx09Yn0hn29V+nKn4CA==

Why does '==' always come with it?

The returned string is base64 encoded I'm pretty sure it is just padding: https://stackoverflow.com/questions/6916805/why-does-a-base64-encoded-string-have-an-sign-at-the-end — mortb, Jan 29 '19 at 07:25
You have a low rate. Important on SO, you have to mark accepted answers by using the tick on the left of the posted answer, below the voting. This will increase your rate. See how this works by visinting this link: http://meta.stackoverflow.com/questions/5234/how-does-accepting-an-answer-work#5235 — Pentium10, Feb 15 '19 at 21:04

score 21 · Accepted Answer · answered Jan 29 '19 at 07:30

You need to use TO_HEX to get the representation you want as md5 returns BYTES and you need strings:

TO_HEX: Converts a sequence of BYTES into a hexadecimal STRING. Converts each byte in the STRING as two hexadecimal characters in the range (0..9, a..f).

select TO_HEX(md5('123456')) as hashed

returns:

e10adc3949ba59abbe56e057f20f883e

score 3 · Answer 2 · answered Feb 04 '19 at 20:23

The = are due to the base64's padding. Nevertheless and according to the documentation, the output should be bytes, but instead the output is a base64 string. You could check this with the following query:

SELECT MD5("Hello World") AS MD5,TO_HEX(MD5("Hello World")) AS BYTES,TO_BASE64(FROM_HEX(TO_HEX(MD5("Hello World")))) as BASE64

With the following output:

Row |MD5                        |BYTES                              |BASE64  
1   |sQqNsWTgdUEFt6mb5y4/5Q==   |b10a8db164e0754105b7a99be72e3fe5   |sQqNsWTgdUEFt6mb5y4/5Q=

md5 in bigquery

2 Answers2