Crypto

Type-aware, length-safe hashing

The interesting design choices here are not which algorithms ship, but how values become bytes before they're hashed. A cryptographic hash is only as trustworthy as the rule that turns a SQL value into the input bytes — and this extension makes that rule strict on purpose.

• • Hashing is type-sensitive by design: 42::INTEGER and 42::BIGINT produce different digests, because their binary representations differ and the hash is computed over those bytes. That is the right behaviour for cryptographic use: a hash that silently collapses across logically distinct domains is a footgun. The flip side is that you must cast inputs deliberately — pin the type if a digest needs to be stable across schema changes.
• • Length-prefixed list elements: Variable-length elements (VARCHAR, BLOB) inside a list are hashed as [8-byte length][content]. This closes a length-extension-style ambiguity: without the length prefix, ['ab', 'c'] and ['a', 'bc'] would serialize to the same byte stream and collide. With it, they produce distinct digests — and the same rule applies whether you hash a list directly or fold a VARCHAR/BLOB column through the aggregate hash.
• • Aggregate equals list, deterministically: The aggregate hash over col ORDER BY ord is byte-equal to a single hash of LIST(col ORDER BY ord) — so any client (Python, Go, Rust) that can build the equivalent ordered list can verify a DuckDB digest byte-for-byte. The ORDER BY is mandatory: DuckDB's aggregate execution does not guarantee row order, so an unordered hash aggregate would be non-deterministic and worthless as a checksum.
• • NULL propagation and BLOB output: crypto_hash and crypto_hmac propagate NULL (NULL in, NULL out); the aggregate returns NULL for an empty group. Coalesce or cast NULLs explicitly if you need a stable representation for them. All hash functions return raw BLOB — compare with = for byte-equality, or wrap with lower(to_hex(...)) only when you need a human-readable string.

Honest scope — what's not in this extension

The extension is deliberately narrow: hashing, keyed authentication, and secure random bytes. If your problem needs primitives from a wider library, this is the wrong tool and you'll need to combine it with something else — knowing the boundary up front prevents misuse.

• • No symmetric encryption: There is no crypto_encrypt / crypto_decrypt. The extension does not implement AES (see NIST FIPS 197) or any other cipher. Encrypt-at-rest belongs at the storage / file level, not in row expressions.
• • No password-hashing KDFs: No bcrypt, scrypt, Argon2, or PBKDF2. For storing user passwords, follow the OWASP Password Storage Cheat Sheet and run a real KDF in your application layer — a fast cryptographic hash in SQL is not a password hash. You can generate salts here with the CSPRNG; the KDF itself must live elsewhere.
• • No public-key signatures: No RSA / ECDSA / Ed25519. Authentication is symmetric only, via HMAC with a shared secret.
• • Legacy algorithms exposed for compatibility: MD4, MD5, and SHA-1 are present so you can interop with existing systems, but they are not cryptographically secure for new designs. See NIST SP 800-107 Rev. 1 for current guidance — prefer sha2-256, sha2-512, or blake3. MD4 may be disabled outright in modern OpenSSL builds.

Where the random bytes come from

Secure random material is a small surface but an easy thing to get wrong — using a non-cryptographic RNG for a key or nonce silently undermines everything built on top of it.

• • OpenSSL's CSPRNG, not a PRNG: crypto_random_bytes draws from OpenSSL's RAND_bytes — the same cryptographically secure generator TLS implementations and key-generation libraries rely on, not DuckDB's ordinary random(). That distinction is the whole point: use it for HMAC keys, salts, nonces, and random IDs.
• • Bounded output: Length must be between 1 byte and 4 GB − 1 (DuckDB's BLOB cap); 0 or negative raises an error. Output is a raw BLOB — hex it for display, store it as-is for use as key material.

SHA-256 of a string

-- BLOB output, hex it for display
SELECT lower(to_hex(crypto_hash('sha2-256', 'hello world'))) AS sha256;

HMAC-SHA256

SELECT lower(to_hex(crypto_hmac('sha2-256', 'my-secret', 'message'))) AS hmac;

32 cryptographically secure random bytes (e.g. an AES-256 key)

SELECT crypto_random_bytes(32) AS key;

Deterministic checksum of an ordered dataset

SELECT lower(to_hex(crypto_hash_agg('sha2-256', email ORDER BY email))) AS dataset_hash
FROM users;