Spring Boot Field-Level Data Masking with ShardingSphere-Encrypt: Production Guide

1. Why the JDBC Proxy Layer Was Chosen

The author evaluated four approaches for data masking and rejected three:

Gateway/API layer response interception : Parses full JSON, high performance cost; fails on cross-table queries, pagination, streaming, and when downstream needs raw values for computation.

MyBatis interceptor or JPA Converter : High intrusion; breaks with pagination plugins, dynamic SQL, complex joins due to wrong interception timing; maintenance cost rises with framework upgrades.

Database views/triggers : DBAs resist granting permissions; compute pressure on DB, scaling difficult, audit logs polluted by encryption logic.

JDBC driver interception (ShardingSphere-Encrypt) : Runs inside Spring Boot app; SQL parsing, routing, rewrite, result merging fully automatic; zero business code changes; transparent to MyBatis, JPA, JdbcTemplate; dynamic policies follow session context. This balances dev efficiency, compliance, and ops cost.

2. ShardingSphere-Encrypt Internal Workflow

Core engine is a transparent SQL proxy. Algorithms implement standard SPI org.apache.shardingsphere.encrypt.api.spi.EncryptAlgorithm, supporting symmetric/asymmetric encryption, assisted query algorithms, and MaskAlgorithm for display-only masking (no ciphertext stored). Custom algorithms (e.g., SM4, AES-GCM) plug in via SPI.

Execution flow:

Incoming SQL parsed into AST.

Engine detects columns with masking rules and rewrites SQL. Example: SELECT phone FROM t_user WHERE phone = ? becomes a query against assistedQueryColumn (hash or ciphertext column) when configured.

After execution, Merge phase decides output per session security context (user role, source IP): plaintext, ciphertext, or masked (e.g., 138****5678).

All occurs between DataSource and Connection; business layer unaware.

3. Spring Boot Integration & Configuration

Dependency: shardingsphere-jdbc 5.4.x (example 5.4.1). Spring Boot delegates DataSource creation to ShardingSphere.

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc</artifactId>
  <version>5.4.1</version>
</dependency>

Application YAML points to encrypt config:

spring:
  datasource:
    url: jdbc:shardingsphere:classpath:encrypt-config.yaml
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver

Core rules in encrypt-config.yaml:

dataSources:
  primary_ds:
    url: jdbc:mysql://127.0.0.1:3306/biz_db
    username: root
    password: xxx
    connectionTimeoutMilliseconds: 30000

rules:
  - !ENCRYPT
    tables:
      t_user:
        columns:
          phone:
            cipherColumn: phone_cipher
            plainColumn: phone_plain
            assistedQueryColumn: phone_hash
            encryptorName: aes_encryptor
            maskGeneratorName: phone_mask
          id_card:
            cipherColumn: id_card_cipher
            encryptorName: sm4_encryptor
    encryptors:
      aes_encryptor:
        type: AES
        props:
          aes-key-value: "yourBase64Key"
      sm4_encryptor:
        type: SM4
    maskGenerators:
      phone_mask:
        type: MASK
        props:
          mask-before: 3
          mask-after: 4
          replace-char: "*"

plainColumn

retained for smooth migration; daily queries use ciphertext. assistedQueryColumn only for high-frequency lookup fields to avoid index bloat.

Custom algorithm: implement EncryptAlgorithm interface ( init, encrypt, decrypt, getType), register fully qualified class name in

META-INF/services/org.apache.shardingsphere.encrypt.api.spi.EncryptAlgorithm

. Multi-data-source: list under dataSources; ShardingSphere handles routing and pools. Monitoring: hook Spring Boot Actuator, track encrypt/decrypt latency distribution and SQL rewrite hit rate via Prometheus.

4. Common Production Pitfalls

Fuzzy Query Compatibility

Ciphertext LIKE useless. Official recommends assistedQueryColumn for equality via hash match. Prefix fuzzy matching problematic. Solutions: split phone into multiple assisted fields by segment, or offload search to ElasticSearch; MySQL stores only ciphertext and exact lookup. Assisted columns consume extra storage/index; avoid on low-frequency fields.

Composite Index Optimization

If composite index includes masked column, engine rewrites index key. Advice: don't put masked column as first index column; selectivity drops, optimizer may full-scan. Prefer covering indexes to avoid table lookups; each lookup adds decryption overhead.

Sharding + Encryption Combination

Encrypt and Sharding work together but order fixed: sharding routing first, then encryption rewrite. Sharding key must never be encrypted or routing breaks. Common pattern: shard by user_id, encrypt phone; ensure routing conditions never reference ciphertext columns.

Dynamic Policy Switching

Push config via Nacos/Apollo; trigger rule hot-reload through ShardingSphere Governance API. New connection pools load new rules immediately; old connections unaffected. Zero-downtime releases work if config validation strict. Avoid pushing large rule changes during traffic peaks to prevent config center jitter.

5. Performance vs Security Trade-offs

Algorithm choice: avoid asymmetric (RSA) for data encryption; too heavy, only for key exchange. Production uses AES-256-GCM or SM4-CBC. Benchmark on JDK 17 + Spring Boot 3.x: 100k batch insert, single encrypt/decrypt ≤0.05ms, overall QPS drop 3–5%, P99 latency +2–5ms. Acceptable for most business lines.

Caching risks: never store plaintext objects in Redis. Key generation: use hash of assisted column. If full user info cached, truncate/mask sensitive fields before serialization. TTL ≤5 minutes; evict on permission change or role downgrade. Local cache (Caffeine) eviction must sync with masking policy refresh frequency.

6. Historical Data Migration & Production Safeguards

Bulk UPDATE downtime risky. Adopt three-phase:

Application dual-write: plaintext and ciphertext columns both populated (ShardingSphere plainColumn auto-retains).

Async backfill: Flink or DataX job gradually converts historical plaintext to ciphertext with checksum verification.

Gradual cutover: via config center force reads to ciphertext column; observe business metrics and slow queries for a week; then async drop plaintext column.

Rule conflicts: multi-table same-name columns must bind precisely by schema.table.column, no global wildcards. JOIN with masked columns on both sides: ShardingSphere rewrites each; use explicit column aliases in SQL to prevent resultMap misalignment. MyBatis resultMap unchanged; avoid manual type conversion or field filtering there — let proxy layer handle, else double-masking or plaintext leak.

Audit logging: SQL logs must filter sensitive parameter values. Separate audit table recording operator role, query target, timestamp, masking applied. Application logs: configure Logback/Log4j2 regex filter to auto-mask patterns like phone=138xxxx. Security logs themselves must be masked.

7. Conclusion

Data masking is now an architectural baseline, not a nice-to-have. Pushing it to JDBC proxy layer eliminates glue code, but upfront config design, historical migration, and rule governance require investment. Privacy-left-shift is the trend; don't wait for incidents. Current best combo: JDBC transparent proxy + config-center hot-reload + gradual migration mechanism . As privacy computing and federated query mature, cross-domain analysis without decryption may arrive, but today solidifying the foundation — balancing compliance red lines and system performance — beats any fancy architecture. Embed masking standards in design docs from project start; security is designed in, not patched later.