Hashing, encoding and decoding in SQLite

You've probably heard about hexadecimal encoding in SQLite:

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

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

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

6437B3AC38465133FFB63B75273A8DB548C558465D79DB03FD359C6CD5BD9D85

crypto_md5 returns an MD5 hash of the data:

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

900150983CD24FB0D6963F7D28E17F72

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

select hex(crypto_sha1('abc'));
-- A9993E364706816A...
select hex(crypto_sha256('abc'));
-- BA7816BF8F01CFEA...
select hex(crypto_sha384('abc'));
-- CB00753F45A35E8B...
select hex(crypto_sha512('abc'));
-- DDAF35A193617ABA...

A9993E364706816ABA3E25717850C26C9CD0D89D
BA7816BF8F01CFEA414140DE5DAE2223B00361A396177A9CB410FF61F20015AD
CB00753F45A35E8BB5A03D699AC65007272C32AB0EDED1631A8B605A43FF5BED8086072BA1E7CC2358BAECA134C825A7
DDAF35A193617ABACC417349AE20413112E6FA4E89A97EA20A9EEEE64B55D39A2192992A274FC1A836BA3C23A3FEEBBD454D4423643CE80E2A9AC94FA54CA49F

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

NBSWY3DP
hello

Base64 uses 64 printable characters to represent binary data:

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

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

aGVsbG8=
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

BOu!rDZ
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

68656c6c6f
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!

hel%20lo%21
hel lo!

Installation and usage

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

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