CODEWITHSUNDEEP
google-bigquerytink
Noura
Noura

Reputation: 59

error in DETERMINISTIC_DECRYPT_STRING expression

I am trying to decrypt specific column (ssn) in Bigquery, that is encrypted with key as explained below.

The below query run successfully (trying to decrypt specific ssn value with my key):

DECLARE KEYSET STRING;
SET KEYSET = '{"primaryKeyId":1,"key":[{"keyData":{"typeUrl":"a","value":"a","keyMaterialType":"SYMMETRIC"},"status":"ENABLED","keyId":1,"outputPrefixType":"TINK"}]}';

SELECT ,DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(KEYSET) , FROM_BASE64("value_of_the_encrypted_col"), "") ;

But when I try to pass the encrypted column name(ssn) to the function FROM_BASE64, as below:

DECLARE KEYSET STRING;
SET KEYSET = '{"primaryKeyId":1,"key":[{"keyData":{"typeUrl":"a","value":"a","keyMaterialType":"SYMMETRIC"},"status":"ENABLED","keyId":1,"outputPrefixType":"TINK"}]}';

SELECT ssn ,DETERMINISTIC_DECRYPT_STRING(KEYS.KEYSET_FROM_JSON(KEYSET) , FROM_BASE64(ssn), "") AS d_ssn FROM `project.dataset.table` ;

I am getting the below error:

DETERMINISTIC_DECRYPT_STRING failed: decryption failed; error in DETERMINISTIC_DECRYPT_STRING expression

Not sure why passing the column name instead of hardcoded value isn't working.

Appreciate your support.

Upvotes: 0

Views: 225

Answers (1)

Noura
Noura

Reputation: 59

The issue was that the value in the first raw in the column specified was an invalid base64 string

Upvotes: 0

Related Questions