SoFunction
Updated on 2025-03-09

How to use MD5 in MySQL statement

In MySQL,MD5()is a hash function that converts a given string to an MD5 hash value.

The MD5 hash algorithm converts input data of any length into a 128-bit hash value, usually expressed as 32 hexadecimal digits.

To use in MySQLMD5()Function, just pass the string to the function as a parameter

Give a general example

SELECT MD5('your_string_here');

In this example,'your_string_here'is the string to have it.MD5()The function will return the MD5 hash value of the string.

It can also be used in INSERT or UPDATE statementsMD5()function to hash the field,

For example:

INSERT INTO users (username, password) VALUES ('john', MD5('password123'));

This willusersInsert a new username and its corresponding password MD5 hash value into the table.

Give a practical example

Suppose there is a nameusersThe table containing the username and password fields will be usedMD5()Function to store user's password.

First, create the data table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(32) NOT NULL -- MD5 hash will be stored here
);

Insert some sample data into the table, including the plaintext of the username and password:

INSERT INTO users (username, password) VALUES
('alice', MD5('password123')),
('bob', MD5('qwerty')),
('charlie', MD5('letmein'));

Now that we have inserted the data, we can check the MD5 hash value stored in the password field by querying:

SELECT username, password FROM users;

The output will be similar to the following:

+----------+----------------------------------+
| username | password                         |
+----------+----------------------------------+
| alice    | 482c811da5d5b4bc6d497ffa98491e38 |
| bob      | d8578edf8458ce06fbc5bb76a58c5ca4 |
| charlie  | 0acf4539a14b96fecc5897b09e43b7c4 |
+----------+----------------------------------+

In this example, useMD5()The function hashed the password and stores the hash value inpasswordin the field.

Now, the passwords actually stored in the database have been hashed, which can increase security and will not directly expose the user's password even if the database is leaked.

In the conventional case, MD5 is a one-way hash function, meaning that it is irreversible and cannot be decrypted directly by inverse operations to obtain the original plaintext password.

When storing a password, only its hash value is stored, when the user logs in, the password provided by the user is hashed, and then the hash value is compared with the hash value stored in the database, and if the two match, the user is considered to have provided the correct password.

However, MD5 has been proven to be an unsafe hash algorithm, and there are a variety of pre-calculated rainbow tables and other technologies, which can be tried to find the original value corresponding to a specific MD5 hash value through brute force cracking and other methods. Therefore, it is not recommended to use MD5 to store passwords or other sensitive information.

To enhance security, it is recommended to use a more secure hashing algorithm and use randomly generated "salt" during the hashing process, which can increase the complexity and security of the password. This safer approach should be used as much as possible when storing passwords in the database.

More application scenarios

MD5()Functions have many application scenarios in computer science and software development, and although they are not recommended for security-related scenarios, they still have their uses in other ways.

  • Data Integrity Verification: The MD5 hash function can be used to verify the integrity of a file or data. The sender can calculate the MD5 hash value of the file and send it to the receiver. After receiving the file, the receiver can calculate the MD5 hash value of the file again, and then compare the calculated hash value with the hash value provided by the sender to ensure that the file has not been tampered with during the transmission process.
  • Password management: Although MD5 is not recommended for storing passwords, it may still be used for temporary password hashing in some non-secure environments. For example, in development testing, the MD5 hash function may be used to quickly process passwords, but it is not recommended in production environments.
  • Data partitioning and data sharding: In distributed systems, the MD5 hash function can be used to determine the partition or shard of data. By passing a unique identifier of the data (such as a key) to the MD5 hash function and determining the partition or shard to which the data belongs within the range of the hash value.
  • Generate unique identifiers: The MD5 hash function can convert data of any length into a fixed length hash value, so MD5 can be used when generating a unique identifier. For example, to generate a globally unique identifier in a distributed system, some properties of the data (such as timestamps, random numbers, etc.) can be used as input, and then MD5 hashing it to generate a unique identifier.

Although MD5 has vulnerabilities in security and is no longer applicable to security fields such as password storage, in the above non-secure application scenarios, the MD5 hash function can still be used.

For scenarios where higher security is required, a safer hashing algorithm should be considered.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.