Hashing, encoding and decoding in SQLite

You've probably heard about hexadecimal encoding in SQLite:

select hex('hello');
select unhex('68656C6C6F');

68656C6C6F
hello

SQLite does not support other encoding algorithms by default. However, you can easily enable them using the sqlean-crypto extension (not related to cryptocurrency in any way). It also provides hashing and message digest functions.

Note. Unlike other DBMS, adding extensions to SQLite is a breeze. Download a file, run one database command — and you are good to go.

Hashing • Encoding/decoding • Installation and usage

Hashing and message digest functions

crypto_blake3 returns a BLAKE3 hash of the data:

select hex(crypto_blake3('abc'));
-- 6437B3AC38465133FFB63B75273A8DB548C558465D79DB03FD359C6CD5BD9D85

crypto_md5 returns an MD5 hash of the data:

select hex(crypto_md5('abc'));
-- 900150983CD24FB0D6963F7D28E17F72

crypto_sha1, crypto_sha256, crypto_sha384 and crypto_sha512 return various SHA hashes:

select hex(crypto_sha1('abc'));
-- A9993E364706...
select hex(crypto_sha256('abc'));
-- BA7816BF8F01CFEA...
select hex(crypto_sha384('abc'));
-- CB00753F45A35E8BB5A0...
select hex(crypto_sha512('abc'));
-- DDAF35A193617ABACC417349...

Encoding and decoding functions

sqlean-crypto adds two functions for encoding and decoding data:

crypto_encode(data, algo) encodes binary data into a textual representation using the specified algorithm.
crypto_encode(text, algo) decodes binary data from a textual representation using the specified algorithm.

Supported algorithms: base32, base64, base85, hex and url.

Base32 uses 32 human-readable characters to represent binary data:

select crypto_encode('hello', 'base32');
-- NBSWY3DP
select crypto_decode('NBSWY3DP', 'base32');
-- hello

Base64 uses 64 printable characters to represent binary data:

select crypto_encode('hello', 'base64');
-- aGVsbG8=
select crypto_decode('aGVsbG8=', 'base64');
-- hello

Base85 (aka Ascii85) uses 85 printable characters to represent binary data:

select crypto_encode('hello', 'base85');
-- BOu!rDZ
select crypto_decode('BOu!rDZ', 'base85');
-- hello

Hexadecimal uses 16 characters (0-9 and A-F) to represent binary data:

select crypto_encode('hello', 'hex');
-- 68656c6c6f
select crypto_decode('68656c6c6f', 'hex');
-- hello

URL encoding replaces non-alphanumeric characters in a string with their corresponding percent-encoded values:

select crypto_encode('hel lo!', 'url');
-- hel%20lo%21
select crypto_decode('hel%20lo%21', 'url');
-- hel lo!

Installation and usage

Download the latest release
Use with SQLite command-line interface:

sqlite> .load ./crypto
sqlite> select crypto_encode('hello', 'base64');