mysql加密与解密函数的用法

发布时间:2020-10-10编辑:脚本学堂
本文介绍下,mysql数据库中的加密与解密函数,有时会用得上的,有需要了解的朋友参考下,希望对大家有所帮助。

为大家介绍下mysql/ target=_blank class=infotextkey>mysql数据库中的加密函数:
PASSWORD():创建一个经过加密的密码字符串,适合于插入到MySQL的安全系
统。该加密过程不可逆,和unix密码加密过程使用不同的算法。主要用于MySQL的认证系统。
 
ENCRYPT(,):使用UNIX crypt()系统加密字符串,ENCRYPT()函数接收要加密的字符串和(可选的)用于加密过程的salt(一个可以唯一确定口令的字符串,就像钥匙一样),注意,windows上不支持。

ENCODE(,)   DECODE(,):加密解密字符串。
该函数有两个参数:被加密或解密的字符串和作为加密或解密基础的密钥。Encode结果是一个二进制字符串,以BLOB类型存储。加密程度相对比较弱

MD5():计算字符串的MD5校验和(128位)

SHA5():计算字符串的SHA5校验和(160位)

以上两个函数返回的校验和是16进制的,适合与认证系统中使用的口令。

AES_ENCRYPT
AES_DECRYPT的例子:
 

复制代码 代码示例:
insert into users(test) values(AES_ENCRYPT('teststr','salt'));
select AES_DECRYPT(test,'salt') from users;

注意:需要linux 且 AES_ENCRYPT 加密结果最好也以BLOB类型存储。

加密:
select   aes_encrypt(name, 'password ');
解密:
select   aes_decrypt(aes_encrypt(name, 'password '), 'password '); 
 
########################
How to Use MySQL's AES_ENCRYPT and AES_DECRYPT to Store Passwords in a Database

Here's the scenario.  You are building a custom member login area to a website.  You need to store the user's name, email address and a password.  The name and email can be stored in 'plain text', but for added security, you want to store the password in an encrypted format (in case someone steals the database somehow, or just for your users' peace of mind).
This mini-tutorial assumes you already know how to connect to your database and work with php/mysql. 
The benefit of using AES_ENCRYPT and AES_DECRYPT is that you can both encrypt the password, then decrypt the password whenever necessary.  This is helpful if you ever want to display the password to the user in an email, or if you're encrypting other account information that you need to display.
View the code here.
1:  The Key
For this to work, you must define a "key" to use when encrypting and decrypting the information from the database.  It would be best to store this key somewhere on your server outside of the main directory in which you're working.  This key can be whatever you want it to be, but you must also reference the same key during encrypting and decryption.
$key = 'ASKSDFNSDFKEISDJAHDLDSDF1235UUUiidfsdf';
2:  Encrypt the password
mysql_query("INSERT INTO users (user_first, user_last, user_password) VALUES ('".$_POST['first']."','".$_POST['last']."',AES_ENCRYPT($_POST['password'],$key))");
3:  Decrypt the password
Now, to display the decrypted password, you'll need a query similar to the one below:
$password = mysql_fetch_row(mysql_query("SELECT AES_DECRYPT(user_password,'$key') FROM users WHERE user_id = 4"));
echo $password[0];
So, using AES_ENCRYPT and AES_DECRYPT can be very useful when you need to store encrypted information in a database as well as display the original, unencrypted information.  Remember, you must use a 'key' in order to "unlock" and display the encrypted information.

可以explain下,看下结果:
 

复制代码 代码示例:
SELECT AES_Encrypt('SECRET DATA','KEY')
UNION
SELECT AES_Decrypt('60316eb379ad9beae9aa057af49bdff5','KEY')
UNION
SELECT AES_Decrypt(AES_Encrypt('SECRET DATA','KEY'),'KEY')