Skip to main content

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

AlgorithmCallUse when
HMAC-SHA256hmac(data, key, 'sha256')General-purpose — recommended default
HMAC-SHA512hmac(data, key, 'sha512')Higher security margin required
HMAC-SHA1hmac(data, key, 'sha1')Legacy system compatibility only
HMAC-MD5hmac(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 keyNoYes
Forgeable without the keyYes — anyone can hash the same dataNo
Use forChecksums, deduplication, fingerprintingAuthentication, tamper detection, signatures
Verifiable byAnyoneOnly 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

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