SQLite Basics: Inspect Binary Values with BLOB, hex, and unhex

Binary values can contain zero bytes, non-text bytes, and empty payloads. Keep them as BLOBs and use hexadecimal rendering when you need to inspect them. A string that looks like hexadecimal is still text until you deliberately decode it.

The unhex() function requires SQLite 3.41.0 or later. The 3.41.0 release notes record its introduction. These examples inspect small toy values; they do not implement a file format or an encryption scheme.

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. All three SQL files below were executed through the Python runner, and the output shown is observed output. The standalone SQLite CLI is not used.

Prepare a scratch directory

You need Bash, Python 3 with SQLite 3.41.0 or later, a text editor, and mktemp, rm, and rmdir. No additional Python packages or SQLite CLI are needed. Keep the same terminal open and save files as UTF-8. Stop if setup fails before saving any example files.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

Save as run-sql.py. Every invocation uses an independent in-memory database. The teaching runner prints pipe-separated rows and NULL for missing values, without escaping embedded pipes or newlines. These queries render binary values as text rather than sending raw bytes to the terminal. Put each statement’s ending semicolon at the end of a line, and do not put two statements on one line.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

The toy tables disappear when the connection closes. No example opens or overwrites an existing database file.

1. Distinguish bytes from their hexadecimal text

Save as inspect-blob-storage.sql:

CREATE TABLE packets (
    id INTEGER PRIMARY KEY,
    payload BLOB NOT NULL
);
INSERT INTO packets VALUES
    (1, X'410042FF'),
    (2, '410042FF'),
    (3, X'');
SELECT id, typeof(payload), length(payload), quote(payload), hex(payload)
FROM packets ORDER BY id;

Run:

python3 run-sql.py inspect-blob-storage.sql

Observed output:

1|blob|4|X'410042FF'|410042FF
2|text|8|'410042FF'|3431303034324646
3|blob|0|X''|

The first value contains four bytes, including 00 and FF. The second contains eight text characters that describe those bytes. The quoted representation makes the distinction visible, and hex() of the text renders the bytes of the characters themselves. The empty BLOB has zero bytes; its hex rendering is the empty field after the final separator.

In an ordinary table, declaring a column BLOB does not prevent storing text there. BLOB affinity performs no preferred conversion. Use a suitable CHECK constraint or a STRICT BLOB column when the stored class must always be binary. SQLite storage classes and affinity.

Hexadecimal is an encoding for display, not encryption. It also takes two text digits per byte. Store the representation that fits the application contract instead of assuming the two values above are interchangeable.

2. Decode hexadecimal and keep invalid input visible

Save as decode-hex-input.sql:

WITH inputs(id, encoded) AS (
    VALUES
        (1, '00ff41'),
        (2, '00-FF-41'),
        (3, '0-0FF41'),
        (4, 'ABC'),
        (5, 'GG'),
        (6, '')
)
SELECT id, quote(encoded),
       typeof(unhex(encoded, '-')) AS decoded_type,
       length(unhex(encoded, '-')) AS byte_count,
       quote(unhex(encoded, '-')) AS decoded_value
FROM inputs ORDER BY id;

Run:

python3 run-sql.py decode-hex-input.sql

Observed output:

1|'00ff41'|blob|3|X'00FF41'
2|'00-FF-41'|blob|3|X'00FF41'
3|'0-0FF41'|null|NULL|NULL
4|'ABC'|null|NULL|NULL
5|'GG'|null|NULL|NULL
6|''|blob|0|X''

Lowercase hex is accepted. The optional second argument permits hyphens between complete byte pairs. It does not allow a hyphen between the two digits of one byte, so the third row is invalid. An odd digit count and a non-hexadecimal character also produce NULL. unhex() reference.

Do not replace every decode failure with an empty BLOB: row 6 is a legitimate empty input, while rows 3 through 5 are malformed. If the destination requires a payload, report invalid rows before insertion or enforce a NOT NULL destination and handle the error. Keep the original text available while diagnosing the rejected input.

Only listed non-hexadecimal separators may be ignored. This is a hex decoder, not a generic punctuation remover. If your format requires fixed-length payloads, check the decoded byte length separately as well.

3. Slice and locate bytes without treating them as text

Save as slice-binary-frames.sql:

CREATE TABLE frames (
    id INTEGER PRIMARY KEY,
    body BLOB NOT NULL CHECK (typeof(body) = 'blob')
);
INSERT INTO frames VALUES
    (1, X'0100FF41'),
    (2, zeroblob(4)),
    (3, X'112233');
SELECT id, length(body),
       quote(substr(body, 1, 2)) AS prefix,
       quote(substr(body, 3)) AS remainder,
       instr(body, X'00') AS first_zero_position
FROM frames ORDER BY id;

Run:

python3 run-sql.py slice-binary-frames.sql

Observed output:

1|4|X'0100'|X'FF41'|2
2|4|X'0000'|X'0000'|1
3|3|X'1122'|X'33'|0

The first frame’s zero byte is ordinary stored data at position 2, not an end-of-string marker. The second frame is four zero bytes, not a missing value. The third frame has no zero byte, so its search position is 0; its shorter remainder contains only one byte.

For BLOB input, length() counts bytes and substr() uses one-based byte positions. instr() also searches by byte when both operands are BLOBs. zeroblob(4) creates four zero bytes. Binary-aware core functions.

The explicit typeof CHECK makes this table’s binary-only intent enforceable even though it is not STRICT. An ordinary INSERT of text would fail rather than silently change the meaning of length() and substr(). Do not cast arbitrary binary data to TEXT just to inspect it; a payload may contain invalid text encoding. Render it with hex() or quote(), and decode it only with knowledge of its actual format.

Clean up

In the same terminal, remove only the named example files and the empty scratch directory:

rm -- run-sql.py inspect-blob-storage.sql decode-hex-input.sql slice-binary-frames.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.