# 數據安全之資料庫欄位加解密檢索和前端返回脫敏？看看我這個最強解決方案 ## 前言數據安全一直是我們老生常談的話題了,隨著國產化的日漸推進和數字化信息改革,數據安全越來越被人們所重視。資料庫作為存儲、管理和檢索數據的核心基礎設施，其中可能包含著大量的敏感信息，如個人手機號、身份證號碼、銀行賬戶、 ...

數據安全之資料庫欄位加解密檢索和前端返回脫敏？看看我這個最強解決方案

前言

數據安全一直是我們老生常談的話題了,隨著國產化的日漸推進和數字化信息改革,數據安全越來越被人們所重視。資料庫作為存儲、管理和檢索數據的核心基礎設施，其中可能包含著大量的敏感信息，如個人手機號、身份證號碼、銀行賬戶、家庭地址等信息。為了保障這些敏感信息在部分情況下被明文泄露和未授權訪問等惡意行為的侵害，資料庫欄位敏感信息加密變得至關重要。但是資料庫列一旦加密那麼就牽扯到很多問題。如何對資料庫欄位進行加密變得非常重要,目前主要有兩個解決方案:

資料庫自帶加密函數或者使用資料庫自定義函數方法進行加密解密
使用應用代碼比如java、c#等語言自帶的加密解密函數庫

為了助力國產化的推進下麵我將用solon + easy-query對其進行實踐演練和原理進行解析。

當前項目地址demo https://gitee.com/xuejm/solon-encrypt

方法	優點	缺點
資料庫函數對	實現簡單,占用磁碟空間少,由資料庫自行實現	模糊搜索效率低,與資料庫函數綁定,相容性差,僅可以使用資料庫提供的函數或者自行編程資料庫支持的加密解密
java代碼	模糊搜索效率高,不與資料庫函數綁定,相容性好,可以自行擴展實現國密等對稱非對稱加密	實現複雜,占用磁碟空間多

solon

文檔地址 https://xuejm.gitee.io/easy-query-doc/

GITHUB地址 https://github.com/noear/solon

GITEE地址 https://gitee.com/noear/solon

easy-qeury

文檔地址 https://xuejm.gitee.io/easy-query-doc/

GITHUB地址 https://github.com/xuejmnet/easy-query

GITEE地址 https://gitee.com/xuejm/easy-query

資料庫處理

這邊我們以mysql為例實現資料庫函數的加密解密,對資料庫列進行數據保護處理。

方法	預設值
to_base64(AES_ENCRYPT('手機號值'),'秘鑰')	將數據進行aes加密,然後進行base64編碼
AES_DECRYPT(from_base64('手機號列'),'秘鑰')	將數據進行base64解碼,然後進行aes進行解密

這兩個方法其實很好理解,就是通過調用資料庫函數讓其在資料庫層面就實現了加密和解密,應用程式獲取到的數據本身就是解密好了的,但是缺點就是如果需要支持列的like搜索性能會變得非常低下,因為需要對加密列進行AES_DECRYPT(from_base64('手機號列'),'秘鑰')的解密處理

select id,name,AES_DECRYPT(from_base64('phone'),'秘鑰') as `phone` from user where AES_DECRYPT(from_base64('phone'),'秘鑰') like '%567%'

當數據量少的時候那麼資料庫是比較輕鬆並且相對性能是可以接受的,但是隨著數據量的越來越大,這種sql會慢慢變成瓶頸,那麼是否有一種方案可以兼顧兩者呢,其實是有的.

應用代碼處理

分段加密,採用分段加密可以實現like語句的模糊搜索,並且支持數據的加密,但是這種方案也有缺點就是會比較占用空間,具體原理可以看下《阿裡巴巴密文欄位檢索方案》 https://jaq-doc.alibaba.com/docs/doc.htm?treeId=1&articleId=106213&docType=1 文章給出了具體的實現方式,約定最少4位數字或者2位中文字元4位英文字元（半形），2個中文字元（全形）,比如12345678901這麼一串

分別對其進行分段[1234,2345,3456,4567,5678,6789,7890,8901]分成8份,並且對每一份進行等長數據加密,也就是加密後的結果需要等長,比如都是16位或者都是8位

演算法/模式/填充	16 位元組加密後數據長度	不滿 16 位元組加密後長度	本次採用
AES/CBC/NoPadding	16	不支持	❌
AES/CBC/PKCS5Padding	32	16	✅
AES/CBC/ISO10126Padding	32	16	❌
AES/CFB/NoPadding	16	原始數據長度	❌
AES/CFB/PKCS5Padding	32	16	❌
AES/CFB/ISO10126Padding	32	16	❌
AES/ECB/NoPadding	16	不支持	❌
AES/ECB/PKCS5Padding	32	16	❌
AES/ECB/ISO10126Padding	32	16	❌
AES/OFB/NoPadding	16	原始數據長度	❌
AES/OFB/PKCS5Padding	32	16	❌
AES/OFB/ISO10126Padding	32	16	❌
AES/PCBC/NoPadding	16	不支持	❌
AES/PCBC/PKCS5Padding	32	16	❌
AES/PCBC/ISO10126Padding	32	16	❌

加密

我們可以選擇任意一種這次選擇AES/CBC/PKCS5Padding 因為我們的數據原因最終肯定不滿16位元組,所以加密後肯定都是16位元組長度,然後可以對其進行base64編碼,編碼後會變成24位元組在對其進行合併存儲

base64(aes('1234')) + base64(aes('2345')) + base64(aes('3456')) +......+ base64(aes('7890')) + base64(aes('8901'))

,然後存入資料庫,之後需要對其like的話限制最小like的數據應該滿足最少4位數字或者2位中文字元4位英文字元（半形），2個中文字元（全形），比如我要查詢包含45678的手機號只需要現對其進行分段[4567,5678]然後對其進行加密base64(aes('4567')) + base64(aes('5678'))

解密

因為我們採用aes加密後用base64編碼拼接存入資料庫,所以我們只需要對資料庫的數據進行獲取,之後判斷其長度%24是否餘數為0，如果是的話那麼就將其進行以每24個長度為一組進行base64解碼,然後通過aes解密.解密後在對其進行拼接還原出最初的明文數據

限制或缺點

通過上述可以知曉解密片段必須小於16位元組長度base64後的加密信息必須是定長
欄位會擴大,原本的n位明文如果需要支持加密那麼將會讓欄位變得非常長,但是好處是支持非常高性能的like搜索
建議使用到定長的數據信息中,譬如手機號,身份證號碼等
資料庫函數加密解密不支持like操作符需要註意
不同的資料庫需要適配不同的函數

優點

資料庫函數實現簡單
支持任意存儲對象比如es

select * from user where phone like '%xxxxx%' -- 其中xxxxx就是base64(aes('4567'))+base64(aes('5678'))

這樣我們就實現了即支持加密又支持like的方式了,但是對於大部分用戶來說雖然原理有了,但是實現起來還是太麻煩了,所以接下來我們就助力國產配合國產web框架和國產orm實現這兩個功能,讓用戶在使用時無感知

實踐案例

添加依賴

新建solon的web項目並且添加依賴


    <dependencies>
        <dependency>
            <groupId>com.easy-query</groupId>
            <artifactId>sql-solon-plugin</artifactId>
            <version>1.3.18</version>
        </dependency>
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.31</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>
        <dependency>
            <groupId>org.noear</groupId>
            <artifactId>solon-web</artifactId>
            <version>2.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.noear</groupId>
            <artifactId>solon.logging.simple</artifactId>
            <version>2.4.2</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.0.7</version>
        </dependency>
    </dependencies>

配置

在resources目錄下新建一個app.yml文件

# 添加配置文件
db1:
  jdbcUrl: jdbc:mysql://127.0.0.1:3306/solon_encrypt_db?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&rewriteBatchedStatements=true
  username: root
  password: root
  driver-class-name: com.mysql.cj.jdbc.Driver

easy-query:
  # 配置自定義日誌
  # log-class: ...
  db1:
    # 支持mysql pgsql h2 mssql dameng mssql_row_number kingbase_es等其餘資料庫在適配中
    database: mysql
    # 支持underlined default lower_camel_case upper_camel_case upper_underlined
    name-conversion: underlined
    default-track: true

# 記錄器級別的配置示例
solon.logging.logger:
  "root": #預設記錄器配置
    level: TRACE
  "com.zaxxer.hikari":
    level: WARN

具體更多參數請參考solon官方文檔和easy-query官方文檔

啟動類

WebApp.java


public class WebApp {
    public static void main(String[] args) {
        Solon.start(WebApp.class,args);
    }
}

加密策略

//支持like的java方法加密解密
public class JavaEncryptionStrategy extends AbstractAesBase64EncryptionStrategy {
    @Override
    public String getIv() {
        return "1234567890123456";
    }

    @Override
    public String getKey() {
        return "1234561234567890";
    }
}

//資料庫函數加密解密
public class MySQLAESColumnValueSQLConverter implements ColumnValueSQLConverter {
    /**
     * 數據加密秘鑰
     */
    private static final String SECRET="1234567890123456";
    @Override
    public void columnConvert(TableAvailable table, ColumnMetadata columnMetadata, SQLPropertyConverter sqlPropertyConverter, QueryRuntimeContext runtimeContext) {
        sqlPropertyConverter.sqlNativeSegment("AES_DECRYPT(from_base64({0}),{1})",context->{
            context
                    .expression(columnMetadata.getPropertyName())//採用變數是因為可能出現join附帶別名所以需要變數
                    .value(SECRET)
                    .setAlias(columnMetadata.getName());
        });
    }

    @Override
    public void valueConvert(TableAvailable table, ColumnMetadata columnMetadata, SQLParameter sqlParameter, SQLPropertyConverter sqlPropertyConverter, QueryRuntimeContext runtimeContext) {
        sqlPropertyConverter.sqlNativeSegment("to_base64(AES_ENCRYPT({0},{1}))",context->{
            context.value(sqlParameter).value(SECRET);
        });
    }
}

資料庫對象

//用戶信息表
@Data
@Table("sys_user")
public class SysUser {
    @Column(primaryKey = true)
    private String id;
    private String name;
    @Column(sqlConversion = MySQLAESColumnValueSQLConverter.class)
    private String phone;
    @Encryption(strategy = JavaEncryptionStrategy.class,supportQueryLike = true)
    private String Address;
    private LocalDateTime createTime;
    @Navigate(value = RelationTypeEnum.OneToMany,targetProperty = "userId")
    private List<UserBook> books;
}
//為了演示覆雜查詢這邊在新增一張用戶書本表
@Data
@Table("user_book")
public class UserBook {
    @Column(primaryKey = true)
    private String id;
    private String userId;
    private String name;
}

資料庫腳本


CREATE DATABASE IF NOT EXISTS solon_encrypt_db CHARACTER SET 'utf8mb4';
create table solon_encrypt_db.sys_user
(
    id varchar(32) not null comment '主鍵ID'primary key,
    name varchar(50) not null comment '姓名',
    phone varchar(256) null comment '手機號',-- 手機號不需要模糊搜索
    address varchar(512) null comment '用戶地址',-- 用戶地址需要模糊搜索
    create_time datetime not null comment '創建時間'
)comment '用戶表';
create table solon_encrypt_db.user_book
(
    id varchar(32) not null comment '主鍵ID'primary key,
    user_id varchar(32) not null comment '姓名',
    name varchar(50) not null comment '姓名'
)comment '用戶書本表';

配置文件


@Configuration
public class DefaultConfiguration {
    @Bean(name = "db1",typed=true)
    public DataSource db1DataSource(@Inject("${db1}") HikariDataSource dataSource){
        return dataSource;
    }
    @Bean
    public void db1QueryConfiguration(@Db("db1") QueryConfiguration configuration){
        configuration.applyEncryptionStrategy(new JavaEncryptionStrategy());
        configuration.applyColumnValueSQLConverter(new MySQLAESColumnValueSQLConverter());
    }
}

測試

新增控制器


@Controller
@Mapping("/test")
public class TestController {
    @Db
    private EasyQuery easyQuery;
    @Mapping(value = "/init",method = MethodType.GET)
    @Tran
    public String init(){
        {

            SysUser sysUser = new SysUser();
            sysUser.setId("1");
            sysUser.setName("用戶1");
            sysUser.setPhone("12345678901");
            sysUser.setAddress("浙江省紹興市越城區城市廣場1234號");
            sysUser.setCreateTime(LocalDateTime.now());
            ArrayList<UserBook> userBooks = new ArrayList<>();
            UserBook userBook = new UserBook();
            userBook.setId("1");
            userBook.setUserId("1");
            userBook.setName("語文");
            userBooks.add(userBook);
            UserBook userBook1 = new UserBook();
            userBook1.setId("2");
            userBook1.setUserId("1");
            userBook1.setName("數學");
            userBooks.add(userBook1);
            easyQuery.insertable(sysUser).executeRows();
            easyQuery.insertable(userBooks).executeRows();
        }
        {

            SysUser sysUser = new SysUser();
            sysUser.setId("2");
            sysUser.setName("用戶2");
            sysUser.setPhone("19012345678");
            sysUser.setAddress("浙江省杭州市上城區武林廣場1234號");
            sysUser.setCreateTime(LocalDateTime.now());
            ArrayList<UserBook> userBooks = new ArrayList<>();
            UserBook userBook = new UserBook();
            userBook.setId("3");
            userBook.setUserId("2");
            userBook.setName("語文");
            userBooks.add(userBook);
            UserBook userBook1 = new UserBook();
            userBook1.setId("4");
            userBook1.setUserId("2");
            userBook1.setName("英語");
            userBooks.add(userBook1);
            easyQuery.insertable(sysUser).executeRows();
            easyQuery.insertable(userBooks).executeRows();
        }
        return "初始化完成";
    }
}


==> Preparing: INSERT INTO `sys_user` (`id`,`name`,`phone`,`address`,`create_time`) VALUES (?,?,to_base64(AES_ENCRYPT(?,?)),?,?)
==> Parameters: 1(String),用戶1(String),12345678901(String),1234567890123456(String),miaKEctf5bGBi4yFHvSV6A==i9CdpEU+Ji+g0pPYOpTcWA==9RprkhoOPwcA13Ye0eE0NA==f0ryEfO7ajP2qQ9Yia/dwA==bFZZS42+JmMlvK+6t9a2xQ==O+TkblfoJWgGu6o/w3RuBQ==urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==8u/RP9cyz8l7udgay5Tbnw==oLi10kERsXzxuJdSFAZN9w==Sgm9i3O/7FtvC4ryFziNug==9gkm5m1HD8qS4ITJ0r/W4A==zppH8USinNqLsEPxJ2jfiQ==RY3Ji2Exl1StrrdrzSVvDQ==lMnY0leaGzXqeK/mukEIQQ==NlthvCsk4jaQkEioF/SWsA==(String),2023-08-13T09:17:01.503(LocalDateTime)
<== Total: 1
==> Preparing: INSERT INTO `user_book` (`id`,`user_id`,`name`) VALUES (?,?,?)
==> Parameters: 1(String),1(String),語文(String)
<== Total: 1
==> Preparing: INSERT INTO `user_book` (`id`,`user_id`,`name`) VALUES (?,?,?)
==> Parameters: 2(String),1(String),數學(String)
<== Total: 1
==> Preparing: INSERT INTO `sys_user` (`id`,`name`,`phone`,`address`,`create_time`) VALUES (?,?,to_base64(AES_ENCRYPT(?,?)),?,?)
==> Parameters: 2(String),用戶2(String),19012345678(String),1234567890123456(String),miaKEctf5bGBi4yFHvSV6A==i9CdpEU+Ji+g0pPYOpTcWA==JdzWF3gRqqCuHO+fiRTsGQ==Ydc2v/Ghy3MbHTvTiLqHIg==B9zPkalGKbJMzyFgw8W6bA==yIJYfG5BGqQnnR5+GhdV4g==V7Zu1p3qHPjOBj+vAc1MQA==+n2a0u3gq1V4L8aKa/eyEg==MEsrlm3QnRdt4entjjf97w==rBJCNrGBSjKI6T77OXD2dg==k75blBdYdH81FSIB4AVjeA==9gkm5m1HD8qS4ITJ0r/W4A==zppH8USinNqLsEPxJ2jfiQ==RY3Ji2Exl1StrrdrzSVvDQ==lMnY0leaGzXqeK/mukEIQQ==NlthvCsk4jaQkEioF/SWsA==(String),2023-08-13T09:17:01.775(LocalDateTime)
<== Total: 1
==> Preparing: INSERT INTO `user_book` (`id`,`user_id`,`name`) VALUES (?,?,?)
==> Parameters: 3(String),2(String),語文(String)
<== Total: 1
==> Preparing: INSERT INTO `user_book` (`id`,`user_id`,`name`) VALUES (?,?,?)
==> Parameters: 4(String),2(String),英語(String)
<== Total: 1

啟動程式訪問http://localhost:8080/test/init,插入對應的初始化數據

查詢數據


    @Mapping(value = "/query",method = MethodType.GET)
    public Object query(){
        List<SysUser> list = easyQuery.queryable(SysUser.class)
                .include(o -> o.many(SysUser::getBooks))
                .toList();
        return list;
    }

[{"id":"1","name":"用戶1","phone":"12345678901","Address":"浙江省紹興市越城區城市廣場1234號","createTime":1691889422000,"books":[{"id":"1","userId":"1","name":"語文"},{"id":"2","userId":"1","name":"數學"}]},{"id":"2","name":"用戶2","phone":"19012345678","Address":"浙江省杭州市上城區武林廣場1234號","createTime":1691889422000,"books":[{"id":"3","userId":"2","name":"語文"},{"id":"4","userId":"2","name":"英語"}]}]

按用戶加密地址模糊匹配


    @Mapping(value = "/queryByAddress",method = MethodType.GET)
    public Object queryByAddress(){
        SysUser user = easyQuery.queryable(SysUser.class)
                .where(o->o.like(SysUser::getAddress,"越城區"))
                .firstOrNull();
        return user;
    }

{"id":"1","name":"用戶1","phone":"12345678901","Address":"浙江省紹興市越城區城市廣場1234號","createTime":1691889422000}


==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE `address` LIKE ? LIMIT 1
==> Parameters: 1234567890123456(String),%urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==%(String)
<== Time Elapsed: 23(ms)
<== Total: 1

分析

浙江省紹興市越城區城市廣場1234號:miaKEctf5bGBi4yFHvSV6A==i9CdpEU+Ji+g0pPYOpTcWA==9RprkhoOPwcA13Ye0eE0NA==f0ryEfO7ajP2qQ9Yia/dwA==bFZZS42+JmMlvK+6t9a2xQ==O+TkblfoJWgGu6o/w3RuBQ==**urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==**8u/RP9cyz8l7udgay5Tbnw==oLi10kERsXzxuJdSFAZN9w==Sgm9i3O/7FtvC4ryFziNug==9gkm5m1HD8qS4ITJ0r/W4A==zppH8USinNqLsEPxJ2jfiQ==RY3Ji2Exl1StrrdrzSVvDQ==lMnY0leaGzXqeK/mukEIQQ==NlthvCsk4jaQkEioF/SWsA==

越城區加密後:urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==

查詢用戶書本表並且獲取用戶書本對應的用戶名

@Data
public class UserBookVO {
    private String id;
    private String userId;
    private String name;
    private String userName;
    private String userPhone;

    /**
     * 返回vo結果如果沒有該註解將是返回資料庫對應的列的原始數據
     */
    @Encryption(strategy = JavaEncryptionStrategy.class)
    private String userAddress;
}

    @Mapping(value = "/queryBook",method = MethodType.GET)
    public Object queryBook(){
        //無需考慮表別名所屬`t`還是`t1`等
        List<UserBookVO> userBooks = easyQuery.queryable(UserBook.class)
                .leftJoin(SysUser.class, (t, t1) -> t.eq(t1, UserBook::getUserId, SysUser::getId))
                .where((t, t1) -> t1.like(SysUser::getAddress, "越城區"))
                .select(UserBookVO.class, (t, t1) -> t.columnAll()
                        .then(t1)
                        .columnAs(SysUser::getName, UserBookVO::getUserName)
                        .columnAs(SysUser::getPhone, UserBookVO::getUserPhone)
                        .columnAs(SysUser::getAddress, UserBookVO::getUserAddress)
                )
                .toList();
        return userBooks;
    }

[{"id":"1","userId":"1","name":"語文","userName":"用戶1","userPhone":"12345678901","userAddress":"浙江省紹興市越城區城市廣場1234號"},{"id":"2","userId":"1","name":"數學","userName":"用戶1","userPhone":"12345678901","userAddress":"浙江省紹興市越城區城市廣場1234號"}]


==> Preparing: SELECT t.`id`,t.`user_id`,t.`name`,t1.`name` AS `user_name`,AES_DECRYPT(from_base64(t1.`phone`),?) AS `user_phone`,t1.`address` AS `user_address` FROM `user_book` t LEFT JOIN `sys_user` t1 ON t.`user_id` = t1.`id` WHERE t1.`address` LIKE ?
==> Parameters: 1234567890123456(String),%urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==%(String)
<== Time Elapsed: 21(ms)
<== Total: 2

修改手機號和地址

    @Mapping(value = "/updateFull",method = MethodType.GET)
    public Object updateFull(){
        SysUser user = easyQuery.queryable(SysUser.class).whereById("1").firstOrNull();
        user.setPhone("12312312312");
        user.setAddress("浙江省杭州市上城區0987號");
        easyQuery.updatable(user).executeRows();
        return null;
    }


==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE `id` = ? LIMIT 1
==> Parameters: 1234567890123456(String),1(String)
<== Time Elapsed: 35(ms)
<== Total: 1

==> Preparing: UPDATE `sys_user` SET `name` = ?,`phone` = to_base64(AES_ENCRYPT(?,?)),`address` = ?,`create_time` = ? WHERE `id` = ?
==> Parameters: 用戶1(String),12312312312(String),1234567890123456(String),miaKEctf5bGBi4yFHvSV6A==i9CdpEU+Ji+g0pPYOpTcWA==JdzWF3gRqqCuHO+fiRTsGQ==Ydc2v/Ghy3MbHTvTiLqHIg==B9zPkalGKbJMzyFgw8W6bA==yIJYfG5BGqQnnR5+GhdV4g==V7Zu1p3qHPjOBj+vAc1MQA==+n2a0u3gq1V4L8aKa/eyEg==+TZVS8QvlcDWZ4UTb8nhYQ==8ovX18Yz7LBizRX/aUCc+w==UY1+e/bka/X9nUuHla9C/w==VUW3oU+N4hm3mVZFJeal7Q==(String),2023-08-13T09:17:02(LocalDateTime),1(String)
<== Total: 1

我只改了兩個欄位但是sql生成了全欄位,下麵就進行優化

    @Mapping(value = "/updateTrack",method = MethodType.GET)
    @EasyQueryTrack
    public Object updateTrack(){
        SysUser user = easyQuery.queryable(SysUser.class).whereById("1").firstOrNull();
        user.setPhone("123123123123");
        user.setAddress("紹興市越城區87651號");
        easyQuery.updatable(user).executeRows();
        return null;
    }


==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE `id` = ? LIMIT 1
==> Parameters: 1234567890123456(String),1(String)
<== Time Elapsed: 10(ms)
<== Total: 1


==> Preparing: UPDATE `sys_user` SET `phone` = to_base64(AES_ENCRYPT(?,?)),`address` = ? WHERE `id` = ?
==> Parameters: 123123123123(String),1234567890123456(String),f0ryEfO7ajP2qQ9Yia/dwA==bFZZS42+JmMlvK+6t9a2xQ==O+TkblfoJWgGu6o/w3RuBQ==urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==ClirMJiuQjNV88CrpX4VKw==HWyz2JynN1dsAf5+bICC1A==X85Lef9VTnHIYUq4iTK4UQ==L32UeQ8jeCATh718YrxeQg==nozQIvfNzmvfkNQL2bI7RQ==(String),1(String)
<== Total: 1

直接更新

    @Mapping(value = "/updateSet",method = MethodType.GET)
    public Object updateSet(){
        easyQuery.updatable(SysUser.class)
                .set(SysUser::getPhone,"1234567")
                .set(SysUser::getAddress,"紹興市越城區123號")
                .whereById("1")
                .executeRows();
        return null;
    }


==> Preparing: UPDATE `sys_user` SET `phone` = to_base64(AES_ENCRYPT(?,?)),`address` = ? WHERE `id` = ?
==> Parameters: 1234567(String),1234567890123456(String),f0ryEfO7ajP2qQ9Yia/dwA==bFZZS42+JmMlvK+6t9a2xQ==O+TkblfoJWgGu6o/w3RuBQ==urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==xznBF4y5j5N4cik+fWz0jw==I3FW5ECJDfcM6yNWsYlH/g==6QBhErf/Lxjd5my5p3v4wg==(String),1(String)
<== Total: 1

資料庫加密函數手機號檢索


    @Mapping(value = "/queryByPhone",method = MethodType.GET)
    public Object queryByPhone(){
        SysUser sysUser = easyQuery.queryable(SysUser.class)
                .where(o -> o.eq(SysUser::getPhone,"1234567"))
                .firstOrNull();
        return sysUser;
    }

{"id":"1","name":"用戶1","phone":"1234567","Address":"紹興市越城區123號","createTime":1691889422000}

//預設只支持全匹配查詢,因為解密函數在資料庫列上是十分低效的
==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE `phone` = to_base64(AES_ENCRYPT(?,?)) LIMIT 1
==> Parameters: 1234567890123456(String),1234567(String),1234567890123456(String)
<== Time Elapsed: 27(ms)
<== Total: 1

如果你希望資料庫加密列也可以被檢索可以自定義資料庫片段函數來實現

    @Mapping(value = "/queryPhone",method = MethodType.GET)
    public Object queryPhone(){
        SysUser sysUser = easyQuery.queryable(SysUser.class)
                .where(o -> o.sqlNativeSegment("AES_DECRYPT(from_base64({0}),{1}) like {2}",context->{
                    context.expression(SysUser::getPhone)//列
                            .value("1234567890123456")//秘鑰
                            .value("%123%");//like值
                }))
                .firstOrNull();
        return sysUser;
    }

{"id":"1","name":"用戶1","phone":"1234567","Address":"紹興市越城區123號","createTime":1691889422000}


==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE AES_DECRYPT(from_base64(`phone`),?) like ? LIMIT 1
==> Parameters: 1234567890123456(String),1234567890123456(String),%123%(String)
<== Time Elapsed: 11(ms)
<== Total: 1

數據脫敏

很多時候我們的資料庫對象不需要脫敏數據,而是我們的VO對象在返回的時候需要對其數據進行脫敏,所以我們可以通過ValueConverter來實現


public class AddressMask implements ValueConverter<String,String> {
    @Override
    public String serialize(String s) {
        //因為是VO對象不需要實現
        throw new UnsupportedOperationException();
    }

    @Override
    public String deserialize(Class<String> propertyClass, String value) {
        if(EasyStringUtil.isBlank(value)){
            return value;
        }
        if(value.length()>5){
            return value.substring(0,3)+"***";
        }
        return value;
    }
}


@Data
public class UserBookVO {
    private String id;
    private String userId;
    private String name;
    private String userName;
    private String userPhone;

    /**
     * 返回vo結果如果沒有該註解將是返回資料庫對應的列的原始數據
     */
    @Encryption(strategy = JavaEncryptionStrategy.class)
    @Column(conversion = AddressMask.class)//添加地址脫敏
    private String userAddress;
}


@Configuration
public class DefaultConfiguration {
    @Bean(name = "db1",typed=true)
    public DataSource db1DataSource(@Inject("${db1}") HikariDataSource dataSource){
        return dataSource;
    }
    @Bean
    public void db1QueryConfiguration(@Db("db1") QueryConfiguration configuration){
        configuration.applyEncryptionStrategy(new JavaEncryptionStrategy());
        configuration.applyEncryptionStrategy(new JavaSM4EncryptionStrategy());
        configuration.applyColumnValueSQLConverter(new MySQLAESColumnValueSQLConverter());
        configuration.applyValueConverter(new AddressMask());
    }
}


    @Mapping(value = "/queryBookMask",method = MethodType.GET)
    public Object queryBookMask(){
        List<UserBookVO> userBooks = easyQuery.queryable(UserBook.class)
                .leftJoin(SysUser.class, (t, t1) -> t.eq(t1, UserBook::getUserId, SysUser::getId))
                .where((t, t1) -> t1.like(SysUser::getAddress, "越城區"))
                .select(UserBookVO.class, (t, t1) -> t.columnAll()
                        .then(t1)
                        .columnAs(SysUser::getName, UserBookVO::getUserName)
                        .columnAs(SysUser::getPhone, UserBookVO::getUserPhone)
                        .columnAs(SysUser::getAddress, UserBookVO::getUserAddress)
                )
                .toList();
        return userBooks;
    }

//數據地址被解密後正確的被脫敏返回到前端
[{"id":"1","userId":"1","name":"語文","userName":"用戶1","userPhone":"1234567","userAddress":"紹興市***"},{"id":"2","userId":"1","name":"數學","userName":"用戶1","userPhone":"1234567","userAddress":"紹興市***"}]


==> Preparing: SELECT t.`id`,t.`user_id`,t.`name`,t1.`name` AS `user_name`,AES_DECRYPT(from_base64(t1.`phone`),?) AS `user_phone`,t1.`address` AS `user_address` FROM `user_book` t LEFT JOIN `sys_user` t1 ON t.`user_id` = t1.`id` WHERE t1.`address` LIKE ?
==> Parameters: 1234567890123456(String),%urDZztVNP45UWWQrQsneOg==+n2a0u3gq1V4L8aKa/eyEg==%(String)
<== Time Elapsed: 13(ms)
<== Total: 2

SM4

方便起見我們這邊直接引入bouncycastle包

        <dependency>
            <groupId>org.bouncycastle</groupId>
            <artifactId>bcprov-jdk15to18</artifactId>
            <version>1.64</version>
        </dependency>

編寫sm4靜態工具類


public class SM4Util {
    static{
        Security.addProvider(new BouncyCastleProvider());
    }
    //演算法名稱
    public static final String ALGORITHM_NAME = "SM4";
    //ECB P5填充
    public static final String ALGORITHM_NAME_ECB_PADDING = "SM4/ECB/PKCS5Padding";
    //CBC P5填充
    public static final String ALGORITHM_NAME_CBC_PADDING = "SM4/CBC/PKCS5Padding";
    //密鑰長度
    public static final int DEFAULT_KEY_SIZE = 128;
    /**
     * 獲取密鑰
     * @return byte
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     */
    public static byte[] generateKey() throws NoSuchAlgorithmException, NoSuchProviderException {
        return generateKey(DEFAULT_KEY_SIZE);
    }
    /**
     * 獲取指定長度密鑰
     * @param keySize 密鑰的長度
     * @return byte
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     */
    public static byte[] generateKey(int keySize) throws NoSuchAlgorithmException, NoSuchProviderException {
        KeyGenerator kg = KeyGenerator.getInstance(ALGORITHM_NAME, BouncyCastleProvider.PROVIDER_NAME);
        kg.init(keySize, new SecureRandom());
        return kg.generateKey().getEncoded();
    }

    public static String encrypt(String plaintext, String key, Charset charset) {
        try {
            byte[] encrypt = encrypt_Ecb_Padding(plaintext.getBytes(charset), key.getBytes(charset));
            return new String(EasyBase64Util.encode(encrypt),charset);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    /**
     * ECB P5填充加密
     * @param key 密鑰
     * @param data 明文數據
     * @return byte
     * @throws InvalidKeyException
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     * @throws NoSuchPaddingException
     * @throws IllegalBlockSizeException
     * @throws BadPaddingException
     */
    public static byte[] encrypt_Ecb_Padding(byte[] data,byte[] key)
            throws InvalidKeyException, NoSuchAlgorithmException, NoSuchProviderException,
            NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException {
        Cipher cipher = generateEcbCipher(ALGORITHM_NAME_ECB_PADDING, Cipher.ENCRYPT_MODE, key);
        return cipher.doFinal(data);
    }

    public static String decrypt(String content, String key, Charset charset) {
        try {
            byte[] decrypt = decrypt_Ecb_Padding(EasyBase64Util.decode(content.getBytes(charset)), key.getBytes(charset));
            return new String(decrypt,charset);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    /**
     * ECB P5填充解密
     * @param key 密鑰
     * @param cipherText 加密後的數據
     * @return byte
     * @throws IllegalBlockSizeException
     * @throws BadPaddingException
     * @throws InvalidKeyException
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     * @throws NoSuchPaddingException
     */
    public static byte[] decrypt_Ecb_Padding( byte[] cipherText,byte[] key)
            throws IllegalBlockSizeException, BadPaddingException, InvalidKeyException,
            NoSuchAlgorithmException, NoSuchProviderException, NoSuchPaddingException {
        Cipher cipher = generateEcbCipher(ALGORITHM_NAME_ECB_PADDING, Cipher.DECRYPT_MODE, key);
        return cipher.doFinal(cipherText);
    }
    /**
     * CBC P5填充加密
     * @param key 密鑰
     * @param iv 偏移量
     * @param data 明文數據
     * @return byte
     * @throws InvalidKeyException
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     * @throws NoSuchPaddingException
     * @throws IllegalBlockSizeException
     * @throws BadPaddingException
     * @throws InvalidAlgorithmParameterException
     */
    public static byte[] encrypt_Cbc_Padding(byte[] key, byte[] iv, byte[] data)
            throws InvalidKeyException, NoSuchAlgorithmException, NoSuchProviderException,
            NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException,
            InvalidAlgorithmParameterException {
        Cipher cipher = generateCbcCipher(ALGORITHM_NAME_CBC_PADDING, Cipher.ENCRYPT_MODE, key, iv);
        return cipher.doFinal(data);
    }
    /**
     * CBC P5填充解密
     * @param key 密鑰
     * @param iv 偏移量
     * @param cipherText 加密數據
     * @return byte
     * @throws IllegalBlockSizeException
     * @throws BadPaddingException
     * @throws InvalidKeyException
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     * @throws NoSuchPaddingException
     * @throws InvalidAlgorithmParameterException
     */
    public static byte[] decrypt_Cbc_Padding(byte[] key, byte[] iv, byte[] cipherText)
            throws IllegalBlockSizeException, BadPaddingException, InvalidKeyException,
            NoSuchAlgorithmException, NoSuchProviderException, NoSuchPaddingException,
            InvalidAlgorithmParameterException {
        Cipher cipher = generateCbcCipher(ALGORITHM_NAME_CBC_PADDING, Cipher.DECRYPT_MODE, key, iv);
        return cipher.doFinal(cipherText);
    }
    /**
     * ECB P5填充加解密Cipher初始化
     * @param algorithmName 演算法名稱
     * @param mode 1 加密  2解密
     * @param key 密鑰
     * @return Cipher
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     * @throws NoSuchPaddingException
     * @throws InvalidKeyException
     */
    private static Cipher generateEcbCipher(String algorithmName, int mode, byte[] key)
            throws NoSuchAlgorithmException, NoSuchProviderException, NoSuchPaddingException,
            InvalidKeyException {
        Cipher cipher = Cipher.getInstance(algorithmName, BouncyCastleProvider.PROVIDER_NAME);
        Key sm4Key = new SecretKeySpec(key, ALGORITHM_NAME);
        cipher.init(mode, sm4Key);
        return cipher;
    }
    /**
     * CBC P5填充加解密Cipher初始化
     * @param algorithmName 演算法名稱
     * @param mode 1 加密  2解密
     * @param key 密鑰
     * @param iv 偏移量
     * @return Cipher
     * @throws InvalidKeyException
     * @throws InvalidAlgorithmParameterException
     * @throws NoSuchAlgorithmException
     * @throws NoSuchProviderException
     * @throws NoSuchPaddingException
     */
    private static Cipher generateCbcCipher(String algorithmName, int mode, byte[] key, byte[] iv)
            throws InvalidKeyException, InvalidAlgorithmParameterException, NoSuchAlgorithmException,
            NoSuchProviderException, NoSuchPaddingException {
        Cipher cipher = Cipher.getInstance(algorithmName, BouncyCastleProvider.PROVIDER_NAME);
        Key sm4Key = new SecretKeySpec(key, ALGORITHM_NAME);
        IvParameterSpec ivParameterSpec = new IvParameterSpec(iv);
        cipher.init(mode, sm4Key, ivParameterSpec);
        return cipher;
    }
}

編寫加密策略


public class JavaSM4EncryptionStrategy implements EncryptionStrategy {
    private static final Log log = LogFactory.getLog(JavaSM4EncryptionStrategy.class);
    private static final String key="1234567890123456";
    @Override
    public Object encrypt(Class<?> entityClass, String propertyName, Object plaintext) {

        if (plaintext == null) {
            return null;
        }
        try {
            return doEncrypt(plaintext);
        }catch (Exception exception){
            log.error(EasyClassUtil.getInstanceSimpleName(this)+" "+ EasyClassUtil.getSimpleName(entityClass)+"."+ "."+propertyName+" decrypt error:" + plaintext, exception);
            throw exception;
        }
    }

    protected Object doEncrypt(Object plaintext){
        String plaintextString = plaintext.toString();
        //4表示多少單位長度為一組,1表示非中文的情況下1個字元為一個單位長度,2表示一個中文字元為2個單位長度
        List<String> stringCharSegments = EasyStringUtil.getStringCharSegments(plaintextString, 4,1,2);
        //符合要求譬如最少4個非中文字元或者2個中文字的情況下,可以選擇拋錯重寫或者直接加密對應的值
        if (EasyCollectionUtil.isEmpty(stringCharSegments)) {
            stringCharSegments.add(plaintextString);
        }
        StringBuilder stringBuilder = new StringBuilder();
        for (String stringCharSegment : stringCharSegments) {
            String str= SM4Util.encrypt(stringCharSegment,key, StandardCharsets.UTF_8);
            stringBuilder.append(str);
        }
        return stringBuilder.toString();
    }

    @Override
    public Object decrypt(Class<?> entityClass,String propertyName,Object ciphertext) {
        try {
            return doDecrypt(entityClass,propertyName,ciphertext);
        } catch (Exception exception) {
            log.error(EasyClassUtil.getInstanceSimpleName(this)+" "+ EasyClassUtil.getSimpleName(entityClass)+"."+ "."+propertyName+" decrypt error:" + ciphertext, exception);
            throw exception;
        }
    }

    private Object doDecrypt(Class<?> entityClass,String propertyName,Object ciphertext) {
        if (ciphertext == null) {
            return null;
        }
        String ciphertextString = ciphertext.toString();
        if (ciphertextString.length() % 24 != 0) {
                throw new IllegalArgumentException(EasyClassUtil.getSimpleName(entityClass)+"."+propertyName+" decrypt cant decode base64:" + ciphertext);
        }

        List<String> segments = EasyStringUtil.splitString(ciphertextString, 24);

        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < segments.size(); i++) {
            String str=SM4Util.decrypt(segments.get(i),key,StandardCharsets.UTF_8);
            boolean last = i == (segments.size() - 1);
            if (last) {
                stringBuilder.append(str);
            } else {
                stringBuilder.append(str.charAt(0));
            }
        }
        return stringBuilder.toString();
    }
}



@Configuration
public class DefaultConfiguration {
    @Bean(name = "db1",typed=true)
    public DataSource db1DataSource(@Inject("${db1}") HikariDataSource dataSource){
        return dataSource;
    }
    @Bean
    public void db1QueryConfiguration(@Db("db1") QueryConfiguration configuration){
        configuration.applyEncryptionStrategy(new JavaEncryptionStrategy());
        configuration.applyEncryptionStrategy(new JavaSM4EncryptionStrategy());//添加sm4
        configuration.applyColumnValueSQLConverter(new MySQLAESColumnValueSQLConverter());
    }
}

@Data
@Table("sys_user")
public class SysUserSM4 {
    @Column(primaryKey = true)
    private String id;
    private String name;
    @Column(sqlConversion = MySQLAESColumnValueSQLConverter.class)
    private String phone;
    @Encryption(strategy = JavaSM4EncryptionStrategy.class,supportQueryLike = true)
    private String Address;
    private LocalDateTime createTime;
}



    @Mapping(value = "/initSM4",method = MethodType.GET)
    @Tran
    public String initSM4(){
        {

            SysUserSM4 sysUser = new SysUserSM4();
            sysUser.setId("5");
            sysUser.setName("用戶5");
            sysUser.setPhone("12345678901");
            sysUser.setAddress("浙江省紹興市越城區城市廣場1234號");
            sysUser.setCreateTime(LocalDateTime.now());
            easyQuery.insertable(sysUser).executeRows();
        }
        {

            SysUserSM4 sysUser = new SysUserSM4();
            sysUser.setId("6");
            sysUser.setName("用戶6");
            sysUser.setPhone("19012345678");
            sysUser.setAddress("浙江省杭州市上城區武林廣場1234號");
            sysUser.setCreateTime(LocalDateTime.now());
            easyQuery.insertable(sysUser).executeRows();
        }
        return "初始化完成";
    }


==> Preparing: INSERT INTO `sys_user` (`id`,`name`,`phone`,`address`,`create_time`) VALUES (?,?,to_base64(AES_ENCRYPT(?,?)),?,?)
==> Parameters: 5(String),用戶5(String),12345678901(String),1234567890123456(String),KHxVEDHBxB0x9kgAltKrMA==llZIL8h9i+2b7sPaSt6qpw==/WFPdFPf569dkeGI2Q9r9A==CAvnuJp9Lz30LTVaZi5U5A==JKhjq5f94+MJgJK7Fc4lRA==flZDUtkyeOZJrdUE0DxlZg==jlVLlk9iVJCOCdln+G11Mg==wFIL7wK7nBctC0slOEomrg==zztIbbTcuUyyS+Zj2JgQ1w==X6DWRoQjqCunrA9w6ZlJ3Q==hN0Bm2/qS3XRK2Xxe8/MIw==iDZGTAU/WlMkwLAoiYuh8Q==R4pbp78Ig7qHCLzn9IF7rw==woPrxebr8Xvyo1qG8QxAUA==65pvnL+1Og20OW+xunqHCA==vJKXxvzbvWtZB9hrWrioCg==(String),2023-08-13T22:28:27.204(LocalDateTime)
<== Total: 1


==> Preparing: INSERT INTO `sys_user` (`id`,`name`,`phone`,`address`,`create_time`) VALUES (?,?,to_base64(AES_ENCRYPT(?,?)),?,?)
==> Parameters: 6(String),用戶6(String),19012345678(String),1234567890123456(String),KHxVEDHBxB0x9kgAltKrMA==llZIL8h9i+2b7sPaSt6qpw==66gmuLlsoaX1sHDabqd/XA==YGGmh56Hc5MS+Wf8dZdl8w==SjvWmsqOacq5Kui8xDCxxw==cQrhVkPp3Hf5s/GKHpNOaw==BDvJOrbklVQGHodEa+eyCA==wFIL7wK7nBctC0slOEomrg==BZcCFFYJzjQzZ7R23fmOUA==M8WFvyffOu6BeTpQgghhUA==Jw8BjPktNN8CPRyi1f5Vrg==iDZGTAU/WlMkwLAoiYuh8Q==R4pbp78Ig7qHCLzn9IF7rw==woPrxebr8Xvyo1qG8QxAUA==65pvnL+1Og20OW+xunqHCA==vJKXxvzbvWtZB9hrWrioCg==(String),2023-08-13T22:28:27.622(LocalDateTime)
<== Total: 1

查詢對應的數據


    @Mapping(value = "/querySM4",method = MethodType.GET)
    public Object querySM4(){
        List<SysUserSM4> list = easyQuery.queryable(SysUserSM4.class)
                .whereByIds(Arrays.asList("5", "6"))
                .toList();
        return list;
    }

[{"id":"5","name":"用戶5","phone":"12345678901","Address":"浙江省紹興市越城區城市廣場1234號","createTime":1691936907000},{"id":"6","name":"用戶6","phone":"19012345678","Address":"浙江省杭州市上城區武林廣場1234號","createTime":1691936908000}]


==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE `id` IN (?,?)
==> Parameters: 1234567890123456(String),5(String),6(String)
<== Time Elapsed: 14(ms)
<== Total: 2

模糊查詢


    @Mapping(value = "/queryLikeSM4",method = MethodType.GET)
    public Object queryLikeSM4(){
        List<SysUserSM4> list = easyQuery.queryable(SysUserSM4.class)
                .whereByIds(Arrays.asList("5", "6"))
                .where(o->o.like(SysUserSM4::getAddress,"武林廣場"))
                .toList();
        return list;
    }

[{"id":"6","name":"用戶6","phone":"19012345678","Address":"浙江省杭州市上城區武林廣場1234號","createTime":1691936908000}]


==> Preparing: SELECT `id`,`name`,AES_DECRYPT(from_base64(`phone`),?) AS `phone`,`address`,`create_time` FROM `sys_user` WHERE `id` IN (?,?) AND `address` LIKE ?
==> Parameters: 1234567890123456(String),5(String),6(String),%M8WFvyffOu6BeTpQgghhUA==Jw8BjPktNN8CPRyi1f5Vrg==iDZGTAU/WlMkwLAoiYuh8Q==%(String)
<== Time Elapsed: 38(ms)
<== Total: 1

註意例子裡面採用的是簡單的ecb模式,您可以自定義或者使用cbc,cbc模式需要多一個iv向量

支持我們實現了資料庫列的加密,資料庫函數對和java代碼自定義,其中java代碼支持like,資料庫需要支持like就會消耗大量的計算,需要對所有加密數據在資料庫層面進行解密後才可以