How to verify the real existence of users' mailboxes in the member system and the member users' mailboxes

How to verify the real existence of users' mailboxes in the member system and the member users' mailboxes

When developing a website, we need to check and verify the email address registered by the user, and whether the email address entered by the user is a valid email address.

Let's start with the database. Modify the user table so that users can enter email fields and add two fields:

ALTER TABLE [dbo].[Users]ADD [Email] VARCHAR(100) NULL, [IsVerify] BIT NOT NULL DEFAULT(0) SELECT * FROM [dbo].[Users]

Because you need to provide two functions, one is to require the user to verify the validity of the mailbox, or you can use the mailbox to allow the user to change the user password. Therefore, you need to create a table to store the two types of data:

Create table [dbo]. [RequestActionType] ([Type] NVARCHAR (2) not null primary key, [Description] NVARCHAR (30) NULL) goinsert into [dbo]. [RequestActionType] ([Type], [Description]) VALUES ('V', n' verify whether the email address is valid. '), ('C', n' User Password Change ')

Next, you need to create another table that records user request events and records user information, such as account, email, and link timeliness:

CREATE TABLE [dbo].[UserRequestAction]( [Type] NVARCHAR(2) NOT NULL FOREIGN KEY REFERENCES [dbo].[RequestActionType] ([Type]), [Token] [uniqueidentifier] NOT NULL DEFAULT(NEWID()), [Account] [nvarchar](30) NOT NULL, [Email] [nvarchar](150) NOT NULL, [Expire] [datetime] NOT NULL DEFAULT (DATEADD(day,(1),CURRENT_TIMESTAMP)),)GOSource Code

When the email address is successfully changed, you must change [IsVerify] to false. Therefore, you need to write a trigger to the first table:

CREATE TRIGGER [dbo].[tri_Users_Update] ON [dbo].[Users]FOR UPDATEASDECLARE @U_nbr NVARCHAR(20),@IsVerify BITDECLARE @old_email VARCHAR(100),@new_email VARCHAR(100)SELECT @new_email = [Email] FROM INSERTEDSELECT @U_nbr = [U_nbr],@old_email = [Email],@IsVerify = [IsVerify] FROM DELETEDIF @IsVerify = 1 AND (lEN(ISNULL(@new_email,'')) = 0 OR @new_email <> @old_email) UPDATE [dbo].[Users] SET [IsVerify] = 0 WHERE [U_nbr] = @U_nbrGOSource Code

When the user sends a verification email address or changes the password, ask the program to execute the following stored procedure:

Create procedure [dbo]. [usp_UserRequestAction_Request] (@ Type NVARCHAR (2), @ U_nbr NVARCHAR (20) asif not exists (select top 1 1 FROM [dbo]. [Users] WHERE [U_nbr] = @ U_nbr) begin raiserror (n' account error or does not exist. Contact the system administrator. ', 100) RETURNENDDECLARE @ Email NVARCHAR () SELECT @ Email = [Email] FROM [dbo]. [Users] WHERE [U_nbr] = @ U_nbrIF EXISTS (select top 1 1 FROM [dbo]. [UserRequestAction] WHERE [Type] = @ Type AND [Account] = @ U_nbr AND [Email] = @ Email) UPDATE [dbo]. [UserRequestAction] SET [Token] = NEWID (), [Expire] = DATEADD (day, (1), CURRENT_TIMESTAMP) WHERE [Type] = @ Type AND [Account] = @ U_nbr AND [Email] = @ EmailELSE insert into [dbo]. [UserRequestAction] ([Type], [Account], [Email]) VALUES (@ Type, @ U_nbr, @ Email) GOSource Code

The user verifies the email address validity after logging on. Therefore, you only need to click the "verify" button. The system sends the verification email to the user's email address.
In addition, when a user forgets the password, the password is not logged on to the system. Therefore, you must enter the user's account before proceeding to the next step.

The stored procedure [dbo]. [usp_UserRequestAction_Request] is used.

The next process is that the user will open his mailbox and check the emails sent by the system. The content of the email is based on the actual needs, such as prompting the user to determine whether to operate and secure the email. What matters is the link.

Instruct the user to click the link. This link is directed to a website page. When this page is reached, the system will perform some program processing on this page to check the validity of the link and whether the time expires. If there is no problem, the IsVerify field will be updated to true.

If the user forgets the password, click the link and the system will check the validity. If there is no period, a form with the password changed will appear, allowing the user to change the new password.

OK, there are two stored procedures. The first one is to update the IsVerify field value:

CREATE PROCEDURE [dbo].[usp_Users_UpdateIsVerifyField](  @token NVARCHAR(36))ASIF EXISTS(SELECT TOP 1 1 FROM [dbo].[UserRequestAction] WHERE [Token] = @token AND [Expire] >= CURRENT_TIMESTAMP)BEGIN  DECLARE @Account NVARCHAR(30)  SELECT @Account = [Account] FROM [dbo].[UserRequestAction] WHERE [Token] = @token  UPDATE [dbo].[Users] SET [IsVerify] = 1 WHERE [U_nbr] = @Account  UPDATE [dbo].[UserRequestAction] SET [Expire] = DATEADD(DAY,-1,CURRENT_TIMESTAMP) WHERE [Token] = @tokenENDGOSource Code

The other is ResetPassword. Reset the password:

Create procedure [dbo]. [usp_Users_ResetPassword] (@ token NVARCHAR (36), @ Password NVARCHAR (100) asif exists (select top 1 1 FROM [dbo]. [UserRequestAction] WHERE [Token] = @ token AND [Expire]> = CURRENT_TIMESTAMP) begin declare @ Account NVARCHAR (30) SELECT @ Account = [Account] FROM [dbo]. [UserRequestAction] WHERE [Token] = @ token DECLARE @ pwd VARBINARY (MAX) = ENCRYPTBYPASSPHRASE ('insus # sec! % Y', @ Password) UPDATE [dbo]. [Users] SET [Pwd] = @ pwd WHERE [U_nbr] = @ Account UPDATE [dbo]. [UserRequestAction] SET [Expire] = DATEADD (DAY,-1, CURRENT_TIMESTAMP) WHERE [Token] = @ tokenENDELSEBEGIN RAISERROR (n' the password cannot be changed. contact our Customer Service or network administrator. ', 16,1) RETURNENDSource Code

This is the case with regard to database development, and you have made full use of the program.

The above section describes how to verify the real existence of users' mailboxes in the member system. I hope it will be helpful to you. If you have any questions, please leave a message for me, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!