Crypto

What this extension adds

DuckDB ships with hash() (non-cryptographic), md5(), and sha256(). The Crypto extension adds the rest of what production cryptographic workloads need:

• More algorithms — BLAKE3, BLAKE2b, BLAKE2s, the full SHA-2 family (including SHA-512/224 and SHA-512/256), the SHA-3 family, SHAKE128 / SHAKE256, Keccak (the Ethereum variant), RIPEMD-160, and SM3. crypto_algorithms() lists them all, with their digest lengths and whether your OpenSSL build provides them.
• More input types — every numeric type, DECIMAL, VARCHAR, BLOB, BOOLEAN, DATE, TIME, TIME_TZ, every TIMESTAMP variant, INTERVAL, UUID, ENUM, string-backed types such as JSON and BIT, and lists or fixed-size arrays of those — not just strings. Different DuckDB types hash to different digests by design.
• Extendable output — crypto_hash_xof produces as many bytes as you ask for from BLAKE3, SHAKE128, or SHAKE256.
• HMAC with the same algorithm vocabulary — for keyed message authentication — and crypto_equals for comparing MACs in constant time.
• Key derivation — PBKDF2, HKDF, scrypt, and Argon2 for password hashing and for deriving keys from a secret.
• crypto_hash_agg — an order-deterministic aggregate hash for dataset checksums and change detection, usable as a window function.
• crypto_random_bytes — cryptographically secure random bytes from OpenSSL’s CSPRNG.

Cryptographic vs non-cryptographic hashing

Use Crypto when you need:

• Tamper resistance — adversaries can’t construct collisions cheaply (BLAKE3, SHA-2, SHA-3).
• Authentication — HMAC for verifying message integrity with a shared secret.
• Stable cross-system fingerprints — agreed-upon algorithm output that other systems can compute identically (e.g. ETags, content-addressed storage).
• Secure random — keys, salts, nonces.
• Password hashing and key derivation — Argon2, scrypt, PBKDF2, HKDF.

Use Hashfuncs instead when you need:

• Speed — xxHash and RapidHash run at near-RAM bandwidth with no cryptographic guarantees.
• Hash partitioning, sharding, cache keys — collision risk on adversarial inputs is acceptable.
• Bloom-filter / cuckoo-filter inputs — paired with bitfilters.

Both extensions can coexist; pick per use case.

Determinism and types

crypto_hash is deterministic for a given (algorithm, value, type) triple but is type-sensitive — 42::INTEGER and 42::BIGINT produce different hashes. This is the right behaviour for cryptographic use: it keeps the hash from collapsing across logically distinct domains.

Every value is turned into bytes by one canonical, platform-independent encoding, shared by crypto_hash, list hashing, and crypto_hash_agg:

SELECT crypto_hash('sha2-256', UUID '550e8400-e29b-41d4-a716-446655440000')
     = crypto_hash('sha2-256', unhex('550e8400e29b41d4a716446655440000')) AS same;
-- true

Lists of VARCHAR or BLOB values (and ENUM labels) include each element’s length (as a 64-bit integer) before its content, defending against length-extension-style ambiguity attacks. Lists can’t contain NULL elements or nested types (lists of lists, structs); UNION and VARIANT values are rejected.

NULLs hash to NULL — crypto_hash('sha2-256', NULL::VARCHAR) IS NULL returns true.

Choosing an algorithm

Algorithm names are case-insensitive. crypto_algorithms() reports each one’s family, implementation, digest length, and whether it is an extendable-output function (XOF) or can be used with HMAC:

SELECT name, family, digest_length, is_xof, supports_hmac
FROM crypto_algorithms()
WHERE available
ORDER BY name;

An unknown or unavailable algorithm name raises an error that lists the algorithms you can use.

Extendable output (XOF)

crypto_hash_xof(algorithm, value, length) produces length bytes (1 to 4,294,967,295) from blake3, shake128, or shake256. A shorter output is a prefix of a longer one, and the default length matches crypto_hash:

SELECT crypto_hash_xof('blake3', 'abc', 32) = crypto_hash('blake3', 'abc') AS blake3_prefix,
       crypto_hash_xof('shake128', 'abc', 32) = crypto_hash('shake128', 'abc') AS shake_prefix;
-- true, true

Any other algorithm is an error: crypto_hash_xof requires an extendable-output algorithm (blake3, shake128, shake256).

HMAC and constant-time comparison

crypto_hmac(algorithm, key, message) accepts VARCHAR or BLOB for both the key and the message, so a key from crypto_random_bytes or unhex() works directly. A VARCHAR key is used verbatim — '\x41' is four characters, not the byte 0x41. blake3 uses its keyed mode, which requires a 32-byte key; shake128 and shake256 can’t be used with HMAC.

When verifying a MAC, compare with crypto_equals(a, b) rather than =. It compares VARCHAR/BLOB values with OpenSSL’s CRYPTO_memcmp, so the time taken doesn’t reveal how many leading bytes matched.

Key derivation

Password, salt, key and info arguments accept VARCHAR or BLOB and give the same result for the same bytes. Output length is capped at 1 MiB. NULL in any argument returns NULL, and every argument may vary per row.

SELECT lower(to_hex(crypto_argon2('argon2id', 'password', 'somesalt', 2, 65536, 4, 32))) AS hash;
-- 1a9677b0afe81fda7b548895e7a1bfeb8668ffc19a530e37e088a668fab1c02a

Aggregate hash semantics

crypto_hash_agg('algo', col ORDER BY ord) produces the same digest as crypto_hash('algo', LIST(col ORDER BY ord)). It accepts the same scalar types as crypto_hash (UUID, DECIMAL, ENUM, BOOLEAN, DATE, TIME, TIMESTAMP, INTERVAL, …) with the same encoding, but not lists or structs.

• ORDER BY is required — DuckDB’s query planner doesn’t guarantee row order, so an unordered aggregate would be non-deterministic. A query without it is rejected when it is planned, with Hash aggregation requires a distinct total ordering. Ordering only by the GROUP BY key is not a total ordering of the rows in a group and is rejected too.
• NULL values are an error (crypto_hash_agg does not support NULL values). Skip them explicitly with FILTER (WHERE col IS NOT NULL).
• It works as a window function. crypto_hash_agg('sha2-256', v ORDER BY v) OVER (ORDER BY v) gives a running digest, equal to crypto_hash of the matching list(v) OVER (...). In a window with no ORDER BY at all — neither in the aggregate nor in the OVER clause — the missing ordering is not detected, so always order the frame.
• An empty group returns NULL.

This equivalence is useful when comparing datasets across systems: anything that can produce the equivalent ordered list (Python, Go, Rust) can compute the same digest and verify a DuckDB result.

Random byte generation

crypto_random_bytes(n) returns n bytes from OpenSSL’s RAND_bytes(). That’s the same CSPRNG used by TLS implementations, key generation libraries, and openssl rand.

• Minimum length: 1 byte
• Maximum length: 4,294,967,295 bytes (4 GB − 1, DuckDB’s BLOB cap)
• Length 0 or negative raises InvalidInputException

Memory limits

Large outputs — crypto_random_bytes, crypto_hash_xof, and the KDFs — are allocated through DuckDB’s buffer manager, and the working memory of scrypt (128 × n × r bytes) and Argon2 (memory_kib) is reserved before OpenSSL allocates it. All of it counts against memory_limit, so an oversized request fails with Out of Memory Error instead of exhausting the machine, and the memory is released when the query finishes.

Changes in the DuckDB 2.0 release

Also new in the DuckDB 2.0 build:

• New functions: crypto_hash_xof, crypto_equals, crypto_pbkdf2, crypto_hkdf, crypto_scrypt, crypto_argon2, and the crypto_algorithms() table function.
• New algorithms: blake2s-256, ripemd160, sha2-512/224, sha2-512/256, shake128 (32 bytes by default), shake256 (64 bytes by default), and sm3.
• crypto_hmac takes VARCHAR or BLOB for the key and the message.
• More crypto_hash types: DECIMAL, ENUM, INTERVAL, TIME_TZ, the TIMESTAMP variants, fixed-size ARRAY (hashed like the equivalent LIST), and JSON, BIT and other string-backed types.
• crypto_hash_agg accepts all the same scalar types, works as a window function, raises an error on NULL values, and enforces ORDER BY when the query is planned.
• Memory: large outputs and KDF working memory respect memory_limit (see Memory limits).
• Performance: scalar hashing and HMAC are roughly 5–16× faster. SHA-256 over 10 million VARCHAR values went from 1.81 s to 0.21 s; HMAC with a constant key from 6.71 s to 0.42 s, because consecutive rows with the same key reuse the expanded key.

Hash a Column

crypto_hash returns a BLOB. For human-readable output, hex-encode it:

SELECT id, lower(to_hex(crypto_hash('sha2-256', email))) AS email_hash
FROM users;

Different DuckDB types of the same numeric value hash differently — 42::INTEGER, 42::BIGINT, and '42'::VARCHAR all produce distinct digests. That’s intentional; it keeps the hash from collapsing across logically distinct values.

Sign a Message

HMAC combines a secret key with a message under a hash algorithm:

SELECT lower(to_hex(crypto_hmac('sha2-256', :secret, request_body))) AS signature
FROM api_requests;

The key and message may be VARCHAR or BLOB, so a key from crypto_random_bytes or unhex() works directly:

-- RFC 4231 test case 1
SELECT lower(to_hex(crypto_hmac('sha2-256', unhex('0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b'), 'Hi There'))) AS mac;
-- b0344c61d8db38535ca8afceaf0bf12b881dc200c9833da726e9376c2e32cff7

To verify, the receiving side computes the same HMAC and compares it with the one it was sent. Use crypto_equals for that comparison — it runs in constant time, so response timing doesn’t leak how many leading bytes matched:

SELECT crypto_equals(crypto_hmac('sha2-256', 'k', 'm'), crypto_hmac('sha2-256', 'k', 'm')) AS valid,
       crypto_equals(crypto_hmac('sha2-256', 'k', 'm'), crypto_hmac('sha2-256', 'k', 'n')) AS tampered;
-- true, false

Generate a Salt

-- 16-byte random salt for password hashing
SELECT crypto_random_bytes(16) AS salt;

The bytes come from OpenSSL’s CSPRNG — suitable for cryptographic use. Length can be anything from 1 byte to 4 GB − 1, and large requests count against DuckDB’s memory_limit.

Hash a Password

A fast hash such as SHA-256 is not a password hash. Use a deliberately slow, memory-hard KDF — Argon2id is the usual first choice — with a random salt per user, and store the salt and parameters next to the hash:

SELECT lower(to_hex(crypto_argon2('argon2id', 'password', 'somesalt', 2, 65536, 4, 32))) AS hash;
-- 1a9677b0afe81fda7b548895e7a1bfeb8668ffc19a530e37e088a668fab1c02a

The arguments are the variant, password, salt (at least 8 bytes), iterations, memory in KiB (64 MiB here), parallelism, and output length. crypto_scrypt and crypto_pbkdf2 are there when a system you interoperate with requires them:

-- RFC 7914 scrypt test vector: n = 1024, r = 8, p = 16
SELECT lower(to_hex(crypto_scrypt('password', 'NaCl', 1024, 8, 16, 64))) AS scrypt;
-- fdbabe1c9d3472007856e7190d01e9fe7c6ad7cbc8237830e77376634b3731622eaf30d92e22a3886ff109279d9830dac727afb94a83ee6d8360cbdfa2cc0640

-- RFC 6070 PBKDF2-HMAC-SHA1 test vector
SELECT lower(to_hex(crypto_pbkdf2('sha1', 'password', 'salt', 4096, 20))) AS pbkdf2;
-- 4b007901b765489abead49d926f721d065a429c1

To check a login, recompute the hash from the stored salt and parameters and compare with crypto_equals. scrypt and Argon2 working memory counts against memory_limit, so a cost that is too high for the session fails with Out of Memory Error.

Derive Keys from a Secret

HKDF turns one high-entropy secret (a random master key, a key-exchange result) into as many independent keys as you need, separated by the info string. It is fast, so don’t use it on passwords:

-- RFC 5869 test case 1
SELECT lower(to_hex(crypto_hkdf('sha2-256',
         unhex('0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b0b'),
         unhex('000102030405060708090a0b0c'),
         unhex('f0f1f2f3f4f5f6f7f8f9'),
         42))) AS okm;
-- 3cb25f25faacd57a90434f64d0362f2a2d2d0a90cf1a5a4c5db02d56ecc4c5bf34007208d5b887185865

The output can be up to 255 × the digest length — 8,160 bytes for sha2-256.

Variable-Length Digests

crypto_hash_xof asks BLAKE3, SHAKE128, or SHAKE256 for exactly as many bytes as you need — a 100-byte fingerprint, a long keystream for a test fixture. Longer outputs extend shorter ones:

SELECT lower(to_hex(crypto_hash_xof('blake3', 'abc', 64))) AS digest;
-- 6437b3ac38465133ffb63b75273a8db548c558465d79db03fd359c6cd5bd9d851fb250ae7393f5d02813b65d521a0d492d9ba09cf7ce7f4cffd900f23374bf0b

Ethereum Keccak-256

Ethereum uses the original Keccak padding, not the standardized SHA-3. keccak256 computes the Ethereum variant:

SELECT lower(to_hex(crypto_hash('keccak256', ''))) AS empty_keccak;
-- c5d2460186f7233c927e7db2dcc703c0e500b653ca82273b7bfad8045d85a470

Builds before the DuckDB 2.0 release returned SHA-3 for the keccak* names; see Changes in the DuckDB 2.0 release.

Dataset Checksum

crypto_hash_agg is the aggregate counterpart of crypto_hash. Hashing all values in a column in a specified order produces a single deterministic digest you can use for change detection or to verify a partition matches a known-good baseline:

-- Whole-table checksum
SELECT lower(to_hex(crypto_hash_agg('sha2-256', email ORDER BY email)))
FROM users;

-- Per-partition checksum — matches across systems if rows are identical
SELECT day,
       lower(to_hex(crypto_hash_agg('sha2-256', payload ORDER BY id))) AS day_hash
FROM events
GROUP BY day
ORDER BY day;

The ORDER BY is required — without it, hashes would be non-deterministic across query plans, so the query is rejected with Hash aggregation requires a distinct total ordering. Ordering by the group key alone isn’t enough; order by something that is unique within each group.

NULL values raise an error rather than being skipped silently. Leave them out explicitly:

SELECT crypto_hash_agg('sha2-256', x ORDER BY x) FILTER (WHERE x IS NOT NULL)
     = crypto_hash('sha2-256', [1, 3]) AS matches
FROM (VALUES (1), (NULL), (3)) t(x);
-- true

crypto_hash_agg also works as a window function — a running checksum, or one per sliding frame. Give the window (or the aggregate) an ORDER BY; with neither, the missing ordering isn’t detected:

SELECT day, id,
       lower(to_hex(crypto_hash_agg('sha2-256', payload ORDER BY id)
                    OVER (PARTITION BY day ORDER BY id))) AS running_hash
FROM events
ORDER BY day, id;

Tamper-Evident Audit Log

Build a hash chain by hashing each row’s content together with the previous row’s hash:

WITH chained AS (
  SELECT
    id,
    action,
    user_id,
    created_at,
    LAG(hash) OVER (ORDER BY created_at) AS prev_hash,
    crypto_hash('sha2-256',
      [CAST(id AS VARCHAR), action, CAST(user_id AS VARCHAR), CAST(created_at AS VARCHAR),
       COALESCE(LAG(hash) OVER (ORDER BY created_at)::VARCHAR, '')]
    ) AS hash
  FROM audit_log
)
SELECT * FROM chained ORDER BY created_at;

Verify the chain later by recomputing each hash and comparing — any tampering breaks the chain at the first altered row.

Stable IDs from Multiple Columns

Generate a deterministic ID from any combination of columns:

SELECT
  customer_email,
  product_sku,
  purchase_date,
  -- stable surrogate key, idempotent across re-runs
  lower(to_hex(crypto_hash('sha2-256',
    [customer_email, product_sku, CAST(purchase_date AS VARCHAR)]
  ))) AS purchase_id
FROM raw_purchases;

Pair with hashfuncs when you want the same property but cheaper (non-cryptographic) for partitioning, sharding, or cache keys.

Algorithm Reference

SELECT * FROM crypto_algorithms() lists every algorithm, including sha2-224, sha2-384, sha2-512/224, the remaining SHA-3, SHAKE and Keccak sizes, and whether each one is available in your OpenSSL build (md4 is not).