Encrypting Columns in MySQL

-- These two rows will have identical encrypted values
INSERT INTO users (email, ssn_enc)
VALUES ('alice@example.com', AES_ENCRYPT('123-45-6789', 'secretkey')),
       ('bob@example.com',   AES_ENCRYPT('123-45-6789', 'secretkey'));

-- An attacker can see that Alice and Bob have the same SSN
-- without knowing the key or the plaintext
SELECT HEX(ssn_enc) FROM users;

SET @@block_encryption_mode = 'aes-256-cbc';

-- Must generate and store IV alongside ciphertext
SET @iv = RANDOM_BYTES(16);
INSERT INTO users (email, ssn_enc, ssn_iv)
VALUES ('alice@example.com', AES_ENCRYPT('123-45-6789', 'key', @iv), @iv);

-- Must retrieve IV to decrypt
SELECT AES_DECRYPT(ssn_enc, 'key', ssn_iv) AS ssn
FROM users WHERE email = 'alice@example.com';

INSTALL EXTENSION vsql_crypto;

CREATE TABLE users (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    email   VARCHAR(255),
    ssn_enc VARBINARY(255)   -- IV + ciphertext stored together
);

SET @key = 'my-32-byte-key-for-aes-256-here!';  -- 32 bytes → AES-256

-- Same plaintext encrypts to different ciphertext every time
INSERT INTO users (email, ssn_enc) VALUES
    ('alice@example.com', encrypt('123-45-6789', @key, 'aes-256')),
    ('bob@example.com',   encrypt('123-45-6789', @key, 'aes-256'));

-- Decrypt in a SELECT
SELECT email, decrypt(ssn_enc, @key, 'aes-256') AS ssn FROM users;

-- For strings up to ~200 chars: VARBINARY(255) is sufficient
-- For longer text: VARBINARY(500) or BLOB
ssn_enc   VARBINARY(64),    -- SSNs, short codes
addr_enc  VARBINARY(512),   -- Addresses, longer strings
notes_enc BLOB              -- Free-form text