How to use MySQL to create a verification code table to implement the verification code function

How to use MySQL to create a verification code table to implement the verification code function

With the continuous development of the Internet, the verification code function has become a necessary security measure for websites and APPs. The verification code verifies the user's true identity by requiring the user to enter a string of random numbers and letters. In this article, I will introduce to you how to use MySQL to create a verification code table and implement the verification code function.

1. Create a verification code table
   Create a verification code table in the MySQL database to store the generated verification code and related information. The structure of the table is as follows:

CREATE TABLE verification_code (

id INT(11) NOT NULL AUTO_INCREMENT,
unique_code VARCHAR(10) NOT NULL,
email VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_used TINYINT(1) DEFAULT 0,
PRIMARY KEY (id)

);

This table contains some fields:

• id: The unique identifier of the verification code, a self-increasing integer.
• unique_code: The generated verification code consists of random letters and numbers.
• email: The user’s email address that receives the verification code.
• created_at: timestamp of verification code generation.
• is_used: Mark whether the verification code has been used or expired.

2. Generate and send verification code
   Before the user needs to perform the verification code verification operation, we need to generate and send the verification code to the user. The following is a sample code that uses Python to send verification code emails:

import random
import string
import smtplib
from email.mime.text import MIMEText

def generate_verification_code():
    characters = string.ascii_letters + string.digits
    verification_code = ''.join(random.choice(characters) for _ in range(6))
    return verification_code

def send_verification_code(email, verification_code):
    sender = 'your_email@gmail.com'
    receiver = email
    subject = 'Verification Code'
    message = f'Your verification code is: {verification_code}'

    msg = MIMEText(message)
    msg['Subject'] = subject
    msg['From'] = sender
    msg['To'] = receiver

    try:
        smtp = smtplib.SMTP('smtp.gmail.com', 587)
        smtp.starttls()
        smtp.login(sender, 'your_password')
        smtp.sendmail(sender, receiver, msg.as_string())
        smtp.quit()
        print('Verification code sent successfully!')
    except Exception as e:
        print(f'Error sending verification code: {e}')

# 生成验证码并发送
verification_code = generate_verification_code()
send_verification_code('user@example.com', verification_code)

In this sample code, we first define a generate_verification_code function to generate a code containing random letters and numbers verification code. Then use the send_verification_code function to send the generated verification code to the user via SMTP email. The sender and receiver need to be replaced with the real sender and recipient email addresses, and the password of sender needs to be filled in with the real SMTP email password.

3. Verification verification code
   After the user enters the verification code, we need to verify the validity of the verification code in the MySQL database. The following is an example of using Python code to verify the captcha:

import mysql.connector

def verify_verification_code(email, verification_code):
    try:
        conn = mysql.connector.connect(
            host='localhost',
            user='your_username',
            password='your_password',
            database='your_database'
        )
        cursor = conn.cursor()

        query = "SELECT * FROM verification_code WHERE email = %s AND unique_code = %s AND is_used = 0 ORDER BY created_at DESC LIMIT 1"
        cursor.execute(query, (email, verification_code))
        result = cursor.fetchone()
        if result:
            # 验证码有效，更新验证码状态
            update_query = "UPDATE verification_code SET is_used = 1 WHERE id = %s"
            cursor.execute(update_query, (result[0],))
            conn.commit()
            print('Verification code verified successfully!')
        else:
            print('Invalid verification code!')

        cursor.close()
        conn.close()
    except Exception as e:
        print(f'Error verifying verification code: {e}')

# 验证验证码
verify_verification_code('user@example.com', 'ABC123')

In this sample code, we first connect to the MySQL database using mysql.connector and pass SQL The statement queries whether the specified email address and verification code exist and have not been used. If the query result exists, set the verification code status to Used and commit the changes. Otherwise, an invalid verification code is output.

Through the above steps, we have realized the process of using MySQL to create a verification code table and implement the verification code function. By generating and sending verification code emails and interacting with the database during verification, the authenticity of the user's identity and the security of the system can be guaranteed. I hope this article can help everyone understand and implement the verification code function.

The above is the detailed content of How to use MySQL to create a verification code table to implement the verification code function. For more information, please follow other related articles on the PHP Chinese website!