google-bigqueryhashopensslmariadbsha512

Google BigQury SHA512 function output vs OpenSSL difference

A hash is not an opinion, why Google BigQuery sha512 function output is inconsistent with OpenSSL?

Google BigQuery output of SELECT SHA512("Hello World"); LHT9F+2v2A6ER7DUZ0HuJDt+t03SFJoKsbkkb7MDgvJ+hT2FhXGeDmfL2g2qj1FnEGRhXWRa4nrLFb+xRH9Fmw==

MariaDB output of SELECT SHA2("Hello World", 512); and OpenSSL output of echo -n "Hello World" | sha512sum both yield: 2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d8585719e0e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b

Even a base64 of the output is widely different from BigQuery's: SELECT TO_BASE64(SHA2("Hello World", 512)); MmM3NGZkMTdlZGFmZDgwZTg0NDdiMGQ0Njc0MWVlMjQzYjdlYjc0ZGQyMTQ5YTBhYjFiOTI0NmZi MzAzODJmMjdlODUzZDg1ODU3MTllMGU2N2NiZGEwZGFhOGY1MTY3MTA2NDYxNWQ2NDVhZTI3YWNi MTViZmIxNDQ3ZjQ1OWI=

Solution

  • You're Google BigQuery output is the base64 encoded binary hash, not base64 encoded hexadecimal hash. A simple PHP script shows where the BigQuery comes from.

      $string = "Hello World";
  $hashstring = hash("sha512", $string, false);
  echo $hashstring."\n";
  echo base64_encode(hex2bin($hashstring))."\n";

    Output:

    2c74fd17edafd80e8447b0d46741ee243b7eb74dd2149a0ab1b9246fb30382f27e853d8585719e0e67cbda0daa8f51671064615d645ae27acb15bfb1447f459b
LHT9F+2v2A6ER7DUZ0HuJDt+t03SFJoKsbkkb7MDgvJ+hT2FhXGeDmfL2g2qj1FnEGRhXWRa4nrLFb+xRH9Fmw==