HMAC Signatures in MySQL

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples in this guide work on VillageSQL. Install Now →

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

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:

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 ;

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:

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

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:

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

Getting Started

Server Development

Extensions

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

The Problem: No Built-In HMAC

With VillageSQL: hmac() in SQL

Signing rows in a trigger

Verifying webhook payloads

Supported algorithms

HMAC vs. Plain Hash

Frequently Asked Questions

Is storing the HMAC key in the trigger safe?

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

How do I rotate the HMAC key?

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

Troubleshooting

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​The Problem: No Built-In HMAC

​With VillageSQL: hmac() in SQL

​Signing rows in a trigger

​Verifying webhook payloads

​Supported algorithms

​HMAC vs. Plain Hash

​Frequently Asked Questions

​Is storing the HMAC key in the trigger safe?

​Can I use HMAC to verify query results haven’t been altered in transit?

​How do I rotate the HMAC key?

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

​Troubleshooting

​See also

The Problem: No Built-In HMAC

With VillageSQL: hmac() in SQL

Signing rows in a trigger

Verifying webhook payloads

Supported algorithms

HMAC vs. Plain Hash

Frequently Asked Questions

Is storing the HMAC key in the trigger safe?

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

How do I rotate the HMAC key?

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

Troubleshooting

See also