Why Storing Plain-text is “Suicide” in the Security Era?
When I first started my career, I used to think that just blocking external database ports was enough to keep data safe. But reality is different. According to a 2023 IBM report, the average cost of a data breach has reached $4.45 million. If a server is compromised via Remote Code Execution (RCE) or a backup file falls into the wrong hands, all personal IDs or customer addresses will be fully exposed as plain text.
Under GDPR standards, protecting personal data is a requirement, not an option. The best way to mitigate risk is to encrypt data at the database layer (Encryption at Rest). That way, if a hacker gets the data file without the decryption key (Secret Key), all they get is a string of meaningless characters.
I have worked on many projects with MySQL and PostgreSQL. For systems requiring high compliance, pgcrypto (Postgres) and AES_ENCRYPT (MySQL) are two powerful tools due to their balance between performance and security.
Encrypting Data in PostgreSQL with the pgcrypto Extension
PostgreSQL does not integrate symmetric encryption directly into its core. Instead, it provides pgcrypto — an incredibly powerful extension supporting everything from AES and Blowfish to PGP.
Installing pgcrypto
To get started, you need to enable this extension in the target database. Just run a single command:
CREATE EXTENSION IF NOT EXISTS pgcrypto;After running this, try listing the functions with the pgp_ prefix to ensure everything is ready.
Insert and Select Operations
Suppose you need to secure the phone_number column in the customers table. One important note: the column containing encrypted data must use the bytea type (binary data).
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
full_name TEXT,
phone_number BYTEA
);When inserting, we use the pgp_sym_encrypt function. To generate a strong enough secret key (usually 32 characters), I often use Password Generator. This tool runs entirely on the client side, so there’s no worry about leaking the key to the server.
INSERT INTO customers (full_name, phone_number)
VALUES ('John Doe', pgp_sym_encrypt('0901234567', 'my_super_secret_key'));To read the data back, you just need to use the pgp_sym_decrypt function to return it to its original text form:
SELECT full_name, pgp_sym_decrypt(phone_number, 'my_super_secret_key') as phone
FROM customers;Protecting Data in MySQL with AES_ENCRYPT
Unlike Postgres, MySQL has built-in encryption functions. However, a common mistake for junior developers is using the default configuration, which is quite weak.
Setting the Encryption Mode
By default, MySQL uses aes-128-ecb. ECB (Electronic Codebook) mode is very weak because identical data blocks produce identical encryption results. To meet modern security standards, switch to aes-256-cbc.
-- Configure in the session or my.cnf file
SET block_encryption_mode = 'aes-256-cbc';Using AES_ENCRYPT and AES_DECRYPT
With CBC mode, you need an additional parameter: the Initialization Vector (IV). The IV ensures that even with the same content, each encryption produces a different result, making it difficult for hackers to spot patterns.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
ssn VARBINARY(255) -- Use VARBINARY instead of VARCHAR
);When inserting, generate a random 16-byte IV string using the RANDOM_BYTES(16) function:
SET @key = 'secret_key_32_chars_long_for_aes256';
SET @iv = RANDOM_BYTES(16);
INSERT INTO users (email, ssn)
VALUES ('[email protected]', AES_ENCRYPT('123-456-789', @key, @iv));Remember to save this @iv in a separate column. If you lose the IV, you’ve essentially “thrown away” that data because it cannot be decrypted.
Real-world Experience and “Hard-learned” Lessons
Encrypting data isn’t just about running a few SQL commands. During operation, I have learned 3 major lessons:
1. Don’t Encrypt Indiscriminately
Encryption and decryption are extremely CPU-intensive. If you encrypt every column in a table with millions of rows, latency can increase 2-3 times. Prioritize only truly sensitive information.
Additionally, you cannot use WHERE phone_number = '090...' directly because the data has been transformed. For fast searching, use the Blind Index technique (storing an additional hash of the data). You can use Hash Generator to select the SHA-256 algorithm for this purpose.
2. Secret Key Management
Never hard-code keys in your code or SQL scripts. If you accidentally push a key to GitHub, the entire system is compromised. Use Environment Variables or specialized services like AWS Secrets Manager.
3. Check Column Length
Encrypted data is usually longer than the original text and is in binary format. If the column length is insufficient, the data will be truncated and cannot be decrypted. I often use Base64 Encoder to check the encrypted string, ensuring it doesn’t break when transmitted via API.
In conclusion, database encryption is the final shield protecting your reputation. Whether you choose Postgres or MySQL, always remember: choose strong algorithms, use CBC mode, and guard your keys carefully!
