sql 实现数据脱敏

近日,公司要求对相关查询页面中的用户名、手机号码、生日、地址等敏感信息进行脱敏处理,所谓脱敏也就是隐去其中的一部分,使之不完整,以保护用户个人信息。通过 sql 实现最为简单,在此总结整理一下。业务系统使用的是 Postgresql 数据库,以下语句只保证在 Postgresql 库中有效。

手机号码脱敏

手机号码脱敏要求中间四位以****代替。

使用拼接

使用 left 和 right 拼接这个方法简单好用,易于维护。

select left('17817812598',3)||'****'||right('17817812598',4); --结果为 178****2598

使用 regexp_replace

regexp_replace 函数支持正则表达式,regexp_replace 的第一个参数是需要做脱敏处理的手机号码,第二个参数是一个正则表达式,第三个字段指定替换文本,即:匹配正则表达式的手机号码,中间4位数字将以****隐藏。

select regexp_replace('17817812598','(\d{3})(\d{4})(\d{4})','\1****\3'); --结果为 178****2598

regexp_replace 不会改变原始字段值,只是简单地返回替换后的副本。这里的正则表达式按手机号码的前三位、中间四位和后四位分为3个部分,替换时首尾原样引用,中间部分则替换为****

邮箱脱敏

邮箱脱敏要求@前1位左边的部分以*代替,由于邮箱格式的特殊性:有且只能由一个@符号,以及 regexp_replace 只替换最先匹配到的字符的特性。可以直接使用 regexp_replace 的基本形式,第一个参数指定邮箱,第二个参数指定需要替换的字符,第三个参数指定替换结果字符。

select regexp_replace('abc@abc.com', left('abc@abc.com',position('@' in 'abc@abc.com')-2), '*'); --结果为 *c@123.com

也可以使用正则表达式实现

--处理方式与对手机号码使用的方式相同
select regexp_replace('abc@123.com','(\w*)(\w{1}@)(\w*)','*\2\3'); --结果为 *c@123.com

生日的脱敏

生日要求月份位和日期位分别以**代替,由于公司产品尊重个人选择,生日中的年份是可选的,因此脱敏后的结果可能出形如:1990/**/** 或 /**/** 两种形式。这里直接用 regexp_replace 配合正则表达式实现(也可以通过条件判断加字符串拼接来实现)。

select regexp_replace('12/30','(/{0,1}\d{1,2}/\d{1,2})\Z', '/**/**'); --显示/**/**
select regexp_replace('1990/12/30','(/{0,1}\d{1,2}/\d{1,2})\Z', '/**/**'); --显示1990/**/**

这里的正则表达式是将月份和日期拆出来,月份的开头有个可选的/,结尾的\Z标志用于告诉程序只在末尾部分匹配得到结果字符串(即这个正则表达式只取出来符合条件的月份和日期,接着用 regexp_replace 的第三个参数完成替换)。

用户名和地址的脱敏比较好处理,直接使用 left 拼接实现反而更方便。

发表评论