How to use MD5 encryption in MySQL

What is MD5?

MD5 Message-Digest Agorithm (English: MD5 Message-Digest Agorithm), a widely used cryptographic hash function that can produce a 128-bit (16-byte) hash value (hash value) , used to ensure complete and consistent information transmission. MD5 was designed by American cryptographer Ronald LinnRivest and made public in 1992 to replace the MD4 algorithm. The program of this algorithm is specified in the RFC1321 standard. After 1996, it was confirmed that the algorithm has weaknesses and can be cracked. For data that requires high security, experts generally recommend using other algorithms, such as SHA-2. In 2004, it was confirmed that the MD5 algorithm cannot prevent collisions, so Not suitable for security authentication, such as SSL public key authentication or digital signatures.

MD5 First Experience

Prepare a simple user table in mysql for case operations.

Next, test various sql statements:

Normal insertion statement (password plain text):

-- 普通新增语句（明文密码）
insert into user(uname,upass) values ('tom','1001');

Encrypt when inserting information**: **

-- 插入时使用MD5 算法加密
insert into user(uname,upass) values ('jack',MD5('1002'));

Query and view results:

Modify: Use the MD5 algorithm to modify the password of the account admin.

-- 将原密码换为MD5加密后的密码
update user set upass=MD5(upass) where id =1;
-- 将原密码123456修改为 MD5加密后的密码（666）
update user set upass=MD5('666') where id =2;

Result:

Encrypt all passwords:

-- 加密全部密码
update user set upass=MD5(upass)

Conditional query for verification:

-- 根据账号密码 查询对应账号信息
select * from user where uname='admin' and upass=MD5('123')

-- 测试 将密码修改为666 的账号信息
select * from user where uname='root' and upass=MD5('666')

The above is a simple encryption of some information in the mysql database through the MD5 algorithm. The specific knowledge needs to be sorted out.

Other methods in MySQL

(1) PASSWORD() function:

-- password() 函数加密
update userinfo set password =password('333') where id=6
-- 查询验证
select * from userinfo where password=password('333')

password(str) calculates and returns the encrypted password string from the original plaintext password str, When the parameter is null, null is returned. Password encryption is one-way and irreversible.

(2) ENCODE() function

ENCODE(str, pass_str) function is used to encode plain text strings and return binary strings after encoding.

Use pswd_str as password, encrypt str.

str: ​​It is used to specify the plain text to be encoded

pass_str: It is used to specify the password string to plain text characters The string is encoded.

-- 字符串
select encode('hello','nice')

-- 字符串和数字
select encode('nihao666','nice')

(3) DECODE(crypt_str,pass_str)

Use pswd_str as the password, Decrypt the encrypted string crypt_str, crypt_str is the string returned by encode().

select DECODE('nihao666','nice')

-- 使用decode函数解密encode加密的字符串
select DECODE(ENCODE('nihao666','nice'),'nice');

The above is the detailed content of How to use MD5 encryption in MySQL. For more information, please follow other related articles on the PHP Chinese website!