The core of the implementation of the login system includes four key points: database design, user input processing, password security and login status maintenance. First, the database design needs to create a user table containing fields such as id, username, email, password hashing and registration time, and ensure that the username and email address are unique; second, when verifying user information, preprocessing statements should be used to query the database to compare the hash passwords entered and stored by the user; third, the protection password must be encrypted using strong hash algorithms such as bcrypt or Argon2 to avoid plaintext storage; finally, the login status can be maintained through Session or Token (such as JWT). The former is suitable for small and medium-sized projects, and the latter is more suitable for front-end and back-end separation scenarios, and pay attention to setting security attributes to prevent attacks.

It is actually not that difficult to log in to the system. Use MySQL to build a simple background verification mechanism and combine it with a little front-end code to basically run. There are only a few key points: database design, user input processing, password security, and login status maintenance. Let’s talk about how to start step by step from the beginning.

How to design the user table?

The core of the login system is the user data table. Generally, we will create a users table, which includes at least the user name, email, and password fields.
Of course, the primary key is still id that increases automatically. The user name or mailbox is usually set to a unique index to avoid repeated registration.

For example:

 CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Here are a few points to note:

• Passwords cannot be stored plain text and must be encrypted with hash (such as password_hash() in PHP or bcrypt in Node.js)
• It is recommended to add a time stamp to facilitate the later checking of registration time
• You can consider adding an is_active field to enable or disable the function of the account.

How to verify user information when logging in?

After the user enters the user name and password, the first thing the program needs to do is to go to the database to check whether the user exists.

The process is probably like this:

1. Receive username (or email) and password entered by the user
2. Query the corresponding record based on the username
3. If not found, return "Username or password error"
4. If found, use the hash function of the corresponding language to check whether the password is correct.
5. After verification is passed, start the session and save user information, or generate a token (such as JWT)

Common Errors:

• Directly splicing user input into SQL statements, which are easy to be injected by SQL (preprocessed statements should be used)
• Forgot to filter spaces or case problems (such as usernames are case sensitive? Do you want trim input? These must be handled uniformly)

How to protect password from being leaked?

Password security is the most critical link. Many people are accustomed to using the same password on multiple websites. Once you leak it, it may affect other accounts.

So remember:

• Never save a plain password
• Using strong hashing algorithm, bcrypt or Argon2 is recommended
• Do not use MD5 or SHA series simple hashing
• Adding salt is not necessary because algorithms like bcrypt have been added automatically

The implementation methods of different languages ??are slightly different, but the core ideas are the same. For example in PHP:

 $hash = password_hash($password, PASSWORD_DEFAULT);
if (password_verify($input, $hash)) {
    // Login successfully}

How to maintain the login status?

After the user logs in, he definitely doesn't want to log in again every time a page is opened. At this time, you need to maintain a "session" state.

There are two common practices:

• Use Session: The server stores user information, and the browser only saves one session ID (usually it exists in cookies)
• Use a token (such as JWT): After successful login, return a signed token, and the client brings it with each request, and the server verifies the validity

The advantage of Session is that it is easy to manage and is suitable for small and medium-sized projects; Tokens are more suitable for front-end separation, mobile and other scenarios.

Notes:

• Session Set a reasonable expiration time
• Tokens should pay attention to signature strength and refresh mechanism
• The front-end remember to set the HttpOnly and Secure attributes to prevent XSS attacks from stealing cookies.

Basically that's it. There may not be many steps, but each link has small details that are easy to ignore. For example, if the database field length is selected incorrectly, the password encryption method is incorrect, or the session is set incorrectly, it may lead to security vulnerabilities or abnormal functions. However, as long as you follow the standard process, a simple login system is quite easy to implement.

The above is the detailed content of mysql tutorial building a simple login system. For more information, please follow other related articles on the PHP Chinese website!