CODEWITHSUNDEEP
t-sqlencryptionobfuscation
AnarchistGeek
AnarchistGeek

Reputation: 3186

Obfuscating an integer column

I could have posted this to SQL forums, but I rather look for an idea or best practice, that is why I have chosen this forum. I have got an integer column in SQL called Payroll Number and it is unique to employee. we will be interrogating employee information from this system via SQL views and put into another system, but we dont want payroll numbers to be appeared as they are on this system. Therefore, we need to hash those payroll numbers on SQL so that views will serve hashed user-friendly numbers.

I spent quite a lot of time reading encryption techniques in SQL, but they are using complex algorithms to hash data and produce binary. But what I am after is less complext and obfuscating a number rather than hashing.

For instance, payroll number is 6 characters long(145674), I want to be able to generate maybe 9-10 characters long integer number from this number and use it on other systems.

I had a look at XOR'ing but I need something more robust and elegant.

How do you guys do these things? Do you write your simple algorithm obfuscate your integers? I need to do this on SQL leve, what do you suggest?

Thanks for your help

Regards

Upvotes: 0

Views: 655

Answers (1)

Hogan
Hogan

Reputation: 70513

It is not hard to hash a value but it is hard to hash a value and be sure of uniqueness and have it be a number. However, I do have a cross database solution.

Make a new table - with two columns, id (auto generated from random starting point) and payroll id.

Everytime you need to use a user externally insert them into this table. This will give you a local unique id you can use (internally and externally) but it is not the payroll id.

In fact, if you have an internal id already (eg user id from the user table) just use that. There is no advantage to hashing this value if it is never decoded. However, you can use the autogen of id as your random unique hash -- it has all the properties you need.

Upvotes: 2

Related Questions