SQL 敏感数据加密与解密

SQL版本: SQL 2012

最近工作，内网WebService映射到外网，敏感数据(收集号码、身份证号)被waf(防火墙)和谐为“*”号，想着接口里将此类敏感数据进行加密返回，特在百度查询了下，进行了相关学习。

原始网址: SQL Server加密与解密 - 缥缈的尘埃 - 博客园

1. PassPhrase加密

--以下内容中的ceaning字符串作为加密和解密的密钥使用

--加密
CREATE FUNCTION dbo.EncryptPass(@EnPass VARCHAR(32))
    RETURNS VARBINARY(MAX)
AS  
BEGIN
    DECLARE @Result VARBINARY(MAX)
    SET @Result=EncryptByPassPhrase('ceaning',@EnPass)
    RETURN @Result
END

--解密
CREATE FUNCTION dbo.DecryptPass(@DePass VARBINARY(MAX))
    RETURNS VARCHAR(32)
AS  
BEGIN 
    DECLARE @Result VARCHAR(32)
    SET @Result=CONVERT(VARCHAR,DecryptByPassPhrase('ceaning',@DePass))
    RETURN @Result
END

--测试
SELECT dbo.EncryptPass('15982425538') AS RESULT
SELECT dbo.DecryptPass(0x01000000F2C7CCB4EBBDB45D834A01F12FB5CE248BD537C40A3B9A2D97C17D9A259C3F51) AS RESULT
SELECT dbo.DecryptPass(dbo.EncryptPass('15982425538')) AS RESULT

2. 证书加密

--以下内容中的ceaning字符串作为加密和解密的密钥使用

--创建证书
CREATE CERTIFICATE EncodeCert
ENCRYPTION BY PASSWORD='ceaning'
WITH SUBJECT='cwx', START_DATE='2022-01-01', EXPIRY_DATE='2022-12-31'

--证书加密
CREATE FUNCTION dbo.EncryptCert(@EnPass VARCHAR(32))
    RETURNS VARBINARY(MAX)
AS  
BEGIN
    DECLARE @Result VARBINARY(MAX)
    SET @Result=EncryptByCert(Cert_ID('EncodeCert'),@EnPass)
    RETURN @Result
END

--证书解密
CREATE FUNCTION dbo.DecryptCert(@DePass VARBINARY(MAX))
    RETURNS VARCHAR(32)
AS  
BEGIN 
    DECLARE @Result VARCHAR(32)
    SET @Result=CONVERT(VARCHAR,DECRYPTBYCERT(Cert_ID('EncodeCert'),@DePass,N'ceaning'))
    RETURN @Result
END

--测试
SELECT dbo.EncryptCert('15982425538')
SELECT dbo.DecryptCert(0xA521A48B2EC1CECBCECB2C138C868CC68D9DE93AB183F1C860C5857CAD286CD0B03123D2098966B2F282A8928307B3F9CAE73C50656E4E6E177B04BE2EF4B2782FB18BB09A4996AF64D79051C8514BDE3C11B02E0B21E6B18948785C151088665C4354BADDE259C41B26303711B7670300AF2A91159FE96A9D9D6EC0E0FC9C0A)
SELECT dbo.DecryptCert(dbo.EncryptCert('15982425538'))