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

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

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

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

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

解密函数：AES_DECRYPT(str,key)

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

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

我的数据库版本是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());
 
    }
 
}