How to fetch record in SQL using following use case

Question

1.00/5 (1 vote)

See more:

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)

Posted 9-May-17 18:33pm

Sajid227

Updated 9-May-17 20:19pm

Add a Solution

Comments

Maciej Los 10-May-17 1:44am

And the problem is...

vivvicks 10-May-17 1:49am

is it possible to paste both tables data here?

kosmas kafataridis 10-May-17 1:52am

If i understand you correct you need to order your sub query by the date desc and if you have the two functions its ok else i would encrypt the password on my programm and then use it something like this

DECLARE @UserID INT = 6
DECLARE @Password_md5 NVARCHAR(200)='xyz'
DECLARE @Password_sha2 NVARCHAR(200)='zyx'

SELECT case when count(*)=0 then 0 else 1 end isoldpass
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 order by somedate desc
) AS result
WHERE PasswordChangeHistory.ChangedPassword = @Password_md5
OR PasswordChangeHistory.Password_Hash = @Password_sha2
or
DECLARE @UserID INT = 6
DECLARE @Password NVARCHAR(200)='admin1952'

SELECT case when count(*)=0 then 0 else 1 end isoldpass
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 order by somedate desc
) AS result
WHERE PasswordChangeHistory.ChangedPassword = dbo.Encrypt_MD5(@Password)
OR PasswordChangeHistory.Password_Hash = dbo.Encrypt_SHA2(@Password)

Richard Deeming 10-May-17 15:10pm

"md5 encrypted"

Nope.

MD5 is NOT an encryption algorithm. It is a cryptographic hash algorithm. And not a very good one at that - no new development should be using it.

MD5 - Wikipedia[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sajid227 · Accepted Answer · 2017-05-09T20:19:00

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)