> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_crypto extension for MySQL

> The vsql_crypto extension brings the pgcrypto function set to MySQL: hashing, HMAC signatures, AES encryption, password hashing, and secure random data.

`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](https://github.com/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:

```sql theme={null}
INSTALL EXTENSION vsql_crypto;
```

Confirm it is there:

```sql theme={null}
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](https://github.com/villagesql/vsql-crypto).

## What it adds

| Function | Returns | What it does |
| - | - | - |
| `digest(data, algorithm)` | binary | Hashes data with `md5`, `sha1`, `sha224`, `sha256`, `sha384`, or `sha512` |
| `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 `pbkdf2-sha256` and `pbkdf2-sha512` 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:

```sql theme={null}
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:

```sql theme={null}
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.

> [!NOTE]
>
> `gen_salt` supports `pbkdf2-sha256` and `pbkdf2-sha512` 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.

## See also

* [Hashing data in MySQL](https://villagesql.com/docs/guides/hashing-data.md) — which algorithm to use and what to store
* [Signing payloads with HMAC](https://villagesql.com/docs/guides/hmac-mysql.md) — webhook signatures and token verification
* [Encrypting columns](https://villagesql.com/docs/guides/encrypting-columns.md) — key handling and what encryption does not protect
* [Install extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/install.md) — how `INSTALL EXTENSION` works and where the server looks for a bundle
* [Available extensions](https://villagesql.com/docs/mysql-8.4/0.0.6/extensions.md) — the full catalog
* [villagesql/vsql-crypto](https://github.com/villagesql/vsql-crypto) — source, build instructions, and the known limitations


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.