SQLite Database Extensions

A free-to-use loadable SQLite database extension providing AES-256 encryption and SHA512 implementations to protect your data.

SQLite Database Extensions

Introduction

SQLite is a robust and competent database engine. It’s the world’s most used database engine. Google uses it extensively on their Android phones. Airbus has confirmed that SQLite is used in the flight software for the A350 XWB family of aircraft. The technology is rock solid, and we use it in our solutions when the use case matches the strength of the database.

By design, SQLite has a compact array of functions that may appear limiting. However, they offer a run-time loadable extension feature allowing developers to write and include their own functions. We wrote the functions in this extension pack to help one of our customers secure their customer and order data.

Loading the Extensions

Open a database and use the .load command to load the extension file. You can rename the extension file to something shorter if you prefer. A status message is printed to the sqlite terminal if the extensions are loaded correctly. Once loaded, you can use the new functions within your SQL statements.

1sqlite3 test.db
2
3SQLite version 3.31.1 2020-01-27 19:55:54
4Enter ".help" for usage hints.
5
6sqlite> .load ./sqlite-extensions-amd64.so
7BRADLEY SOFTWARE - EXTENSIONS LOADED
8
9sqlite>

sha512()

The sha512 function accepts one input and returns the cryptographic hash for that input value. Supplying the same input will always produce the same output. Developers can use this function to anonymise data within your database tables. The return value will always be 128 characters long.

1SELECT sha512("123-789-4560");

Will return:

11f8d4a59c79cb81368ebfe7011f31d48
25739fc0e0c9256d5a892e1abe521258c
376935a9db39369603f5155563e4aad69
4518c30ada46b8561bf9f41c1427346fc

sha512_fixed_length()

There are times were it’s appropriate and convenient to store the first n characters of a cryptographic hash. The sha512_fixed_length function accepts two inputs. The first being the value you want to hash, the second being the fixed length you want to return. Supplying the same input and fixed length will always produce the same output. Developers can use this function to anonymise data within your database tables.

1SELECT sha512_fixed_length("123-789-4560", 10);

Will return:

11f8d4a59c7

generate_aes256_key()

To use the AES encryption and decryption functions, you must create a 32-bit key and store it within an environment variable. The variable name needs to be: BRADLEY_SOFTWARE_SQLITE_AES

You can either generate your own key, or use the generate_aes256_key() to generate a random AES key. For example, if you run the following command several times, you’ll see it generates unique keys. Pick one and set the value in the environment variable.

1SELECT generate_aes256_key();

aes_encrypt()

The example below demonstrates how to use the aes_encrypt function. The function accepts one value, the text you want to encrypt and returns the bytes of the encrypted text. The example creates a table to hold two values. It contains the plain text and the encrypted version as a BLOB datatype.

1CREATE TABLE spread (
2    "plain_text"     TEXT,
3    "encrypted_text" BLOB
4);
5
6INSERT INTO spread VALUES ("Butter", aes_encrypt("Butter"));
7INSERT INTO spread VALUES ("Margarine", aes_encrypt("Margarine"));
8
9SELECT plain_text, encrypted_text FROM spread;

The last SELECT statement in the above example returns the following, showing the encrypted data.

1Butter|K};7�=C��W�#<���v`[uf�
2                             �q犼LD
3Margarine|$���lxxƲr>
4�02��Z��k/L��9
5              �1�5� L

aes_decrypt()

The example below demonstrates how to use the aes_decrypt function. The function accepts one value, the encrypted byte sequence you want to decrypt and returns the original plain text.

1SELECT plain_text,
2       aes_decrypt(encrypted_text)
3  FROM spread;

Platforms/Download

The extension pack supports:

Language

Written and compiled in Go 1.23.0

Source Code Availability

None

Future Plans

Version History