MySQL数据加密，模糊查询

实现配置文件加密，数据库敏感字段加密，加密字段模糊查询

配置文件加密使用Jasypt。数据库加密解密方法自己用java实现mysql的aes_encrypt和aes_decrypt方法，查询时先用mysql的解密方法aes_decrypt把加密字段解密再用like查。

配置文件加密参考：Springboot 配置文件、隐私数据脱敏的最佳实践（原理+源码）

spring:
  ### 数据源
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/plm?characterEncoding=utf-8&nullCatalogMeansCurrent=true
    username: root
#    password: 666666
    password: ENC(hhhhhhhhhhh==)
#加密配置
#  需要脱敏的value值替换成预先经过加密的内容ENC(XXX)，ENC(XXX）格式主要为了便于识别该值是否需要解密，如不按照该格式配置，在加载配置项的时候jasypt将保持原值，不进行解密。
#  加密命令：java -cp D:\maven_lib\org\jasypt\jasypt\1.9.3\jasypt-1.9.3.jar org.jasypt.intf.cli.JasyptPBEStringEncryptionCLI input="666666" password=mypassword algorithm=PBEWithMD5AndDES
#  input待加密文本，password秘钥，algorithm为使用的加密算法。
jasypt:
  encryptor:
    password: mypassword
    poolSize: 3
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

关于加密解密的一些常识：加盐"到底加的是什么 - 知乎

mysql数据库加密函数：AES_ENCRYPT(str,key)

加密在存入数据库的时候，转成十六进制

insert into t2 select hex(aes_encrypt(('身份证号'),'mima'));
1

解密函数：AES_DECRYPT(str,key)

解密之前先用huhex函数转一次

select aes_decrypt(unhex(c1),'mima') from t2;
1

我的数据库版本是5.6，编码utf8mb4 – UTF-8 Unicode，utf8不行。

模糊查询语句：" and aes_decrypt(unhex("+propertyStr+"),'"+aesKey+"') like '%"+likeStr+"%'";

package com.godsheepteam.plm.aspect;
 
import com.godsheepteam.util.web.WebContextListener;
import com.ulisesbocchio.jasyptspringboot.properties.JasyptEncryptorConfigurationProperties;
import org.apache.commons.codec.DecoderException;
 
import org.apache.commons.codec.binary.Hex;
 
import javax.crypto.*;
 
import javax.crypto.spec.SecretKeySpec;
 
import java.io.UnsupportedEncodingException;
 
import java.security.InvalidKeyException;
 
import java.security.NoSuchAlgorithmException;
 
import java.util.Arrays;
 
public class AesUtil {
    /**
 
     * mysql 加密一直的SecretKeySpec
     * @param key
     * @return
     */
    public static SecretKeySpec gener(String key){
        try {
            byte[] finalKey = new byte[16];
 
            int i=0;
 
            for (byte b : key.getBytes("utf-8")) {
                finalKey[i++ % 16] ^= b;
 
            }
 
            return new SecretKeySpec(finalKey,"AES");
 
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
 
        }
 
        return null;
 
    }
 
    public static String aes_encrypt(String password, String strKey) {
        try {
            byte[] keyBytes = Arrays.copyOf(strKey.getBytes("ASCII"), 16);
 
//通用的
 
// SecretKey key = new SecretKeySpec(keyBytes, "AES");
 
//生成和mysql一直的加密数据
 
            SecretKeySpec key = gener(strKey);
 
            Cipher cipher = Cipher.getInstance("AES/ECB/PKCS5Padding");
 
            cipher.init(Cipher.ENCRYPT_MODE, key);
 
            byte[] cleartext = password.getBytes("UTF-8");
 
            byte[] ciphertextBytes = cipher.doFinal(cleartext);
 
            return new String(Hex.encodeHexString(ciphertextBytes));
 
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
 
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
 
        } catch (NoSuchPaddingException e) {
            e.printStackTrace();
 
        } catch (InvalidKeyException e) {
            e.printStackTrace();
 
        } catch (IllegalBlockSizeException e) {
            e.printStackTrace();
 
        } catch (BadPaddingException e) {
            e.printStackTrace();
 
        } return null;
 
    }
 
    public static SecretKeySpec generateMySQLAESKey(final String key, final String encoding) {
        try {
            final byte[] finalKey = new byte[16];
 
            int i = 0;
 
            for(byte b : key.getBytes(encoding))
 
                finalKey[i++%16] ^= b;
 
            return new SecretKeySpec(finalKey, "AES");
 
        } catch(UnsupportedEncodingException e) {
            throw new RuntimeException(e);
 
        }
 
    }
 
    public static String aes_decrypt(String content, String aesKey){
        try {
            SecretKey key = generateMySQLAESKey(aesKey,"ASCII");
 
            Cipher cipher = Cipher.getInstance("AES");
 
            cipher.init(Cipher.DECRYPT_MODE, key);
 
            byte[] cleartext = Hex.decodeHex(content.toCharArray());
 
            byte[] ciphertextBytes = cipher.doFinal(cleartext);
 
            return new String(ciphertextBytes, "UTF-8");
 
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
 
        } catch (NoSuchAlgorithmException e) {
            e.printStackTrace();
 
        } catch (NoSuchPaddingException e) {
            e.printStackTrace();
 
        } catch (InvalidKeyException e) {
            e.printStackTrace();
 
        } catch (IllegalBlockSizeException e) {
            e.printStackTrace();
 
        } catch (BadPaddingException e) {
            e.printStackTrace();
 
        } catch (DecoderException e) {
            e.printStackTrace();
 
        }
 
        return null;
 
    }
 
    public static String aes_decrypt(String content){
        return aes_decrypt(content,aesKey);
    }
    public static String aes_encrypt(String password) {
        return aes_encrypt(password,aesKey);
    }
 
    public static String createLikeHql(String propertyStr,String likeStr){
        return " and aes_decrypt(unhex("+propertyStr+"),'"+aesKey+"') like '%"+likeStr+"%'";
    }
 
    private static String aesKey;
    static {
        if (aesKey==null){
            JasyptEncryptorConfigurationProperties jasyptEncryptorConfigurationProperties= (JasyptEncryptorConfigurationProperties) WebContextListener.getBean("jasyptEncryptorConfigurationProperties");
            aesKey=jasyptEncryptorConfigurationProperties.getPassword();
        }
    }
 
 
 
    public static void main(String[] args) {
//        -- insert into t2 select hex(aes_encrypt(('字符串'),'mima'));
//        select aes_decrypt(unhex(c1),'mima') from t2;
 
//解密
        String s = aes_decrypt("36CD256BB4BD99CB184D089408954681", "mima");
        System.out.println(s);
//加密
        String a = aes_encrypt("字符串", "mima");
        System.out.println(a.toUpperCase());
 
    }
 
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188