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
- Writing and Reading Encrypted Data
- Searching Encrypted Columns -
DB::encryptValue() - Decrypting in MySQL with
{{column}} - Special Cases: Ignoring Case and Values Decrypted Twice or Not at All
- Decrypting Raw mysqli Results -
DB::decryptRows() - When Decryption Fails
- Schema Changes Mid-Request
- How the Keys Line Up
- What This Protects
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 bound parameter, so it stays out of the SQL text and
ZenDB's query log),
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, encrypt those rows yourself (read them on a
connection without the key, then write them back on one with it).
CMS Builder does this for you when you check the encryption box on an
existing field.
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 decryptedNULL passes through unencrypted in both directions: a NULL token is stored
as NULL and read back as NULL. Booleans throw InvalidArgumentException
because true/false have no encrypted form; pass a string or number instead.
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 decryptedencryptValue() 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 = 1NULL 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.
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 that ZenDB doesn't expand, such as a DB::rawSql() value or a query
sent through DB::$mysqli.
{{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; with tablePrefix 'cms_' this runs:
// SELECT * FROM cms_users u WHERE AES_DECRYPT(`u`.`apiToken`, @ek) LIKE '%token%'Internal detail, safe to ignore:
@ekis 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 theSET @ekline is masked as********in the query log).
{{column}} 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.
Most queries never need this section.
ZenDB decrypts a result column when MySQL reports it as a blob (type 252)
with charset binary and length 16777215, which is how a MEDIUMBLOB
column is reported. A {{column}} value has already been decrypted by
MySQL, but in a SELECT list with DISTINCT, GROUP BY, or UNION, MySQL can
report it with those same three values, and ZenDB decrypts it a second
time. Converting the value to text changes its charset, so ZenDB leaves it
alone:
$rows = DB::query("SELECT DISTINCT CONVERT({{apiToken}} USING utf8mb4) AS apiToken FROM ::users");
// SELECT DISTINCT CONVERT(AES_DECRYPT(`apiToken`, @ek) USING utf8mb4) AS apiToken FROM usersThe same conversion covers three cases:
- A search should ignore case. MySQL compares a
{{column}}value as bytes, so "TOKEN" doesn't match "token". WithCONVERT({{apiToken}} USING utf8mb4) LIKE ?it matches, as it would on a regular text column. - A value is decrypted twice. A query with
{{column}}in the SELECT list raises the "cannot decrypt" warning, although the stored data is fine. Convert it as shown above. - A value isn't decrypted at all. A column inside a function or a
UNION ALL comes back still encrypted. Some servers (MariaDB among them)
report
COALESCE(apiToken, ?)differently from the column itself, so ZenDB doesn't decrypt it. Written asCOALESCE(CONVERT({{apiToken}} USING utf8mb4), ?), MySQL decrypts it.
The conversion is for text. A binary value that isn't valid UTF-8 comes back changed, so read those by selecting the column by itself.
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 metadataInstead 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'].
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: cannot 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. The check can miss: a value encrypted with a different key, or
unencrypted data whose length is a multiple of 16 bytes, decrypts without an
error about 1 time in 255 and comes back as garbage bytes, with no warning.
If you see this warning, the key changed, the column still holds
unencrypted rows from before encryption was turned on, or a {{column}}
value was decrypted twice. For a changed key, set the original key back, or
read the rows with the old key and write them back with the new one. For
unencrypted rows, encrypt them as described in
Turning Encryption On. For a value decrypted
twice, the stored data is fine: convert it to text as shown in
Special Cases.
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
are handled automatically: when DB::query() runs DDL (ALTER, CREATE, DROP,
RENAME, TRUNCATE) or switches databases with USE, 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 or select_db() 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.
DB::disconnect() throws while the connection holds a
named lock, so a script that
runs under one releases it before reconnecting.
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. MySQL and MariaDB 11.4+ also offer
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.
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. Here's
where that protection 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
MEDIUMBLOBon the connection uses the same key; there's no per-column opt-out. - Someone with deep server access can read the key. ZenDB sends it to
MySQL as a bound parameter, so
SHOW PROCESSLISTandperformance_schemastatement history only showSET @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@ekreadable for as long as the connection lives, to any account with access to it.
- The general query log (
- Ordinary data can make ZenDB send the key. The key goes to the server the
first time a query contains
@ekanywhere in its text, including inside a quoted value, so a stored email at a domain starting withekcan trigger it on a connection that never decrypts in SQL. Nothing extra leaks: the key ends up only in the same two places listed 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: Named Locks →