Skip to content
Tutorials Center
My learning

Topics

Lesson 101 of 125 ยท Database Systems

Encrypting Data in the Database

Learn how databases protect data in transit with TLS, at rest with TDE and per column with pgcrypto or Always Encrypted, plus password hashing and keys.

Permissions decide who may run which queries. Encryption protects the data when those rules don't apply: when someone copies a backup file, steals a disk, listens on the network between your application and the database, or gets a dump of a table through a bug. A well-protected database uses several layers of encryption, and each layer stops a different attacker.

This lesson walks through those layers from the outside in: encryption in transit (TLS on the connection), encryption at rest (disk and transparent data encryption), and column-level encryption for the most sensitive values. You'll also see why passwords are hashed, never encrypted, and why most of the real work lies in key management. Server features appear as read-only code; the storage patterns behind them (ciphertext columns, blind indexes, key versions, salted hashes) run in SQLite with values produced by real cryptographic code.

Encoding, hashing and encryption

Three words get mixed up constantly, and mixing them up causes real breaches:

What it does Reversible? Needs a key? Use it for
Encoding (Base64, hex) Changes the representation of bytes Yes, by anyone No Moving binary data through text channels. Never for secrecy
Hashing (SHA-256, Argon2) Turns input into a fixed-size fingerprint No No (but see salts below) Passwords, integrity checks, lookups
Encryption (AES, ChaCha20) Scrambles data so only key holders can read it Yes, with the key Yes Data you must read back: card tokens, health records, documents

If you'll need the original value later, you encrypt it. If you only ever need to check whether a value matches (a password at login), you hash it. And a value that's merely Base64-encoded is as readable as plain text to anyone who looks.

Encryption in transit: TLS on the connection

Every query and every result travels over the network between your application and the database server. Without encryption, anyone who can observe that traffic (a compromised machine on the same network, a misconfigured cloud network, a hostile Wi-Fi) can read customer data and even the login exchange. TLS, the same protocol behind HTTPS, encrypts the connection and lets the client verify it's talking to the real server.

All major databases support TLS; the important part is requiring it and verifying the server certificate, not just allowing it. That's mostly configured in the connection string (read-only):

TEXT
# PostgreSQL: encrypt AND check the certificate and host name
postgresql://[email protected]:5432/shop?sslmode=verify-full&sslrootcert=/etc/ssl/db-ca.pem

# MySQL client
mysql --host=db.example.com --user=app --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/db-ca.pem shop

# SQL Server (ADO.NET / Microsoft.Data.SqlClient)
Server=db.example.com;Database=shop;Encrypt=True;TrustServerCertificate=False

Weaker settings are common and dangerous. PostgreSQL's sslmode=require encrypts but doesn't check who's on the other end, so a man-in-the-middle can still present their own certificate; SQL Server's TrustServerCertificate=True does the same. On the server side, you can refuse unencrypted connections entirely: hostssl lines in PostgreSQL's pg_hba.conf, require_secure_transport = ON in MySQL, or "Force Encryption" in SQL Server.

To confirm that your current session is really encrypted (read-only):

SQL
-- PostgreSQL
SELECT ssl, version, cipher FROM pg_stat_ssl WHERE pid = pg_backend_pid();

-- MySQL: an empty value means the session is NOT encrypted
SHOW SESSION STATUS LIKE 'Ssl_cipher';

-- SQL Server
SELECT encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Managed cloud databases usually enforce TLS by default and publish their certificate authority bundle for clients to verify against. SQLite has no network connection at all, so this layer doesn't apply to it.

Encryption at rest: disks, files and TDE

At rest means the data sitting on storage: data files, logs, temporary files and backups. Encryption at rest protects against someone who gets the files without going through the database: a stolen or discarded disk, a snapshot shared by mistake, a backup in a misconfigured storage bucket.

There are two common levels:

  • Disk or volume encryption. The operating system or cloud provider encrypts the whole volume (LUKS on Linux, BitLocker on Windows, encrypted cloud volumes). The database doesn't know about it. Cloud providers typically enable it by default for managed databases and their snapshots.
  • Transparent Data Encryption (TDE). The database itself encrypts its data and log files as it writes pages and decrypts them as it reads them. "Transparent" means applications and queries don't change at all.

TDE support varies (read-only):

SQL
-- SQL Server: needs a master key and a certificate in the master database first
CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE tde_certificate;
ALTER DATABASE shop SET ENCRYPTION ON;

-- MySQL / InnoDB: per-table encryption once a keyring component is configured
CREATE TABLE payments (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  card_token VARCHAR(64) NOT NULL
) ENCRYPTION = 'Y';
ALTER INSTANCE ROTATE INNODB MASTER KEY;

Oracle offers TDE as part of its Advanced Security option. Community PostgreSQL has no built-in TDE, so PostgreSQL deployments rely on disk encryption (some commercial distributions add TDE). For SQLite, the file is the database, so encryption means an encrypting build such as SQLCipher or the official SQLite Encryption Extension, where you supply the key when opening the file (for example PRAGMA key = '...' in SQLCipher).

At-rest encryption doesn't hide data from queries. Once the database is running, TDE decrypts everything for anyone who can log in. It does nothing against SQL injection, a stolen application password or an over-privileged account. Those are handled by parameterized queries, least privilege and the column-level encryption below.

Encrypt your backups too. A perfectly encrypted server with plain-text dumps in a storage bucket is not encrypted. Most backup tools and managed snapshot systems can encrypt their output, and the backups lesson explains why copies should live somewhere separate.

Column-level encryption

For the most sensitive values, such as national ID numbers, bank details or medical notes, you can encrypt individual columns so that even someone with full SELECT access sees only scrambled bytes. There are two places to do it.

Inside the database, with SQL functions (read-only):

SQL
-- PostgreSQL with the pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

UPDATE patients
SET notes_enc = pgp_sym_encrypt(notes, 'key-from-a-secret-store')
WHERE id = 42;

SELECT pgp_sym_decrypt(notes_enc, 'key-from-a-secret-store') AS notes
FROM patients
WHERE id = 42;

-- MySQL: switch away from the default ECB mode, and always use a random IV
SET block_encryption_mode = 'aes-256-cbc';
SET @iv = RANDOM_BYTES(16);
INSERT INTO patients (id, notes_enc, notes_iv)
VALUES (42, AES_ENCRYPT('Allergic to penicillin', @key, @iv), @iv);

This is simple, but the key appears in the SQL text, where it can end up in query logs, monitoring tools and error messages, and the database server sees the plain data. MySQL's default mode, aes-128-ecb, encrypts identical values to identical bytes and needs no IV, which leaks patterns; don't use it.

In the application (or the database driver), so the database only ever stores ciphertext. The application encrypts before INSERT and decrypts after SELECT, with keys that never reach the database server. SQL Server's Always Encrypted builds this into the driver: you mark columns as encrypted, and the client driver encrypts parameters and decrypts results automatically (read-only):

SQL
-- SQL Server Always Encrypted: the server never sees the plain values
CREATE TABLE customers (
  id INT IDENTITY PRIMARY KEY,
  national_id CHAR(11) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (
      COLUMN_ENCRYPTION_KEY = cek_customers,
      ENCRYPTION_TYPE = DETERMINISTIC,
      ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NOT NULL
);

With application-level encryption, a stolen database dump, a curious administrator or an injection attack all get only ciphertext. The price is that the database can no longer work with the values: it can't sort them, compare them with <, search them with LIKE, or index them meaningfully.

Storing ciphertext

Modern encryption such as AES-GCM uses a fresh random nonce (number used once) for every value, so encrypting the same email twice gives different bytes. That's a security feature: an attacker can't tell which rows share a value. A common layout stores the nonce, ciphertext and authentication tag together in one BLOB. The values below were produced by real AES-256-GCM code, the way an application would before inserting them:

SQL Encrypted values look like random bytes

Press Run (or Ctrl + Enter) to see the result here.

Each value is 50 bytes: 12 for the nonce, 22 for the 22-character email, and 16 for the tag. The ciphertext of rows 1 and 3 looks completely different, and COUNT(DISTINCT ...) reports 3 distinct values although only 2 distinct emails exist. Without the key, the database (and anyone who steals it) learns almost nothing except the length.

Searching encrypted columns with a blind index

So how does the application find a customer by email? Two options:

  • Deterministic encryption encrypts equal values to equal bytes, so WHERE email_enc = ? works. It also reveals which rows share a value, and frequency analysis can guess common ones (if one value appears in 40% of rows of a country column, it's probably the largest market).
  • A blind index: keep randomized encryption for the value, and add a separate column with a keyed hash (an HMAC with its own secret key) of the normalized value. The application computes the same HMAC for the search term and looks that up.
SQL Looking up rows with a blind index

Press Run (or Ctrl + Enter) to see the result here.

The lookup returns rows 1 and 3, and the plan is a SEARCH on the blind index, so it stays fast on millions of rows. The blind index still reveals that rows 1 and 3 match each other, so it leaks equality just like deterministic encryption; truncating it (here to 16 hex characters) adds deliberate collisions that make large-scale analysis harder, at the cost of the occasional extra row that the application filters out after decrypting. What it can't do is ranges or partial matches: LIKE 'rosa%' on encrypted data is impossible by design. Decide which columns truly need encryption, and keep the searchable, sortable data in clear text where the risk allows it.

Hashing passwords

Passwords are the one secret you should never be able to read back, not even as the database owner. You store a hash, and at login you hash what the user typed and compare. But a plain fast hash like SHA-256 isn't enough. Look at what an attacker sees in a stolen table:

SQL Unsalted hashes give passwords away

Press Run (or Ctrl + Enter) to see the result here.

In the unsalted table, three users share one hash, so they share one password, and an attacker who cracks it once gets all three accounts. Worse, the SHA-256 of common passwords is listed in huge precomputed tables: a941a4c4... is the SHA-256 of sunshine, and a search engine would tell you so. In the salted table, all five hashes differ, even for the three users who chose sunshine, because each user has a random salt mixed in, so every account must be attacked separately.

The second ingredient is slowness. SHA-256 is designed to be fast, so a graphics card can try billions of guesses per second. Password hashing functions (Argon2id, bcrypt, scrypt, or PBKDF2 where regulations require it) are deliberately slow and memory-hungry, and they store the salt and cost settings inside the hash string. In practice, use your framework's password functions (password_hash() in PHP, bcrypt/argon2 libraries elsewhere) rather than building this yourself. In PostgreSQL, pgcrypto's crypt(password, gen_salt('bf', 12)) produces a salted bcrypt hash if you must do it in SQL.

Key management

Encryption turns the problem of protecting a lot of data into the problem of protecting a small key. If the key sits in the same database, in the same backup or in the source code repository, the encryption protects nothing. Good practice:

  • Keep keys in a key management service (KMS) or secrets manager (AWS KMS, Google Cloud KMS, Azure Key Vault, HashiCorp Vault), not in config files committed to Git.
  • Use envelope encryption. Data is encrypted with a data encryption key (DEK); the DEK is itself encrypted with a key encryption key (KEK) that never leaves the KMS. You store the encrypted DEK next to the data. Rotating the KEK only means re-encrypting the small DEKs, not every row. TDE in SQL Server and MySQL uses exactly this two-level design.
  • Separate duties. The people who administer the database shouldn't also control the keys, so neither can read protected data alone.
  • Plan rotation from day one by storing which key version encrypted each value.

That last point is a schema decision. With a key_version column, rotation becomes a gradual background job, and SQL tells you how far it has come:

SQL Tracking key rotation with a key_version column

Press Run (or Ctrl + Enter) to see the result here.

Twelve rows start out with 9 on version 1 and 3 on version 2. The batch UPDATE reports "OK, 5 rows affected.", and the report then shows 4 rows left on version 1 and 8 on version 2. Small batches keep each transaction short, and only when the version 1 count reaches 0 is it safe to destroy the old key. (randomblob() stands in for real ciphertext here, since the decryption and encryption happen in the application.)

Deciding what to encrypt

You can't protect data you don't know you have. Start with an inventory of sensitive columns: personal data (names, emails, phone numbers, addresses), credentials, payment data, health data and anything regulations such as GDPR, HIPAA or PCI DSS name. The database's own catalog helps you find candidates:

SQL Finding columns that may hold personal data ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In the shop database, this finds categories.name, customers.first_name, customers.last_name, customers.email, customers.city and products.name. A human still has to judge the list: product and category names are public, while the customer columns are personal data. Name-based searches miss columns called notes or extra that hold personal data anyway, so treat this as a starting point. PostgreSQL, MySQL and SQL Server expose the same information through information_schema.columns, and SQL Server even has a built-in classification feature for labeling sensitive columns.

Then pick the lightest layer that covers each risk:

Risk Layer that addresses it
Traffic sniffed between app and database TLS with certificate verification
Stolen disk, snapshot or backup file Disk encryption or TDE, plus encrypted backups
Leaked dump, injection, over-curious admin Application-level column encryption, least privilege
Stolen password table Salted, slow password hashing (Argon2id, bcrypt, scrypt)
Analysts who need data but not identities Masking, views with fewer columns, pseudonymized copies

Encryption is one layer among several. It works best combined with least-privilege permissions, parameterized queries and tested backups.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A database uses Transparent Data Encryption (TDE). An attacker finds a SQL injection bug in the web application. What does TDE do against it?

    TDE protects files on storage (disks, snapshots, backups). Once the database is running, queries see decrypted data. Injection is stopped with parameterized queries, and column-level encryption limits what a successful attack can read.
  2. 2. Which PostgreSQL connection setting both encrypts the connection and verifies the server's certificate and host name?

    require only encrypts, so a man-in-the-middle with their own certificate goes unnoticed. verify-full checks the certificate against a trusted CA and the host name.
  3. 3. Why should passwords be hashed with a salted, slow function instead of encrypted with AES?

    Login only needs a comparison, so there's no reason to keep a way back to the original. A salt makes equal passwords hash differently, and slowness makes guessing expensive.
  4. 4. An email_enc column uses AES-GCM with a random nonce per value. What does SELECT COUNT(DISTINCT email_enc) FROM contacts; tell you if the table has 1,000 rows?

    Randomized encryption hides equality. To look up or count equal values, add a blind index (a keyed HMAC column) or use deterministic encryption, accepting that both reveal which rows share a value.
  5. 5. What is the main benefit of envelope encryption (a data key encrypted by a key in a KMS)?

    The key encryption key stays in the KMS. Data keys are stored encrypted next to the data, so a database copy alone is useless, and rotation is cheap.

Summary

  • Encoding is not secrecy, hashing is one-way (for passwords and lookups), and encryption is reversible with a key (for data you must read back).
  • In transit: require TLS and verify the server certificate (sslmode=verify-full, VERIFY_IDENTITY, TrustServerCertificate=False).
  • At rest: disk encryption or TDE protects files, snapshots and backups, but not data read through SQL. Encrypt backups too.
  • Column-level: pgcrypto, AES_ENCRYPT (never in ECB mode) or, better, application-level encryption such as Always Encrypted, so the database stores only ciphertext. Encrypted columns can't be sorted, range-searched or LIKE-searched.
  • Randomized encryption hides equal values; a blind index (HMAC column) or deterministic encryption allows equality lookups at the cost of revealing duplicates.
  • Hash passwords with a salt and a slow function (Argon2id, bcrypt, scrypt); plain SHA-256 exposes shared and common passwords.
  • Keys belong in a KMS; use envelope encryption, separate duties, and a key_version column so rotation can run in batches.

Next: Backups, Migrations and Maintenance, where you'll learn how to back up, restore, migrate and maintain a database safely.