hashgoogle-bigquerysalt-cryptography

How to create sha256 hash with salt

I would like to migrate hash generation to BigQuery which has SHA256, but does not have salt as parameter.

For example in R I can do something like this:

library(openssl)
sha256("test@gmail.com", key = "111")
# [1] "172f052058445afd9fe3afce05bfec573b5bb4c659bfd4cfc69a59d1597a0031"

Update

same with python based on an answer here:

import hmac
import hashlib
print(hmac.new(b"111", b"test@gmail.com", hashlib.sha256).hexdigest())
# 172f052058445afd9fe3afce05bfec573b5bb4c659bfd4cfc69a59d1597a0031
Solution

  • I hope by "migrate", you mean to migrate the logic not the exact byte-wise output from R Sha256() function.

    R is using hmacsha256 and looking at Microsoft's HMACSHA256 class, it can be roughly expressed as:

    The HMAC process mixes a secret key with the message data, hashes the result with the hash function, mixes that hash value with the secret key again, and then applies the hash function a second time. The output hash is 256 bits in length.

    create temp function hmacsha256(content STRING, key STRING)
AS (SHA256(
     CONCAT(
       TO_HEX(SHA256(CONCAT(content, key))), key)
       ));

SELECT TO_HEX(hmacsha256("test@gmail.com", "111"));

    Output:

    +------------------------------------------------------------------+
|                               f0_                                |
+------------------------------------------------------------------+
| 4010f74e5c69ddbe1e36975f7cb8be64bcfd1203dbc8e009b29d7a12a8bf5fef |
+------------------------------------------------------------------+