Skip to content

encryption.md

Latest commit

 

History

History
285 lines (228 loc) · 12.9 KB

encryption.md

File metadata and controls

285 lines (228 loc) · 12.9 KB
Raw

Encryption

Set encryptionKey in your connection config and every MEDIUMBLOB column is encrypted with AES-128 on insert() and update() and decrypted on read, with no changes to your queries. This page covers setup, searching encrypted columns, the {{column}} syntax for decrypting in MySQL, and exactly what this encryption does and does not protect against.

Contents:

Turning Encryption On

Using CMS Builder? Encryption is already integrated: set the key in Admin > Security, then check "Automatically encrypt data stored in this column" on each field in the Field Editor. The rest of this page still applies when you query those columns yourself.

Encryption is off until you set encryptionKey at connect time:

DB::connect([
    'hostname'      => 'localhost',
    'username'      => 'dbuser',
    'password'      => 'secret',
    'database'      => 'my_app',
    'encryptionKey' => $encryptionKey, // from an env variable or secrets manager, not hardcoded
]);

Store the key outside your code (environment variable or secrets manager). Like your database password, it's kept in the connection's credential vault, so it doesn't show up in var_dump() output or stack traces, and it's masked as ******** in logged SQL.

If the connection to MySQL crosses the public internet or any network shared with people you don't trust, also set requireSSL so the connection is encrypted. ZenDB sends encryptionKey to MySQL once per connection (as a query parameter, so it stays out of SQL text and logs), and on an unencrypted connection anyone who can watch that traffic can read it, along with every query and result. On localhost, or a database server on your own or your hosting provider's internal network, anyone positioned to watch the traffic already has access to the servers themselves, so requireSSL adds nothing there.

requireSSL encrypts the connection but doesn't verify the server's certificate. That stops someone reading the traffic in transit; it doesn't stop someone who can point your connection at a server they control. ZenDB has no certificate-verification option, so if that's part of your threat model, reach the database over a private network or an SSH tunnel instead.

ZenDB decides which columns to encrypt by column type: every MEDIUMBLOB is encrypted, everything else is left alone (including TINYBLOB, BLOB, and LONGBLOB):

CREATE TABLE users (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(255),
    apiToken MEDIUMBLOB,     -- encrypted
    ssn      MEDIUMBLOB      -- encrypted
);

With the key set, MEDIUMBLOB is reserved for encrypted data: every MEDIUMBLOB in every table is encrypted on write and treated as ciphertext on read. For regular binary data (images, uploaded files), use a neighboring type ZenDB leaves alone: BLOB holds up to 64 KB and LONGBLOB up to 4 GB, either side of MEDIUMBLOB's 16 MB.

Turning on encryption does not encrypt existing rows. If your MEDIUMBLOB columns already hold data, re-encrypt those rows first (read without the key, write back with it). CMS Builder does this for you when you check the encryption box on an existing field.

Writing and Reading Encrypted Data

With the key set, nothing about your queries changes: insert() and update() encrypt MEDIUMBLOB values before they reach MySQL, and every read method (select(), selectOne(), query(), queryOne()) decrypts them in the results:

DB::insert('users', ['name' => 'Alice', 'apiToken' => 'secret-token-value']);
// INSERT INTO `users` SET `name` = 'Alice', `apiToken` = '<ciphertext>'

DB::update('users', ['apiToken' => 'new-token'], ['id' => 1]);
// UPDATE `users` SET `apiToken` = '<ciphertext>' WHERE `id` = 1

$user = DB::selectOne('users', ['id' => 1]);
echo $user->apiToken;  // "new-token" - already decrypted

NULL passes through unencrypted in both directions: a NULL token is stored as NULL and read back as NULL. Booleans throw: true/false have no encrypted form, so pass a string or number instead.

Searching Encrypted Columns - DB::encryptValue()

ZenDB uses AES in ECB mode, which is deterministic: the same plaintext with the same key always produces the same ciphertext. That makes exact-match searches work: encrypt the search value in PHP with DB::encryptValue() and MySQL compares the stored bytes directly, no decryption needed:

$user = DB::selectOne('users', ['apiToken' => DB::encryptValue('secret-token-value')]);
// WHERE `apiToken` = '<ciphertext>' - byte comparison, nothing decrypted

encryptValue() produces the same ciphertext that insert() and update() generate, so it's also the way to write encrypted values through raw SQL, where auto-encryption doesn't apply:

DB::query("UPDATE ::users SET apiToken = ? WHERE id = ?", DB::encryptValue('new-token'), 1);
// UPDATE users SET apiToken = '<ciphertext>' WHERE id = 1

NULL input returns NULL (before any key check), and SmartString values unwrap automatically. Calling it with anything else on a connection without encryptionKey throws RuntimeException.

Determinism is also the tradeoff: anyone who can read the table can see which rows share a value, without knowing what the value is. See What This Protects below.

Decrypting in MySQL with {{column}}

Exact match compares bytes, but LIKE, string functions, and range comparisons need the plaintext, which means asking MySQL to decrypt the column inside the query: an AES_DECRYPT() call around every column reference, typed by hand. ZenDB makes that easier with a shorthand: wrap the column name in {{...}} and it expands to exactly that call:

$users = DB::select('users', "{{apiToken}} LIKE ?", '%token%');
// SELECT * FROM `users` WHERE AES_DECRYPT(`apiToken`, @ek) LIKE '%token%'

The same expansion is available as a string from DB::decryptExpr('apiToken'), for SQL built outside a template.

{{table.column}} works too, for joins. Write the column reference exactly as you would without encryption, then wrap it in braces: :: applies the table prefix inside {{}} just as it does outside, and alias qualifiers stay as written:

$users = DB::select('users', "{{::users.apiToken}} LIKE ?", '%token%');
// with tablePrefix 'cms_' this runs:
// SELECT * FROM `cms_users` WHERE AES_DECRYPT(`cms_users`.`apiToken`, @ek) LIKE '%token%'

$rows = DB::query("SELECT * FROM ::users u WHERE {{u.apiToken}} LIKE ?", '%token%');
// aliases pass through as written: WHERE AES_DECRYPT(`u`.`apiToken`, @ek) LIKE ...

Internal detail, safe to ignore: @ek is a MySQL session variable holding the key. ZenDB sets it once per connection, before the first query that mentions @ek, so the key isn't repeated in every statement (and the SET @ek line is masked as ******** in the query log).

This decrypts every row scanned, so it's slower than exact match and can't use an index. On large tables, prefer exact match with encryptValue() where the query allows it.

Decrypting Raw mysqli Results - DB::decryptRows()

Every regular ZenDB read method decrypts results automatically; there's nothing to do. But results fetched through DB::$mysqli directly come back as raw ciphertext, and decryptRows() decrypts them in place:

$result = DB::$mysqli->query("SELECT * FROM users");
$rows   = $result->fetch_all(MYSQLI_ASSOC);
DB::decryptRows($rows, $result->fetch_fields());  // detects MEDIUMBLOB columns from field metadata

Instead of field metadata you can name the keys yourself: pass column names for associative rows (['apiToken', 'ssn']) or field indexes for numeric rows ([2, 3]). The related helper DB::getEncryptedColumns($result->fetch_fields()) returns the detected MEDIUMBLOB columns as an array keyed by field index, e.g. [2 => 'apiToken', 3 => 'ssn'].

When Decryption Fails

A value that fails to decrypt (wrong encryptionKey, or the column holds data that was never encrypted) passes through as its raw bytes, and the first failure triggers an E_USER_WARNING:

ZenDB: can't decrypt MEDIUMBLOB column 'apiToken', returning raw bytes. Wrong encryptionKey, or the column holds unencrypted data.

One warning, not one per row, so a table of pre-encryption data doesn't flood the log. If you see this warning, either the key changed or the column still holds unencrypted rows from before encryption was turned on; both mean stop and re-encrypt, not ignore.

Schema Changes Mid-Request

ZenDB reads a table's column types the first time it needs them and caches the answer for the life of the connection. Schema changes made through the library take care of themselves: when DB::query() runs DDL (ALTER, CREATE, DROP, RENAME, TRUNCATE), ZenDB drops the cached lists and re-reads each table on its next query.

Schema changes ZenDB can't see keep the stale cache: DDL sent through raw DB::$mysqli, or another process altering tables while yours is running. A column can then stop being encrypted on write or stop being decrypted on read, with no warning either way. Reconnect after one:

DB::disconnect();
DB::connect($config);

Import and upgrade scripts are where this comes up. Normal page requests don't change schemas, so the cached list stays correct for the whole request.

How the Keys Line Up

ZenDB uses AES-128-ECB because it's the strongest encryption that works on every database ZenDB supports: MySQL 5.7.32+ and MariaDB both implement it as the AES_ENCRYPT() / AES_DECRYPT() default, and on most MariaDB versions it's the only mode those functions offer. Newer servers add CBC and 256-bit modes, but they aren't available everywhere, they need a per-value IV stored alongside the data, and a random IV breaks the exact-match search above. Using the shared default also means PHP-side and MySQL-side produce identical ciphertext. On the PHP side, encryptionKey is hashed with SHA-512 and XOR-folded into a 16-byte AES key. On the MySQL side, @ek is set to UNHEX(SHA2(key, 512)) and AES_DECRYPT() does the same folding internally. Same effective key both places: data encrypted in PHP decrypts in MySQL and vice versa.

What This Protects

This encryption protects a database dump at rest: someone who steals the .sql file or a backup cannot read the encrypted values without the key. Be clear about where that ends:

  • ECB is deterministic. Equal plaintexts produce equal ciphertexts, which is what makes exact-match search work, and also means an attacker reading the table can tell which rows share a value and can spot repeated 16-byte blocks inside a value.
  • No integrity check. ECB is unauthenticated; nothing detects ciphertext that was modified in place or copied from another row. An attacker with write access can swap two rows' encrypted values and both still decrypt cleanly.
  • One key for everything. Every MEDIUMBLOB on the connection uses the same key; there's no per-column opt-out.
  • Deep server access reads the key. ZenDB sends it to MySQL as a bound parameter, so SHOW PROCESSLIST and performance_schema statement history only show SET @ek = UNHEX(SHA2(?, 512)). Two places still record it:
    • The general query log (general_log) writes every query with its bound values inlined: the key, but also login passwords, password hashes, session tokens, and anything else your queries carry. That's what the log is for, and running it while debugging is fine; just know it captures secrets, and check that only people you'd trust with database admin access can read it (normally already true).
    • performance_schema (on by default in MySQL, off in MariaDB) keeps @ek readable for as long as the connection lives, to any account with access to it.
  • Ordinary data can trigger the key send. The key goes to the server the first time a query contains @ek anywhere in its text, including inside a quoted value, so a stored email at a domain starting with ek can trigger it on a connection that never decrypts in SQL. Nothing extra leaks: the exposure is the same log and performance_schema surfaces above.

If you need protection against an attacker who can compare or tamper with ciphertext in the live database, encrypt with an authenticated cipher (such as AES-GCM via openssl_encrypt()) in your application before storing, and give up in-SQL search on those columns.

← Multiple Connections | Documentation Index | Next: Security Gotchas →