How to create sha256 hash with salt

user_599911 0
Viewed 2274

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
google-bigquery hash salt
2 Answers

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 |
+------------------------------------------------------------------+
user_112062020

With the help of @Yun I have managed to solve this.

To apply HMAC you will need to include external library file in the example function bellow.

CREATE TEMP FUNCTION USER_HASH(message STRING, secret STRING)
  RETURNS STRING
  LANGUAGE js
  OPTIONS (
    -- copy this Forge library file to Storage:
    -- https://cdn.jsdelivr.net/npm/node-forge@0.7.0/dist/forge.min.js
    -- @see https://github.com/digitalbazaar/forge
    library=["gs://.../forge.min.js"]
  )
  AS
"""
  var hmac = forge.hmac.create();
  hmac.start('sha256', secret);
  hmac.update(message);
  return hmac.digest().toHex();
""";


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

-- Row  f0_
-- 1    172f052058445afd9fe3afce05bfec573b5bb4c659bfd4cfc69a59d1597a0031
user_5999110
Related