混淆/掩盖/混淆个人信息

Question

我正在寻找一种本地方法来混淆生产数据，以便在开发和测试中使用。我已经编写了几个脚本，可以生成随机社保号码、移动出生日期、混淆电子邮件等等。但是在尝试混淆客户姓名时遇到了困难。我希望保留真实姓名，因此我们仍然可以使用搜索功能，所以随机字母生成不可行。截至目前，我尝试的方法是构建一个包含表中所有姓氏的临时表，然后从临时表中随机选择一个值更新客户表。像这样：

DECLARE @Names TABLE (Id int IDENTITY(1,1),[Name] varchar(100))

/* Scramble the last names (randomly pick another last name) */
INSERT @Names SELECT LastName FROM Customer ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)
UPDATE [Customer ORDERED BY ROWID] SET LastName=(SELECT [Name] FROM @Names WHERE ROWID=Id)

这在测试中效果不错，但处理更大量的数据时会完全降低速度（>20分钟针对40K行）。

这一切都是为了问一个问题：如何在保持真实姓名和生产数据权重的情况下混淆客户姓名？

更新：总是会出现这种情况，你试图在帖子中放入所有信息，但你会忘记一些重要的东西。这些数据也将用于我们的销售和演示环境，这些环境是公开的。有些答案是我正在尝试做的事情，即“切换”名称，但我的问题实际上是如何在T-SQL中编写代码？

- Chip

Answer 1

我使用generatedata。这是一个开源的PHP脚本，可以生成各种虚拟数据。

- Peter Hoffmann

---

非常好的提示 - 谢谢。[这是我多年来一直想写但从未有时间写的东西之一]... - Richard Harrison

Answer 2

一个非常简单的解决方案是对文本进行ROT13编码。

更好的问题可能是为什么你觉得需要混淆数据？如果你有一个加密密钥，你也可以考虑运行文本通过DES或AES或类似的加密算法。然而，这可能会带来潜在的性能问题。

- warren

---

正如我所说，我需要与生产环境中相似/相同权重的真实名称，以便搜索具有类似的性能。 - Computer Chip

附加的 ROT13 并没有真正混淆数据，因为它是一种容易可逆的算法... - Guvante

是的，它很容易被逆转 - 但它确实符合“掩盖”或“混淆”的标准 - 至少你需要认识到它已经被ROT13处理过，并将其解密 :) - warren

Answer 3

做这种事情的时候，我通常会编写一个小程序，首先在两个数组中加载大量的名字和姓氏，然后使用数组中的随机名字/姓氏更新数据库。即使对于非常大的数据集（200,000+条记录），它也能运行得非常快。

- Milan Babuskov

Answer 4

我使用一种方法，将名称中的字符更改为在英文名称使用频率相同的其他字符。 显然，名称中字符的分布与正常的英语对话不同。 例如，“x”和“z”的出现频率为0.245％，因此它们被交换。 在另一个极端，“w”使用5.5％，“s”使用6.86％，“t”使用15.978％。 我将“s”更改为“w”，“t”更改为“s”，“w”更改为“t”。

我将元音字母“aeio”放在单独的组中，以便元音只被另一个元音替换。 同样，“q”，“u”和“y”根本不被替换。 我的分组和决策完全是主观的。

最终我得到了7个不同的“组”，每个组由2-5个字符组成，主要基于频率。 每个组内的字符与该组中的其他字符交换。

总的结果是名称看起来有点像可能是名字，但并非“这里的名字”。

Original name     Morphed name
Loren             Nimag
Juanita           Kuogewso
Tennyson          Saggywig
David             Mijsm
Julie             Kunewa

以下是我使用的SQL代码，其中包含一个“TitleCase”函数。根据我在网上找到的不同字母频率，有两个不同版本的“变形”名称。最初的回答：

这是我使用的SQL代码，其中包含“TitleCase”函数。我根据在网上找到的不同字母频率，创建了两个不同版本的“morphed”名称。

--    from     https://dev59.com/hnVC5IYBdhLWcg3woStW#28712621

-- Convert and return param as Title Case

CREATE FUNCTION [dbo].[fnConvert_TitleCase] (@InputString VARCHAR(4000) )
RETURNS VARCHAR(4000)AS
BEGIN
DECLARE @Index INT
DECLARE @Char CHAR(1)
DECLARE @OutputString VARCHAR(255)

SET @OutputString = LOWER(@InputString)
SET @Index = 2
SET @OutputString = STUFF(@OutputString, 1, 1,UPPER(SUBSTRING(@InputString,1,1)))

WHILE @Index <= LEN(@InputString)
BEGIN
   SET @Char = SUBSTRING(@InputString, @Index, 1)
   IF @Char IN (' ', ';', ':', '!', '?', ',', '.', '_', '-', '/', '&','''','(','{','[','@')
      IF @Index + 1 <= LEN(@InputString)
      BEGIN
         IF @Char != ''''  OR  UPPER(SUBSTRING(@InputString, @Index + 1, 1)) != 'S'
            SET @OutputString = STUFF(@OutputString, @Index + 1, 1,UPPER(SUBSTRING(@InputString, @Index + 1, 1)))
      END
         SET @Index = @Index + 1
      END

   RETURN ISNULL(@OutputString,'')

END
Go

--    00.045 x 0.045%
--    00.045 z 0.045%
--
--    Replace(Replace(Replace(TS_NAME,'x','#'),'z','x'),'#','z')
--
--    00.456 k 0.456%
--    00.511 j 0.511%
--    00.824 v 0.824%
--    kjv
--    Replace(Replace(Replace(Replace(TS_NAME,'k','#'),'j','k'),'v','j'),'#','v')
--
--    01.642 g 1.642%
--    02.284 n 2.284%
--    02.415 l 2.415%
--    gnl
--    Replace(Replace(Replace(Replace(TS_NAME,'g','#'),'n','g'),'l','n'),'#','l')
--
--    02.826 r 2.826%
--    03.174 d 3.174%
--    03.826 m 3.826%
--    rdm
--    Replace(Replace(Replace(Replace(TS_NAME,'r','#'),'d','r'),'m','d'),'#','m')
--
--    04.027 f 4.027%
--    04.200 h 4.200%
--    04.319 p 4.319%
--    04.434 b 4.434%
--    05.238 c 5.238%
--    fhpbc
--    Replace(Replace(Replace(Replace(Replace(Replace(TS_NAME,'f','#'),'h','f'),'p','h'),'b','p'),'c','b'),'#','c')
--
--    05.497 w 5.497%
--    06.686 s 6.686%
--    15.978 t 15.978%
--    wst
--    Replace(Replace(Replace(Replace(TS_NAME,'w','#'),'s','w'),'t','s'),'#','t')
--
--
--    02.799 e 2.799%
--    07.294 i 7.294%
--    07.631 o 7.631%
--    11.682 a 11.682%
--    eioa
--    Replace(Replace(Replace(Replace(Replace(TS_NAME,'e','#'),'i','ew'),'o','i'),'a','o'),'#','a')
--
--    -- dont replace
--    00.222 q 0.222%
--    00.763 y 0.763%
--    01.183 u 1.183%

-- Obfuscate a name
Select
   ts_id,
   Cast(ts_name as varchar(42)) as [Original Name]

   Cast(dbo.fnConvert_TitleCase(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(TS_NAME,'x','#'),'z','x'),'#','z'),'k','#'),'j','k'),'v','j'),'#','v'),'g','#'),'n','g'),'l','n'),'#','l'),'r','#'),'d','r'),'m','d'),'#','m'),'f','#'),'h','f'),'p','h'),'b','p'),'c','b'),'#','c'),'w','#'),'s','w'),'t','s'),'#','t'),'e','#'),'i','ew'),'o','i'),'a','o'),'#','a')) as VarChar(42)) As [morphed name] ,
   Cast(dbo.fnConvert_TitleCase(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(TS_NAME,'e','t'),'~','e'),'t','~'),'a','o'),'~','a'),'o','~'),'i','n'),'~','i'),'n','~'),'s','h'),'~','s'),'h','r'),'r','~'),'d','l'),'~','d'),'l','~'),'m','w'),'~','m'),'w','f'),'f','~'),'g','y'),'~','g'),'y','p'),'p','~'),'b','v'),'~','b'),'v','k'),'k','~'),'x','~'),'j','x'),'~','j')) as VarChar(42)) As [morphed name2]

From
   ts_users
;

- mnemotronic

---

@memotronic：我喜欢你的方法，想在我的场景中实现它，但是我有两个问题：

1. 为什么要用 '#' 替换第一个字符，然后再用另一个字符替换它？为什么不能一开始就替换呢？这样做有什么原因吗？
2. 你能分享一下你使用的分布度量链接吗？

谢谢。 - Zee786

@Zee786：我在交换字符时使用“＃”作为临时字符，因为我不知道如何在单行中将所有的'x'字符替换为'z'，并将所有的'z'字符替换为'x'。Replace（Replace（TS_NAME，'x'，'z'），'z'，'x'）只会将所有的'z'字符更改为'x'，而不会将'x'更改为'z'（我认为-我没有测试）。抱歉-我记不得我从哪里获得字母频率统计数据了。 - mnemotronic

Answer 5

这里有两种方法，一种使用可逆的ROT47加密，另一种是随机加密。您可以在任何一种方法中添加PK以链接回“未加密”的版本。

declare @table table (ID int, PLAIN_TEXT nvarchar(4000))
insert into @table
values
(1,N'Some Dudes name'),
(2,N'Another Person Name'),
(3,N'Yet Another Name')

--split your string into a column, and compute the decimal value (N) 
if object_id('tempdb..#staging') is not null drop table #staging
select 
    substring(a.b, v.number+1, 1) as Val
    ,ascii(substring(a.b, v.number+1, 1)) as N
    --,dense_rank() over (order by b) as RN
    ,a.ID
into #staging
from (select PLAIN_TEXT b, ID FROM @table) a
    inner join
         master..spt_values v on v.number < len(a.b)
where v.type = 'P' 

--select * from #staging


--create a fast tally table of numbers to be used to build the ROT-47 table.

;WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )



--Here we put it all together with stuff and FOR XML
select 
    PLAIN_TEXT
    ,ENCRYPTED_TEXT =
        stuff((
        select
            --s.Val
            --,s.N
            e.ENCRYPTED_TEXT
        from #staging s
        left join(
        select 
            N as DECIMAL_VALUE
            ,char(N) as ASCII_VALUE
            ,case 
                when 47 + N <= 126 then char(47 + N)
                when 47 + N > 126 then char(N-47)
            end as ENCRYPTED_TEXT
        from cteTally
        where N between 33 and 126) e on e.DECIMAL_VALUE = s.N
        where s.ID = t.ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '')
from @table t


--or if you want really random
select 
    PLAIN_TEXT
    ,ENCRYPTED_TEXT =
        stuff((
        select
            --s.Val
            --,s.N
            e.ENCRYPTED_TEXT
         from #staging s
        left join(
        select 
            N as DECIMAL_VALUE
            ,char(N) as ASCII_VALUE
            ,char((select ROUND(((122 - N -1) * RAND() + N), 0))) as ENCRYPTED_TEXT
        from cteTally
        where (N between 65 and 122) and N not in (91,92,93,94,95,96)) e on e.DECIMAL_VALUE = s.N
        where s.ID = t.ID
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 0, '')
from @table t

- S3S

Answer 6

以下方法适用于我们，假设我们有2个表：Customers和Products：

CREATE FUNCTION [dbo].[GenerateDummyValues]
(
    @dataType varchar(100),
    @currentValue varchar(4000)=NULL
)
RETURNS varchar(4000)
AS
BEGIN
IF @dataType = 'int'
    BEGIN
        Return '0'
    END
ELSE IF @dataType = 'varchar' OR @dataType = 'nvarchar' OR @dataType = 'char' OR @dataType = 'nchar'
    BEGIN
        Return 'AAAA'
    END
ELSE IF @dataType = 'datetime'
    BEGIN
        Return Convert(varchar(2000),GetDate())
    END
-- you can add more checks, add complicated logic etc
Return 'XXX'
END

上述函数将根据传入的数据类型生成不同的数据。

现在，对于每个表的每列中不含有单词"id"的列，请使用以下查询来生成进一步的操作数据的查询：

select 'select ''update '' + TABLE_NAME + '' set '' + COLUMN_NAME + '' = '' +  '''''''' + dbo.GenerateDummyValues( Data_type,'''') + '''''' where id = '' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, ' + table_name + ' where RIGHT(LOWER(COLUMN_NAME),2) <> ''id'' and TABLE_NAME = '''+ table_name + '''' + ';' from  INFORMATION_SCHEMA.TABLES;

当您执行上述查询时，它将为每个表和该表的每个列生成更新查询，例如：

select 'update ' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = ' +  '''' + dbo.GenerateDummyValues( Data_type,'') + ''' where id = ' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, Customers where RIGHT(LOWER(COLUMN_NAME),2) <> 'id' and TABLE_NAME = 'Customers';
select 'update ' + TABLE_NAME + ' set ' + COLUMN_NAME + ' = ' +  '''' + dbo.GenerateDummyValues( Data_type,'') + ''' where id = ' + Convert(varchar(10),Id) from INFORMATION_SCHEMA.COLUMNS, Products where RIGHT(LOWER(COLUMN_NAME),2) <> 'id' and TABLE_NAME = 'Products';

现在，当您执行上述查询时，您将获得最终的更新查询，这将更新您的表格数据。

您可以在任何SQL服务器数据库上执行此操作，无论您有多少个表格，它都会为您生成可进一步执行的查询。

希望这能帮到您。

- AUR

Answer 7

另一个生成形状虚假数据集的网站，提供T-SQL输出选项： https://mockaroo.com/

- Josh

Answer 8

为什么不使用某种随机名称生成器呢？

- Ryan

Answer 9

我自己遇到了同样的问题，并想出了一种替代解决方案，可能适用于其他人。

思路是对名称使用MD5，然后取其最后3个十六进制数字映射到一个名称表中。您可以分别对名字和姓氏进行此操作。

3个十六进制数字表示0到4095之间的十进制数，因此我们需要一个包含4096个名字和4096个姓氏的列表。

因此，conv(substr(md5(first_name), 3),16,10)（在MySQL语法中）将是从0到4095的索引，可以与一个包含4096个名字的表连接。相同的概念也可以应用于姓氏。

使用MD5（而不是随机数）保证原始数据中的名称始终映射到测试数据中的相同名称。

您可以在此处获取名称列表：

https://gist.github.com/elifiner/cc90fdd387449158829515782936a9a4

- elifiner

Answer 10

使用临时表，查询速度非常快。我刚刚在6万行数据上运行了4秒钟。今后我会一直使用这个方法。

DECLARE TABLE #Names 
(Id int IDENTITY(1,1),[Name] varchar(100))

/* 混淆姓氏（随机选择另一个姓氏）*/

INSERT #Names
  SELECT LastName 
  FROM Customer 
  ORDER BY NEWID();
WITH [Customer ORDERED BY ROWID] AS
(SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ROWID, LastName FROM Customer)

UPDATE [Customer ORDERED BY ROWID] 

SET LastName=(SELECT [Name] FROM #Names WHERE ROWID=Id)

DROP TABLE #Names

- Tom Powers

---

你仍然可能会得到一个糟糕的结果，然后有两个...等等。NewID() 生成 UUID。我改正了。 - Broam