1

I would like to obfuscate (scramble) sensitive data from a SQL Server database, but in the way which will provide:

  • irreversibility (the plaintext can't be derived from the obfuscated data),
  • obfuscated data length needs to be the same as a length of data before obfuscation.
  • obfuscated value does not need to be unique for repeated obfuscations of the same input value. To be honest, I rather like getting the same value for the same input which can used (e.g. some matching data in different tables, probably useful in test cases).

Example:

Abc -> zyx (lenght: 3)
StackOverflow -> a65vr4doqjd (lenght: 11)

Usually I avoid "home made" algorithms, so are you aware of some MS builtin solution which could provide this kind of obfuscation?

I hope I expressed my problem clearly, otherwise let me know and I'll try to add as much info as needed.

  • 4
    This would meet your criteria: REPLICATE('1',LEN(field)): Abc -> 111 StackOverflow -> 1111111111111 ...and definitely irreversible. – piet.t Jul 4 '16 at 13:00
  • Do you want it to still look like a character string (with random characters)? Kind of like base64 encoding but preserving the length and irreversible? – Andriy M Jul 4 '16 at 13:18
  • @piet.t - this is not what I'm looking for, you know this :) – boleslaw.smialy Jul 4 '16 at 14:08
  • @boleslaw.smialy That's why it's a comment and not an answer ;) – piet.t Jul 4 '16 at 14:09
  • 2
    Are there any particular requirements that the solution needs to meet in terms of characters used etc.? Should the same value obfuscated twice produce the same or different results? – Martin Smith Jul 4 '16 at 15:32
6

No, I am not aware of any built-in function that does exactly this. But, you can still accomplish this without doing anything too complicated.

You could use the built-in CRYPT_GEN_RANDOM function (introduced in SQL Server 2008 R2) which generates random values based on a supplied length. The output is in hex/binary values so each byte returned is represented as two alphanumeric characters (hence the / 2 + 1 part below).

DECLARE @InputString NVARCHAR(4000) = 'hello';
SELECT SUBSTRING(CONVERT(VARCHAR(8000),
                         CRYPT_GEN_RANDOM((LEN(@InputString) / 2) + 1),
                         2),
                 1,
                 LEN(@InputString)) AS [Obfuscated];

SET @InputString = 'test';
SELECT SUBSTRING(CONVERT(VARCHAR(8000),
                         CRYPT_GEN_RANDOM((LEN(@InputString) / 2) + 1),
                         2),
                 1,
                 LEN(@InputString)) AS [Obfuscated];

Returns something along the lines of:

8C108

9A7A

The only real downside here is that this needs to be done inline as CRYPT_GEN_RANDOM cannot be used in a User-Defined Function (UDF: Scalar or Table-Valued). However, it can still be applied in a set-based approach using a CTE as shown here (just set @MaxLength to the max length of the column being obfuscated):

DECLARE @MaxLength INT = 10;

;WITH cte AS
(
    SELECT CONVERT(VARCHAR(8000),
                   CRYPT_GEN_RANDOM((@MaxLength / 2) + 1),
                2) AS [Random]

)
SELECT tmp.[String],
       cte.[Random],
       SUBSTRING(cte.[Random], 1, LEN(tmp.[String])) AS [Obfuscated]
FROM   (VALUES (N'test'), (N'Hello')) tmp(String)
CROSS JOIN  cte;

Returns something along the lines of:

String    Random          Obfuscated
------    ------------    ----------
test      F99B3888F993    F99B
Hello     D3250E74F0A3    D3250

As you can see, CRYPT_GEN_RANDOM returns a different value for each row.

Also, not sure if this is acceptable or not, but the only alpha characters returned are A - F.


OR, if you want the obfuscation to be repeatable for the same input value, or at least don't mind it being repeatable and prefer that this code be in a function so that it is easier to apply to multiple columns, you can use the HASHBYTES function which, like CRYPT_GEN_RANDOM, returns hex/binary bytes. Unlike CRYPT_GEN_RANDOM, the output length is fixed (in this case at 64 characters since I am using SHA2_256), so I used REPLICATE to repeat the hashed valued if the length of the input string is more than 64 characters. Also unlike CRYPT_GEN_RANDOM, HASHBYTES can be used in a User-Defined Function (UDF) :-).

CREATE FUNCTION dbo.Obfuscate(@InputString NVARCHAR(4000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  SELECT SUBSTRING(REPLICATE(CONVERT(VARCHAR(8000),
                                     HASHBYTES('SHA2_256', @InputString),
                                     2),
                             (LEN(@InputString) / 64) + 1),
                   1,
                   LEN(@InputString)) AS [Obfuscated];
GO

And that can be used as follows:

SELECT tmp.[String],
       LEN(tmp.[String]) AS [InputLength],
       ob.[Obfuscated],
      LEN(ob.[Obfuscated]) AS [OutputLength]
FROM   (VALUES (N'test'), (N'Hello'), (REPLICATE(N'A', 63)),
               (REPLICATE(N'B', 64)), (REPLICATE(N'C', 65)),
               (REPLICATE(N'D', 4000))) tmp(String)
CROSS APPLY dbo.Obfuscate(tmp.[String]) ob;

Returns something along the lines of:

String                       InputLength    Obfuscated                    OutputLength
------                       -----------    ----------                    ------------
test                               4        FE52                                 4
Hello                              5        A07E4                                5
AAAAAAAAAAAAAAAAAAAAAA...         63        4B589C85DE74E76487730F3...          63
BBBBBBBBBBBBBBBBBBBBBB...         64        79813FB6480F354F1C6017A...          64
CCCCCCCCCCCCCCCCCCCCCC...         65        FB4B38FBA41ECC24B5B0F68...          65
DDDDDDDDDDDDDDDDDDDDDD...       4000        5D01CC6508C164E652B5C77...        4000

PLEASE NOTE: If you need alpha characters beyond A - F and/or need to have distinct obfuscated values for distinct input values (i.e. reduce chances of collisions), then either method above can be adapted easily enough to do that.

  • When CRYPT_GEN_RANDOM hex represantation is truncated to the len of original string collisions are quite possible. – Serg Jul 4 '16 at 16:08
  • 2
    @Serg Yes, there can definitely be collisions in both approaches (I just added another method), but there is no indication (yet) from the O.P. that collisions would be a problem. If they will be, then I can make a slight change, but not worth the time for that modification until it is deemed necessary. – Solomon Rutzky Jul 4 '16 at 16:23
  • 1
    @boleslaw.smialy Handling strings of over 64 characters has already been addressed in the code. I mention in the paragraph just above the function definition that I use REPLICATE to handle that (so it repeats the 64 hashed characters for n groups of 64 input characters). And the example shows that the C input string is 65 characters. When I get back home I will update the example to show the input and output lengths so that it is clearer that this case case been handled :-). – Solomon Rutzky Jul 5 '16 at 12:57
  • 1
    @boleslaw.smialy I have updated the test code for the function (at the bottom of the answer) to now include InputLength and OutputLength fields, and added a test case for a string of 4000 characters. And yes, using the hashing method, you will get repeatable values to compare between tables. Just be aware that collisions will cause some values that aren't the same prior to obfuscation to have the same value post-obfuscation. If that is a problem, minor modifications could be made to reduce those occurrences. – Solomon Rutzky Jul 5 '16 at 14:39
  • 1
    @srutzky - perfect, I appreciate your help! – boleslaw.smialy Jul 6 '16 at 9:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.