数据库字段加密解密-Mybatis简单实现

数据库字段加密解密-Mybatis简单实现，数据库经常会遇到一些字段包含敏感信息，不能以明文方式存储可以使用该方式加密

POM 加解密依赖

<dependency>
   <groupId>org.bouncycastle</groupId>
   <artifactId>bcprov-jdk15on</artifactId>
</dependency>

加解密工具类

import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Base64;

public class TableFieldEncryptionAndDecryptionHandler implements TypeHandler<String> {

    private static String key = "8dFph568fsDjzzfWQx6/8g==";
     //是否开启加解密操作
    private static Boolean openEncrypt = true;


    public static String loadKey(){
        return key;
    }

    public static boolean loadOpenEncrypt(){
        return openEncrypt;
    }

    /**
     * 入库前加密
     */
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, String data, JdbcType jdbcType) throws SQLException {
        if(loadOpenEncrypt()){
            try{
                byte[] keyByte = Base64.getDecoder().decode(loadKey());
                byte[] cipherText = SM4Util.encrypt_ECB_Padding(keyByte, data.getBytes());
                String dataEncrypt = Base64.getEncoder().encodeToString(cipherText);
                System.out.println("1-数据库字段加密-原始值:"+data+";加密值:"+dataEncrypt);
                preparedStatement.setString(i, dataEncrypt);
            }catch(Exception e){
                e.printStackTrace();
                System.out.println("1-数据库字段加密-失败:"+data+"  :  "+e.getMessage());
            }
        }else{
            preparedStatement.setString(i, data);
        }
    }

    /**
     * 出库前解密
     */
    @Override
    public String getResult(ResultSet resultSet, String s) throws SQLException {
        String data = resultSet.getString(s);
        if(loadOpenEncrypt()){
            if(StringUtils.isNotEmpty(data)){
                try {
                    byte[] jiemiInput = Base64.getDecoder().decode(data);
                    byte[] keyByte = Base64.getDecoder().decode(loadKey());
                    byte[] decryptedData = SM4Util.decrypt_ECB_Padding(keyByte, jiemiInput);
                    data = new String(decryptedData);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            System.out.println("1-数据库字段解密-原始值:{"+s+"};解密值:{"+data+"}");
        }
        return data;
    }

    @Override
    public String getResult(ResultSet resultSet, int i) throws SQLException {
        String data = resultSet.getString(i);
        if(loadOpenEncrypt()){
            String old = new String(data);
            if(StringUtils.isNotEmpty(data)){
                try {
                    byte[] jiemiInput = Base64.getDecoder().decode(data);
                    byte[] keyByte = Base64.getDecoder().decode(loadKey());
                    byte[] decryptedData = SM4Util.decrypt_ECB_Padding(keyByte, jiemiInput);
                    data = new String(decryptedData);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            System.out.println("2-数据库字段解密-原始值:{"+old+"};解密值:{"+data+"}");
        }
        return data;
    }

    @Override
    public String getResult(CallableStatement callableStatement, int i) throws SQLException {
        String data = callableStatement.getString(i);
        if(loadOpenEncrypt()){
            String old = new String(data);
            if(StringUtils.isNotEmpty(data)){
                try {
                    byte[] jiemiInput = Base64.getDecoder().decode(data);
                    byte[] keyByte = Base64.getDecoder().decode(loadKey());
                    byte[] decryptedData = SM4Util.decrypt_ECB_Padding(keyByte, jiemiInput);
                    data = new String(decryptedData);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            System.out.println("3-数据库字段解密-原始值:{"+old+"};解密值:{"+data+"}");
        }
        return data;
    }


    /**
     * 获取一个新 Key
     * @throws Exception
     */
    private static void getNewKey() throws Exception {
        byte[] key = SM4Util.generateKey();
        String newKeyString = Base64.getEncoder().encodeToString(key);
        System.out.println("key:"+newKeyString);
    }

    public static void main(String[] args)throws Exception {
        getNewKey();
        String data = "123456";
        try{
            byte[] keyByte = Base64.getDecoder().decode(loadKey());
            byte[] cipherText = SM4Util.encrypt_ECB_Padding(keyByte, data.getBytes());
            String dataEncrypt = Base64.getEncoder().encodeToString(cipherText);
            System.out.println(dataEncrypt);
        }catch(Exception e){
            e.printStackTrace();
        }



}

加密解密工具

import org.bouncycastle.crypto.CipherParameters;
import org.bouncycastle.crypto.engines.SM4Engine;
import org.bouncycastle.crypto.macs.CBCBlockCipherMac;
import org.bouncycastle.crypto.macs.GMac;
import org.bouncycastle.crypto.modes.GCMBlockCipher;
import org.bouncycastle.crypto.paddings.BlockCipherPadding;
import org.bouncycastle.crypto.paddings.PKCS7Padding;
import org.bouncycastle.crypto.params.KeyParameter;
import org.bouncycastle.crypto.params.ParametersWithIV;
import org.bouncycastle.jce.provider.BouncyCastleProvider;

import javax.crypto.*;
import javax.crypto.spec.IvParameterSpec;
import javax.crypto.spec.SecretKeySpec;
import java.security.*;

public class SM4Util extends GMBaseUtil {
    public static final String ALGORITHM_NAME = "SM4";
    public static final String ALGORITHM_NAME_ECB_PADDING = "SM4/ECB/PKCS5Padding";
    public static final String ALGORITHM_NAME_ECB_NOPADDING = "SM4/ECB/NoPadding";
    public static final String ALGORITHM_NAME_CBC_PADDING = "SM4/CBC/PKCS5Padding";
    public static final String ALGORITHM_NAME_CBC_NOPADDING = "SM4/CBC/NoPadding";

    /**
     * SM4算法目前只支持128位（即密钥16字节）
     */
    public static final int DEFAULT_KEY_SIZE = 128;

    public static byte[] generateKey() throws NoSuchAlgorithmException, NoSuchProviderException {
        return generateKey(DEFAULT_KEY_SIZE);
    }

    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 byte[] encrypt_ECB_Padding(byte[] key, byte[] data)
            throws InvalidKeyException, NoSuchAlgorithmException, NoSuchProviderException,
            NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException {
        Cipher cipher = generateECBCipher(ALGORITHM_NAME_ECB_PADDING, Cipher.ENCRYPT_MODE, key);
        return cipher.doFinal(data);
    }

    public static byte[] decrypt_ECB_Padding(byte[] key, byte[] cipherText)
            throws IllegalBlockSizeException, BadPaddingException, InvalidKeyException,
            NoSuchAlgorithmException, NoSuchProviderException, NoSuchPaddingException {
        Cipher cipher = generateECBCipher(ALGORITHM_NAME_ECB_PADDING, Cipher.DECRYPT_MODE, key);
        return cipher.doFinal(cipherText);
    }

    public static byte[] encrypt_ECB_NoPadding(byte[] key, byte[] data)
            throws InvalidKeyException, NoSuchAlgorithmException, NoSuchProviderException,
            NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException {
        Cipher cipher = generateECBCipher(ALGORITHM_NAME_ECB_NOPADDING, Cipher.ENCRYPT_MODE, key);
        return cipher.doFinal(data);
    }

    public static byte[] decrypt_ECB_NoPadding(byte[] key, byte[] cipherText)
            throws IllegalBlockSizeException, BadPaddingException, InvalidKeyException,
            NoSuchAlgorithmException, NoSuchProviderException, NoSuchPaddingException {
        Cipher cipher = generateECBCipher(ALGORITHM_NAME_ECB_NOPADDING, Cipher.DECRYPT_MODE, key);
        return cipher.doFinal(cipherText);
    }

    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);
    }

    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);
    }

    public static byte[] encrypt_CBC_NoPadding(byte[] key, byte[] iv, byte[] data)
            throws InvalidKeyException, NoSuchAlgorithmException, NoSuchProviderException,
            NoSuchPaddingException, IllegalBlockSizeException, BadPaddingException,
            InvalidAlgorithmParameterException {
        Cipher cipher = generateCBCCipher(ALGORITHM_NAME_CBC_NOPADDING, Cipher.ENCRYPT_MODE, key, iv);
        return cipher.doFinal(data);
    }

    public static byte[] decrypt_CBC_NoPadding(byte[] key, byte[] iv, byte[] cipherText)
            throws IllegalBlockSizeException, BadPaddingException, InvalidKeyException,
            NoSuchAlgorithmException, NoSuchProviderException, NoSuchPaddingException,
            InvalidAlgorithmParameterException {
        Cipher cipher = generateCBCCipher(ALGORITHM_NAME_CBC_NOPADDING, Cipher.DECRYPT_MODE, key, iv);
        return cipher.doFinal(cipherText);
    }

    public static byte[] doCMac(byte[] key, byte[] data) throws NoSuchProviderException, NoSuchAlgorithmException,
            InvalidKeyException {
        Key keyObj = new SecretKeySpec(key, ALGORITHM_NAME);
        return doMac("SM4-CMAC", keyObj, data);
    }

    public static byte[] doGMac(byte[] key, byte[] iv, int tagLength, byte[] data) {
        org.bouncycastle.crypto.Mac mac = new GMac(new GCMBlockCipher(new SM4Engine()), tagLength * 8);
        return doMac(mac, key, iv, data);
    }

    /**
     * 默认使用PKCS7Padding/PKCS5Padding填充的CBCMAC
     *
     * @param key
     * @param iv
     * @param data
     * @return
     */
    public static byte[] doCBCMac(byte[] key, byte[] iv, byte[] data) {
        SM4Engine engine = new SM4Engine();
        org.bouncycastle.crypto.Mac mac = new CBCBlockCipherMac(engine, engine.getBlockSize() * 8, new PKCS7Padding());
        return doMac(mac, key, iv, data);
    }

    /**
     * @param key
     * @param iv
     * @param padding 可以传null，传null表示NoPadding，由调用方保证数据必须是BlockSize的整数倍
     * @param data
     * @return
     * @throws Exception
     */
    public static byte[] doCBCMac(byte[] key, byte[] iv, BlockCipherPadding padding, byte[] data) throws Exception {
        SM4Engine engine = new SM4Engine();
        if (padding == null) {
            if (data.length % engine.getBlockSize() != 0) {
                throw new Exception("if no padding, data length must be multiple of SM4 BlockSize");
            }
        }
        org.bouncycastle.crypto.Mac mac = new CBCBlockCipherMac(engine, engine.getBlockSize() * 8, padding);
        return doMac(mac, key, iv, data);
    }


    private static byte[] doMac(org.bouncycastle.crypto.Mac mac, byte[] key, byte[] iv, byte[] data) {
        CipherParameters cipherParameters = new KeyParameter(key);
        mac.init(new ParametersWithIV(cipherParameters, iv));
        mac.update(data, 0, data.length);
        byte[] result = new byte[mac.getMacSize()];
        mac.doFinal(result, 0);
        return result;
    }

    private static byte[] doMac(String algorithmName, Key key, byte[] data) throws NoSuchProviderException,
            NoSuchAlgorithmException, InvalidKeyException {
        Mac mac = Mac.getInstance(algorithmName, BouncyCastleProvider.PROVIDER_NAME);
        mac.init(key);
        mac.update(data);
        return mac.doFinal();
    }

    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;
    }

    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;
    }
}

MyBatis Xml样例

<resultMap id="BaseResultMap" type="com.model">
    <result column="NAME" jdbcType="VARCHAR" property="name" typeHandler="com.TableFieldEncryptionAndDecryptionHandler"/>
</resultMap>

<insert id="insert" parameterType="com.model" useGeneratedKeys="true" keyProperty="id">
    insert into table_a (NAME, USABLE)
    values (
      #{name,jdbcType=VARCHAR,javaType=String,typeHandler=com.TableFieldEncryptionAndDecryptionHandler},
      #{usable,jdbcType=INTEGER}, #{createtime,jdbcType=TIMESTAMP}
      )
</insert>

<update id="update" parameterType="com.model">
        update table_a
        <set>
            NAME= #{name,jdbcType=VARCHAR,javaType=String,typeHandler=com.TableFieldEncryptionAndDecryptionHandler},
        </set>
        where ID = #{id,jdbcType=INTEGER}
</update>

<sql id="Example_Where_Clause">
        <where>
            <foreach collection="oredCriteria" item="criteria" separator="or">
                <if test="criteria.valid">
                    <trim prefix="(" prefixOverrides="and" suffix=")">
                        <foreach collection="criteria.criteria" item="criterion">
                            <choose>
                                <when test="criterion.noValue">
                                    and ${criterion.condition}
                                </when>
                                <when test="criterion.singleValue">
                                    <choose>
                                        <when test="criterion.condition=='name =' or criterion.condition=='phone ='">
                                            and ${criterion.condition} #{criterion.value,javaType=String,jdbcType=VARCHAR,typeHandler=com.TableFieldEncryptionAndDecryptionHandler}
                                        </when>
                                        <otherwise>
                                            and ${criterion.condition} #{criterion.value}
                                        </otherwise>
                                    </choose>
                                </when>
                                <when test="criterion.betweenValue">
                                    and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
                                </when>
                                <when test="criterion.listValue">
                                    and ${criterion.condition}
                                    <foreach close=")" collection="criterion.value" item="listItem" open="("  separator=",">
                                        #{listItem}
                                    </foreach>
                                </when>
                            </choose>
                        </foreach>
                    </trim>
                </if>
            </foreach>
        </where>
    </sql>

注意事项：被加密的字段会比原始值长，需要检查原有字段长度是否够用；

回滚方案：再原来表的基础上创建字段 存储原始值，待历史数据都转化为密文失败时方便回滚；

-- 添加字段
alter table_a add column name_old varchar(128)  DEFAULT NULL COMMENT '老数据';

-- 数据备份
update table_a set name_old = name;

-- 数据回滚
update table_a set  name = name_old;

加解密测试：

//元数据
String data = "15226971825";
//秘钥
String key = "Qxq3fRvNkSyYzRdLFdR7Dg==";
String dataEncrypt = null;
try{
    byte[] keyByte = Base64.getDecoder().decode(key);
    byte[] cipherText = SM4Util.encrypt_ECB_Padding(keyByte, data.getBytes());
    dataEncrypt= Base64.getEncoder().encodeToString(cipherText);
    System.out.println(dataEncrypt);
}catch(Exception e){
    e.printStackTrace();
}
//解密
String jiemi = dataEncrypt;
try {
    byte[] jiemiInput = Base64.getDecoder().decode(jiemi);
    byte[] keyByte = Base64.getDecoder().decode(key);
    byte[] decryptedData = SM4Util.decrypt_ECB_Padding(keyByte, jiemiInput);
    String jm = new String(decryptedData);
    System.out.println(jm);
} catch (Exception e) {
    e.printStackTrace();
}