How can I generate a custom - UUID with some salt/ private key out of some string column in table from sql-query?

Question

let say we have a Table of employees having the column EmployeeName, Department:

EmployeeName	Department
John	HR.
Jenny	Tech.

I want to have a new column in a view taking EmployeeName as data along with static private-key. Don't wanna change the table structure, but just to get UUID in select query For eg: select EmployeeName, Department, UUID({private-key},EmployeeName) as UUID from EmployeeTable

View result example:

EmployeeName	Department	UUID.
John	HR.	f1cf5eqf0-63c6-11ec-90d6-0242ac120003
Jenny	Tech.	z1cf5emk0-63c6-11ec-90d6-0242ac120003

is this achievable?

As far as I know, UUID is randomly generated not by a value. What you are looking for is a hash, not UUID. — Amir M, Dec 23 '21 at 08:20
I removed the conflicting DBMS tags. Please add only one tag for the database product you are really using. — , Dec 23 '21 at 08:36

Bill Karwin · Answer 1 · 2021-12-23T20:54:01.720

3

It sounds like you want a cryptographic hash, not a UUID. A UUID is not a function of the input string, it's just a unique id.

You could use MD5() to create a 128-bit cryptographic hash of the EmployeeName, which is the same length as a UUID:

SELECT EmployeeName, Department,
  MD5(CONCAT(@privateKey, EmployeeName)) AS Not_Really_Uuid
FROM EmployeeTable;

Re comments:

I was assuming you wanted the hash to be 128-bits like a UUID. Reversible encryption generally produces a string that is proportional in length to the input. Whereas a hash function can take any input, long or short, and produces a hash string of exactly the same length every time. In the case of MD5, this is 128 bits, like a UUID. Other hash functions produce strings of more bits.

A hash function is not reversible. How could a 128 bit hash string contain enough information to recover an input of arbitrary length?

edited Dec 23 '21 at 20:54

answered Dec 23 '21 at 08:17

Bill Karwin

538,548
86
673
828

can it be decoded by using the same private key? by some programming language like java or python, if I want to use or check the valid hash – Rahul Sharma Dec 23 '21 at 08:40
1

@RahulSharma hashes like md5, sha224, sha256 aren't meant to be decoded or decrypted. They are one-way. They can be used to compare between 2 systems. If 2 hashes created by the same method are the same, then the odds are very high that the values are the same. – LukStorms Dec 23 '21 at 10:08

score 1 · Answer 2 · answered Dec 23 '21 at 08:29

1

Use sha256, it is deterministic strong cryptographic one-way function:

SHA2(CONCAT(@salt, EmployeeName), 256)

Sha256 is collision tolerant, different input will produce different hashes, the same input will produce the same hash. So, you can use it for joins and grouping the same as original values

answered Dec 23 '21 at 08:29

leftjoin

36,950
8
57
116

can it be decoded by using the same private key? by some programming language like java or python, if I want to use or check the valid hash – Rahul Sharma Dec 23 '21 at 08:44
1

@RahulSharma No. This is not encryption. sha256 is a one-way hash, impossible to decode, though it is deterministic: for the same argument, it will be the same result. If you want to decode, then you need encryption using private key, not salt. Salt is a method to protect hash from dictionary and brute-force attack – leftjoin Dec 23 '21 at 08:49
@RahulSharma Encryption: https://www.mysql.com/products/enterprise/encryption.html – leftjoin Dec 23 '21 at 08:58

score 0 · Answer 3 · answered Dec 23 '21 at 15:26

The benefit of using hash algorithms is that you can be sure that the length of the hash string is constant.
So you can define a varchar of that length for it.

But if you really want an id that can be decrypted, then the length will depend on the encrypted name.

For example, here's a test that works in Postgresql.

It uses 2 functions, for encrypting and decrypting the names. The base64 encoding is just to make the encrypted string shorter.

create table test (name varchar(50), nameid varchar(80));

insert into test (name) values 
('Jane Doe'), 
('John James Archibald Doe Senior');

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION fn_encrypt_name(txt text)
RETURNS text
AS $$
DECLARE salt text;
BEGIN
  salt := 'briny';
  RETURN 
    encode(
      encrypt(txt::bytea, salt::bytea, 'bf'::text)
    , 'base64');
END $$ LANGUAGE plpgsql STRICT IMMUTABLE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION fn_decrypt_nameid(txt text)
RETURNS text
AS $$
DECLARE salt text;
BEGIN
    salt := 'briny';
    RETURN 
     convert_from(
      decrypt(decode(txt,'base64')::bytea, salt::bytea, 'bf'::text)
       , 'SQL_ASCII');
END $$ LANGUAGE PLPGSQL STRICT IMMUTABLE SECURITY DEFINER;

Normal users shouldn't be using those functions.

REVOKE ALL ON FUNCTION fn_encrypt_name(txt text) FROM PUBLIC;
REVOKE ALL ON FUNCTION fn_decrypt_nameid(txt text) FROM PUBLIC;
COMMIT;

update test
set nameid =fn_encrypt_name(name)
where nameid is null;

select *
, length(name) as len_name
, length(nameid) as len_nameid
, fn_decrypt_nameid(nameid) as decrypted_uuid
from test

name                            | nameid                                       | len_name | len_nameid | decrypted_uuid                 
:------------------------------ | :------------------------------------------- | -------: | ---------: | :------------------------------
Jane Doe                        | sy/4OkdCmm6maeuvjgpHIw==                     |        8 |         24 | Jane Doe                       
John James Archibald Doe Senior | f2IxooMK3FtPs1Fd2fdr4Slv6j5+23mMeul43vFtbDo= |       31 |         44 | John James Archibald Doe Senior

Demo on db<>fiddle here

How can I generate a custom - UUID with some salt/ private key out of some string column in table from sql-query?

3 Answers3