WebSite Login - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

WebSite Login

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2011-11-16 : 00:30:24

Ok I am trying to roll my own login instead of using the .net membership. I have a stored Proc that I am trying to put together but running into trouble.
Essentially the user should login using their email address and the password
It needs to check to see if the Email address even exists if it does if it is locked out if the email address is right but the password is wrong the failed login attempts increases by 1 the last failed attempt gets current timestamp.
if valid credentials than last failed goes to null lastsuccessful gets timestamped, isloggedin goes to 1 islocked goes to 0
and through out all of this each stage should store an int value in @returnval so that I can than determine in the website what the results were

God I hope all of that just made sense


ALTER PROCEDURE [dbo].[CheckLogin]
	-- Add the parameters for the stored procedure here
	@email varchar(255),
	@passhash varchar(260),
	@returnval INT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @lockedstatus bit, @credentialspass smallint, @failedlogincount tinyint 
	SET NOCOUNT ON;
	
	 
	@lockedstatus=SELECT [IsLocked] FROM dbo.[Profile] WHERE [Email]=@email
	
	IF @lockedstatus=0
	BEGIN
		@credentialspass=SELECT COUNT(*)
						 FROM dbo.[Profile] 
						 WHERE [Password]=@passhash AND [Email]=@email
		@failedlogincount=SELECT [FailedLoginAttempts]
						 FROM dbo.[Profile] 
						 WHERE [Password]=@passhash AND [Email]=@email
		IF @credentialspass=0
		BEGIN
			IF @failedlogincount<6
			BEGIN
				UPDATE dbo.[Profile] SET [LastFailedLoginAttempt]=GETDATE(),[FailedLoginAttempts]=[FailedLoginAttempts]+1, [IsLoggedIn]=0, [IsLocked]=1
				WHERE [Password]=@passhash AND [Email]=@email
			END
			ELSE
				@returnval=0
		END
		ELSE 
			UPDATE dbo.[Profile] SET [LastSuccessfulLogin]=GETDATE(), [LastFailedLoginAttempt]=null, [FailedLoginAttempts]=0, [IsLoggedIn]=1, [IsLocked]=0
			WHERE [Password]=@passhash AND [Email]=@email
				@returnval=2
    END
    ELSE
		@returnval=0

END

You can view the table here

http://blog.redeyeproject.com/profile.png

Kristen
Test

22859 Posts

Posted - 2011-11-16 : 03:32:43

So what's your problem??

In terms of efficiency, you should be able to do all that with a single UPDATE, rather than 3 x SELECT and an UPDATE.

Why are you counting the consecutive FailedLoginAttempts? You stop updating the counter (and LastFailedLoginAttempt date) when it gets to 6 - so you don't have any subsequent LastFailedLoginAttempt date beyond that. But a successful Email/Password will still login (I would expect the account to be locked, perhaps for a period of time, once some maximum is reached (but you'd need to Email the owned of the email address otherwise they will be scratching their head as to why they can't login if they are just a bit goofy with their passwords)

Looks like IsLocked is set on the first wrong-password, which is probably not what you want?

Personally I would return 0=Success, and Non-zero for failure (as the value can indicate WHAT the failure is

scripter
Starting Member

49 Posts

Posted - 2011-11-16 : 09:02:05

Ok there are a few things wrong first of when I try to save this proc I get the following errors

Msg 102, Level 15, State 1, Procedure CheckLogin, Line 19
Incorrect syntax near '@lockedstatus'.
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 23
Incorrect syntax near '@credentialspass'.
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 26
Incorrect syntax near '@failedlogincount'.
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 37
Incorrect syntax near '@returnval'.
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 42
Incorrect syntax near '@returnval'.

second thing yes I see I forgot setting the IsLocked which I believe this will do that

IF @failedlogincount<6
BEGIN
UPDATE dbo.[Profile] SET [LastFailedLoginAttempt]=GETDATE(),[FailedLoginAttempts]=[FailedLoginAttempts]+1, [IsLoggedIn]=0, [IsLocked]=0
WHERE [Password]=@passhash AND [Email]=@email
END
ELSE
UPDATE dbo.[Profile] SET [LastFailedLoginAttempt]=GETDATE(),[FailedLoginAttempts]=[FailedLoginAttempts]+1, [IsLoggedIn]=0, [IsLocked]=1
WHERE [Password]=@passhash AND [Email]=@email
@returnval=0

scripter
Starting Member

49 Posts

Posted - 2011-11-16 : 09:07:50

Ok I have updated this but still getting same errors with
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 39
Incorrect syntax near '@returnval'.
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 44
Incorrect syntax near '@returnval'.



ALTER PROCEDURE [dbo].[CheckLogin]
	-- Add the parameters for the stored procedure here
	@email varchar(255),
	@passhash varchar(260),
	@returnval INT OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @lockedstatus bit, @credentialspass smallint, @failedlogincount tinyint 
	SET NOCOUNT ON;
	
	 
	SELECT @lockedstatus=[IsLocked] FROM dbo.[Profile] WHERE [Email]=@email
	
	IF @lockedstatus=0
	BEGIN
		SELECT @credentialspass=COUNT(*)
						 FROM dbo.[Profile] 
						 WHERE [Password]=@passhash AND [Email]=@email
		SELECT @failedlogincount=[FailedLoginAttempts]
						 FROM dbo.[Profile] 
						 WHERE [Password]=@passhash AND [Email]=@email
		IF @credentialspass=0
		BEGIN
			IF @failedlogincount<6
			BEGIN
				UPDATE dbo.[Profile] SET [LastFailedLoginAttempt]=GETDATE(),[FailedLoginAttempts]=[FailedLoginAttempts]+1, [IsLoggedIn]=0, [IsLocked]=0
				WHERE [Password]=@passhash AND [Email]=@email
			END
			ELSE
				UPDATE dbo.[Profile] SET [LastFailedLoginAttempt]=GETDATE(),[FailedLoginAttempts]=[FailedLoginAttempts]+1, [IsLoggedIn]=0, [IsLocked]=1
				WHERE [Password]=@passhash AND [Email]=@email
				@returnval=0
		END
		ELSE 
			UPDATE dbo.[Profile] SET [LastSuccessfulLogin]=GETDATE(), [LastFailedLoginAttempt]=null, [FailedLoginAttempts]=0, [IsLoggedIn]=1, [IsLocked]=0
			WHERE [Password]=@passhash AND [Email]=@email
				@returnval=2
    END
    ELSE
		@returnval=0

END

scripter
Starting Member

49 Posts

Posted - 2011-11-16 : 09:12:42

Now to address your statement about the @returnval part. If an account is locked the status must return one value if the credentials don't match/exist return different value and if everything passes a different value is supplied. that way I can provide the end user with a
"Your account has been locked due to multiple failed login attempts" or "Sorry the credentials you have entered do not match an account"
Or "Congrats you figured out how to enter your credentials in correctly, some would think this is a simple thing to do but we understand people can't seem to remember anything"

Ok so the last message I think I need to tweak but I think you got the idea

Kristen
Test

22859 Posts

Posted - 2011-11-16 : 13:34:46

quote:
Originally posted by scripter

Msg 102, Level 15, State 1, Procedure CheckLogin, Line 39
Incorrect syntax near '@returnval'.
Msg 102, Level 15, State 1, Procedure CheckLogin, Line 44
Incorrect syntax near '@returnval'.

This:


UPDATE ...
SET ...
WHERE ...
@returnval=999

needs to be this:


UPDATE ...
SET ...
WHERE ...
SELECT @returnval=999

scripter
Starting Member

49 Posts

Posted - 2011-11-17 : 00:07:21

Actually I found out earlier today the answer was
SET @returnval=0
this was the first time I was able to get online

Kristen
Test

22859 Posts

Posted - 2011-11-17 : 03:43:18

You can use SET. But SET can only assign one variable at a time, and in certain circumstances you need to be able to assign multiple variables in a single statement, and for that you would need to use SELECT, so here we always use SELECT for assignment in order to be consistent.

But either will do for a single variable assignment

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources