Data Masking with Sharding-JDBC: Concepts, Configuration, and Custom Encryptors

Important Concepts

Sharding-JDBC provides a transparent data‑masking layer that encrypts sensitive fields without requiring developers to modify their SQL. The main concepts include data source configuration, encryptor configuration, masking table configuration, and query attribute configuration.

1. Data Source Configuration

Defines the Datasource used by Sharding-JDBC. The same configuration was shown in the previous article.

2. Encryptor Configuration

Sharding-JDBC ships with two built‑in encryptors:

MD5Encryptor : an irreversible MD5 hash, typically used for passwords.

AESEncryptor : a reversible AES algorithm, suitable for fields such as ID cards or phone numbers that need to be displayed in plain text after decryption.

Custom encryptors can also be added.

3. Masking Table Configuration

Specifies which column stores the ciphertext ( cipherColumn ), which column stores the plaintext ( plainColumn ), and which logical column ( logicColumn ) developers use in their SQL.

logicColumn : the column name used in SQL (e.g., pwd instead of the real column cipher_pwd ).

cipherColumn : column that stores encrypted data.

plainColumn : column that stores plaintext (usually omitted for security).

4. Query Attribute Configuration

Controls whether queries return encrypted or decrypted data. By default, Sharding‑JDBC returns decrypted data when the logical column appears in the SELECT list.

Data Masking Practice

The article demonstrates data masking using a single‑database setup.

1. Create Table

CREATE TABLE `t_user` (
  `user_id` bigint(20) NOT NULL COMMENT 'User unique ID',
  `fullname` varchar(50) DEFAULT NULL COMMENT 'Name',
  `user_type` varchar(255) DEFAULT NULL COMMENT 'Type',
  `cipher_pwd` varchar(255) DEFAULT NULL COMMENT 'Password',
  `mobile` varchar(100) DEFAULT NULL COMMENT 'Phone number',
  `mobile_data` varchar(100) DEFAULT NULL COMMENT 'Phone number (assist column)',
  `id_card` varchar(60) DEFAULT NULL COMMENT 'ID card',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. Data Source Configuration

spring:
  shardingsphere:
    datasource:
      names: ds
      ds:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=utf-8
        username: root
        password: 123456

3. Encryptor Declaration

spring:
  encrypt:
    encryptors:
      encryptor_md5:
        type: MD5
      encryptor_aes:
        type: aes
        props:
          aes.key.value: myjszl

This declares two encryptors: MD5 (irreversible) and AES (reversible with the provided key).

4. Masking Configuration for Specific Columns

spring:
  shardingsphere:
    encrypt:
      tables:
        t_user:
          columns:
            password:
              cipherColumn: cipher_pwd
              encryptor: encryptor_md5
            id_card:
              cipherColumn: id_card
              encryptor: encryptor_aes
            mobile:
              cipherColumn: mobile
              encryptor: encryptor_aes

Passwords use MD5, while ID cards and mobile numbers use AES.

5. Query Example

SELECT password AS cipherPwd, fullname, user_type, id_card AS id_card FROM t_user WHERE user_id=?

The query returns the decrypted password (MD5 remains ciphertext) and decrypted ID card and mobile number.

Limitations

Masked columns cannot be used in comparison operators (e.g., > , < , ORDER BY , BETWEEN , LIKE ).

Masked columns cannot be used in aggregate functions such as AVG or SUM .

Principle

When inserting or updating data, Sharding‑JDBC rewrites the SQL, replaces logical columns with real columns, and calls encrypt(Object plaintext) to store ciphertext. During SELECT, it calls decrypt(String ciphertext) to return the original value.

Encryption Strategies

1. Encryptor

Provides encrypt() and decrypt() methods. Used for INSERT, UPDATE, DELETE (encrypt) and SELECT (decrypt).

2. QueryAssistedEncryptor

In addition to encrypt() and decrypt() , it offers queryAssistedEncrypt() which generates a deterministic auxiliary column for equality queries while still using a random factor for the main ciphertext, improving security without breaking queryability.

Custom Encryptor Implementation

SHA256Encryptor (Irreversible)

/**
 * @author 不才陈某
 * Custom SHA256 encryptor (irreversible)
 */
@Data
public class Sha256HexEncryptor implements Encryptor {
    public static final String ALGORITHM_NAME = "SHA256";
    private Properties properties = new Properties();
    @Override
    public void init() {}
    @Override
    public String encrypt(final Object plaintext) {
        if (plaintext == null) return null;
        return DigestUtils.sha256Hex(String.valueOf(plaintext));
    }
    @Override
    public String decrypt(final String ciphertext) {
        return ciphertext; // SHA256 cannot be reversed
    }
    @Override
    public String getType() {
        return ALGORITHM_NAME;
    }
}

Register the class in resource/META-INF/services/org.apache.shardingsphere.encrypt.strategy.spi.Encryptor with the fully qualified class name.

Using the Custom SHA256 Encryptor

spring:
  shardingsphere:
    encrypt:
      encryptors:
        encryptor_sha256:
          type: SHA256

spring:
  shardingsphere:
    encrypt:
      tables:
        t_user:
          columns:
            password:
              cipherColumn: cipher_pwd
              encryptor: encryptor_sha256

Custom QueryAssistedEncryptor Implementation

Base64AssistedEncryptor (Reversible with Assist Column)

@Data
public class Base64AssistedEncryptor implements QueryAssistedEncryptor {
    public static final String ALGORITHM_NAME = "Base64_Assisted";
    private Properties properties = new Properties();
    @Override
    public String queryAssistedEncrypt(String plaintext) {
        if (plaintext == null) return null;
        return Base64.encode(plaintext);
    }
    @Override
    public void init() {}
    @Override
    public String encrypt(final Object plaintext) {
        if (plaintext == null) return null;
        String randomFactor = String.valueOf(new Date().getTime());
        return Base64.encode(plaintext + "_" + randomFactor);
    }
    @SneakyThrows
    @Override
    public Object decrypt(final String ciphertext) {
        if (ciphertext == null) return null;
        return new String(Base64.decode(ciphertext), "UTF-8").split("_")[0];
    }
    @Override
    public String getType() {
        return ALGORITHM_NAME;
    }
}

Register the class similarly in the SPI file.

Configuration for the Assisted Encryptor

spring:
  shardingsphere:
    encrypt:
      encryptors:
        encryptor_base64_assisted:
          type: Base64_Assisted

spring:
  shardingsphere:
    encrypt:
      tables:
        t_user:
          columns:
            mobile:
              cipherColumn: mobile
              assistedQueryColumn: mobile_data
              encryptor: encryptor_base64_assisted

The assistedQueryColumn stores the deterministic Base64 value, enabling equality queries while the main cipherColumn contains a random‑factor encrypted value.

Conclusion

The article demonstrates how to use Sharding‑JDBC for data masking, how to configure built‑in encryptors, and how to extend the framework with custom encryptors and query‑assisted encryptors to meet various security requirements.