> ## 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, 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:

```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 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.
</Note>

## See also

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