如何使用以下用例在SQL中获取记录 [英] How to fetch record in SQL using following use case

问题描述

我有一个用例，我必须从passwordhistory表中获取最后五个密码，并且有两个列一个md5加密，第二个是哈希，在检索五个记录之后我必须将这些列与新密码进行比较，移动后他们来自md5和哈希函数，如果找到任何记录则返回true，否则返回false。



我尝试过的事情：

I have a use case in which I have to get last five password from passwordhistory table,and there two column one md5 encrypted and second is hash ,after retrieving five records then I have to compared these column with new password,after moving them from md5 and hash function,if any record found then return true else return false.

What I have tried:

DECLARE @UserID INT = 6 
DECLARE @Password NVARCHAR(200)='admin1952'


SELECT  *
FROM    ( SELECT TOP 5
                    ChangedPassword AS ChangedPassword ,
                    PasswordChangeHistory.Password_Hash AS Password_Hash
          FROM      PasswordChangeHistory WITH ( NOLOCK )
                    INNER JOIN [User] U WITH ( NOLOCK ) ON PasswordChangeHistory.UserID = U.Id
          WHERE     PasswordChangeHistory.UserID = @UserID
        )  AS result       
WHERE   PasswordChangeHistory.ChangedPassword = dbo.Encrypt_MD5(@Password)
        OR PasswordChangeHistory.Password_Hash = dbo.Encrypt_SHA2(@Password)

推荐答案

这是问题的解决方案。正如我之前解释的那样，我需要从表中获取五条记录，然后用现有的五条记录检查新密码，如果这个新密码与之前的密码匹配，则返回false，否则返回true。

Here Is the solution of problem. as I have explain earlier I need five records from the table and then check new password with existing five record that if this new password has any matched with previous one then return false else return true.

DECLARE @UserID INT = 6 
DECLARE @Password NVARCHAR(200)= 'admin1958'
SELECT * FROM  
( SELECT    TOP(5)    ChangedPassword AS Changed ,PasswordChangeHistory.Password_Hash AS Password_Hash
                    
          FROM      PasswordChangeHistory WITH ( NOLOCK )
                    INNER JOIN [User] U WITH ( NOLOCK ) ON PasswordChangeHistory.UserID = U.Id
          WHERE     PasswordChangeHistory.UserID = @UserID
          ORDER BY  PasswordChangeHistory.ID DESC

) AS result

WHERE result.Changed= dbo.Encrypt_MD5(@Password) or result.Password_Hash = dbo.Encrypt_SHA2(@Password)

这篇关于如何使用以下用例在SQL中获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！