Encrypt sensitive MySQL fields in Node.js

Encrypting a sensitive field before it reaches MySQL can reduce what a database-only reader learns. It does not make the application unable to read that field. The server still has a decryption path, so an attacker controlling the application or its keys may recover plaintext.

This tutorial supplies a small AES-256-GCM envelope module with tested failure cases. It was run on Node.js 20.11.1 and has no dependencies. MySQL storage is shown as an integration boundary; the module is not a substitute for authorization, TLS, backup controls or a managed key system.

Give every ciphertext a format

The envelope stores a format version, key identifier, IV, authentication tag and ciphertext. The key identifier selects a key from a trusted in-memory map; it is not itself a secret.

For each encryption, generate a fresh random 12-byte IV. Bind the envelope to its record ID and field name as authenticated additional data. That prevents copying a ciphertext from one record or column into another and having it silently decrypt in the wrong context.

The module authenticates version context and key identifier too. Unsupported versions and unknown keys fail closed. Choose a record identifier before encryption and keep that identity stable through migrations.

encrypt-sensitive-mysql-fields-in-nodejs/
├── README.md
├── fields.mjs
└── test.mjs

Encrypt at a deliberate boundary

import { encryptField, decryptField } from "./fields.mjs";
 
// keys is a Map populated from an authorized secret-management system.
// recordId is generated server-side and remains stable for this record.
const envelope = encryptField(
  "Synthetic private note", keys, activeKeyId, recordId, "note"
);
await connection.execute(
  "INSERT INTO private_notes (id, encrypted_note) VALUES (?, ?)",
  [recordId, JSON.stringify(envelope)]
);
 
// After an independently authorized read:
const plaintext = decryptField(envelope, keys, recordId, "note");

Use a JSON or suitably sized text column for the envelope. Treat its contents as untrusted on read: malformed encodings, oversized ciphertext and authentication failures must not crash the whole process or leak detailed cryptographic errors to a caller.

Do not return plaintext until authentication succeeds. The helper buffers this small field and returns only after GCM finalization. Node documents that failed authentication causes decipher finalization to throw. Node crypto documentation.

The 64 KiB field cap is intentional. Large-file encryption needs a different framing and streaming design; repeating this format over arbitrary chunks without ordering and completeness protection is not equivalent.

Keep keys outside the database

Load keys from a secret-management boundary with tightly scoped access. Do not commit them to source control, store them alongside ciphertext exports or place them in browser-visible environment variables.

Generate cryptographic keys with a secure random generator, not a memorable password padded to 32 bytes. The example uses raw AES keys; passphrase-based encryption would require an explicit password-based key derivation design and different operational tradeoffs.

Plan backup restoration before using real data. A backup of ciphertext without the required historical key is unrecoverable. A backup containing both the database and unprotected keys may defeat the protection you intended.

Decide how search should work

Randomized encryption means the same plaintext produces different envelopes. Ordinary SQL equality searches and indexes on the encrypted value therefore do not behave like plaintext search.

Do not “fix” that by using a fixed IV. If your product needs lookup by a sensitive field, design a separate keyed lookup index with a clear leakage model, normalization rules and key lifecycle. Alternatively, redesign the workflow so the sensitive field is not a search key.

Encryption also does not hide record counts, timestamps, approximate value sizes or access patterns. Consider padding or data minimization only when the threat model justifies their complexity.

Test failures as well as round trips

Run node --test test.mjs. The suite verifies Unicode plaintext, different IVs, wrong keys, altered tags, unsupported versions and copying an envelope to a different record or field. These are actual helper tests, not a claim of independent cryptographic audit.

For MySQL integration, save and reload a JSON envelope, rotate the application process, remove a required key in a test environment and restore a backup with its historical keyring. Expect explicit failure when the key is missing; never replace unreadable data with an empty string.

Continue with key rotation and restore planning, or discuss a private-data Next.js application.