SQLite Leading Zero Loss in STRING Column with UTF-16 Binding

Issue Overview: STRING Column Type and UTF-16 Binding Causing Leading Zero Truncation

The core issue revolves around the loss of leading zeros when inserting UTF-16-encoded phone numbers into a SQLite column declared with the STRING type. The user observes that inserting "01012345678" via sqlite3_bind_text16 results in "1012345678" being stored. This truncation occurs despite explicit UTF-16 binding, raising questions about SQLite’s internal encoding conversions and type affinity rules.

Key Observations

1. Column Declaration: The column Info_Tel is declared as STRING, a non-standard SQLite type name that triggers NUMERIC affinity under SQLite’s type affinity rules. This causes SQLite to prioritize numeric conversion when possible.
2. UTF-16 Binding: The use of sqlite3_bind_text16 inserts a UTF-16 string. However, the NUMERIC affinity of the STRING column leads SQLite to interpret the input as a number, stripping leading zeros during storage.
3. UTF-8 Conversion Attempt: When converting the input to UTF-8 and binding it with sqlite3_bind_text, an unexpected space is appended (e.g., "01012345678 "). This suggests an error in handling string termination or length parameters.

Technical Context

• Type Affinity: SQLite uses column type names to assign one of five affinities: TEXT, NUMERIC, INTEGER, REAL, or BLOB. The STRING type maps to NUMERIC affinity, which attempts to convert values to integers or floats. If the input resembles a number (e.g., "0123"), it is stored as an integer (123), discarding leading zeros.
• UTF-16 vs. UTF-8: SQLite internally converts UTF-16 inputs to UTF-8 for storage unless the database uses a UTF-16 encoding. The sqlite3_bind_text16 function automatically performs this conversion, but type affinity takes precedence over encoding in determining storage behavior.

Possible Causes: NUMERIC Affinity and Encoding Misalignment

1. NUMERIC Affinity Stripping Leading Zeros

• Type Name Mismatch: The declaration Info_Tel STRING assigns NUMERIC affinity because "STRING" does not match SQLite’s reserved type names for TEXT affinity (e.g., TEXT, VARCHAR). NUMERIC affinity converts text that parses as a number into an integer or float. For "01012345678", this results in the integer 1012345678.
• Implicit Type Conversion: Even when binding text, NUMERIC affinity triggers a "try-to-convert-to-number" step. If the bound text is a numeric string, SQLite converts it to an integer, removing non-significant leading zeros.

2. UTF-16 to UTF-8 Conversion Artifacts

• Encoding Overhead: When using sqlite3_bind_text16, SQLite converts the UTF-16 string to UTF-8 for storage in a UTF-8 database. This conversion is lossless for ASCII characters (e.g., digits), but the NUMERIC affinity still applies, causing zero truncation.
• Incorrect Length Parameter: The user’s UTF-8 binding attempt (sqlite3_bind_text(..., length=12)) may include the null terminator or miscalculate byte counts. For "01012345678" in UTF-8, the byte length is 11 (1 byte per digit), but specifying length=12 includes an extra byte, resulting in a trailing space or null character.

3. Schema Design Flaws

• Ambiguous Column Type: Using non-standard type names like STRING introduces unpredictability. A column intended for text storage should use a type name that enforces TEXT affinity (e.g., TEXT, CHAR).
• Data Validation Gaps: Phone numbers often include leading zeros, hyphens, or country codes, making them unsuitable for numeric storage. Storing them in a NUMERIC-affinity column risks data loss.

Troubleshooting Steps, Solutions & Fixes

Step 1: Enforce TEXT Affinity via Column Type

Solution: Modify the column type to a name that triggers TEXT affinity.
Implementation:

ALTER TABLE ABCDE RENAME COLUMN Info_Tel TO Info_Tel_tmp;
ALTER TABLE ABCDE ADD COLUMN Info_Tel TEXT;
UPDATE ABCDE SET Info_Tel = Info_Tel_tmp;
ALTER TABLE ABCDE DROP COLUMN Info_Tel_tmp;

Rationale: Columns declared as TEXT enforce TEXT affinity, preserving input exactly as provided. This prevents SQLite from interpreting phone numbers as numbers.

Alternative: Use a non-reserved type name that still maps to TEXT affinity, such as CHARSTRING or VARCHAR(15):

CREATE TABLE ABCDE (..., Info_Tel CHARSTRING);

Step 2: Validate Binding Parameters and Encoding

Solution: Ensure correct use of sqlite3_bind_text with UTF-8 encoding and proper length.
Code Fix:

// For UTF-16 input (e.g., Windows wchar_t strings)
const wchar_t* phone = L"01012345678";
int byte_length = wcslen(phone) * sizeof(wchar_t); // Length in bytes, excluding null terminator
sqlite3_bind_text16(stmt, param_index, phone, byte_length, SQLITE_TRANSIENT);

// For UTF-8 input
const char* phone_utf8 = "01012345678";
sqlite3_bind_text(stmt, param_index, phone_utf8, strlen(phone_utf8), SQLITE_TRANSIENT);

Key Parameters:

• Length: Use strlen() for UTF-8 (byte count without null terminator) or wcslen() * sizeof(wchar_t) for UTF-16.
• Destructor: SQLITE_TRANSIENT ensures SQLite makes a copy of the string if needed.

Debugging Tips:

• Use sqlite3_column_type(stmt, column_index) after insertion to verify the stored data type is SQLITE_TEXT.
• Retrieve data with sqlite3_column_text() and compare to the original input.

Step 3: Address Encoding-Specific Edge Cases

Issue: Trailing space when binding UTF-8 with length=12.
Cause: The string "01012345678" has 11 characters. In UTF-8, this requires 11 bytes. Specifying length=12 includes an extra byte, which may be a null terminator or garbage data.
Fix: Use strlen() to compute the exact length:

const char* phone_utf8 = "01012345678";
sqlite3_bind_text(stmt, param_index, phone_utf8, strlen(phone_utf8), SQLITE_TRANSIENT);

Step 4: Schema Validation and Data Integrity

Preventative Measures:

1. Use Standard Type Names: Replace STRING with TEXT in schema definitions.
2. Add Check Constraints: Enforce phone number format:

   ALTER TABLE ABCDE ADD CHECK (Info_Tel GLOB '[0-9]*');
   

3. Migration Scripts: Update existing databases to replace NUMERIC-affinity columns with TEXT-affinity equivalents.

Step 5: Advanced Debugging with SQLite Internals

Diagnostic Queries:

-- Check column affinity
SELECT name, type, affinity FROM pragma_table_info('ABCDE');

Expected Output:

name      | type    | affinity
Info_Tel  | TEXT    | text

Hexdump Verification:
Use HEX() to inspect stored values:

SELECT HEX(Info_Tel) FROM ABCDE;

For "01012345678" stored as TEXT, the output should be 3031303132333435363738 (hex for UTF-8 "01012345678"). If stored as INTEGER, the hex will represent the number 1012345678 (e.g., 0x3C34FB6E).

Final Solution Summary

1. Schema Fix: Change STRING to TEXT or equivalent to enforce TEXT affinity.
2. Binding Fix: Use correct length parameters and encoding functions.
3. Validation: Add checks to ensure data integrity.

By addressing type affinity and binding parameters, the leading zero truncation issue is resolved, ensuring phone numbers are stored as exact text strings.