identify hacking

ALTER PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16)
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password
And
Active = 1)
begin
return(1)
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)

declare @totalFails int
Select @totalFails = Count(*) From FailedLogons
Where UserName = @UserName
And dtFailed > GetDate()-1
if (@totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @UserName
return(0)
end
Go



Now, let's take a closer look at what I was doing. First thing, check to see if the
username and password exist on the same row, and that that user is active, if so, login is fine, return 1 to the
user and exit the procedure. If the login is not ok though, we want to log it. The first
thing the procedure does is insert the record into the 'FailedLogons' table.
Next we declare a variable to hold the number of failed logons for that same day. Next we
assign that value by using a sql statement to retrieve the number of records for that username,
within the same day. If that number is greater than 5, it's likely someone is trying to
hack that account so the the username will be disabled by setting the active flag in the
'User_Table' to 0. Finally, return 0 letting the calling code (ASP) know that
the login was unsuccessful. To accomplish this same task using only ASP, you would have
needed to make 4 database calls. The way we just did it it is still only one database call,
plus the fact that all that functionality we added at the end was in the stored procedure,
we didn't have to touch the ASP code at all!


Note about 'begin/end': When using an 'If' statement in a stored procedure, as long
as you keep the conditional code to one line you won't need a 'begin' or
'end' statement.

Note about 'begin/end': When using an 'If' statement in a stored procedure, as long
as you keep the conditional code to one line you won't need a 'begin' or
'end' statement. Example:


if (@myvar=1)
return(1)
else
return(2)



However, if you need more than one line, it is required that you use begin
and end. Example:


if (@myvar=1)
begin
do this.....
and this.....
return(1)
end
else
begin
do this....
return(2)
end