Published: • Updated:
SQLite Extensions - Signal Zero
A free-to-use loadable SQLite Extension providing AES-256 encryption and SHA512 implementations to protect your data.
Table of Contents
- Loading the Extensions
- Source Code Availability
- Future Plans
- Version History
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 appropriate extension file for your system/architecture. 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.
sqlite3 test.db SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .load ./s0-sqlite-extensions-amd64.so SIGNAL ZERO EXTENSIONS LOADED. sqlite>
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.
1f8d4a59c79cb81368ebfe7011f31d48 5739fc0e0c9256d5a892e1abe521258c 76935a9db39369603f5155563e4aad69 518c30ada46b8561bf9f41c1427346fc
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.
SELECT sha512_fixed_length("123-789-4560", 10);
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: SIGNALZERO_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.
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.
CREATE TABLE spread ( "plain_text" TEXT, "encrypted_text" BLOB ); INSERT INTO spread VALUES ("Butter", aes_encrypt("Butter")); INSERT INTO spread VALUES ("Margarine", aes_encrypt("Margarine")); SELECT plain_text, encrypted_text FROM spread;
The last SELECT statement in the above example returns the following, showing the encrypted data.
Butter|K};7�=C��W�#<���v`[uf� �q犼LD Margarine|$���lxxƲr> �02��Z��k/L��9 �1�5� L
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.
SELECT plain_text, aes_decrypt(encrypted_text) FROM spread;
The extension pack supports:
- s0-sqlite-extensions-amd64.so - Linux
- s0-sqlite-extensions-osx-amd64.so - OSX for Intel based macOS
- s0-sqlite-extensions-win-amd64.dll - Windows
All binaries are 64-bit and require a 64-bit version of SQLite.
Written and compiled in Go 1.19.4
↑Source Code Availability
- If anyone would like to store the AES key in a secrets vault like AWS Secrets Manager or Vault by HashiCorp, then please get in touch.
- Add Argon2 password hash function.
- Add text compression functions.
- 1.04 [09.07.2023] – Added Windows DLL.
- 1.03 [07.07.2023] – Added macOS version of the binary.
- 1.02 [27.05.2023] – Added sha512_fixed_length function.
- 1.01 [22.04.2023] – Initial release.