Stored Procedure For Login Page and Custom Error Handling

This is a continuation of the previous article in which we used a generic handler to generate a CAPTCHA image.

  1. Generation of CAPTCHA Image Using Generic Handler for Login Page

I have added many columns to the basic UserMaster Table to integrate a standard login policy.

# Column DataType Function
1 IsLocked Varchar(1) If the user attempts login with the wrong password for 5
times then the user account is locked ('Y': Unlocked , 'N': Locked)
2 IsLogin Bit This flag states the User Login Status
3 LoginCnt Int Count of Login attempts with invalid password
4 UserSession Varchar(200) This is the user's unique session id
5 LastLogin DateTime DateTime of last login
6 IsActive Bit User Account Status

Step 1: Prepare Table structure



Set the Key/Constraint on the table on the LoginID column since it must be unique.



Step 2: Stored Procedure

  1. CREATE PROCEDURE [dbo].[USP_UserLogin]  
  2.     @loginID        VARCHAR(25) ,  
  3.     @password       VARCHAR(200),  
  4.     @sessionID      VARCHAR(200),  
  5.     @mode           VARCHAR(15)  
  6. AS  
  7. BEGIN  
  8.   
  9. IF    @mode = 'LOGIN'  
  10. BEGIN  
  11.       
  12. -- USER NOT EXISTS  
  13. IF NOT EXISTS(  SELECT 1   
  14.                 FROM tbl_UserMaster  
  15.                 WHERE LoginID = @loginID AND IsDelete = 0 )  
  16.         BEGIN  
  17.             SELECT 'USER NOT EXISTS.' AS RES  
  18.             RETURN;  
  19.         END  
  20.   
  21. -- USER INACTIVE  
  22. IF EXISTS ( SELECT 1   
  23.             FROM tbl_UserMaster  
  24.             WHERE LoginID = @loginID    
  25.             AND IsActive = 0 )  
  26.         BEGIN  
  27.             SELECT 'USER INACTIVE.' AS RES  
  28.             RETURN;  
  29.         END  
  30.   
  31. -- USER LOCKED   
  32. IF EXISTS ( SELECT 1   
  33.             FROM tbl_UserMaster  
  34.             WHERE LoginID = @loginID   
  35.             AND IsLocked = 'Y' AND IsActive = 1 AND IsDelete = 0 )  
  36.         BEGIN  
  37.             SELECT 'USER LOCKED.' AS RES  
  38.             RETURN;  
  39.         END           
  40.   
  41. -- PASSWORD CHECK  
  42. IF NOT EXISTS ( SELECT 1   
  43.                 FROM tbl_UserMaster  
  44.                 WHERE LoginID = @loginID   
  45.                 AND [Password] = @password   
  46.                 AND IsActive = 1 AND IsDelete = 0 )  
  47.         BEGIN  
  48. -- UPDATE INCORRECT LOGIN ATTEMPT  
  49.             UPDATE tbl_UserMaster  
  50.             SET LoginCnt = ISNULL(LoginCnt,0)  + 1   
  51.             WHERE LoginID = @loginID   
  52.               
  53. -- IF INCORRECT LOGIN ATTEMPT MORE THAN 5 TIMES THEN LOCK USER ACCOUNT  
  54.             IF((SELECT LoginCnt FROM tbl_UserMaster WHERE LoginID = @loginID ) > 5 )  
  55.             BEGIN  
  56.                 UPDATE tbl_UserMaster  
  57.                 SET IsLocked = 'Y'  
  58.                 WHERE LoginID = @loginID   
  59.             END  
  60.               
  61.             SELECT 'PASSWORD INCORRECT.' AS RES  
  62.         END               
  63.         ELSE   
  64.         BEGIN  
  65.             SELECT UserID ,UserName ,LoginID ,LastLogin ,UserRole ,IsLogin  
  66.             FROM tbl_UserMaster  
  67.             WHERE LoginID = @loginID   
  68.             AND [Password] = @password   
  69.             AND IsActive = 1 AND IsDelete = 0   
  70.       
  71.   
  72. -- UPDATE USER LOGIN DETAILS      
  73.             IF ( (SELECT IsLogin    FROM tbl_UserMaster  
  74.             WHERE LoginID = @loginID AND [Password] = @password AND IsActive = 1 AND IsDelete = 0 ) = 0 )  
  75.             BEGIN  
  76.                 UPDATE tbl_UserMaster  
  77.                 SET IsLogin = 1 ,  
  78.                     userSession = @sessionID ,  
  79.                     LastLogin = GETDATE()   
  80.                 WHERE LoginID = @loginID   
  81.             END       
  82.         END  
  83. END  
  84.   
  85. ELSE IF @mode = 'CHANGELOGIN'  
  86. BEGIN  
  87.   
  88.     UPDATE tbl_UserMaster  
  89.     SET IsLogin = 1 ,  
  90.         userSession = @sessionID ,  
  91.         LastLogin = GETDATE()   
  92.     WHERE LoginID = @loginID   
  93. END       
  94. END 

Step 3: Passing the user credentials from Login.aspx and authenticating CAPTCHA spambots/adbots from signing up.

(Refer to the aspx script from the previous article "Generation of CAPTCHA Image Using Generic Handler for Login Page".)

  1. #region " [ using ] "  
  2. using System;  
  3. using System.Configuration;  
  4. using System.Data;  
  5. using System.Data.SqlClient;  
  6. using System.Web.UI;  
  7. #endregion  
  8.   
  9. public partial class Login : System.Web.UI.Page  
  10. {  
  11.     protected void Page_Load(object sender, EventArgs e)  
  12.     {  
  13.         if (!Page.IsPostBack)  
  14.         {  
  15.             UpdateCaptchaText();  
  16.         }  
  17.     }  
  18.  
  19.     #region " [ Button Event ] "  
  20.     protected void btnRefresh_Click(object sender, ImageClickEventArgs e)  
  21.     {  
  22.         UpdateCaptchaText();  
  23.     }  
  24.   
  25.     protected void btnLogin_Click(object sender, EventArgs e)  
  26.     {  
  27.         if (!string.Equals(txtCode.Text.Trim(), (string)Session["Captcha"]))  
  28.         {  
  29.             lblError.Text = "Enter correct code.";  
  30.             return;  
  31.         }  
  32.   
  33.         lblError.Text = string.Empty;  
  34.         DataTable dtUser = new DataTable();  
  35.         string userSession = Guid.NewGuid().ToString();  
  36.         Session["UserSession"] = userSession;  
  37.         try  
  38.         {  
  39.             dtUser = checkUserLogin(userSession, "LOGIN");  
  40.   
  41.             if (dtUser != null)  
  42.             {  
  43.                 if (dtUser.Columns.Contains("RES"))  
  44.                 {  
  45.                     lblError.Text = dtUser.Rows[0][0].ToString();  
  46.                     ClearPage();  
  47.                 }  
  48.                 else  
  49.                 {  
  50.                     Session["UserID"] = dtUser.Rows[0]["UserID"];  
  51.                     Session["UserName"] = dtUser.Rows[0]["UserName"];  
  52.                     Session["LastLogin"] = dtUser.Rows[0]["LastLogin"];  
  53.   
  54.                     Response.Redirect("~/Welcome.aspx");  
  55.                 }  
  56.             }  
  57.             else  
  58.             {  
  59.                 ClearPage();  
  60.                 lblError.Text = "Unexpected error.";  
  61.             }  
  62.         }  
  63.         catch  
  64.         {  
  65.             throw;  
  66.         }  
  67.         finally  
  68.         {  
  69.             dtUser.Dispose();  
  70.         }  
  71.     }  
  72.     #endregion  
  73.  
  74.     #region " [ Private Function ] "  
  75.     private DataTable checkUserLogin(string userSession, string mode)  
  76.     {  
  77.         DataSet dsData = new DataSet();  
  78.         SqlConnection sqlCon = null;  
  79.         SqlDataAdapter sqlCmd = null;  
  80.   
  81.         try  
  82.         {  
  83.             using (sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))  
  84.             {  
  85.                 sqlCmd = new SqlDataAdapter("USP_UserLogin", sqlCon);  
  86.                 sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure;  
  87.                 sqlCmd.SelectCommand.Parameters.AddWithValue("@loginID", txtLogin.Text.Trim());  
  88.                 sqlCmd.SelectCommand.Parameters.AddWithValue("@password", txtPassword.Text.Trim());  
  89.                 sqlCmd.SelectCommand.Parameters.AddWithValue("@sessionID", userSession);  
  90.                 sqlCmd.SelectCommand.Parameters.AddWithValue("@mode", mode);  
  91.   
  92.                 sqlCon.Open();  
  93.                 sqlCmd.Fill(dsData);  
  94.   
  95.                 sqlCon.Close();  
  96.             }  
  97.         }  
  98.         catch  
  99.         {  
  100.             throw;  
  101.         }  
  102.         return dsData.Tables[0];  
  103.     }  
  104.   
  105.     private void ClearPage()  
  106.     {  
  107.         txtCode.Text = string.Empty;  
  108.         txtPassword.Text = string.Empty;  
  109.         txtCode.Text = string.Empty;  
  110.         UpdateCaptchaText();  
  111.     }  
  112.   
  113.     private void UpdateCaptchaText()  
  114.     {  
  115.         txtCode.Text = string.Empty;  
  116.         Random randNum = new Random();  
  117.   
  118.         //Store the captcha text in session to validate  
  119.         Session["Captcha"] = randNum.Next(10000, 99999).ToString();  
  120.         imgCaptcha.ImageUrl = "~/ghCaptcha.ashx?" + Session["Captcha"];  
  121.     }  
  122.     #endregion  

Step 4: Insert dummy data into the tbl_userMaster table as in the following:

  1. INSERT INTO tbl_UserMaster  
  2.            (UserName ,LoginID , [Password]  
  3.            , IsLocked, IsLogin, LoginCnt, Email  
  4.            , ContactNo, UserRole, IsActive, IsDelete)  
  5. VALUES     ('Daniel Craig''daniel''Password@123',  
  6.         'N', 0, 0, 'daniel@gmail.com',  
  7.         '9837197322', 1, 1, 0 ) 

Step 5: Log into the new account, you will have the following page.

After successful login, the user is redirected to the home.aspx page.




Step 6: Error handling

  1. If invalid credentials are entered:



  2. If the user enters an invalid CAPTCHA code:



  3. If the user enters an invalid password:



  4. If the user has more than 5 incorrect login attempts then the user is locked as in the following:



    To unlock the user, change the loginCnt value to 0 and LoginLocked flag to "N".
  5. If the user is inactive:


Enhancements

  1. Filtering an XSS attack that occurrs from the login page.
  2. Alert the user if another session is active on another machine for the requested credentials.
  3. Having a single session per user, in other words only one session at a time will be active for each user.