Using MySQL built-in encryption functions
AES_ENCRYPT and AES_DECRYPT functions
Method introduction：
AES (Advanced Encryption Standard) is a symmetric encryption algorithm. In MySQL, you can useAES_ENCRYPTFunction encrypts data usingAES_DECRYPTThe function is decrypted. This encryption method is characterized by the use of the same keys for encryption and decryption.
Example：
First, create a table to store the encrypted data:
CREATE TABLE encrypted_data (
id INT AUTO_INCREMENT PRIMARY KEY,
encrypted_text BLOB
);
Then, assume that the text to be encrypted is'Sensitive Information', the key is'MySecretKey', insert encrypted data:
SET @sensitive_text = 'Sensitive Information'; SET @encryption_key = 'MySecretKey'; INSERT INTO encrypted_data (encrypted_text) VALUES (AES_ENCRYPT(@sensitive_text, @encryption_key));
When reading data, decrypt it:
SELECT AES_DECRYPT(encrypted_text, @encryption_key) FROM encrypted_data;
MD5 function (one-way encryption for verification)
Method introduction：
MD5 is a one-way cryptographic hash function that converts data of any length into a fixed length (128 bits) hash value. Since it is one-way, the original data cannot be restored from the hash value. It is usually used to verify whether the data has been tampered with, such as storing the hash value of the user's password, and comparing whether the hash value of the entered password and the hash value stored when the user logs in.
Example：
Assuming storing the user password, create a table:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
password_hash VARCHAR(32)
);
When the user registers, the MD5 hash of the password is calculated and stored:
SET @password = 'UserPassword';
SET @password_hash = MD5(@password);
INSERT INTO users (username, password_hash)
VALUES ('User', @password_hash);
When the user logs in, verify the password:
SET @input_password = 'UserPassword';
SET @stored_password_hash = (SELECT password_hash FROM users WHERE username = 'User');
IF MD5(@input_password) = @stored_password_hash THEN
SELECT 'Password is correct';
ELSE
SELECT 'Password is incorrect';
END IF;
It should be noted that due to the safety hazards such as collisions, in scenarios with high safety requirements, it is now recommended to use safer hash functions such as SHA-256.
Using Transparent Data Encryption (TDE)
Method introduction：
TDE is an encryption solution provided at the database level. It can encrypt the entire database, tablespace, or specific tables. MySQL Enterprise Edition supports TDE, and its principle is to automatically encrypt data when it is written to disk and automatically decrypt data when it is read from disk. This provides data security without changing the way applications access the database. TDEs typically use the database server's key management infrastructure (KMI) to manage encryption keys.
Example (taking MySQL Enterprise Edition as an example)：
First, key management needs to be configured on the server, which usually involves installing and configuring specialized key management software or using hardware security modules (HSMs).
Assume that a database is to be encrypted, in the MySQL configuration file (or) add the following configuration:
[mysqld] early-plugin - load = keyring_file.so keyring_file_data = /path/to/keyring/file
After restarting MySQL service, useCREATE TABLESPACECommands create encrypted tablespaces, for example:
CREATE TABLESPACE encrypted_tablespace ADD DATAFILE 'encrypted_file.ibd' ENCRYPTION = 'Y' KEY_BLOCK_SIZE = 16;
Then, tables can be created in this encrypted tablespace to store the encrypted data. In this way, the data stored in this tablespace is encrypted on disk.
Application layer encryption
Method introduction：
Encryption at the application level can provide more flexible encryption methods. For example, use encryption libraries in programming languages (such as Python, Java, etc.) to encrypt sensitive data and then store it in a MySQL database. This method can better control the management of encryption keys and the selection of encryption algorithms, and can also perform more complex encryption operations according to the needs of business logic.
Example (taking Python as an example)：
Assume usecryptographyThe library encrypts the data, first of all, needs to be installedcryptographyLibrary(pip install cryptography）。
The sample code is as follows:
from import Fernet
import
# Generate a keykey = Fernet.generate_key()
cipher_suite = Fernet(key)
# Sensitive data to be encryptedsensitive_data = "Confidential Data".encode('utf - 8')
encrypted_data = cipher_suite.encrypt(sensitive_data)
# Connect to MySQL databasemydb = (
host="localhost",
user="your_user",
password="your_password",
database="your_database"
)
mycursor = ()
# Create a table to store encrypted data("CREATE TABLE if not exists encrypted_data (id INT AUTO_INCREMENT PRIMARY KEY, encrypted_text BLOB)")
# Insert encrypted data("INSERT INTO encrypted_data (encrypted_text) VALUES (%s)", (encrypted_data,))
()
# Read encrypted data and decrypt it("SELECT encrypted_text FROM encrypted_data")
result = ()
decrypted_data = cipher_suite.decrypt(result[0]).decode('utf - 8')
print(decrypted_data)
In this example, usecryptographyIn the libraryFernetThe class generates a key and encrypts the data. The encrypted data is stored in a table in the MySQL database and decrypted when reading the data. This method can flexibly handle the encryption and decryption process according to the specific application scenario, and can be combined with other security measures, such as secure storage and management of keys.
This is the end of this article about several methods for encrypting MySQL sensitive data. For more related MySQL sensitive encryption content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!