Stored Procedure For Login Page and Custom Error Handling

Introduction

This article will teach us about Stored Procedure For Login Pages and Custom Error Handling.

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

Generation of CAPTCHA Image Using Generic Handler for Login Page

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

#	Column	DataType	Function
1	IsLocked	Varchar(1)	If the user attempts to log in with the wrong password five times, then the user account is locked ('Y': Unlocked, 'N': Locked)
2	IsLogin	Bit	This flag states the User's Login Status
3	LoginCnt	Int	Count of login attempts with an invalid password
4	UserSession	Varchar(200)	This is the user's unique session id
5	LastLogin	DateTime	DateTime of the 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

CREATE PROCEDURE [dbo].[USP_UserLogin]  
    @loginID        VARCHAR(25) ,  
    @password       VARCHAR(200),  
    @sessionID      VARCHAR(200),  
    @mode           VARCHAR(15)  
AS  
BEGIN  
  
IF    @mode = 'LOGIN'  
BEGIN  
      
-- USER NOT EXISTS  
IF NOT EXISTS(  SELECT 1   
                FROM tbl_UserMaster  
                WHERE LoginID = @loginID AND IsDelete = 0 )  
        BEGIN  
            SELECT 'USER NOT EXISTS.' AS RES  
            RETURN;  
        END  
  
-- USER INACTIVE  
IF EXISTS ( SELECT 1   
            FROM tbl_UserMaster  
            WHERE LoginID = @loginID    
            AND IsActive = 0 )  
        BEGIN  
            SELECT 'USER INACTIVE.' AS RES  
            RETURN;  
        END  
  
-- USER LOCKED   
IF EXISTS ( SELECT 1   
            FROM tbl_UserMaster  
            WHERE LoginID = @loginID   
            AND IsLocked = 'Y' AND IsActive = 1 AND IsDelete = 0 )  
        BEGIN  
            SELECT 'USER LOCKED.' AS RES  
            RETURN;  
        END           
  
-- PASSWORD CHECK  
IF NOT EXISTS ( SELECT 1   
                FROM tbl_UserMaster  
                WHERE LoginID = @loginID   
                AND [Password] = @password   
                AND IsActive = 1 AND IsDelete = 0 )  
        BEGIN  
-- UPDATE INCORRECT LOGIN ATTEMPT  
            UPDATE tbl_UserMaster  
            SET LoginCnt = ISNULL(LoginCnt,0)  + 1   
            WHERE LoginID = @loginID   
              
-- IF INCORRECT LOGIN ATTEMPT MORE THAN 5 TIMES THEN LOCK USER ACCOUNT  
            IF((SELECT LoginCnt FROM tbl_UserMaster WHERE LoginID = @loginID ) > 5 )  
            BEGIN  
                UPDATE tbl_UserMaster  
                SET IsLocked = 'Y'  
                WHERE LoginID = @loginID   
            END  
              
            SELECT 'PASSWORD INCORRECT.' AS RES  
        END               
        ELSE   
        BEGIN  
            SELECT UserID ,UserName ,LoginID ,LastLogin ,UserRole ,IsLogin  
            FROM tbl_UserMaster  
            WHERE LoginID = @loginID   
            AND [Password] = @password   
            AND IsActive = 1 AND IsDelete = 0   
      
  
-- UPDATE USER LOGIN DETAILS      
            IF ( (SELECT IsLogin    FROM tbl_UserMaster  
            WHERE LoginID = @loginID AND [Password] = @password AND IsActive = 1 AND IsDelete = 0 ) = 0 )  
            BEGIN  
                UPDATE tbl_UserMaster  
                SET IsLogin = 1 ,  
                    userSession = @sessionID ,  
                    LastLogin = GETDATE()   
                WHERE LoginID = @loginID   
            END       
        END  
END  
  
ELSE IF @mode = 'CHANGELOGIN'  
BEGIN  
  
    UPDATE tbl_UserMaster  
    SET IsLogin = 1 ,  
        userSession = @sessionID ,  
        LastLogin = GETDATE()   
    WHERE LoginID = @loginID   
END       
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.")

#region " [ using ] "  
using System;  
using System.Configuration;  
using System.Data;  
using System.Data.SqlClient;  
using System.Web.UI;  
#endregion  
  
public partial class Login : System.Web.UI.Page  
{  
    protected void Page_Load(object sender, EventArgs e)  
    {  
        if (!Page.IsPostBack)  
        {  
            UpdateCaptchaText();  
        }  
    }  

    #region " [ Button Event ] "  
    protected void btnRefresh_Click(object sender, ImageClickEventArgs e)  
    {  
        UpdateCaptchaText();  
    }  
  
    protected void btnLogin_Click(object sender, EventArgs e)  
    {  
        if (!string.Equals(txtCode.Text.Trim(), (string)Session["Captcha"]))  
        {  
            lblError.Text = "Enter correct code.";  
            return;  
        }  
  
        lblError.Text = string.Empty;  
        DataTable dtUser = new DataTable();  
        string userSession = Guid.NewGuid().ToString();  
        Session["UserSession"] = userSession;  
        try  
        {  
            dtUser = checkUserLogin(userSession, "LOGIN");  
  
            if (dtUser != null)  
            {  
                if (dtUser.Columns.Contains("RES"))  
                {  
                    lblError.Text = dtUser.Rows[0][0].ToString();  
                    ClearPage();  
                }  
                else  
                {  
                    Session["UserID"] = dtUser.Rows[0]["UserID"];  
                    Session["UserName"] = dtUser.Rows[0]["UserName"];  
                    Session["LastLogin"] = dtUser.Rows[0]["LastLogin"];  
  
                    Response.Redirect("~/Welcome.aspx");  
                }  
            }  
            else  
            {  
                ClearPage();  
                lblError.Text = "Unexpected error.";  
            }  
        }  
        catch  
        {  
            throw;  
        }  
        finally  
        {  
            dtUser.Dispose();  
        }  
    }  
    #endregion  

    #region " [ Private Function ] "  
    private DataTable checkUserLogin(string userSession, string mode)  
    {  
        DataSet dsData = new DataSet();  
        SqlConnection sqlCon = null;  
        SqlDataAdapter sqlCmd = null;  
  
        try  
        {  
            using (sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))  
            {  
                sqlCmd = new SqlDataAdapter("USP_UserLogin", sqlCon);  
                sqlCmd.SelectCommand.CommandType = CommandType.StoredProcedure;  
                sqlCmd.SelectCommand.Parameters.AddWithValue("@loginID", txtLogin.Text.Trim());  
                sqlCmd.SelectCommand.Parameters.AddWithValue("@password", txtPassword.Text.Trim());  
                sqlCmd.SelectCommand.Parameters.AddWithValue("@sessionID", userSession);  
                sqlCmd.SelectCommand.Parameters.AddWithValue("@mode", mode);  
  
                sqlCon.Open();  
                sqlCmd.Fill(dsData);  
  
                sqlCon.Close();  
            }  
        }  
        catch  
        {  
            throw;  
        }  
        return dsData.Tables[0];  
    }  
  
    private void ClearPage()  
    {  
        txtCode.Text = string.Empty;  
        txtPassword.Text = string.Empty;  
        txtCode.Text = string.Empty;  
        UpdateCaptchaText();  
    }  
  
    private void UpdateCaptchaText()  
    {  
        txtCode.Text = string.Empty;  
        Random randNum = new Random();  
  
        //Store the captcha text in session to validate  
        Session["Captcha"] = randNum.Next(10000, 99999).ToString();  
        imgCaptcha.ImageUrl = "~/ghCaptcha.ashx?" + Session["Captcha"];  
    }  
    #endregion  
}

Step 4. Insert dummy data into the tbl_userMaster Table as in the following.

INSERT INTO tbl_UserMaster  
           (UserName ,LoginID , [Password]  
           , IsLocked, IsLogin, LoginCnt, Email  
           , ContactNo, UserRole, IsActive, IsDelete)  
VALUES     ('Daniel Craig', 'daniel', 'Password@123',  
        'N', 0, 0, '[email protected]',  
        '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

If invalid credentials are entered.
If the user enters an invalid CAPTCHA code.
If the user enters an invalid password.
If the user has more than five incorrect login attempts, then the user is locked as in the following.

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

Enhancements

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

Conclusion

This article taught us about Stored Procedure For Login Page and Custom Error Handling in SQL Server. Before reading this, please review the Generation of CAPTCHA Image Using Generic Handler for Login Page.