在Google BigQuery SQL中计算HMAC

问题描述

如何在Google BigQuery中计算HMAC?

How do I calculate an HMAC in Google BigQuery?

BigQuery包含许多与密码有关的函数，例如哈希函数和加密函数，但是缺少HMAC函数(用于计算签名).

BigQuery includes a number of crypto related functions, such as hash functions and encryption functions, but a HMAC function (that calculates a signature) is missing.

推荐答案

BigQuery开发人员显然忘记了添加HMAC函数，但是幸运的是，它可以基于可用的东西来构建. HMAC定义为

The BigQuery developers apparently forgot to add HMAC functions, but fortunately that can be built on what is available. HMAC is defined as

HMAC(Key, Message) = H((Key xor opad) concat H((Key xor ipad) concat Message))

其中，H是哈希函数，并且Key，ipad和opad都是与哈希函数的块大小相同长度的字节字符串.

where H is a hash function andKey, ipad and opad are all byte strings of the same length as the blocksize of the hash function.

BigQuery提供了在自定义函数中实现此功能所需的所有必要构造块.以下代码实现了HMAC_SHA1，但是您可以通过替换哈希函数并更新块大小来将其更改为使用其他哈希函数.

BigQuery provides all the necessary building blocks to implement this in custom functions. The following code implements HMAC_SHA1, but you can change it to use a different hash function by replacing the hash function and updating the block size.

-- HMAC(K, M) = H((K xor opad) concat H((K xor ipad) concat M))
-- K, opad and ipad are all the same size as the hash function's block size
-- ipad = 0x36 repeated to fill the block size
-- opad = 0x5c repeated to fill the block size
-- If K is smaller than the block size, it must be padded with 0x00s

-- This implementation gives the same result as the online HMAC generator at https://www.freeformatter.com/hmac-generator.html


create temp function blocksize() as (64); -- the block size of the hash function, 64 in the case of SHA1
create temp function ipad() as (repeat(b"\x36", blocksize()));
create temp function opad() as (repeat(b"\x5c", blocksize()));

create temp function pad(key BYTES) as (
  concat(key, repeat(b"\x00", blocksize() - byte_length(key)))
);

create temp function hash_if_needed(key BYTES) as (
  if(byte_length(key) > blocksize(), sha1(key), key)
);

-- size of key == block size
create temp function hmac_sha1_core(key BYTES, message BYTES) as (
  sha1(concat(
    key ^ opad(),
    sha1(concat(key ^ ipad(), message))
  ))
);

-- key must not be larger than the block size
create temp function hmac_sha1_bytes(key BYTES, message BYTES) as (
  hmac_sha1_core(pad(key), message)
);

create temp function hmac_sha1(key STRING, message STRING) as (
  hmac_sha1_bytes(hash_if_needed(cast(key as BYTES)), cast(message as BYTES))
);

要进行验证，请尝试select to_hex(hmac_sha1("my secret key", "hello world"))并将其与在 https://上生成的hmac进行比较www.freeformatter.com/hmac-generator.html

To verify, try select to_hex(hmac_sha1("my secret key", "hello world")) and compare it to the hmac generated on https://www.freeformatter.com/hmac-generator.html

这篇关于在Google BigQuery SQL中计算HMAC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！