[Discuss] Do you need to exclude some SQL from NeedRewriteForEncrypt before rewrite it?

[cheese8]

The current situation is that:

1. SS's Parser and Encrypt module has some SQL statements that are not well supported, such as join, subQuery, insert select etc.
2. The systems in your company needs to meet compliance requirements (To ensure that the sensitive data is all stored by encryption.）, but not all of the SQLs are simple CRUD, the sql simplification is in progress of course.

Now there are a few tables all involving sensitive data, INSERT / UPDATE and DELETE good to say but some of the SELECT that meet the encryption rewrite condition, and even if no encryption rewrite, the current business results are also right, but the SELECT was scheduled to the next release window.

Actually, for now exceptions may occurred currently with encrypt rewrite, and you may consider to exclude those SQLs from NeedRewriteForEncrypt before rewrite it, I think one of the solution should be as below:

1. Define an annotation such as EncryptRewriteManual to mark the methods as manual in mybatis/jpa interfaces.
2. Check whether it is manual or auto in isNeedRewriteForEncrypt method, If it is manual, exclude it from rewrited.

[cheese8]

@strongduanmu @tristaZero What do you think?

[strongduanmu]

@cheese8 I think the way of using annotations is a bit more intrusive. We can consider using hint or sql annotations to skip some SQL rewriting.

[cheese8]

hint like /* no rewrite */ update table set plain_column = '', Can it be parsed success? @strongduanmu

[strongduanmu]

@cheese8 Yes, comment parsing is currently supported. What we need to do is to add the processing of comment conditions in the encrypt module.

[cheese8]

Ok, I would create the issue and pr it by sql hint. I think it might also be used in the sharing-proxy too. @strongduanmu

[cheese8]

Scenario Added:
We need to clear the plain value on sensitive column after encryption finished, so we write sql like
update table set plain_column = ''
but it was rewrite as
update table set cipher_column='xxx', assisted_column='yyy', plain_column=''
it was incorrect, we should keep it as original with no rewrite.

AS-IS:
update table set plain_column = null would not be rewrited, but column not null allowed on DBA‘s rule.

[strongduanmu]

@cheese8 Maybe we can consider introducing a configuration parameter, and it is up to the user to decide when the plain field needs to be rewritten.