Converting Hexadecimal Address Strings to BLOB in SQLite: Resolving Length Mismatch and Encoding Issues

Understanding the Hexadecimal-to-BLOB Conversion Challenge

Core Problem: Mismatch Between String Representation and BLOB Encoding

The primary issue revolves around converting a hexadecimal string (e.g., 0x5DF9B87991262F6BA471F09758CDE1c0FC1De734) into a 20-byte BLOB in SQLite. The user’s initial approach used CAST(substr(address, 3) AS BLOB), which resulted in a BLOB of length 40 instead of the expected 20 bytes. This discrepancy arises from a fundamental misunderstanding of how SQLite handles string-to-BLOB conversions and hexadecimal encoding.

Hexadecimal strings represent binary data in a human-readable format where each pair of characters (e.g., 5D, F9) corresponds to a single byte. However, SQLite does not natively interpret hexadecimal strings as binary data. When a string is cast to a BLOB, SQLite treats each character in the string as a single byte. For example, the substring 5DF9 (4 characters) becomes a 4-byte BLOB instead of being interpreted as two bytes (0x5D and 0xF9). This explains why the length of the resulting BLOB is 40: the original substring has 40 characters (after removing the 0x prefix), and each character is stored as a byte.

Limitations of SQLite’s Built-in Functions

SQLite lacks a native function to directly convert hexadecimal strings to BLOBs. The HEX() function performs the inverse operation—converting a BLOB to a hexadecimal string—but there is no counterpart like UNHEX() in standard SQLite. This absence forces users to implement workarounds, which can be inefficient or require external tools. The problem is exacerbated when migrating data between tables with different column types (e.g., TEXT to BLOB), as seen in the wallet and test tables in the example.

Key Requirements for a Valid Solution

A valid solution must:

1. Strip the 0x prefix from the input string.
2. Parse the hexadecimal pairs (e.g., 5D, F9) into their corresponding byte values.
3. Concatenate the bytes into a single BLOB of length 20.
4. Handle case insensitivity (e.g., a1 vs. A1).
5. Validate the input to ensure it contains only valid hexadecimal characters.

Root Causes of the Conversion Failure

Misuse of the CAST Operator

The CAST operator in SQLite does not perform hexadecimal decoding. When applied to a string, it simply reinterprets the string’s bytes as a BLOB. For example:

SELECT CAST('4142' AS BLOB);  -- Result: BLOB '4142' (4 bytes), not x'4142' (2 bytes)

This behavior is why CAST(substr(address, 3) AS BLOB) produces a 40-byte BLOB: the input string has 40 characters, each stored as a byte.

Lack of Native Hexadecimal Decoding Support

SQLite’s core functions do not include a mechanism to parse hexadecimal strings into BLOBs. While the HEX() function encodes BLOBs to strings, the reverse operation requires custom logic. This gap necessitates either:

• Custom SQLite extensions (written in C/C++),
• External scripts (e.g., Python, Perl), or
• Complex SQL workarounds using built-in functions.

Insufficient String Manipulation in Initial Approach

The user’s initial attempt removed the 0x prefix with substr(address, 3) but did not process the remaining string into hexadecimal pairs. Without this step, the string 5DF9B879... is treated as literal bytes rather than encoded data.

Comprehensive Solutions for Hexadecimal-to-BLOB Conversion

Solution 1: Custom SQLite Extension (UNHEX Function)

Implementing the UNHEX Function

The most robust solution is to create a custom SQLite function using the C API. The UNHEX function provided in the forum discussion decodes a hexadecimal string into a BLOB. Here’s how to use it:

1. Compile the Extension:
   • Save the C code as sqlite_unhex.c.
   • Compile with:

     gcc -g -fPIC -shared sqlite_unhex.c -o sqlite_unhex.so
     

2. Load the Extension in SQLite:

   .load ./sqlite_unhex
   

3. Execute the Conversion:

   INSERT INTO test (address)
   SELECT UNHEX(substr(address, 3)) FROM wallet;
   

Advantages:

• Handles large datasets efficiently.
• Validates input (returns NULL for invalid hex strings).

Limitations:

• Requires C/C++ compilation skills.
• Platform-dependent (needs recompilation for different OSes).

Solution 2: Pure SQL Workaround Using Recursive CTEs

For users unable to compile extensions, a pure SQL approach can be used. This method leverages recursive Common Table Expressions (CTEs) to split the hexadecimal string into pairs and convert them to bytes.

WITH RECURSIVE
  hex_string AS (
    SELECT substr(address, 3) AS hex, 1 AS pos FROM wallet
  ),
  byte_pairs AS (
    SELECT hex, pos, substr(hex, pos, 2) AS pair
    FROM hex_string
    WHERE pos <= length(hex)
    UNION ALL
    SELECT hex, pos + 2, substr(hex, pos + 2, 2)
    FROM byte_pairs
    WHERE pos + 2 <= length(hex)
  )
SELECT hex_grouped.hex, hex_grouped.blob
FROM (
  SELECT hex, GROUP_CONCAT(CHAR((CASE SUBSTR(pair, 1, 1)
    WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12
    WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15
    ELSE CAST(SUBSTR(pair, 1, 1) AS INTEGER)
  END) * 16 + 
  (CASE SUBSTR(pair, 2, 1)
    WHEN 'A' THEN 10 WHEN 'B' THEN 11 WHEN 'C' THEN 12
    WHEN 'D' THEN 13 WHEN 'E' THEN 14 WHEN 'F' THEN 15
    ELSE CAST(SUBSTR(pair, 2, 1) AS INTEGER)
  END)), '') AS blob
  FROM byte_pairs
  GROUP BY hex
) AS hex_grouped;

Steps:

1. Strip the 0x Prefix: substr(address, 3).
2. Split into Pairs: Use a recursive CTE to iterate over the string in 2-character increments.
3. Convert Pairs to Bytes: Manually calculate the byte value using CASE statements.
4. Concatenate Bytes: Use GROUP_CONCAT to assemble the final BLOB.

Advantages:

• No external dependencies.
• Works on all SQLite installations.

Limitations:

• Extremely slow for large datasets (e.g., millions of rows).
• Case-sensitive (requires uppercase hex characters).

Solution 3: External Script for Conversion

A practical alternative is to export the data, process it with a scripting language (e.g., Python), and re-import it.

Python Example:

import sqlite3
import binascii

conn = sqlite3.connect('wallet.db')
cursor = conn.cursor()

# Export data
cursor.execute("SELECT address FROM wallet")
rows = cursor.fetchall()

# Process and insert into test table
for row in rows:
    hex_str = row[0][2:]  # Remove '0x'
    try:
        blob = binascii.unhexlify(hex_str)
        cursor.execute("INSERT INTO test (address) VALUES (?)", (blob,))
    except binascii.Error:
        print(f"Invalid hex string: {hex_str}")

conn.commit()
conn.close()

Steps:

1. Export Data: Retrieve all address values from the wallet table.
2. Strip Prefix: Remove the 0x prefix.
3. Decode Hex: Use binascii.unhexlify to convert the string to bytes.
4. Insert BLOB: Write the decoded bytes into the test table.

Advantages:

• Handles large datasets efficiently.
• Built-in error checking (e.g., invalid hex characters).

Limitations:

• Requires temporary data export/import.
• Depends on external scripting skills.

Solution 4: Hybrid Approach Using SQLite’s HEX Function

If the wallet table’s address column was originally populated using HEX(some_blob), the conversion can be reversed using a combination of X'...' literals:

INSERT INTO test (address)
SELECT CAST(X'||substr(address, 3)||' AS BLOB) FROM wallet;

Steps:

1. Construct X’…’ Literal: Concatenate X' with the hex string (without 0x).
2. Cast to BLOB: Use CAST to ensure the result is treated as a BLOB.

Advantages:

• Simple and fast.
• No external tools required.

Limitations:

• Only works if the hex string is uppercase and has no invalid characters.
• Requires manual validation.

Summary of Recommendations

Choose the method that best aligns with your technical constraints and performance requirements. For most users, the external script or custom extension will provide the best balance of reliability and efficiency.