preface

This chapter is based on the official document of shardingSphere data encryption implementation principle, according to the migration before, migration, migration after three stages, observe the changes in SQL execution, in-depth source analysis of the principle.

It is recommended to read the following article first:

Data desensitization implementation principle
ShardingJDBC source code to read (six) rewrite

First, preparation

Fragment Policy Configuration

Database based on user_id%2 and table based on user_id%4/2. User_id =1 is assigned to ds_1.my_user_0; User_id =2 is allocated to ds_0.my_user_0; User_id =3 is assigned to ds_1.my_user_1.

public DataSource getDataSource(a) throws SQLException {
    ShardingRuleConfiguration shardingConfig = new ShardingRuleConfiguration();
    / / table rules
    TableRuleConfiguration userTable = new TableRuleConfiguration("my_user"."ds_${0.. 1}.my_user_${[0, 1]}");
    // Library split policy
    ShardingStrategyConfiguration shardingDb = new InlineShardingStrategyConfiguration("id"."ds_${id%2}");
    userTable.setDatabaseShardingStrategyConfig(shardingDb);
    // Table split policy
    ShardingStrategyConfiguration shardingTable = new StandardShardingStrategyConfiguration("id", (PreciseShardingAlgorithm<Integer>) (availableTargetNames, shardingValue) -> {
        Integer l = shardingValue.getValue() % 4 / 2;
        return availableTargetNames.stream().filter(t -> t.endsWith(String.valueOf(l))).findFirst().orElse(null);
    });
    userTable.setTableShardingStrategyConfig(shardingTable);
    shardingConfig.setTableRuleConfigs(Collections.singletonList(userTable));
    // Encryption policy
    EncryptRuleConfiguration encryptRuleConfig = createEncryptRuleConfig();
    shardingConfig.setEncryptRuleConfig(encryptRuleConfig);
    // Global configuration
    Properties properties = new Properties();
    properties.setProperty(ConfigurationPropertyKey.SQL_SHOW.getKey(), "true");
    // query.with.cipher.column
    properties.setProperty(ConfigurationPropertyKey.QUERY_WITH_CIPHER_COLUMN.getKey(), "false");
    return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingConfig, properties);
}
Copy the code

DDL

private static void addCipherColumn(Connection connection) throws SQLException {
  String sql = "alter table my_user add column pwd_encrypt varchar(255) not null";
  Statement statement = connection.createStatement();
  statement.execute(sql);
  statement.close();
}
Copy the code

The encryption configuration

private EncryptRuleConfiguration createEncryptRuleConfig(a) {
  EncryptRuleConfiguration configuration = new EncryptRuleConfiguration();
  // Encrypt and decrypt
  Properties properties = new Properties();
  properties.setProperty("aes.key.value"."123456");
  EncryptorRuleConfiguration aesRuleConfiguration = new EncryptorRuleConfiguration("aes", properties);
  configuration.getEncryptors().put("user_pwd_encryptor", aesRuleConfiguration);
  // table - field - encryption rules
  EncryptColumnRuleConfiguration pwdEncryptConfig = new EncryptColumnRuleConfiguration("pwd"."pwd_encrypt".""."user_pwd_encryptor");
  Map<String, EncryptColumnRuleConfiguration> columns = new HashMap<>();
  columns.put("pwd", pwdEncryptConfig);// Logical field - encryption configuration
  configuration.getTables().put("my_user".new EncryptTableRuleConfiguration(columns));
  return configuration;
}
Copy the code

Before migration: Query.with.cipher. column=false

Old user

The query

Logic SQL: select * from my_user where id = ? Actual SQL: ds_1 ::: select id, pwd AS pwd from my_user_1 where id = ? ::: [3] Logic SQL: select id, pwd from my_user where id = ? Actual SQL: ds_1 ::: select id, pwd AS pwd from my_user_1 where id = ? ::: [3] Logic SQL: select * from my_user where pwd = ? Actual SQL: ds_0 ::: select id, pwd AS pwd from my_user_0 where pwd = ? ::: [1608881254186] Actual SQL: ds_0 ::: select id, pwd AS pwd from my_user_1 where pwd = ? ::: [1608881254186] Actual SQL: ds_1 ::: select id, pwd AS pwd from my_user_0 where pwd = ? ::: [1608881254186] Actual SQL: ds_1 ::: select id, pwd AS pwd from my_user_1 where pwd = ? : : : [1608881254186]Copy the code

Conclusion:

* will be rewritten as id, PWD as PWD; Id, PWD will be rewritten As PWD As PWD.
Where query conditions will not be overridden.

Why was the query field overwritten?

Review SQLRewriteEntry to create AN SQL rewrite context.

When enabling the encryption rule configuration, SQL overwrites SQLRewriteEntry#createSQLRewriteContext to do two things, One is to perform EncryptSQLRewriteContextDecorator put encryption related Token generator (SQLTokenGenerator) in rewriting context, another is generated SQLToken, Whereas SQLToken, if implemented by Substitutable, would result in partial string substitution for SQL (see AbstractSQLBuilder#toSQL)

public SQLRewriteContext createSQLRewriteContext(final String sql, final List<Object> parameters, final SQLStatementContext sqlStatementContext, final RouteContext routeContext) {
  SQLRewriteContext result = new SQLRewriteContext(schemaMetaData, sqlStatementContext, sql, parameters);
  / / perform all SQLRewriteContextDecorator, rewrite the Param, create SQLTokenGenerators
  decorate(decorators, result, routeContext);
  / / generated SQLToken
  result.generateSQLTokens();
  return result;
}
Copy the code

EncryptSQLRewriteContextDecorator will put EncryptProjectionTokenGenerator in generator list, will eventually perform EncryptProjectionTokenGenerator.

private Collection<SubstitutableColumnNameToken> generateSQLTokens(final ProjectionsSegment segment, final String tableName, 
                                                                       final SelectStatementContext selectStatementContext, final EncryptTable encryptTable) {
  Collection<SubstitutableColumnNameToken> result = new LinkedList<>();
  for (ProjectionSegment each : segment.getProjections()) {
      if (each instanceof ColumnProjectionSegment) { // id, pwd
          if(encryptTable.getLogicColumns().contains(((ColumnProjectionSegment) each).getColumn().getIdentifier().getValue())) { result.add(generateSQLToken((ColumnProjectionSegment) each, tableName)); }}if (each instanceof ShorthandProjectionSegment) { / / *result.add(generateSQLToken((ShorthandProjectionSegment) each, selectStatementContext.getProjectionsContext(), tableName, encryptTable)); }}return result;
}
Copy the code

For ShorthandProjectionSegment, such as select * will cycle all actual field (from TableMetaData, pay attention to the cipher text fields will not exist in the list, EncryptTableMetaDataDecorator when adornment TableMetaData, ruled out the cipher text field), common fields will directly join the result set, encryption rule configuration logic field may be rewritten.

private SubstitutableColumnNameToken generateSQLToken(final ShorthandProjectionSegment segment, 
                                                          final ProjectionsContext projectionsContext, final String tableName, final EncryptTable encryptTable) {
    ShorthandProjection shorthandProjection = getShorthandProjection(segment, projectionsContext);
    List<String> shorthandExtensionProjections = new LinkedList<>();
    // Get all the actual columns
    for (ColumnProjection each : shorthandProjection.getActualColumns()) {
        // If the logical fields match the actual SQL fields, they may be overwritten
        if (encryptTable.getLogicColumns().contains(each.getName())) {
            shorthandExtensionProjections.add(new ColumnProjection(each.getOwner(), getEncryptColumnName(tableName, each.getName()), each.getName()).getExpressionWithAlias());
        } else {
        // Add other common fields directlyshorthandExtensionProjections.add(each.getExpression()); }}return new SubstitutableColumnNameToken(segment.getStartIndex(), segment.getStopIndex(), Joiner.on(",").join(shorthandExtensionProjections));
}
Copy the code

The getEncryptColumnName method returns the ciphertext field if query.with.cipher.column is true, otherwise it returns the plaintext field.

private String getEncryptColumnName(final String tableName, final String logicEncryptColumnName) {
    // Locate the original column according to the logicEncryptColumnName logical column name
    Optional<String> plainColumn = getEncryptRule().findPlainColumn(tableName, logicEncryptColumnName);
    // If the original field exists and query.with.cipher.column=false, the original field is returned
    // If the original field does not exist or query.with.cipher.column=true, the ciphertext field is returned
    returnplainColumn.isPresent() && ! queryWithCipherColumn ? plainColumn.get() : getEncryptRule().getCipherColumn(tableName, logicEncryptColumnName); }Copy the code

ColumnProjectionSegment, such as ID and PWD. The PWD matches the logical field of the encryption rule and enters the generateSQLToken method. The getEncryptColumnName method is also used to generate the original text or ciphertext. If there is no custom AS alias in SQL, the logical field name is spelled.

private SubstitutableColumnNameToken generateSQLToken(final ColumnProjectionSegment segment, final String tableName) {
    String encryptColumnName = getEncryptColumnName(tableName, segment.getColumn().getIdentifier().getValue());
    if(! segment.getAlias().isPresent()) {// Splicing the AS logical column names to ensure that client logic is not affected
        encryptColumnName += " AS " + segment.getColumn().getIdentifier().getValue();
    }
    return segment.getColumn().getOwner().isPresent() 
    	? new SubstitutableColumnNameToken(segment.getColumn().getOwner().get().getStopIndex() + 2, segment.getStopIndex(), encryptColumnName)
        : new SubstitutableColumnNameToken(segment.getStartIndex(), segment.getStopIndex(), encryptColumnName);
}
Copy the code

update

Logic SQL: update my_user set pwd = ? where id = ? Actual SQL: ds_1 ::: update my_user_1 set pwd_encrypt = ? , pwd = ? where id = ? ::: [UVZAHLRN/LfAgbNp2BP7KA==, 1608881254186, 3]Copy the code

Why does updating a field add an encryption field?

1. Added SQLToken

Locate SQLTokenGenerators#generateSQLTokens to see which SQLTokenGenerator creates a new SQLToken. Locate the EncryptAssignmentTokenGenerator soon.

public final class EncryptAssignmentTokenGenerator extends BaseEncryptSQLTokenGenerator implements CollectionSQLTokenGenerator {
    
  @Override
  public Collection<EncryptAssignmentToken> generateSQLTokens(final SQLStatementContext sqlStatementContext) {
      Collection<EncryptAssignmentToken> result = new LinkedList<>();
      String tableName = ((TableAvailable) sqlStatementContext).getAllTables().iterator().next().getTableName().getIdentifier().getValue();
      // Loop through param for each update, such as PWD
      for (AssignmentSegment each : getSetAssignmentSegment(sqlStatementContext.getSqlStatement()).getAssignments()) {
          / / find EncryptColumnRuleConfiguration according to the logical name
          / / based on the encryptor find EncryptorRuleConfiguration EncryptColumnRuleConfiguration
          if (getEncryptRule().findEncryptor(tableName, each.getColumn().getIdentifier().getValue()).isPresent()) {
              // Create SQLToken and put resultgenerateSQLToken(tableName, each).ifPresent(result::add); }}returnresult; }}Copy the code

EncryptAssignmentToken update my_user set PWD =? Param pattern with placeholder; Update my_user set PWD = ‘XXX’ Literal mode

private Optional<EncryptAssignmentToken> generateSQLToken(final String tableName, final AssignmentSegment assignmentSegment) {
  // param
  if (assignmentSegment.getValue() instanceof ParameterMarkerExpressionSegment) {
      return Optional.of(generateParameterSQLToken(tableName, assignmentSegment));
  }
  // literal
  if (assignmentSegment.getValue() instanceof LiteralExpressionSegment) {
      return Optional.of(generateLiteralSQLToken(tableName, assignmentSegment));
  }
  return Optional.empty();
}
Copy the code

Take a look at the Param schema.

private EncryptAssignmentToken generateParameterSQLToken(final String tableName, final AssignmentSegment assignmentSegment) {
  EncryptParameterAssignmentToken result = new EncryptParameterAssignmentToken(assignmentSegment.getColumn().getStartIndex(), assignmentSegment.getStopIndex());
  String columnName = assignmentSegment.getColumn().getIdentifier().getValue();
  // Add the ciphertext column
  addCipherColumn(tableName, columnName, result);
  // Add the secondary query column
  addAssistedQueryColumn(tableName, columnName, result);
  // Add the original column
  addPlainColumn(tableName, columnName, result);
  return result;
}
Copy the code

EncryptParameterAssignmentToken is eventually generated SQLToken, focus on the toString method, as after AbstractSQLBuilder# toSQL splicing SQL String.

public final class EncryptParameterAssignmentToken extends EncryptAssignmentToken {
    private final Collection<String> columnNames = new LinkedList<>();
    public EncryptParameterAssignmentToken(final int startIndex, final int stopIndex) {
        super(startIndex, stopIndex);
    }
    public void addColumnName(final String columnName) {
        columnNames.add(columnName);
    }
    // Return pwd_encrypt =? , pwd = ?
    @Override
    public String toString(a) {
        Collection<String> items = Collections2.transform(columnNames, input -> String.format("%s = ?", input));
        return Joiner.on(",").join(items); }}Copy the code

2. Added Param

SQLRewriteEntry# createSQLRewriteContext, performed SQLRewriteContextDecorator rewrite param first, and then execute SQLTokenGenerators create SQLToken.

public SQLRewriteContext createSQLRewriteContext(final String sql, final List<Object> parameters, final SQLStatementContext sqlStatementContext, final RouteContext routeContext) {
  SQLRewriteContext result = new SQLRewriteContext(schemaMetaData, sqlStatementContext, sql, parameters);
  / / perform all SQLRewriteContextDecorator, rewrite the Param, create SQLTokenGenerators
  decorate(decorators, result, routeContext);
  / / generated SQLToken
  result.generateSQLTokens();
  return result;
}
Copy the code

Performs all ParameterRewriter EncryptSQLRewriteContextDecorator.

public final class EncryptSQLRewriteContextDecorator implements SQLRewriteContextDecorator<EncryptRule> {
    
@Override
public void decorate(final EncryptRule encryptRule, final ConfigurationProperties properties, final SQLRewriteContext sqlRewriteContext) {
    boolean isQueryWithCipherColumn = properties.<Boolean>getValue(ConfigurationPropertyKey.QUERY_WITH_CIPHER_COLUMN);
    // Construct the ParameterRewriter list
    for (ParameterRewriter each : new EncryptParameterRewriterBuilder(encryptRule, isQueryWithCipherColumn).getParameterRewriters(sqlRewriteContext.getSchemaMetaData())) {
        if(! sqlRewriteContext.getParameters().isEmpty() && each.isNeedRewrite(sqlRewriteContext.getSqlStatementContext())) {/ / ParameterRewriter execution
            each.rewrite(sqlRewriteContext.getParameterBuilder(), sqlRewriteContext.getSqlStatementContext(), sqlRewriteContext.getParameters());
        }
    }
    sqlRewriteContext.addSQLTokenGenerators(new EncryptTokenGenerateBuilder(encryptRule, isQueryWithCipherColumn).getSQLTokenGenerators());
}
Copy the code

The final location to EncryptAssignmentParameterRewriter.

public final class EncryptAssignmentParameterRewriter extends EncryptParameterRewriter<SQLStatementContext> {
    
  @Override
  public void rewrite(final ParameterBuilder parameterBuilder, final SQLStatementContext sqlStatementContext, final List<Object> parameters) {
      String tableName = ((TableAvailable) sqlStatementContext).getAllTables().iterator().next().getTableName().getIdentifier().getValue();
      for (AssignmentSegment each : getSetAssignmentSegment(sqlStatementContext.getSqlStatement()).getAssignments()) {
          // Is the parameter mode, and this is a logical field in the encryption rule configuration
          if (each.getValue() instanceof ParameterMarkerExpressionSegment && getEncryptRule().findEncryptor(tableName, each.getColumn().getIdentifier().getValue()).isPresent()) {
              StandardParameterBuilder standardParameterBuilder = parameterBuilder instanceof StandardParameterBuilder
                      ? (StandardParameterBuilder) parameterBuilder : ((GroupedParameterBuilder) parameterBuilder).getParameterBuilders().get(0);
              // Execute the parameters overrideencryptParameters(standardParameterBuilder, tableName, each, parameters); }}}}Copy the code

The encryptParameters method overrides the parameter list in StandardParameterBuilder.

private void encryptParameters(final StandardParameterBuilder parameterBuilder, final String tableName, final AssignmentSegment assignmentSegment, final List<Object> parameters) {
  String columnName = assignmentSegment.getColumn().getIdentifier().getValue();
  // The initial PWD value (plaintext) corresponds to the subscript
  int parameterMarkerIndex = ((ParameterMarkerExpressionSegment) assignmentSegment.getValue()).getParameterMarkerIndex();
  // PWD initial value (plain text)
  Object originalValue = parameters.get(parameterMarkerIndex);
  // Find the Encryptor and execute the encryption to get the encrypted value
  Object cipherValue = getEncryptRule().getEncryptValues(tableName, columnName, Collections.singletonList(originalValue)).iterator().next();
  // Replace the plaintext value with the encrypted value
  parameterBuilder.addReplacedParameters(parameterMarkerIndex, cipherValue);
  Collection<Object> addedParameters = new LinkedList<>();
  // Add the auxiliary query value
  if (getEncryptRule().findAssistedQueryColumn(tableName, columnName).isPresent()) {
      Object assistedQueryValue = getEncryptRule().getEncryptAssistedQueryValues(tableName, columnName, Collections.singletonList(originalValue)).iterator().next();
      addedParameters.add(assistedQueryValue);
  }
  // Add the original value
  if (getEncryptRule().findPlainColumn(tableName, columnName).isPresent()) {
      addedParameters.add(originalValue);
  }
  if(! addedParameters.isEmpty()) { parameterBuilder.addAddedParameters(parameterMarkerIndex +1, addedParameters); }}Copy the code

Replace the original value with the ciphertext value, then check whether the original field exists in the encryption rule, and then add the original value again. Instead of adding the ciphertext value directly? This involves deleting the original column after the fourth migration, because the original column cannot exist forever.

A new user

new

Logic SQL: insert into my_user (id, pwd) values (?, ?) 
Actual SQL: ds_0 ::: insert into my_user_1 (id, pwd_encrypt, pwd) values (?, ?, ?) ::: [2, TcZjXe0lAwrT19nIJ9+0Iw==, 1608881254477]
Copy the code

Why does an insert statement add an encryption field?

1. Added Param

EncryptInsertValueParameterRewriter perform encryption param rewrite rules the insert statement.

@Override
public void rewrite(final ParameterBuilder parameterBuilder, final InsertStatementContext insertStatementContext, final List<Object> parameters) {
    String tableName = insertStatementContext.getSqlStatement().getTable().getTableName().getIdentifier().getValue();
    // Field names in reverse order
    Iterator<String> descendingColumnNames = insertStatementContext.getDescendingColumnNames();
    while (descendingColumnNames.hasNext()) {
        String columnName = descendingColumnNames.next();
        getEncryptRule()
                Find the Encryptor / /
                .findEncryptor(tableName, columnName)
                // Rewrite Param to put ParameterBuilder.ifPresent(encryptor -> { encryptInsertValues((GroupedParameterBuilder) parameterBuilder, insertStatementContext, encryptor, tableName, columnName); }); }}Copy the code

Finally locate the encryptInsertValue method and place the override parameters into the StandardParameterBuilder.

private void encryptInsertValue(final Encryptor encryptor, final String tableName, final int columnIndex,
                                    final Object originalValue, final StandardParameterBuilder parameterBuilder, final String encryptLogicColumnName) {
    // FIXME: can process all part of insert value is ? or literal, can not process mix ? and literal
    // For example: values (? ,?) , (1, 1) can process
    // For example: values (?, 1), (?, 2) can not process
    // Add the encrypted plain text to parameterBuilder
    parameterBuilder.addReplacedParameters(columnIndex, encryptor.encrypt(originalValue));
    Collection<Object> addedParameters = new LinkedList<>();
    // Auxiliary query values are put into parameterBuilder
    if (encryptor instanceof QueryAssistedEncryptor) {
        Optional<String> assistedColumnName = getEncryptRule().findAssistedQueryColumn(tableName, encryptLogicColumnName);
        Preconditions.checkArgument(assistedColumnName.isPresent(), "Can not find assisted query Column Name");
        addedParameters.add(((QueryAssistedEncryptor) encryptor).queryAssistedEncrypt(originalValue.toString()));
    }
    // The plaintext values are put into parameterBuilder
    if (getEncryptRule().findPlainColumn(tableName, encryptLogicColumnName).isPresent()) {
        addedParameters.add(originalValue);
    }
    if(! addedParameters.isEmpty()) {if(! parameterBuilder.getAddedIndexAndParameters().containsKey(columnIndex +1)) {
            parameterBuilder.getAddedIndexAndParameters().put(columnIndex + 1.new LinkedList<>());
        }
        parameterBuilder.getAddedIndexAndParameters().get(columnIndex + 1).addAll(addedParameters); }}Copy the code

As you can see from the source code comments above, mixing literal and param modes into a single row is not supported when encryption rules are configured. For example, with this insertion, it’s a really big hole.

private static void insert(Connection connection, int id, String pwd) throws SQLException {
    String sql = "insert into my_user (id, pwd) values (9998, ?) ";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, String.valueOf(System.currentTimeMillis()));
    statement.execute();
    statement.close();
}
Copy the code

Problem is StandardParameterBuilder# getParameters this method executes, Java. Proceedings of the lang. IndexOutOfBoundsException anomalies.

Sqlrouterewriteengineer #rewrite (AbstractSQLBuilder#toSQL) Call the StandardParameterBuilder#getParameters method when you get the param list overridden.

@RequiredArgsConstructor
public final class StandardParameterBuilder implements ParameterBuilder {
    // Raw argument list
    private final List<Object> originalParameters;
    // index - The parameter to be added
    @Getter
    private final Map<Integer, Collection<Object>> addedIndexAndParameters = new TreeMap<>();
    // index - The parameter to be replaced
    private final Map<Integer, Object> replacedIndexAndParameters = new LinkedHashMap<>();
    // The index to remove
    private final List<Integer> removeIndexAndParameters = new ArrayList<>();
    // To be exposed to the outside
    public void addAddedParameters(final int index, final Collection<Object> parameters) {
        addedIndexAndParameters.put(index, parameters);
    }
    // To be exposed to the outside
    public void addReplacedParameters(final int index, final Object parameter) {
        replacedIndexAndParameters.put(index, parameter);
    }
    // To be exposed to the outside
    public void addRemovedParameters(final int index) {
        removeIndexAndParameters.add(index);
    }
    @Override
    public List<Object> getParameters(a) {
        List<Object> result = new LinkedList<>(originalParameters);
        // Replace the value of the subscript
        for (Entry<Integer, Object> entry : replacedIndexAndParameters.entrySet()) {
            // IndexOutOfBoundsException
            result.set(entry.getKey(), entry.getValue());
        }
        // Add the value of the subscript
        for (Entry<Integer, Collection<Object>> entry : ((TreeMap<Integer, Collection<Object>>) addedIndexAndParameters).descendingMap().entrySet()) {
            if (entry.getKey() > result.size()) {
                result.addAll(entry.getValue());
            } else{ result.addAll(entry.getKey(), entry.getValue()); }}// Remove the subscript value
        for (int index : removeIndexAndParameters) {
            result.remove(index);
        }
        returnresult; }}Copy the code

2. SQLToken is replaced

SQLToken EncryptInsertValuesTokenGenerator create encrypted insert. Nothing special, just the (? ,?) (instead? ,? ,?) .

 @Override
public InsertValuesToken generateSQLToken(final InsertStatementContext insertStatementContext) {
    Optional<SQLToken> insertValuesToken = findPreviousSQLToken(InsertValuesToken.class);
    if (insertValuesToken.isPresent()) {
        processPreviousSQLToken(insertStatementContext, (InsertValuesToken) insertValuesToken.get());
        return (InsertValuesToken) insertValuesToken.get();
    }
    return generateNewSQLToken(insertStatementContext);
}

// Core logic
private void encryptToken(final InsertValue insertValueToken, final String tableName, final InsertStatementContext insertStatementContext, final InsertValueContext insertValueContext) {
    Optional<SQLToken> useDefaultInsertColumnsToken = findPreviousSQLToken(UseDefaultInsertColumnsToken.class);
    Iterator<String> descendingColumnNames = insertStatementContext.getDescendingColumnNames();
    while (descendingColumnNames.hasNext()) {
        String columnName = descendingColumnNames.next();
        // Find the corresponding Encryptor to the logical column name
        Optional<Encryptor> encryptor = getEncryptRule().findEncryptor(tableName, columnName);
        if (encryptor.isPresent()) {
            int columnIndex = useDefaultInsertColumnsToken.map(sqlToken -> ((UseDefaultInsertColumnsToken) sqlToken).getColumns().indexOf(columnName))
                    .orElseGet(() -> insertStatementContext.getColumnNames().indexOf(columnName));
            / / clear
            Object originalValue = insertValueContext.getValue(columnIndex);
            // Add the original column
            addPlainColumn(insertValueToken, columnIndex, tableName, columnName, insertValueContext, originalValue);
            // Add secondary query columns
            addAssistedQueryColumn(insertValueToken, encryptor.get(), columnIndex, tableName, columnName, insertValueContext, originalValue);
            // Set the ciphertext columnsetCipherColumn(insertValueToken, encryptor.get(), columnIndex, insertValueContext.getValueExpressions().get(columnIndex), originalValue); }}}Copy the code

Historical data update

Sharding-sphere does not provide desensitization schemes with historical data. The pwd_ENCRYPT field is always empty and requires script initialization.

When the new data is inserted, it is encrypted into ciphertext data through Apache ShardingSphere and stored in the cipherColumn. Now you need to deal with the historical cleartext stock data. Since Apache ShardingSphere does not provide relevant data migration and washing tools, the service needs to encrypt the plaintext data in the PWD and store it in the PWd_cipher.

Query. With. Cipher. Column =true

Old user

The query

Logic SQL: select * from my_user where id = ? 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_1 where id = ? ::: [3] 

Logic SQL: select id, pwd from my_user where id = ? 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_1 where id = ? ::: [3] 

Logic SQL: select * from my_user where pwd = ? 
Actual SQL: ds_0 ::: select id, pwd_encrypt AS pwd from my_user_0 where pwd_encrypt = ? ::: [/sRcoTnWHoY1+PT9F6tpAg==] 
Actual SQL: ds_0 ::: select id, pwd_encrypt AS pwd from my_user_1 where pwd_encrypt = ? ::: [/sRcoTnWHoY1+PT9F6tpAg==] 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_0 where pwd_encrypt = ? ::: [/sRcoTnWHoY1+PT9F6tpAg==] 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_1 where pwd_encrypt = ? ::: [/sRcoTnWHoY1+PT9F6tpAg==] 
Copy the code

1. The query field is overwritten pwd_encrypt AS PWD

The reason and the front before the migration, see EncryptProjectionTokenGenerator# getEncryptColumnName, due to the query. With. Cipher. The column = true will return here pwd_encrypt cipher text field.

private String getEncryptColumnName(final String tableName, final String logicEncryptColumnName) {
  // Locate the original column according to the logicEncryptColumnName logical column name
  Optional<String> plainColumn = getEncryptRule().findPlainColumn(tableName, logicEncryptColumnName);
  // If the original field exists and query.with.cipher.column=false, the original field is returned
  // If the original field does not exist or query.with.cipher.column=true, the ciphertext field is returned
  returnplainColumn.isPresent() && ! queryWithCipherColumn ? plainColumn.get() : getEncryptRule().getCipherColumn(tableName, logicEncryptColumnName); }Copy the code

Where pwd_encrypt =?

First founded SQLToken EncryptPredicateColumnTokenGenerator.

private Collection<SubstitutableColumnNameToken> generateSQLTokens(final SQLStatementContext sqlStatementContext, final AndPredicate andPredicate) {
  Collection<SubstitutableColumnNameToken> result = new LinkedList<>();
  for (PredicateSegment each : andPredicate.getPredicates()) {
      Optional<EncryptTable> encryptTable = findEncryptTable(sqlStatementContext, each);
      if(! encryptTable.isPresent() || ! encryptTable.get().findEncryptor(each.getColumn().getIdentifier().getValue()).isPresent()) {continue;
      }
      int startIndex = each.getColumn().getOwner().isPresent() ? each.getColumn().getOwner().get().getStopIndex() + 2 : each.getColumn().getStartIndex();
      int stopIndex = each.getColumn().getStopIndex();
      // query.with.cipher.column=false
      if(! queryWithCipherColumn) { Optional<String> plainColumn = encryptTable.get().findPlainColumn(each.getColumn().getIdentifier().getValue());if (plainColumn.isPresent()) {
              result.add(new SubstitutableColumnNameToken(startIndex, stopIndex, plainColumn.get()));
              continue; }}// query.with.cipher.column=true
      // Determine whether the secondary query column matches the current PredicateSegment assertion. If it is a secondary query column, the secondary query column is returned
      Optional<String> assistedQueryColumn = encryptTable.get().findAssistedQueryColumn(each.getColumn().getIdentifier().getValue());
      SubstitutableColumnNameToken encryptColumnNameToken = assistedQueryColumn.map(columnName -> new SubstitutableColumnNameToken(startIndex, stopIndex, columnName))
              // Otherwise return the ciphertext column
              .orElseGet(() -> new SubstitutableColumnNameToken(startIndex, stopIndex, encryptTable.get().getCipherColumn(each.getColumn().getIdentifier().getValue())));
      result.add(encryptColumnNameToken);
  }
  return result;
}
Copy the code

Secondly EncryptPredicateParameterRewriter will rewrite the query parameter for the cipher text.

@Setter
public final class EncryptPredicateParameterRewriter extends EncryptParameterRewriter<SQLStatementContext> implements SchemaMetaDataAware.QueryWithCipherColumnAware {
  private SchemaMetaData schemaMetaData;
  private boolean queryWithCipherColumn;
  @Override
  public void rewrite(final ParameterBuilder parameterBuilder, final SQLStatementContext sqlStatementContext, final List<Object> parameters) {
      // Create an EncryptCondition, similar to ShardingCondition, that represents an = or in assertion for a field in a table
      List<EncryptCondition> encryptConditions = new EncryptConditionEngine(getEncryptRule(), schemaMetaData).createEncryptConditions(sqlStatementContext);
      if (encryptConditions.isEmpty()) {
          return;
      }
      for (EncryptCondition each : encryptConditions) {
          if (queryWithCipherColumn) {
              // Encrypt the raw query parameters
              List<Object> encryptedValues = getEncryptedValues(each, each.getValues(parameters));
              // parameterBuilder replaces query parametersencryptParameters(parameterBuilder, each.getPositionIndexMap(), encryptedValues); }}}}Copy the code

EncryptCondition #createEncryptCondition creates an EncryptCondition. and… Comparison.

private Optional<EncryptCondition> createEncryptCondition(final PredicateSegment predicateSegment, final String tableName) {
  // Compare operators such as <>! = =
  if (predicateSegment.getRightValue() instanceof PredicateCompareRightValue) {
      PredicateCompareRightValue compareRightValue = (PredicateCompareRightValue) predicateSegment.getRightValue();
      return isSupportedOperator(compareRightValue.getOperator()) ? createCompareEncryptCondition(tableName, predicateSegment, compareRightValue) : Optional.empty();
  }
  // in
  if (predicateSegment.getRightValue() instanceof PredicateInRightValue) {
      return createInEncryptCondition(tableName, predicateSegment, (PredicateInRightValue) predicateSegment.getRightValue());
  }
   // Between and is not supported
  if (predicateSegment.getRightValue() instanceof PredicateBetweenRightValue) {
      throw new ShardingSphereException("The SQL clause 'BETWEEN... AND... ' is unsupported in encrypt rule.");
  }
  return Optional.empty();
}
Copy the code

EncryptPredicateParameterRewriter# getEncryptedValues prefer using auxiliary query value as a condition of the query.

private List<Object> getEncryptedValues(final EncryptCondition encryptCondition, final List<Object> originalValues) {
  String tableName = encryptCondition.getTableName();
  String columnName = encryptCondition.getColumnName();
  return getEncryptRule().findAssistedQueryColumn(tableName, columnName).isPresent()
          // If the secondary query exists, use the secondary query value
          ? getEncryptRule().getEncryptAssistedQueryValues(tableName, columnName, originalValues)
          // Otherwise use encrypted values
          : getEncryptRule().getEncryptValues(tableName, columnName, originalValues);
}
Copy the code

EncryptPredicateParameterRewriter# encryptParameters, will rewrite the parameters to pass into StandardParameterBuilder addReplacedParameters method.

private void encryptParameters(final ParameterBuilder parameterBuilder, final Map<Integer, Integer> positionIndexes, final List<Object> encryptValues) {
    if(! positionIndexes.isEmpty()) {for(Entry<Integer, Integer> entry : positionIndexes.entrySet()) { ((StandardParameterBuilder) parameterBuilder).addReplacedParameters(entry.getValue(), encryptValues.get(entry.getKey())); }}}Copy the code

update

Logic SQL: update my_user set pwd = ? where id = ? Actual SQL: ds_1 ::: update my_user_1 set pwd_encrypt = ? , pwd = ? where id = ? ::: [b9ValhsVZfJ2Mu7GKmsHag==, 1609060586708, 3]Copy the code

And open the query with. Cipher. The column as before, create SQLToken EncryptAssignmentTokenGenerator, EncryptAssignmentParameterRewriter encryption parameter has been added, SQL has not changed.

A new user

new

Logic SQL: insert into my_user (id, pwd) values (?, ?) 
Actual SQL: ds_0 ::: insert into my_user_0 (id, pwd_encrypt, pwd) values (?, ?, ?) ::: [5140, NHj62xHlEngXKI9YV7twyg==, 1609061001092] 
Copy the code

And open the query with. Cipher. The column before, EncryptInsertValueParameterRewriter new encryption parameters, create SQLToken EncryptInsertValuesTokenGenerator.

4. After migration

The plaintext column was deleted

Pay attention to the first argument here EncryptColumnRuleConfiguration configuration, originally is the PWD represents the text of the actual field data table, this time.

private EncryptRuleConfiguration createEncryptRuleConfig2(a) {
  EncryptRuleConfiguration configuration = new EncryptRuleConfiguration();
  // Encrypt and decrypt
  Properties properties = new Properties();
  properties.setProperty("aes.key.value"."123456");
  EncryptorRuleConfiguration aesRuleConfiguration = new EncryptorRuleConfiguration("aes", properties);
  configuration.getEncryptors().put("user_pwd_encryptor", aesRuleConfiguration);
  The pwdEncryptConfig field PWD has been removed
  EncryptColumnRuleConfiguration pwdEncryptConfig = new EncryptColumnRuleConfiguration(null."pwd_encrypt".""."user_pwd_encryptor");
  Map<String, EncryptColumnRuleConfiguration> columns = new HashMap<>();
  columns.put("pwd", pwdEncryptConfig);// Logical field - encryption configuration
  configuration.getTables().put("my_user".new EncryptTableRuleConfiguration(columns));
  return configuration;
}
Copy the code

DDL deletes plaintext fields

private static void dropPlainPwdColumn(Connection connection) throws SQLException {
  String sql = "alter table my_user drop column pwd";
  Statement statement = connection.createStatement();
  statement.execute(sql);
  statement.close();
}
Copy the code

To add to check

Logic SQL: select * from my_user where id = ? 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_1 where id = ? ::: [3] 

Logic SQL: select id, pwd from my_user where id = ? 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_1 where id = ? ::: [3] 

Logic SQL: update my_user set pwd = ? where id = ? 
Actual SQL: ds_1 ::: update my_user_1 set pwd_encrypt = ? where id = ? ::: [4tzE6aM2SQXhj8sJcJrjmQ==, 3] 
Logic SQL: select * from my_user where pwd = ? 
Actual SQL: ds_0 ::: select id, pwd_encrypt AS pwd from my_user_0 where pwd_encrypt = ? ::: [4tzE6aM2SQXhj8sJcJrjmQ==] 
Actual SQL: ds_0 ::: select id, pwd_encrypt AS pwd from my_user_1 where pwd_encrypt = ? ::: [4tzE6aM2SQXhj8sJcJrjmQ==] 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_0 where pwd_encrypt = ? ::: [4tzE6aM2SQXhj8sJcJrjmQ==] 
Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_1 where pwd_encrypt = ? ::: [4tzE6aM2SQXhj8sJcJrjmQ==] 

Logic SQL: insert into my_user (id, pwd) values (?, ?) 
Actual SQL: ds_1 ::: insert into my_user_1 (id, pwd_encrypt) values (?, ?) ::: [9343, enAqjGiquCRS7lhheLgmnA==] 

Copy the code

There is no change for SELECT.

For insert, such as EncryptInsertValuesTokenGenerator# addPlainColumn, when creating SQLToken judgment field configuration does not exist, the original will not generate SQLToken text field.

private void addPlainColumn(final InsertValue insertValueToken, final int columnIndex,
                          final String tableName, final String columnName, final InsertValueContext insertValueContext, final Object originalValue) {
  if (getEncryptRule().findPlainColumn(tableName, columnName).isPresent()) {
      DerivedSimpleExpressionSegment derivedExpressionSegment = insertValueContext.getParameters().isEmpty()
              ? new DerivedLiteralExpressionSegment(originalValue) : new DerivedParameterMarkerExpressionSegment(getParameterIndexCount(insertValueToken));
      insertValueToken.getValues().add(columnIndex + 1, derivedExpressionSegment); }}Copy the code

Similarly, EncryptInsertValueParameterRewriter# encryptInsertValue rewrite the argument list, also because I couldn’t find the original field configuration, not the original parameters in the parameter list.

private void encryptInsertValue(final Encryptor encryptor, final String tableName, final int columnIndex,
                                final Object originalValue, final StandardParameterBuilder parameterBuilder, final String encryptLogicColumnName) {
    // Add the encrypted plain text to parameterBuilder
    parameterBuilder.addReplacedParameters(columnIndex, encryptor.encrypt(originalValue));
    Collection<Object> addedParameters = new LinkedList<>();
    // Auxiliary query values are put into parameterBuilder
    if (encryptor instanceof QueryAssistedEncryptor) {
        Optional<String> assistedColumnName = getEncryptRule().findAssistedQueryColumn(tableName, encryptLogicColumnName);
        Preconditions.checkArgument(assistedColumnName.isPresent(), "Can not find assisted query Column Name");
        addedParameters.add(((QueryAssistedEncryptor) encryptor).queryAssistedEncrypt(originalValue.toString()));
    }
    // The result set will not be added
    if (getEncryptRule().findPlainColumn(tableName, encryptLogicColumnName).isPresent()) {
        addedParameters.add(originalValue);
    }
    if(! addedParameters.isEmpty()) {if(! parameterBuilder.getAddedIndexAndParameters().containsKey(columnIndex +1)) {
            parameterBuilder.getAddedIndexAndParameters().put(columnIndex + 1.new LinkedList<>());
        }
        parameterBuilder.getAddedIndexAndParameters().get(columnIndex + 1).addAll(addedParameters); }}Copy the code

For the same is true for the update, see EncryptAssignmentTokenGenerator# addPlainColumn and EncryptAssignmentParameterRewriter# encryptParameters.

4. Auxiliary query column

The secondary query column has been mentioned above, why do you need this field? The main need is this:

Even if the same data, such as two users with the same password, the encrypted data stored in the database should be different. This concept is more conducive to the protection of user information, prevent the success of the collision library.

In response to this need, Org. Apache. Shardingsphere. Encrypt. Strategy. Spi. The Encryptor a subinterface org. Apache. Shardingsphere. Encrypt. Strategy. The spi. QueryAssist EdEncryptor provides a secondary query method that converts plaintext values into secondary query values to query secondary query fields. Just implement an Encryptor that is guaranteed to be reversible and the same original ciphertext is different.

public interface QueryAssistedEncryptor extends Encryptor {
    String queryAssistedEncrypt(String plaintext);
}
Copy the code

It provides three functions for implementation, namely encrypt(), decrypt(), and queryAssistedEncrypt(). In the ENCRYPT () phase, the user sets some variation seed, such as a timestamp. Encrypting the contents of the combination of original data and variable seeds can ensure that even if the original data is the same, the encrypted data is not the same because of the existence of variable seeds. In Decrypt (), the seed data is decrypted using the previously specified encryption algorithm.

Because queryAssistedEncrypt() and encrypt() produce different encrypted data for storage, decrypt() is reversible and queryAssistedEncrypt() is irreversible. When querying raw data, we automatically parse, rewrite, and route SQL, use secondary query columns to query WHERE conditions, and use decrypt() to decrypt the encrypted () data and return the raw data to the user. All of this is transparent to the user.

The following is an example implemented according to the official document. AES encryption method with time stamps random seeds ensures that database data is different. AES decryption removes random seeds and returns them to the user.

QueryAssistedEncryptor implements the following, remember to inject via SPI under meta-info /services:

public class CustomQueryAssistedEncryptor implements QueryAssistedEncryptor {
	// Delegate AES to perform encrypt and decrypt
    private final Encryptor aesEncryptor;
    // Delegate MD5 to queryAssistedEncrypt
    private final Encryptor digestEncryptor;
    private Properties properties;
	// Random seed length
    private final int seedLength = String.valueOf(System.currentTimeMillis()).length();

    public CustomQueryAssistedEncryptor(a) {
        this.aesEncryptor = new AESEncryptor();
        this.digestEncryptor = new MD5Encryptor();
    }

    @Override
    public String queryAssistedEncrypt(String plaintext) {
        String salt = this.getProperties().getProperty("md5.salt");
        return digestEncryptor.encrypt(plaintext + salt);
    }

    @Override
    public void init(a) {}@Override
    public String encrypt(Object plaintext) {
        return aesEncryptor.encrypt(plaintext + String.valueOf(System.currentTimeMillis()));
    }

    @Override
    public Object decrypt(String ciphertext) {
        String decrypt = (String) aesEncryptor.decrypt(ciphertext);
        return decrypt.substring(0, decrypt.length() - seedLength);
    }

    @Override
    public String getType(a) {
        return "CustomQueryAssistedEncryptor";
    }

    @Override
    public Properties getProperties(a) {
        return this.properties;
    }

    @Override
    public void setProperties(Properties properties) {
        this.properties = properties; aesEncryptor.setProperties(properties); digestEncryptor.setProperties(properties); }}Copy the code

The encryption configuration is as follows:

 private EncryptRuleConfiguration createEncryptRuleConfig(a) {
    EncryptRuleConfiguration configuration = new EncryptRuleConfiguration();
    // Encrypt and decrypt
    Properties properties = new Properties();
    properties.setProperty("aes.key.value"."123456");
    properties.setProperty("md5.salt"."abcdef");
    EncryptorRuleConfiguration aesRuleConfiguration = new EncryptorRuleConfiguration("CustomQueryAssistedEncryptor", properties);
    configuration.getEncryptors().put("user_pwd_encryptor", aesRuleConfiguration);
    // table - field - encryption rules
    EncryptColumnRuleConfiguration encryptConfig = new EncryptColumnRuleConfiguration(null."pwd_encrypt"."pwd_assist"."user_pwd_encryptor");
    Map<String, EncryptColumnRuleConfiguration> columns = new HashMap<>();
    columns.put("pwd", encryptConfig);// Logical field - encryption configuration
    configuration.getTables().put("my_user_assist".new EncryptTableRuleConfiguration(columns));
    return configuration;
}
Copy the code

Corresponding to the DDL:

create table my_user_assist (id bigint(20) not null primary key, pwd_encrypt varchar(255) not null, pwd_assist varchar(255) not null)
Copy the code

Increase, change, check and rewrite results:

Logic SQL: insert into my_user_assist (id, PWD) values (? ,?) Actual SQL: ds_1 ::: insert into my_user_assist_1 (id, pwd_encrypt, pwd_assist) values (? ,? ,?) ::: [3, 1d/2wa8LM9FlepSZPmy3EC9HI2/xGzx9sq0qEBVSvC4=, 6f3b8ded65bd7a4db11625ac84e579bb] Logic SQL: insert into my_user_assist (id, pwd) values (? ,?) Actual SQL: ds_0 ::: insert into my_user_assist_0 (id, pwd_encrypt, pwd_assist) values (? ,? ,?) ::: [4, NPdqE1U22vN2awqLfcQGVmq90U7dDOEve/JY/7PbzCQ=, 6f3b8ded65bd7a4db11625ac84e579bb] Logic SQL: select * from my_user_assist where id = ? Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_assist_1 where id = ? ::: [3] Logic SQL: select id, pwd from my_user_assist where id = ? Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_assist_1 where id = ? ::: [3] Logic SQL: update my_user_assist set pwd = ? where id = ? Actual SQL: ds_1 ::: update my_user_assist_1 set pwd_encrypt = ? , pwd_assist = ? where id = ? ::: [4KpzieXQFNUpjdBToqGmYt1os93mcmEDK2zLftdNxIE=, 6f3b8ded65bd7a4db11625ac84e579bb, 3] Logic SQL: select * from my_user_assist where pwd = ? Actual SQL: ds_0 ::: select id, pwd_encrypt AS pwd from my_user_assist_0 where pwd_assist = ? ::: [6f3b8ded65bd7a4db11625ac84e579bb] Actual SQL: ds_0 ::: select id, pwd_encrypt AS pwd from my_user_assist_1 where pwd_assist = ? ::: [6f3b8ded65bd7a4db11625ac84e579bb] Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_assist_0 where pwd_assist = ? ::: [6f3b8ded65bd7a4db11625ac84e579bb] Actual SQL: ds_1 ::: select id, pwd_encrypt AS pwd from my_user_assist_1 where pwd_assist = ? ::: [6f3b8ded65bd7a4db11625ac84e579bb]Copy the code

conclusion

The main logic for sharding-jdbc data desensitization points is focused on the SQL rewrite step, and on the first and second steps of the SQLRewriteEntry#createSQLRewriteContext methods.
- The first step, performed by the EncryptSQLRewriteContextDecorator ParameterRewriter rewrite the Parameter.
- The second step, founded by EncryptSQLRewriteContextDecorator SQLTokenGenerator, create SQLToken.
Query.with.cipher. column indicates whether ciphertext query is enabled. If yes, the SELECT field needs to be queried and decrypted, and the WHERE condition needs to be encrypted before query.
One pitfall to note is that whenever you configure an EncryptRuleConfiguration encryption configuration, ensure that inserts are either full of question mark placeholders or dead values. Insert into table (id, PWD, deleted) values (? ,? , 0), otherwise an error will be reported.
Auxiliary query columns. If the same plain text needs to generate a different ciphertext database, and you need to use the logical column to perform where conditional query, you need to implement the QueryAssistedEncryptor interface.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

ShardingJDBC source code reading (ten) data desensitization combat

preface

First, preparation

Fragment Policy Configuration

DDL

The encryption configuration

Before migration: Query.with.cipher. column=false

Old user

The query

update

A new user

new

Historical data update

Query. With. Cipher. Column =true

Old user

The query

update

A new user

new

4. After migration

The plaintext column was deleted

DDL deletes plaintext fields

To add to check

4. Auxiliary query column

conclusion

ShardingJDBC source code reading (ten) data desensitization combat

preface

First, preparation

Fragment Policy Configuration

DDL

The encryption configuration

Before migration: Query.with.cipher. column=false

Old user

The query

update

A new user

new

Historical data update

Query. With. Cipher. Column =true

Old user

The query

update

A new user

new

4. After migration

The plaintext column was deleted

DDL deletes plaintext fields

To add to check

4. Auxiliary query column

conclusion

Related Posts

Software Architecture – ZooKeeper scenario (distributed lock) and ZkClient usage

Project lightweight deployment artifact: Fabric

Use CentOS7 mysql8.0