• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

password and verify store procedures - not working -

After I exec and create a new user.
then I try to verify it but it is always returning "Failed"

am I doing it right?

----------

USE [SA02012011]
GO
/****** Object:  StoredProcedure [dbo].[pAddUser]    Script Date: 02/28/2011 10:56:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pAddUser](
      @UserName VARCHAR(20),
      @eMail VARCHAR(20),
      @Algorithm VARCHAR(16),
      @Pass VARCHAR(16)
)
AS
BEGIN
      set nocount on
      
      INSERT INTO PersonID (UserName, eMail, passwordID)
select @UserName, @eMail, hashbytes(@Algorithm,@Pass)
END


USE [SA02012011]
GO
/****** Object:  StoredProcedure [dbo].[pVerifyCredentials]    Script Date: 02/28/2011 10:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pVerifyCredentials](
      @UserName VARCHAR(20),
      @Algorithm VARCHAR(16),
      @Pass VARCHAR(16)
)
AS
BEGIN
      set nocount on
      select case num when 1 then 'OK'
                      else 'Failed'
                      end as Result
        from (
      SELECT count(*) as Num
        FROM PersonID
       WHERE UserName = @UserName
         AND passwordID = convert(binary(16),HASHBYTES(@Algorithm, @Pass))
              ) AS X
END
return

0
goodk
Asked:
goodk
  • 2
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
I think the Stored Procedure is having issue - Try this modified one
/****** Object:  StoredProcedure [dbo].[pVerifyCredentials]    Script Date: 02/28/2011 10:55:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pVerifyCredentials]
    (
      @UserName VARCHAR(20) ,
      @Algorithm VARCHAR(16) ,
      @Pass VARCHAR(16)
    )
AS 
    BEGIN
        SET nocount ON
        SELECT  CASE num
                  WHEN 1 THEN 'OK'
                  ELSE 'Failed'
                END AS Result
        FROM    ( SELECT    COUNT(*) AS Num
                  FROM      PersonID
                  WHERE     UserName = @UserName
                            AND passwordID = HASHBYTES(@Algorithm, @Pass)
                ) AS X
    END
GO

Open in new window



0
 
Rajkumar GsSoftware EngineerCommented:

You need to compare password in the same way that you saved to database
Changed
passwordID = convert(binary(16),HASHBYTES(@Algorithm, @Pass))
to
passwordID = HASHBYTES(@Algorithm, @Pass)

Raj
0
 
derekkrommCommented:
"AND passwordID = convert(binary(16),HASHBYTES(@Algorithm, @Pass))"

You may be truncating part of the result with "binary(16)"

Try just using "binary" instead.
0
 
goodkAuthor Commented:
thanks - it was so timely!

and CORRECT
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now