> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Encrypting Columns in MySQL

> How to encrypt sensitive column data in MySQL — why MySQL's AES_ENCRYPT uses an insecure default mode, and how VillageSQL's encrypt() fixes it automatically.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

MySQL has `AES_ENCRYPT()` built in. The problem is that its default mode — `aes-128-ecb` — is insecure. ECB encryption is deterministic: the same plaintext encrypted with the same key always produces the same ciphertext. An attacker who sees your encrypted column can spot repeated values without ever knowing the key. VillageSQL's `encrypt()` uses CBC mode with a random IV automatically, so identical inputs produce different output every time.

## The MySQL Default: ECB Mode

MySQL's `AES_ENCRYPT(data, key)` defaults to `aes-128-ecb` (`block_encryption_mode`). ECB — Electronic Codebook — encrypts each block independently with no randomness. Two rows with the same value produce the same ciphertext:

```sql theme={null}
-- 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;
```

MySQL 8.0 lets you change `block_encryption_mode` to use CBC, but then you have to manage the IV yourself: generate it, store it alongside the ciphertext, and pass it back to `AES_DECRYPT()` every time.

## The Standard Workaround: Manual IV with AES-CBC

Using CBC mode correctly in MySQL requires storing and passing the IV explicitly:

```sql theme={null}
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';
```

This is correct but verbose — every table needs an extra IV column, and every query that touches encrypted data has to pass the IV.

## With VillageSQL: Automatic IV Management

VillageSQL's `vsql_crypto` extension adds `encrypt()` and `decrypt()`, which handle the IV automatically. The IV is generated fresh for each call and prepended to the ciphertext in the stored value. `decrypt()` extracts it without you having to think about it.

```sql theme={null}
INSTALL EXTENSION vsql_crypto;
```

```sql theme={null}
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;
```

No IV column, no IV parameter in queries. Two rows with the same SSN produce different ciphertext — no pattern leakage.

### Key sizes

| Cipher type | Key length | When to use |
| :- | :- | :- |
| `'aes'` or `'aes-128'` | 16 bytes | Lower-sensitivity data, performance-sensitive workloads |
| `'aes-192'` | 24 bytes | Intermediate security |
| `'aes-256'` | 32 bytes | PII, financial data, anything regulated — recommended default |

A key shorter than the cipher requires raises `ERROR 3200: VDF error in function 'encrypt': key too short for <cipher>: need N bytes, got M` — it is not zero-padded. A longer key is silently truncated. Use exactly 16, 24, or 32 bytes.

### What to store

The `VARBINARY` column holds the IV (16 bytes) prepended to the ciphertext. PKCS7 padding always adds a full extra AES block (16 bytes) when the plaintext length is already an exact multiple of 16 — it's never zero bytes of padding. So the safe formula is plaintext length + 32 bytes total (16 for the IV, up to 16 for padding). Size the column with headroom:

```sql theme={null}
-- 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
```

## What This Doesn't Cover

Column-level encryption in SQL protects data at rest from someone who has database access but not the key. It doesn't protect against:

* Application code that holds the key and queries decrypted data — an attacker who compromises the app gets both
* Queries that filter or sort on encrypted columns (you can't `WHERE ssn = ?` without decrypting)

For searchable encryption or envelope key management (rotating keys without re-encrypting all data), you need additional infrastructure at the application layer.

## Frequently Asked Questions

#### Where should I store the encryption key?

Not in the database. The key belongs in a secrets manager (AWS Secrets Manager, HashiCorp Vault, GCP Secret Manager) or environment variable, loaded by the application at runtime. A key stored in the same database it encrypts provides no protection.

#### Can I search on encrypted columns?

Not directly. Decrypting every row to find a match is a full table scan. Common patterns: store a deterministic hash of the value alongside the encrypted column for equality lookups (`sha256(ssn)` for SSN lookup), or decrypt in the application layer and build an encrypted search index.

#### What happens if I lose the key?

The data is unrecoverable. Back up keys separately from the database. Consider envelope encryption — wrapping the data key with a master key — so you can rotate the master key without re-encrypting everything.

#### Can I encrypt a column with a DEFAULT expression?

Not currently. Non-deterministic functions (which `encrypt()` is, due to the random IV) can't be used in DEFAULT expressions. Generate the encrypted value in each INSERT.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `FUNCTION <database>.encrypt does not exist` | Run `INSTALL EXTENSION vsql_crypto` |
| `decrypt()` returns NULL | Ciphertext is corrupted, key is wrong, or wrong cipher type specified |
| Encrypted values are identical for same plaintext | You're using MySQL's `AES_ENCRYPT` in ECB mode — switch to `encrypt()` |
| Column too small for ciphertext | Add 32 bytes to your VARBINARY size (16 for IV + up to 16 for padding) |
| Key is the wrong length | AES-128 needs 16 bytes, AES-192 needs 24, AES-256 needs 32 — check `LENGTH(@key)` |

## See also

* [Symmetric Encryption in MySQL](https://villagesql.com/docs/en/guides/symmetric-encryption.md) — the encryption algorithm and key management behind this
* [Password Hashing in MySQL](https://villagesql.com/docs/en/guides/password-hashing.md) — for credentials, hashing is better than encryption
* [Hashing Data in MySQL](https://villagesql.com/docs/en/guides/hashing-data.md) — one-way fingerprinting when you don't need decryption


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.