Results 1 to 9 of 9

Thread: FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL

  1. #1
    Crabby Smurf Umfriend's Avatar
    Join Date
    Mar 2001
    Location
    Netherlands
    Posts
    6,918

    Default FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL

    FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL Server

    Been wrecking my brains and googling like hell. Sure, reversible and deterministic will mean it'll be crackable. I don't care as long as it is harder then a simple REVERSE and wider than Rot13. I can not believe that others are not looking for such a function.

    Blast!

    Sry, had to vent somewhere.
    Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
    [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

  2. #2
    Super MURCer UtwigMU's Avatar
    Join Date
    Jul 2002
    Location
    Sin City
    Posts
    5,253

    Default

    What are you trying to achieve?

    Not expert on MS SQL, know a bit of MySQL.

  3. #3
    Crabby Smurf Umfriend's Avatar
    Join Date
    Mar 2001
    Location
    Netherlands
    Posts
    6,918

    Default

    I need to create a file, basically a table, where certain columns may not be easily readable. For instance, a contract number. It needs to be encoded. However, that contract number may appear more than once in this file and the reader must be aware that certain records relate to the same contract. So, each encryption of contract number needs to be the same. It also needs to be reversible because in certain cases, the client must be provided a key/algorithm/instructions to decode.

    Input is varchar so alphabet-encryption is out. Rot13 won't work as it ecrypts to few values. Hashbytes are not reversible. It does not need to be state of the art, as long as regular bankers and lawyers don't easily hack it. I have seen the use of Reverse for similar issues but thta is a running gag by now I think, I won;t do that.
    Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
    [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

  4. #4
    Super MURCer UtwigMU's Avatar
    Join Date
    Jul 2002
    Location
    Sin City
    Posts
    5,253

    Default

    Quote Originally Posted by Umfriend View Post
    I need to create a file, basically a table, where certain columns may not be easily readable. For instance, a contract number. It needs to be encoded. However, that contract number may appear more than once in this file and the reader must be aware that certain records relate to the same contract. So, each encryption of contract number needs to be the same. It also needs to be reversible because in certain cases, the client must be provided a key/algorithm/instructions to decode.

    Input is varchar so alphabet-encryption is out. Rot13 won't work as it ecrypts to few values. Hashbytes are not reversible. It does not need to be state of the art, as long as regular bankers and lawyers don't easily hack it. I have seen the use of Reverse for similar issues but thta is a running gag by now I think, I won;t do that.

    i think your problem comes from requirement of encryption to be reversible. Could you store hashes and then another table with id hash data (varchar)? Or in some encrypted file?

    I used hashes because I have around 2000 folders with files that I need to make available. So wrote a script that entered hashes of all files in a table. Then did select distinct to create table of unique files and then created table of folders and file ids.

  5. #5
    Crabby Smurf Umfriend's Avatar
    Join Date
    Mar 2001
    Location
    Netherlands
    Posts
    6,918

    Default

    Well, reversible encryption with SQL Server is actually rather simple, it has AES_256 out of the box. I could secure the keys with a lawyer and have them released when required. The issue with that is that AES_256 is not deterministic. So if I would run a SELECT and encrypt ContractNumber on the fly, any subsequent instance of that ContractNumber would be different.

    I could sorta do what you suggested, with AES_256 encrypted values, but it'd be a pain. Not only would I need an additional table solely for this purpose, I would also need to pre-load it. So the first time I would need to have a distinct list of values to be encrypted, encrypt them once, store encrypted value, join in query. Next query would then first have a distinct list of values not yet in table with encrypted values etc. It is certainly doable but just a lot of hassle. Given that I would effectively be taking out the random seed for encryption, it becomes more vulnerable in any case. It would help if there was a way to fix the Initialization Vector. And there is the hassle of permissions that I'd need to deal with.

    If I really need to do this, I would probably use HASHBYTES and secure the encryption table with a lawyer. Still a hassle. And I would have to deal with the, remote, probability of collision... A real PITA this is.

    It really does not have to be that strong, just a tad harder than a simple REVERSE or Rot13.
    Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
    [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

  6. #6
    Super MURCer UtwigMU's Avatar
    Join Date
    Jul 2002
    Location
    Sin City
    Posts
    5,253

    Default

    Quote Originally Posted by Umfriend View Post
    Well, reversible encryption with SQL Server is actually rather simple, it has AES_256 out of the box. I could secure the keys with a lawyer and have them released when required. The issue with that is that AES_256 is not deterministic. So if I would run a SELECT and encrypt ContractNumber on the fly, any subsequent instance of that ContractNumber would be different.

    I could sorta do what you suggested, with AES_256 encrypted values, but it'd be a pain. Not only would I need an additional table solely for this purpose, I would also need to pre-load it. So the first time I would need to have a distinct list of values to be encrypted, encrypt them once, store encrypted value, join in query. Next query would then first have a distinct list of values not yet in table with encrypted values etc. It is certainly doable but just a lot of hassle. Given that I would effectively be taking out the random seed for encryption, it becomes more vulnerable in any case. It would help if there was a way to fix the Initialization Vector. And there is the hassle of permissions that I'd need to deal with.

    If I really need to do this, I would probably use HASHBYTES and secure the encryption table with a lawyer. Still a hassle. And I would have to deal with the, remote, probability of collision... A real PITA this is.

    It really does not have to be that strong, just a tad harder than a simple REVERSE or Rot13.
    Well then, I think MURC SQL knowledge has been exhausted. Stack overflow or reddit is the next step though they will probably tell you to securely encrypt everything and don't give decryption data to client.

    What's wrong with just plain: select distinct contract number and creating table with id index. Then just using index from table of contract numbers your customers are not supposed to see? I'm just generating ideas.

  7. #7
    Crabby Smurf Umfriend's Avatar
    Join Date
    Mar 2001
    Location
    Netherlands
    Posts
    6,918

    Default

    Sorta what I was suggesting with HASHBYTES but simpler because, of course, having such a translation table removes all further ubfuscation requirements.

    Still don;t like it but if I have to....
    Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
    [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

  8. #8
    Moderator dZeus's Avatar
    Join Date
    Aug 2000
    Location
    over there
    Posts
    4,571

    Default

    Quote Originally Posted by Umfriend View Post
    FCOL: Why isn't there a reversible deterministic data obfuscation routine for SQL Server

    Been wrecking my brains and googling like hell. Sure, reversible and deterministic will mean it'll be crackable. I don't care as long as it is harder then a simple REVERSE and wider than Rot13. I can not believe that others are not looking for such a function.

    Blast!

    Sry, had to vent somewhere.
    Check for 'tokenization' solutions for obfuscating the PAN (personal account number). I would be very surprised if that doesn't yield results, as this is a common issue in obtaining PCI-DSS certification.

  9. #9
    Crabby Smurf Umfriend's Avatar
    Join Date
    Mar 2001
    Location
    Netherlands
    Posts
    6,918

    Default

    Thanks, yes, that is basically what I would be doing. At some stage I'll need to do this with actual name/address data as well. Going to be a long list of tokens as they'll need to be consistent inter temporarily as well.
    Join MURCs Distributed Computing effort for Rosetta@Home and help fight Alzheimers, Cancer, Mad Cow disease and rising oil prices.
    [...]the pervading principle and abiding test of good breeding is the requirement of a substantial and patent waste of time. - Veblen

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •