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.
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:
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.
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!