Three-Field Schema for Encrypted Phone Number Queries by Last Four Digits

Problem: Query Encrypted Phone Numbers by Last Four Digits

Systems must encrypt phone numbers to meet privacy regulations (e.g., China's Personal Information Protection Law, MLPS). However, operations, customer service, and analytics still need to filter users by the last four digits. The article evaluates four common but unsuitable approaches before presenting the industry-standard three-field solution.

1. Unsuitable Approaches

1.1 Decrypt All in Memory Then Filter

Load every encrypted phone number into memory, decrypt, then match. Failure mode: at millions or tens of millions of records, memory overflow crashes the service. Not viable for production.

1.2 Ciphertext Fuzzy Match (LIKE)

Encrypt the whole number and use SQL LIKE on the ciphertext. Failure mode: AES-256 or SM4 output is random; no pattern matches the last four digits. Queries return zero results.

1.3 Partial Encryption (First/Last Digits Plaintext)

Store first 3 and last 4 digits in plaintext, encrypt only the middle 4. Failure mode: if the database is breached, attackers can brute-force the middle 4 digits (10,000 combinations) and reconstruct the full number. This violates compliance and is considered obsolete.

1.4 Separate Mapping Table

Create a dedicated table mapping last four digits to user IDs. Drawbacks: high redundancy, dual-write maintenance (insert/update/delete on two tables), consistency risks, and increased storage.

2. Standard Solution: Three-Field Design

The recommended schema uses three columns per user:

full_cipher (VARCHAR): AES-256 or SM4 encryption of the complete phone number. Used only by core services (SMS sending, login verification) with strict access control. Never exposed to front-end.

masked_display (VARCHAR): Fixed format 100 **** 0000 (first 3, asterisks, last 4). Returned directly to the front-end for display; no further processing needed.

last_four (CHAR(4), indexed): Plaintext last four digits. A B-tree index enables exact-match queries (e.g., WHERE last_four = '0088') that avoid full-table scans. At million-user scale, response is milliseconds.

Exact Full-Number Lookup

When a query requires the complete phone number (e.g., login), use searchable encryption : generate a deterministic token from the plaintext with the same key, store it in a unique-indexed column phone_token, and match the token directly — no decryption required. This preserves both security and performance.

Summary

The three-field design (full ciphertext, masked display, indexed last-four plaintext) satisfies regulatory compliance, prevents full-number exposure in breaches, eliminates dual-table consistency issues, and delivers indexed query performance for last-four-digit filters at scale. It is a proven pattern in production internet systems.