"ASP.NET MVC Application: Database Issue - Need Assistance"

Question

I'm encountering a perplexing issue with my ASP.NET application's database functionality and could really use some expert advice and insights from the community.

My application involves user registration, login, and OTP verification. While the logic seems correct, I'm facing a problem where the otpTimestamp field in the database isn't getting updated, remaining null even after generating OTPs.

I've thoroughly reviewed my code and ensured that the relevant data is being passed correctly. The YourDataAccessLayer class handles the database operations, and the AccountsController manages the application flow.

I've attached key snippets of my code below:
AccountsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataLayer;
using DataLayer.Models;
using HRA.Utilities;
using System.Web.Mvc;
using System.Net;
using System.Net.Mail;
using System.Diagnostics;
using System.Web.Http.Cors;

namespace HRA.Controllers
{
    public class AccountsController : Controller
    {
        public ActionResult Login()
        {
            var model = new ViewModel.LoginViewModel();
            return View(model);
        }

        public ActionResult Register()
        {
            var model = new ViewModel.LoginViewModel();
            ViewBag.IsRegister = true;
            return View("Login", model);
        }


        [HttpPost]
        public ActionResult Register(string email, string password)
        {
            Debug.WriteLine($"Received email: {email}, password: {password}");
            var existingLogin = YourDataAccessLayer.GetLoginByEmail(email);

            if (existingLogin != null)
            {
                ModelState.AddModelError("", "Email already exists.");
                Console.WriteLine("Email already exists.");
                return View();
            }

            var newLogin = new Login
            {
                email = email,
                password = PasswordHasher.HashPassword(password), // The actual password is hashed in YourDataAccessLayer.CreateLogin
                                                                  // Set other properties as needed
            };
            Console.WriteLine($"Hashed Password to Save: {newLogin.password}");
            try
            {
                YourDataAccessLayer.CreateLogin(newLogin);
                Console.WriteLine("Login record saved successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error while saving login record: " + ex.Message);
            }

            // Redirect user after successful login creation
            return RedirectToAction("Login", "Accounts");
        }



        [HttpPost]
        [EnableCors(origins: "*", headers: "*", methods: "*")]
        public ActionResult Login(string email, string password)
        {
            var login = YourDataAccessLayer.GetLoginByEmail(email);

            if (login != null)
            {
                Debug.WriteLine($"Found login for email: {login.email}, isLocked: {login.isLocked}, loginAttempts: {login.loginAttempts}");
                if (login.isLocked)
                {
                    ModelState.AddModelError("", "Account is locked. Contact support.");
                    return Json(new { success = false, message = "Account is locked", loginAttempts = login.loginAttempts });
                }

                Debug.WriteLine($"Hashed Password from DB: {login.password}");
                if (PasswordHasher.VerifyPassword(password, login.password))
                {
                    Debug.WriteLine("Successful login!");
                    // Successful login
                    login.loginAttempts = 0; // Reset loginAttempts to 0 on successful login
                    login.isLocked = false;
                    YourDataAccessLayer.UpdateLogin(login);

                    return Json(new { success = true });
                }
                else
                {
                    Debug.WriteLine($"Incorrect password! Entered: {password}, Hashed: {login.password}");
                    login.loginAttempts++;

                    if (login.loginAttempts >= 3)
                    {
                        login.isLocked = true;
                        YourDataAccessLayer.UpdateLogin(login);
                        return Json(new { success = false, message = "Account is locked", loginAttempts = login.loginAttempts });
                    }
                    else
                    {
                        YourDataAccessLayer.UpdateLogin(login);
                        return Json(new { success = false, message = "Invalid email or password", loginAttempts = login.loginAttempts });
                    }
                }
            }
            else
            {
                Debug.WriteLine("Login not found for email!");
                return Json(new { success = false, message = "Invalid email or password" });
            }
        }


        [HttpPost]
        public ActionResult SendOTP(string email)
        {
            var login = YourDataAccessLayer.GetLoginByEmail(email);

            if (login == null)
            {
                return Json(new { success = false, message = "Email not found" });
            }

            // Generate and save OTP in the database
            string otp = GenerateOTP();
            login.otp = otp;
            login.otpTimestamp = DateTime.UtcNow; // Set the timestamp when generating OTP
            YourDataAccessLayer.UpdateLogin(login);

            // Send OTP to user's email
            SendOTP(email, otp);

            return Json(new { success = true });
        }

        private void SendOTP(string email, string otp)
        {
            using (var smtpClient = new SmtpClient())
            {
                var mailMessage = new MailMessage();
                mailMessage.To.Add(email);
                mailMessage.Subject = "Your OTP Code";
                mailMessage.Body = $"Your OTP code is: {otp}";

                // Set the From address (sender's email)
                mailMessage.From = new MailAddress("t274603@gmail.com", "Test");

                // Configure your SMTP settings
                smtpClient.Host = "smtp.gmail.com";
                smtpClient.Port = 587;
                smtpClient.UseDefaultCredentials = false;
                smtpClient.Credentials = new NetworkCredential("t274603@gmail.com", "xropvrqncoyrfcsd");
                smtpClient.EnableSsl = true;

                try
                {
                    // Send the email
                    smtpClient.Send(mailMessage);
                    Debug.WriteLine("OTP email sent successfully.");
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("Error sending OTP email: " + ex.Message);
                }
            }
        }


        [HttpPost]
        public ActionResult VerifyOTP(string email, string otp)
        {
            Debug.WriteLine($"Received email: {email}, OTP: {otp}");

            var login = YourDataAccessLayer.GetLoginByEmail(email);

            if (login == null)
            {
                return Json(new { success = false, message = "Email not found" });
            }

            if (login.otp != otp)
            {
                return Json(new { success = false, message = "Invalid OTP", email = email });
            }

            if (login.otpTimestamp.HasValue)
            {
                // Convert UTC timestamp to local time
                DateTime localTimestamp = TimeZoneInfo.ConvertTimeFromUtc(login.otpTimestamp.Value, TimeZoneInfo.Local);

                // Calculate the time elapsed since OTP generation
                const int OTP_EXPIRATION_SECONDS = 40;

                TimeSpan timeElapsed = DateTime.Now - localTimestamp;

                // Check if the OTP is expired (40 seconds threshold)
                if (timeElapsed.TotalSeconds > OTP_EXPIRATION_SECONDS)
                {
                    // OTP has expired, prevent successful verification
                    return Json(new { success = false, message = "OTP expired. Please resend it.", email = email });
                }
            }

            // Compare the received OTP with the stored OTP
            if (login.otp == otp)
            {
                // reset otp and update login
                //login.otp = null;
                //login.loginAttempts = 0; // reset login attempts
                //login.isLocked = false;
                YourDataAccessLayer.UpdateLogin(login);

                return Json(new { success = true, email = email });
            }
            else
            {
                // Increment loginAttempts and update login
                login.loginAttempts++;
                if (login.loginAttempts >= 3)
                {
                    login.isLocked = true;
                }
                YourDataAccessLayer.UpdateLogin(login);

                return Json(new { success = false, message = "Invalid OTP", email = email });
            }
        }


        [HttpPost]
        public ActionResult ResendOTP(string email)
        {
            var login = YourDataAccessLayer.GetLoginByEmail(email);

            if (login == null)
            {
                return Json(new { success = false, message = "Email not found" });
            }

            // Generate and save new OTP in the database
            string newOtp = GenerateOTP();
            login.otp = newOtp; // Update the otp field with the new OTP

            // Update the otpTimestamp when generating a new OTP
            login.otpTimestamp = DateTime.UtcNow;
            YourDataAccessLayer.UpdateLogin(login);

            // Send the new OTP to user's email
            SendOTP(email, newOtp);

            return Json(new { success = true });
        }


        private string GenerateOTP()
        {
            // Generate and return a random OTP
            Random random = new Random();
            int otpValue = random.Next(100000, 999999);
            return otpValue.ToString();
        }


    }
}

YourDataAccessLayer.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataLayer.Models;
using BCrypt.Net;
using System.Diagnostics;

namespace DataLayer
{
    public static class YourDataAccessLayer
    {
        public static Models.Login GetLoginByEmail(string email)
        {
            using (var context = new HraDbContext())
            {
                Debug.WriteLine($"Querying for email: {email}");
                return context.Login.FirstOrDefault(Login => Login.email == email);
                //return null;
            }
        }

        public static void CreateLogin(Models.Login Login)
        {
            using (var context = new HraDbContext())
            {
                // Hash the password before storing it
                //string hashedPassword = BCrypt.Net.BCrypt.HashPassword(Login.password);
                //Login.password = hashedPassword;

                // Add the new login record to the context
                context.Login.Add(Login);

                // Save changes to the database
                context.SaveChanges();
                Debug.WriteLine("Login record saved successfully.");

            }
        }


        public static void UpdateLogin(Models.Login login)
        {
            using (var context = new HraDbContext())
            {
                var existingLogin = context.Login.Find(login.id);

                if (existingLogin != null)
                {
                    existingLogin.loginAttempts = login.loginAttempts;
                    existingLogin.isLocked = login.isLocked;
                    existingLogin.otp = login.otp; // Update the otp field

                    context.SaveChanges(); // Commit changes to the database
                }
            }
        }

    }
}

Login.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations.Schema;


namespace DataLayer.Models
{
    [Table("Login")]
    public class Login
    {
        public int id { get; set; }
        public string email { get; set; }
        public string password { get; set; }
        public int loginAttempts { get; set; }
        public bool isLocked { get; set; }
        public string otp { get; set; }
        public DateTime? otpTimestamp { get; set; } // Use DateTime? for nullable timestamp
    }
}

One important aspect to note is that I'm implementing a logic where the user should not be allowed to verify the correct OTP after 40 seconds of generation. To achieve this, I'm comparing the generated OTP's timestamp with the current time and ensuring that the verification process occurs within the 40-second timeframe.
Despite my best efforts, I haven't been able to identify the root cause of the issue. I suspect that there might be an error related to the timestamp conversion or storage. It's worth mentioning that I'm using UTC time for timestamp comparison.
Thank you for taking the time to read this. Your expertise will go a long way in helping me resolve this puzzling database problem and get my application back on track.

Answer 1

Hi @Mehak,

I'm facing a problem where the otpTimestamp field in the database isn't getting updated, remaining null even after generating OTPs.

I checked your code.

I think your otpTimestamp field is not updated because otpTimestamp field is not updated in UpdateLogin.

If you still have the problem, I think you need to provide all your cshtml code so we can help you better.

 public static void UpdateLogin(Models.Login login)
 {
     using (var context = new HraDbContext())
     {
         var existingLogin = context.Login.Find(login.id);

         if (existingLogin != null)
         {
             existingLogin.loginAttempts = login.loginAttempts;
             existingLogin.isLocked = login.isLocked;
             existingLogin.otp = login.otp; // Update the otp field
             existingLogin.otpTimestamp = login.otpTimestamp;
             context.SaveChanges(); // Commit changes to the database
         }
     }
 }

Best regards,
Lan Huang

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.