How Apache ShardingSphere Enables Transparent Data Masking with Encrypt‑JDBC

Background and Problem

Data masking is required for sensitive columns such as identity numbers, phone numbers, and card numbers. Traditional in‑house encryption solutions often need code changes and risk heavy refactoring when masking rules evolve. A solution that can apply masking transparently, without modifying business SQL or application logic, is needed.

ShardingSphere Encrypt‑JDBC

Apache ShardingSphere provides a transparent data‑masking capability through its Encrypt‑JDBC component. Encrypt‑JDBC sits between the application and the database, implements all standard JDBC interfaces, intercepts SQL statements, rewrites them according to user‑defined masking rules, stores ciphertext (and optionally plaintext) in the underlying tables, and decrypts data on query. No changes to the application code are required.

Core Architecture

Encrypt‑JDBC maps three column concepts:

logicColumn – the virtual column name used in business SQL.

plainColumn – the physical column that stores plaintext (optional).

cipherColumn – the physical column that stores ciphertext.

The component rewrites SQL so that operations on the logicColumn are routed to the appropriate plainColumn or cipherColumn based on configuration.

Masking Rules Configuration

Masking rules consist of four parts: data source configuration, encryptor configuration, table‑column mapping, and query‑property configuration. A typical YAML configuration is shown below.

encryptRule:
  encryptors:
    aes_encryptor:
      type: aes
      props:
        aes.key.value: 123456abc
  tables:
    t_user:
      columns:
        pwd:
          plainColumn: pwd
          cipherColumn: pwd_cipher
          encryptor: aes_encryptor
          props:
            query.with.cipher.column: false

Configuration Elements

encryptors – define the encryption algorithm (e.g., AES, MD5) and its parameters.

tables – list tables that require masking.

columns – for each column, specify plainColumn, cipherColumn, the encryptor to use, and optional properties such as query.with.cipher.column which controls whether queries read from the ciphertext column.

Use‑Case Scenarios

New Business Launch – Define logicColumn and cipherColumn (plainColumn optional). Encrypt‑JDBC automatically encrypts new writes and decrypts reads.

Existing Business Refactor – Three challenges: (1) encrypt historical plaintext data, (2) encrypt new data without changing SQL, (3) migrate from plaintext to ciphertext transparently.

Migration Process

Pre‑migration

Add a cipherColumn to the target tables.

Configure Encrypt‑JDBC with logicColumn and cipherColumn.

Set query.with.cipher.column=false so existing queries continue to read the plaintext column.

During migration

Encrypt historical data manually (e.g., via a batch job that reads plainColumn, encrypts the value with the configured encryptor, and writes to cipherColumn).

Switch query.with.cipher.column=true and restart the application.

From this point, reads retrieve ciphertext, decrypt it, and return plaintext to the client; writes store both plaintext and ciphertext.

Post‑migration

Validate that all queries return correct results.

Drop the plainColumn if plaintext storage is no longer required.

Keep query.with.cipher.column=true permanently for a zero‑code‑change, fully encrypted deployment.

Advantages

Fully automated and transparent masking – developers do not need to write encryption logic.

Supports built‑in encryptors (AES, MD5) and custom implementations via the ShardingSphere SPI.

Optionally stores both plaintext and ciphertext, enabling safe rollback during migration.

Works with any SQL‑compliant database (MySQL, Oracle, PostgreSQL, SQL Server, etc.).

Limitations

Migration of existing plaintext data must be performed by the user (e.g., batch encryption scripts).

Masked columns cannot be used in range queries, ORDER BY, BETWEEN, LIKE, or aggregate functions such as AVG and SUM.

Applicable Environments

Java‑based projects that use relational databases and require column‑level encryption without altering existing SQL. Both the JDBC driver (Encrypt‑JDBC) and the binary‑protocol proxy (Encrypt‑Proxy) are supported.

References

Official site: https://shardingsphere.apache.org

GitHub repository: https://github.com/apache/incubator-shardingsphere