How to encrypt and decrypt data in MySQL database?

How to encrypt and decrypt data in MySQL database?

With the continuous development of Internet technology, data security has become more and more important. In the MySQL database, we often need to encrypt sensitive data to protect user privacy and data security. This article will explain how to encrypt and decrypt data in a MySQL database, with code examples.

Encryption method one: Encrypt using AES algorithm

The AES (Advanced Encryption Standard) algorithm is a symmetric encryption algorithm that uses the same key to encrypt and decrypt data. Here is an example of using the AES algorithm to encrypt and decrypt data:

1. Create an AES encryption function

   DELIMITER $$
   
   CREATE FUNCTION AES_ENCRYPT_STR(str TEXT, key_str TEXT) RETURNS TEXT
   BEGIN
    DECLARE encrypted_str TEXT;
    SELECT AES_ENCRYPT(str, key_str) INTO encrypted_str;
    RETURN encrypted_str;
   END$$
   
   DELIMITER ;

   Copy after login

2. Create an AES decryption function

   DELIMITER $$
   
   CREATE FUNCTION AES_DECRYPT_STR(str TEXT, key_str TEXT) RETURNS TEXT
   BEGIN
    DECLARE decrypted_str TEXT;
    SELECT AES_DECRYPT(str, key_str) INTO decrypted_str;
    RETURN decrypted_str;
   END$$
   
   DELIMITER ;

   Copy after login

3. Use AES encryption function to encrypt data

   INSERT INTO table_name (encrypted_data) VALUES (AES_ENCRYPT_STR('原始数据', '密钥'));

   Copy after login

4. Use AES decryption function to decrypt data

   SELECT AES_DECRYPT_STR(encrypted_data, '密钥') FROM table_name;

   Copy after login

Encryption method two: Encrypt using SHA2 function

The SHA2 function is a one-way hash function that can convert data of any length into a fixed-length string. Data can be encrypted using the SHA2 function, but cannot be decrypted. The following is an example of using the SHA2 function to encrypt data:

1. Create a SHA2 encryption function

   DELIMITER $$
   
   CREATE FUNCTION SHA2_ENCRYPT_STR(str TEXT) RETURNS TEXT
   BEGIN
    DECLARE encrypted_str TEXT;
    SELECT SHA2(str, 256) INTO encrypted_str;
    RETURN encrypted_str;
   END$$
   
   DELIMITER ;

   Copy after login

2. Use the SHA2 encryption function to encrypt data

   INSERT INTO table_name (encrypted_data) VALUES (SHA2_ENCRYPT_STR('原始数据'));

   Copy after login

Encryption method three: Encrypt using RSA algorithm

RSA (Rivest-Shamir-Adleman) algorithm is an asymmetric encryption algorithm that uses the public key to encrypt data. Encryption uses the private key to decrypt the data. The RSA algorithm is mainly used to encrypt smaller data such as passwords and keys. The following is an example of using the RSA algorithm to encrypt and decrypt data:

1. First, you need to generate a pair of RSA key pairs (public and private keys)

   CREATE TABLE rsa_keys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    public_key TEXT,
    private_key TEXT
   );

   Copy after login

2. Generate RSA key pair and save to database

   DELIMITER $$
   
   CREATE PROCEDURE generate_rsa_keys()
   BEGIN
    DECLARE public_key_text TEXT;
    DECLARE private_key_text TEXT;
    
    SET @private_key = 'temp.key';
    
    -- 生成RSA密钥对
    CALL mysql.rsa_generate_key_pair(2048);
    
    -- 保存公钥
    SELECT public_key INTO public_key_text FROM mysql.rsa_keys;
    INSERT INTO rsa_keys (public_key) VALUES (public_key_text);
    
    -- 保存私钥
    LOAD_FILE(@private_key) INTO private_key_text;
    UPDATE rsa_keys SET private_key = private_key_text WHERE id = LAST_INSERT_ID();
    
    -- 删除临时文件
    SET @delete_cmd = CONCACT('rm -f ', @private_key);
    PREPARE delete_stmt FROM @delete_cmd;
    EXECUTE delete_stmt;
    DEALLOCATE PREPARE delete_stmt;
    
    -- 显示公钥和私钥
    SELECT public_key_text AS public_key, private_key_text AS private_key;
   END$$
   
   DELIMITER ;

   Copy after login

3. Use RSA public key to encrypt data

   SET @public_key_text = (SELECT public_key FROM rsa_keys WHERE id = 1);
   SET @data_to_encrypt = '原始数据';
   SET @encrypted_data = RSA_ENCRYPT(@data_to_encrypt, @public_key_text);
   INSERT INTO table_name (encrypted_data) VALUES (@encrypted_data);

   Copy after login

4. Use RSA private key decrypts data

   SET @private_key_text = (SELECT private_key FROM rsa_keys WHERE id = 1);
   SELECT RSA_DECRYPT(encrypted_data, @private_key_text) AS decrypted_data FROM table_name;

   Copy after login

Summary:

This article introduces three methods on how to encrypt and decrypt data in a MySQL database: using the AES algorithm, SHA2 functions and the RSA algorithm. These methods can select appropriate encryption algorithms based on data security requirements. By using these methods, we can better protect the security of data and thus meet users' needs for data privacy protection.

The above is the detailed content of How to encrypt and decrypt data in MySQL database?. For more information, please follow other related articles on the PHP Chinese website!