> ## 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.

# HMAC Signatures in MySQL

> How to compute and verify HMAC signatures in MySQL using VillageSQL's hmac() function — for webhook verification, data integrity, and tamper detection.

<Card title="VillageSQL is a drop-in replacement for MySQL with extensions." icon="database" href="/mysql-8.4/0.0.4/quickstart">
  All examples in this guide work on VillageSQL. Install Now →
</Card>

A hash tells you what data looks like. An HMAC tells you that the data came from someone who knows the secret key. The difference matters when you're verifying that a row wasn't tampered with, that a webhook payload is authentic, or that a token was generated by your system and not forged. MySQL has no built-in HMAC function — the standard answer is to compute it in application code. VillageSQL's `hmac()` lets you do it in SQL.

## The Problem: No Built-In HMAC

MySQL has `SHA2()` and `MD5()` for hashing, but nothing that takes a key. HMAC (Hash-based Message Authentication Code) uses a shared secret to produce an authentication code — it's fundamentally different from a plain hash because someone without the key can't reproduce it.

The typical approach is to compute HMACs in the application before writing to the database:

```python theme={null}
# Python: compute HMAC in app code before writing
import hmac, hashlib
sig = hmac.new(b'secret_key', row_data.encode(), hashlib.sha256).hexdigest()
db.execute("INSERT INTO audit_log (data, signature) VALUES (?, ?)", (row_data, sig))
```

This works, but it means every write path — every language, service, and bulk loader — must replicate the signing logic. A migration script that bypasses the application will write unsigned rows.

## With VillageSQL: hmac() in SQL

VillageSQL's `vsql_crypto` extension adds `hmac(data, key, algorithm)`, which returns a `VARBINARY` authentication code computed entirely in MySQL.

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

### Signing rows in a trigger

Use a trigger to sign every row at write time, regardless of which application path created it:

```sql theme={null}
CREATE TABLE audit_log (
    id        INT AUTO_INCREMENT PRIMARY KEY,
    event     VARCHAR(255),
    payload   TEXT,
    signature VARBINARY(32),   -- 32 bytes for HMAC-SHA256
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sign every insert automatically
DELIMITER $$
CREATE TRIGGER sign_audit_log
BEFORE INSERT ON audit_log
FOR EACH ROW
BEGIN
    SET NEW.signature = hmac(
        CONCAT(NEW.event, '|', COALESCE(NEW.payload, '')),
        'your-hmac-secret-key',
        'sha256'
    );
END$$
DELIMITER ;
```

```sql theme={null}
INSERT INTO audit_log (event, payload)
VALUES ('user.login', '{"user_id": 42, "ip": "10.0.1.5"}');

-- Verify a row hasn't been tampered with
SELECT
    event,
    payload,
    signature = hmac(
        CONCAT(event, '|', COALESCE(payload, '')),
        'your-hmac-secret-key',
        'sha256'
    ) AS valid
FROM audit_log;
```

### Verifying webhook payloads

If your application stores incoming webhook payloads, you can verify signatures directly in SQL before processing:

```sql theme={null}
-- Webhook arrives with header X-Signature: <hex>
-- Store the raw payload and verify before processing
SELECT
    payload,
    HEX(hmac(payload, 'webhook-secret', 'sha256')) AS expected_sig,
    incoming_sig = HEX(hmac(payload, 'webhook-secret', 'sha256')) AS valid
FROM webhook_queue
WHERE id = 1;
```

### Supported algorithms

| Algorithm   | Call                        | Use when                              |
| :---------- | :-------------------------- | :------------------------------------ |
| HMAC-SHA256 | `hmac(data, key, 'sha256')` | General-purpose — recommended default |
| HMAC-SHA512 | `hmac(data, key, 'sha512')` | Higher security margin required       |
| HMAC-SHA1   | `hmac(data, key, 'sha1')`   | Legacy system compatibility only      |
| HMAC-MD5    | `hmac(data, key, 'md5')`    | Legacy system compatibility only      |

Use HMAC-SHA256 for new code. The HMAC construction doesn't inherit the collision vulnerabilities of the underlying hash, so HMAC-SHA1 isn't broken the way plain SHA-1 is — but SHA-1 is deprecated and regulators treat it as legacy. Don't use it for new code.

## HMAC vs. Plain Hash

|                               | Plain hash (`digest()`)                  | HMAC (`hmac()`)                              |
| :---------------------------- | :--------------------------------------- | :------------------------------------------- |
| **Takes a key**               | No                                       | Yes                                          |
| **Forgeable without the key** | Yes — anyone can hash the same data      | No                                           |
| **Use for**                   | Checksums, deduplication, fingerprinting | Authentication, tamper detection, signatures |
| **Verifiable by**             | Anyone                                   | Only holders of the key                      |

If the data you're protecting is public, a plain hash doesn't prove anything. Use HMAC when you need to prove the data was produced by someone who knows the secret. For hashing without a key, see [Hashing Data in MySQL](/guides/hashing-data).

## Frequently Asked Questions

#### Is storing the HMAC key in the trigger safe?

No — it's hardcoded in the trigger definition, visible to anyone with `SHOW CREATE TRIGGER` access. For production, retrieve the key from a secure configuration path or pass it as a session variable set by the application on connection. Treat the key as a credential.

#### Can I use HMAC to verify query results haven't been altered in transit?

HMAC protects against unauthorized modifications, not eavesdropping. For in-transit protection, use TLS for your MySQL connection. HMAC is useful for detecting tampering after data reaches the database.

#### How do I rotate the HMAC key?

Re-sign all existing rows with the new key before switching:

```sql theme={null}
UPDATE audit_log
SET signature = hmac(
    CONCAT(event, '|', COALESCE(payload, '')),
    'new-hmac-key',
    'sha256'
);
-- Then update the key in your application/trigger
```

#### Does hmac() return the same output for the same inputs?

Yes — HMAC is deterministic. The same data + same key + same algorithm always produce the same result. This is what makes verification work.

## Troubleshooting

| Problem                           | Solution                                                                                                                      |
| :-------------------------------- | :---------------------------------------------------------------------------------------------------------------------------- |
| `FUNCTION hmac does not exist`    | Run `INSTALL EXTENSION vsql_crypto`                                                                                           |
| `hmac()` returns NULL             | Check for NULL in data or key argument, or an unsupported algorithm name                                                      |
| Verification always returns 0     | Check that the data string is byte-for-byte identical to what was signed — whitespace, encoding, and NULL handling all matter |
| Signature column fills with NULLs | The trigger is running but key or data is NULL — add `COALESCE` around nullable fields                                        |

## See also

* [Hashing Data in MySQL](/guides/hashing-data) — keyless hashing for fingerprints and deduplication
* [Sending Webhooks from Triggers](/guides/http-webhooks) — HMAC is the standard way to sign webhook payloads