SQL Server 数据加密实战——应用用户密码的加密和验证

在应用开发中，我们会经常遇到一个问题，即用户密码如何加密？登录应用时如何验证用户密码的正确性？本文将使用EncryptByPassPhrase函数实现这两个过程。

首先我们在数据库EncryptionTest库中创建会员信息表MemberInfo，这里我们只创建两个列，一个会员编号，一个密码列，实际生产中需要更多的列来记录信息，这里就不列举了。

USE EncryptionTest
GO
CREATE TABLE MemberInfo(
   MemberID INT NOT NULL PRIMARY KEY
  ,PWD VARBINARY(256)
)

封装加密函数

CREATE FUNCTION EncryptByPassPhraseTest
(@pwd varchar(15),@MemberID INT)
RETURNS VARBINARY(256)
WITH ENCRYPTION
BEGIN
      DECLARE @EncryptPWD VARBINARY(256)
      SET @EncryptPWD= EncryptByPassPhrase('This is password',@pwd,1,CONVERT(VARBINARY,@MemberID))
      RETURN @EncryptPWD
END

函数中使用SQL Server 加密函数EncryptByPassPhrase对字符串进行加密，传入密码字符串和会员编号，输出加密二进制串。

测试函数的可用性

SELECT  dbo.EncryptByPassPhraseTest('dfe2aHk(', '123456789')

可以看到，函数成功的对字符串进行加密处理。

下面我们给出会员注册过程：

/*
      注册过程
*/
CREATE PROC Register
      @MemberID INT
      ,@PWD VARCHAR(15)
AS
BEGIN
      SET NOCOUNT ON
      DECLARE @EncryptPWD VARBINARY(256)
      SELECT @EncryptPWD=dbo.EncryptByPassPhraseTest(@PWD,@MemberID)
      INSERT INTO MemberInfo(MemberID,PWD)
      VALUES(@MemberID,@EncryptPWD)
END   

该过程调用了加密函数，对传入的密码进行加密。

然后我们注册两个会员，用于后面登陆验证的试验：

EXEC Register 123456,'adfe$3AdeF!'
EXEC Register 654321,'ddfeS3#Rd6'

从会员信息表可以看到，注册会员的密码已经实现加密。

密码验证过程

会员登录应用APP，即需要验证会员号和密码是否匹配。完全匹配，才能登录应用APP。我们接下来创建密码验证的过程，因为使用EncryptByPassPhrase每次加密同一个字符串，其结果均不相同，所以我们不能够使用加密字符串来验证密码的准确性。只能通过解密库中存储的密码，与会员输入的密码进行比较来验证密码是否匹配。所以下面先创建一个解密函数，同样解密函数也是加密的：

USE EncryptionTest
GO
/*
      解密函数
*/
CREATE FUNCTION DencryptByPassPhraseTest
(@pwd VARBINARY(256),@MemberID INT)
RETURNS VARCHAR(15)
WITH ENCRYPTION
BEGIN
      DECLARE @DencryptPWD VARCHAR(15)
      SET @DencryptPWD= CONVERT(VARCHAR(15),DECRYPTBYPASSPHRASE('This is password',@pwd,1,CONVERT(VARBINARY,@MemberID)))
      RETURN @DencryptPWD
END
GO

创建验证密码的过程，过程中使用到解密函数：

/*
      密码验证过程
*/
CREATE PROC MemberLogin
      @MemberID INT
      ,@PWD VARCHAR(15)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @DencryptPWD VARCHAR(15)
    DECLARE @EncryptPWD VARBINARY(256)
    SELECT  @EncryptPWD = PWD
    FROM    MemberInfo
    WHERE   MemberId = @MemberID
    SELECT @DencryptPWD=dbo.DencryptByPassPhraseTest(@EncryptPWD,@MemberID)
    IF @EncryptPWD=@OldPWD
          PRINT '成功登陆'
    ELSE
          PRINT '密码和会员号不匹配，请重新输入'
END

最后来验证是否能正确登陆：

--匹配的会员号和密码
EXEC MemberLogin 123456,'adfe$3AdeF!'
--会员号和密码不匹配
EXEC MemberLogin 654321,'cdfeS3#Rd6'

这里为了演示，所以打印出字符串说明，实际运用中，可以返回标记值，前端用标记进行判断密码验证是否成功。