Advanced Cryptography in MySQL with vsql-crypto

There are a few cryptography tasks that are common to many backends. For example, you should store passwords such that a database leak doesn't expose them, you should sign payloads so the receiving side can verify them, you should keep a column's data unreadable outside the database, and you should mint tokens an attacker cannot guess. MySQL's built-in functions cover part of that ground but fall short for many users. The SHA2() function will hash a password, but nothing salts it for you, so two people who pick the same password end up with the same stored value. MySQL does not have a built-in HMAC function. An HMAC is the keyed hash a receiver uses to tell a real message from a forged one.

When a database doesn't solve a problem natively, the work invariably moves up into application code, where every service reimplements it just a little bit differently. This adds ongoing maintenance complexity.

The vsql-crypto extension for VillageSQL Server adds a number of useful encryption and cryptographic capabilities to MySQL. It provides hashing, HMAC (Hash-based Message Authentication Code), salted password hashing, AES (Advanced Encryption Standard) encryption, and secure random generation, all backed by the OpenSSL cryptography library. For those moving from PostgreSQL or familiar with pgcrypto, it uses familiar function names: digest, hmac, crypt, gen_salt, encrypt, decrypt, gen_random_bytes, and gen_random_uuid.

VillageSQL is the innovation platform for MySQL that adds an extension framework (similar to PostgreSQL's extension framework) to enable permissionless innovation. Instead of waiting for a feature to be implemented in a few years in a future version of MySQL, new functionality can be dynamically added to a version of MySQL you run today. VillageSQL Server now supports MySQL 8.4, 9.7, and Percona Server 8.4. The vsql-crypto extension is an example of what we mean by permissionless innovation.

If you installed VillageSQL with the install script, the Docker image, or a release tarball, vsql-crypto comes bundled with the server. Installing the extension is then just one statement:

INSTALL EXTENSION vsql_crypto;

The rest of this post walks through four examples of how to implement common cryptography scenarios using vsql-crypto and VillageSQL: passwords that survive a database hack, signatures your webhooks can check, encryption that hides which rows match, and randomness worth trusting.

Set up a working demo of pgcrypto-style cryptography in MySQL on my machine,
using the vsql-crypto extension for VillageSQL. Work only against a local
throwaway server. If the only VillageSQL or MySQL server you find looks like
something I depend on, stop and ask me before touching it.

Do all of this yourself, and show me the real output of each step:

1. Find a VillageSQL server, or install one. The install script needs a codebase and a method:
   `curl -fsSL https://install.villagesql.com | VSQL_CODEBASE=mysql-8.4 INSTALL_METHOD=prebuilt bash`.
   Confirm with `SELECT VERSION()` before continuing. vsql-crypto needs no
   server flags, so do not turn on preview extensions for it.

2. Install the extension: `INSTALL EXTENSION vsql_crypto;`. It ships with the
   server, so there is nothing to download. Then read
   `INFORMATION_SCHEMA.EXTENSION_REGISTRATION` and tell me every function it
   registered, with argument and return types, and what `crypto_version()`
   reports.

3. Store a password. Hash one with `crypt` over a `gen_salt('pbkdf2-sha256', ...)`
   salt, show me the stored string, and read its parts back to me. Verify the
   right password and a wrong one in a single statement. Then hash the same
   password again at ten times the iteration count and show me that the older
   hash still verifies with no migration step. Time each verification over
   enough repetitions that the number means something, and tell me what the two
   timings cost an attacker.

4. Sign a payload. Compute an `hmac` over a JSON body with a shared secret, then
   show me what changes when you alter one byte of the body, and what changes
   when you alter the secret. Verify a signature in SQL the way a webhook
   receiver would, then tell me whether the comparison you wrote is
   constant-time and whether that matters here.

5. Encrypt a column. Create a table with a `VARBINARY` column, because
   ciphertext is binary, insert the same value twice through `encrypt`, and show
   me that the two stored ciphertexts differ. Do the same in a second table
   using the built-in `AES_ENCRYPT` and show me its answer, along with
   `@@block_encryption_mode`. Decrypt your own rows back. Then tell me which
   attack that difference stops. Also try `encrypt` in a generated column and
   show me what the server says.

6. Generate identifiers. Insert 1000 rows of `gen_random_uuid()` and 1000 rows
   of `HEX(gen_random_bytes(16))` into tables, then count the distinct values in
   each table. Tell me the largest `n` that `gen_random_bytes(n)` will accept
   and what it does past that.

7. Try to break it. One change at a time: decrypt with the wrong key, decrypt a
   ciphertext you flipped a byte in, encrypt with a key shorter than the cipher
   needs, name a cipher that does not exist, and ask `gen_salt` for pgcrypto's
   `bf` scheme. For each one, tell me whether it raised an error, returned NULL,
   or returned something that looks valid and is not, and quote exactly what
   came back.

Then give me a table of what you ran and what came back, tell me anything that
did not behave the way this asked, and drop every table and database you
created, leaving my server back where it started.

Scenario 1: Passwords that survive a database hack

A stolen user table that contains usernames and passwords can keep paying out to the attacker long after the breach. The attacker is off your network and working at their own pace, so the only thing standing between them and your users' passwords or other sensitive data is how those columns were written. Passwords should get hashed rather than encrypted, because anyone holding the key can undo encryption. You store a hash of each password instead. A hash is a one-way fingerprint: easy to compute from the password, but not reversible back into it. To check a login, you hash what the user typed and compare fingerprints.

That alone is not enough though, because the same password always produces the same fingerprint. An attacker who fingerprints a list of common passwords once can then match it against every account in every leak they have. A salt breaks that weakness. A salt is a random value generated per password, stored next to the hash, and mixed into the password before hashing. Identical passwords now produce different fingerprints, so each account has to be attacked on its own.

vsql-crypto hashes with PBKDF2 (Password-Based Key Derivation Function 2), which repeats a keyed hash of the password a number of times. The repetition is there to make the hashing slow on purpose, and what it spends is CPU time. At 100,000 repetitions one check takes roughly 7 milliseconds on a developer laptop, and at a million it takes about 70 milliseconds. Your user pays that once at login and never notices it. An attacker spends it on every password they try, and because each account carries its own salt, none of that work carries over to the next account.

Two functions do the work. gen_salt produces the random salt and records the iteration count with it, and crypt runs the password through PBKDF2. Verification recomputes the hash using the stored value as the salt, so the check is one comparison:

SET @hash = crypt('correct horse battery', gen_salt('pbkdf2-sha256', 100000));
SELECT crypt('correct horse battery', @hash) = @hash AS right_password,
       crypt('tr0ub4dor', @hash) = @hash AS wrong_password;
+----------------+----------------+
| right_password | wrong_password |
+----------------+----------------+
|              1 |              0 |
+----------------+----------------+

The stored string carries the algorithm and the iteration count in front of the salt and the hash:

SELECT crypt('correct horse battery', gen_salt('pbkdf2-sha256', 100000)) AS stored_hash;
+------------------------------------------------------------------------------------------+
| stored_hash                                                                              |
+------------------------------------------------------------------------------------------+
| $pbkdf2-sha256$100000$wxu24gc1Lg24l6WO0sQFYQ$j2DMnv+jgqZxTp+GMbhp+m/PeuO2T0iUNQKj9p99lzw |
+------------------------------------------------------------------------------------------+

Because every hash names its own cost, you can raise the iteration count for new passwords whenever hardware gets cheaper and leave the old rows alone. They keep verifying at the count they were written with, and each user moves up the next time they change their password.

Scenario 2: Signatures your webhooks can check

A webhook is a URL you publish so another system can call you when something happens, such as a payment clearing or a build finishing. It sits on the public internet, and anyone who learns the address can post to it too. So the receiver needs a way to separate a genuine callback from a forged one, and the sender needs a way to prove authorship without shipping a password on every request.

An HMAC does both. The sender hashes the payload together with a secret that only the two parties hold, and sends the resulting signature alongside the message. The receiver recomputes the signature from the payload it just got and its own copy of the secret. A match proves two things at once: the message came from someone holding the secret, and nobody altered it in transit. Change one byte of the payload and the signature no longer agrees.

With hmac in SQL, the database can sign an outgoing payload, or verify an incoming one, in the same statement that reads or writes it:

SELECT HEX(hmac('order-4711', 'shared-secret', 'sha256'));
+------------------------------------------------------------------+
| HEX(hmac('order-4711', 'shared-secret', 'sha256'))               |
+------------------------------------------------------------------+
| C5CAE86AA4B0CE3C183C346C5FED957FB3B2560136EA21CD1C5061A0D33A5AAF |
+------------------------------------------------------------------+

HEX() is there because an HMAC is raw bytes, and hex is how you put those in a header or compare them to what a sender sent. For plain hashing with no secret involved, digest accepts md5, sha1, sha224, sha256, sha384, and sha512.

Scenario 3: Encryption that hides which rows match

Some columns hold data you have to be able to read back: a card number, a bank account, a date of birth. A one-way fingerprint is no help there, so those columns get encrypted rather than hashed. encrypt turns the readable value, the plaintext, into scrambled bytes called the ciphertext, and the key turns it back.

Encryption happens one value at a time, so encrypting a column means each row's value is written as ciphertext and decrypted on the way out. That protects the data where your access controls end: in a backup file, on a disk that leaves the building, or in front of anyone who can read the data files but does not have your key.

The part that catches people is repetition. If the same plaintext always encrypts to the same ciphertext, then an attacker who never breaks the key still learns which rows hold the same value, and often the equality is the secret. For example, grouping an encrypted salary column tells you who is paid the same; grouping an encrypted diagnosis column tells you who shares a condition.

encrypt and decrypt run AES in CBC (Cipher Block Chaining) mode, which mixes each block of plaintext with the encrypted output of the block before it, and starts that chain from a random value called the initialization vector, or IV. encrypt draws a fresh IV on every single call, so in practice the same input never produces the same output twice. Ciphertext is binary, so the column that holds it is VARBINARY or BLOB:

CREATE TABLE secrets (c VARBINARY(255));
INSERT INTO secrets VALUES (encrypt('same message', 'my-secret-key-16', 'aes')),
                           (encrypt('same message', 'my-secret-key-16', 'aes'));
SELECT COUNT(*) AS rows_stored, COUNT(DISTINCT c) AS distinct_ciphertexts FROM secrets;
+-------------+----------------------+
| rows_stored | distinct_ciphertexts |
+-------------+----------------------+
|           2 |                    2 |
+-------------+----------------------+

The same value went in twice under the same key, and two different ciphertexts came out. Run those same two inserts through the built-in AES_ENCRYPT instead and the distinct count comes back as 1, because block_encryption_mode defaults to aes-128-ecb. ECB (Electronic Codebook) encrypts each block on its own with no IV, so identical input gives identical output every time. A GROUP BY on that column then sorts your users into buckets of equal values without anyone needing the key.

A random IV would normally be one more thing to store and hand back at decryption time. Here it is not: encrypt writes the IV into the front of the ciphertext and decrypt reads it from there, so decryption needs only the stored bytes, the key, and the cipher name:

SET @c = encrypt('Hello, World!', 'my-secret-key-16', 'aes');
SELECT CAST(decrypt(@c, 'my-secret-key-16', 'aes') AS CHAR) AS plaintext;
+---------------+
| plaintext     |
+---------------+
| Hello, World! |
+---------------+

Encryption hides the value, but it does not prove that nobody changed it. CBC carries no integrity check of its own, so if tampering is part of what you are defending against, store an hmac of the row alongside the ciphertext and check it before you trust what comes back.

aes and aes-128 name the same cipher, and aes-192 and aes-256 take the longer keys. An unrecognized cipher name returns NULL, while a key shorter than the cipher requires stops the statement, because silently padding a weak key would be worse than failing:

SELECT encrypt('data', 'short', 'aes-256');
ERROR 3200 (HY000): VDF error in function 'encrypt': key too short for aes-256: need 32 bytes, got 5

A fresh IV per call is also why the server won't let encrypt compute a generated column, a column whose value MySQL derives from other columns. A generated column has to give the same answer every time it is recomputed, encrypt deliberately does not, and ERROR 3763 says so. Encrypt the value in the INSERT or UPDATE that writes the row instead.

Scenario 4: Randomness worth trusting

A session token, a password reset link, and an API key are all secrets that hold only as long as nobody can guess them. That is a higher bar than being unique, and it is where MySQL's own UUID() falls down. A UUID (Universally Unique Identifier) comes in several versions, and UUID() returns a version 1, which is built from the current time and the server's network address rather than from bits an attacker cannot predict. Ask for three in a row and you can see it incrementing:

SELECT UUID() FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3) t;
+--------------------------------------+
| UUID()                               |
+--------------------------------------+
| b95ff010-a7d2-11f1-8e99-2a389a5e0709 |
| b95ff011-a7d2-11f1-8e99-2a389a5e0709 |
| b95ff012-a7d2-11f1-8e99-2a389a5e0709 |
+--------------------------------------+

Everything after the first field is shared, and the first field counts up. Hand one of those to a user as a reset link and you have handed them their neighbors' links as well.

gen_random_uuid() returns a version 4 UUID, which is random bits apart from the six that mark the version and the variant, and gen_random_bytes(n) returns up to 1024 raw random bytes for cases where you want your own format. Both draw from OpenSSL's cryptographically secure generator, which is built to be unpredictable even to someone who has already seen its earlier output:

SELECT gen_random_uuid();
+--------------------------------------+
| gen_random_uuid()                    |
+--------------------------------------+
| 758e918c-4096-4b55-b192-150e3fa9d8ea |
+--------------------------------------+

The 4 opening the third group marks the version, and the first digit of the fourth group is a variant marker that is always 8, 9, a, or b. Every other digit is drawn fresh, with no clock and no network address for an attacker to work forward from.

gen_random_uuid() covers what this section is about: one value nobody can guess, generated on the spot. If UUIDs are a column you store, index, and read back, reach for the vsql-uuid extension, which is bundled with the server as well. It adds a real uuid type that holds 16 bytes where a VARCHAR(36) column needs 36 characters, and its UUID_V7() is the generator worth knowing about. A version 7 value opens with a millisecond timestamp, so values written a millisecond or more apart sort in that same order, while version 4 values sort at random. The tail stays random, which is what makes version 7 the usual choice for a primary key you also want to be unguessable.

Try it out

Please try vsql-crypto on your own data, and tell us how we can improve it. The README is the full reference for every function, argument, and error case. Find us on Discord or leave an issue on vsql-crypto.

To get started with VillageSQL Server, go to villagesql.com.