vsql_crypto extension for MySQL

vsql_crypto brings a pgcrypto-compatible set of functions to MySQL, built on OpenSSL. It covers four jobs: hashing a value, signing a payload, encrypting a column, and storing a password you can verify later.


Maintainer	VillageSQL
Source and full documentation	villagesql/vsql-crypto
License	GPL-2.0

Install

vsql_crypto.veb is already in the server’s lib/veb/ directory if you installed VillageSQL with the install script, the Docker image, or a release tarball. Install it into the server with one statement:

INSTALL EXTENSION vsql_crypto;

Confirm it is there:

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_crypto';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_crypto    |
+----------------+

To build it yourself, follow the build instructions in the repository.

What it adds

Function	Returns	What it does
`digest(data, algorithm)`	binary	Hashes data with MD5, SHA-1, SHA-224, SHA-256, SHA-384, or SHA-512
`hmac(data, key, algorithm)`	binary	Signs data with a keyed hash
`encrypt(data, key, type)`	binary	Encrypts with AES
`decrypt(data, key, type)`	binary	Reverses `encrypt` with the same key
`crypt(password, salt)`	text	Hashes a password into a string that carries its own parameters
`gen_salt(type, iterations)`	text	Produces a salt for `crypt`, for the PBKDF2 family only
`gen_random_bytes(n)`	binary	Cryptographically secure random bytes
`gen_random_uuid()`	text	A random version 4 UUID as text
`crypto_version()`	text	The OpenSSL version the extension is linked against

The functions marked binary return raw bytes, so wrap them in HEX() to read them. The rest return text.

Example

Sign a payload and check the signature against a tampered copy:

SET @secret = 'shared-key';
SELECT HEX(hmac('{"order":4711}', @secret, 'sha256')) AS signature;

+------------------------------------------------------------------+
| signature                                                        |
+------------------------------------------------------------------+
| 7261870227EF8090041AB514536550671974770FCE19416ABB634C55389C3141 |
+------------------------------------------------------------------+

Store a password. The result carries the algorithm, the iteration count, and the salt, so verification needs nothing else kept alongside it:

SELECT crypt('correct horse battery', gen_salt('pbkdf2-sha256', 100000)) AS hash;

+------------------------------------------------------------------------------------------+
| hash                                                                                     |
+------------------------------------------------------------------------------------------+
| $pbkdf2-sha256$100000$N6MXcYhD7SS4DS9sVhaQag$rv6zFwkwfo4N8+lgEpU5/LR4mOLtio04b6iKCSRlF5E |
+------------------------------------------------------------------------------------------+

Your salt is random, so the hash differs on every call.

gen_salt supports the PBKDF2 family only. Asking for bf, md5, or des returns NULL, and crypt with a NULL salt returns NULL, so bcrypt hashes written by pgcrypto cannot be verified here.

Getting Started

Server Development

Using Extensions

Creating Extensions

Install

What it adds

Example

See also

​Install

​What it adds

​Example

​See also

Install

What it adds

Example

See also