Signal Zero

A digital agency developing cloud-native software solutions.

Published: • Updated:

SQLite Extensions - Signal Zero

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

SQLite Extensions

Table of Contents
  1. Introduction
  2. Loading the Extensions
  3. sha512()
  4. sha512_fixed_length()
  5. generate_aes256_key()
  6. aes_encrypt()
  7. aes_decrypt()
  8. Platforms/Download
  9. Language
  10. Source Code Availability
  11. Future Plans
  12. Version History

↑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 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>

↑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.

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

Will return:

1f8d4a59c79cb81368ebfe7011f31d48
5739fc0e0c9256d5a892e1abe521258c
76935a9db39369603f5155563e4aad69
518c30ada46b8561bf9f41c1427346fc

↑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.

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

Will return:

1f8d4a59c7

↑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: 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.

SELECT 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.

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

↑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.

SELECT plain_text,
       aes_decrypt(encrypted_text)
  FROM spread;

↑Platforms/Download

The extension pack supports:

All binaries are 64-bit and require a 64-bit version of SQLite.

↑Language

Written and compiled in Go 1.19.4

↑Source Code Availability

None

↑Future Plans

↑Version History