前言java
Github:https://github.com/HealerJeanmysql
博客:http://blog.healerjean.comgit
爲了保護用戶隱私,咱們須要對數據庫用戶關鍵數據,入庫加密,取出來解密。爲了咱們系統自身的安全數據庫鏈接用戶名和密碼都要加解密github
#################################### ### DB #################################### #durid spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver spring.datasource.druid.initialSize=5 spring.datasource.druid.minIdle=5 spring.datasource.druid.maxActive=20 spring.datasource.druid.maxWait=60000 spring.datasource.druid.timeBetweenEvictionRunsMillis=60000 spring.datasource.druid.minEvictableIdleTimeMillis=300000 spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL spring.datasource.druid.testWhileIdle=true spring.datasource.druid.testOnBorrow=false spring.datasource.druid.testOnReturn=false ##################################### #### DB #################################### spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/hlj_demo?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true spring.datasource.druid.username=GCBeAUOZNANpmXfIUPO42qx/dQP80Lae3BI7ABxQN2AzWhgQAG+S6Dhe spring.datasource.druid.password=GCAfE1p20be+BX5TZsVlFe1/T1bQ+f2IhnjqOQKe7CJT7xgQ8YOQrf7U #################################### #是否須要數據鏈接加密 spring.datasource.encrypt=true
package com.fintech.confin.web.config; import com.alibaba.druid.pool.DruidDataSource; import com.fintech.confin.sensitivity.KeycenterUtils; import org.springframework.beans.factory.annotation.Value; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.sql.DataSource; /** * @author HealerJean * @ClassName DateSourceConfig * @date 2020/4/9 10:43. * @Description */ @Configuration public class DateSourceConfig { @Value("${spring.datasource.druid.driver-class-name}") private String driverClassName; @Value("${spring.datasource.druid.url}") private String dbUrl; @Value("${spring.datasource.druid.username}") private String username; @Value("${spring.datasource.druid.password}") private String password; @Value("${spring.datasource.druid.initialSize}") private int initialSize; @Value("${spring.datasource.druid.minIdle}") private int minIdle; @Value("${spring.datasource.druid.maxActive}") private int maxActive; @Value("${spring.datasource.druid.maxWait}") private int maxWait; @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}") private int timeBetweenEvictionRunsMillis; @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}") private int minEvictableIdleTimeMillis; @Value("${spring.datasource.druid.validationQuery}") private String validationQuery; @Value("${spring.datasource.druid.testWhileIdle}") private boolean testWhileIdle; @Value("${spring.datasource.druid.testOnBorrow}") private boolean testOnBorrow; @Value("${spring.datasource.druid.testOnReturn}") private boolean testOnReturn; @Value("${spring.datasource.encrypt}") private boolean encrypt; @Bean(name = "dataSource") public DataSource dataSource(KeycenterUtils keycenterUtils) { DruidDataSource datasource = new DruidDataSource(); datasource.setDriverClassName(driverClassName); datasource.setUrl(dbUrl); if (encrypt) { datasource.setUsername(keycenterUtils.decrypt(username)); datasource.setPassword(keycenterUtils.decrypt(password)); } else { datasource.setUsername(username); datasource.setPassword(password); } datasource.setInitialSize(initialSize); datasource.setMinIdle(minIdle); datasource.setMaxActive(maxActive); datasource.setMaxWait(maxWait); datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); datasource.setValidationQuery(validationQuery); datasource.setTestWhileIdle(testWhileIdle); datasource.setTestOnBorrow(testOnBorrow); datasource.setTestOnReturn(testOnReturn); return datasource; } }
KeyCenterUtils
:加解密工具類package com.healerjean.proj.config.keycenter.one; import org.springframework.stereotype.Service; import java.util.Base64; /** * @author HealerJean * @ClassName AES * @date 2020/4/9 14:28. * @Description */ @Service public class KeyCenterUtils { /** * 本身寫加密邏輯 */ public String encrypt(String src) { try { String result = Base64.getEncoder().encodeToString(src.getBytes("UTF-8")); return result; } catch (Exception e) { throw new RuntimeException("encrypt fail!", e); } } /** * 本身寫解密邏輯 */ public String decrypt(String src) { try { byte[] asBytes = Base64.getDecoder().decode(src); String result = new String(asBytes, "UTF-8"); return result; } catch (Exception e) { throw new RuntimeException("decrypt fail!", e); } } }
CustomTypeHandler
數據庫字段加解密控制器package com.healerjean.proj.config.keycenter.one; /** * @author HealerJean * @ClassName AESTypeHandler * @date 2020/4/9 14:27. * @Description */ import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.commons.lang3.StringUtils; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; @Service public class CustomTypeHandler<T> extends BaseTypeHandler<T> { @Autowired private KeyCenterUtils keyCenterUtils; public CustomTypeHandler() { } @Override public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException { ps.setString(i, this.keyCenterUtils.encrypt((String)parameter)); } @Override public T getNullableResult(ResultSet rs, String columnName) throws SQLException { String columnValue = rs.getString(columnName); //有一些多是空字符 return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue); } @Override public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException { String columnValue = rs.getString(columnIndex); return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue); } @Override public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { String columnValue = cs.getString(columnIndex); return StringUtils.isBlank(columnValue) ? (T)columnValue : (T)this.keyCenterUtils.decrypt(columnValue); } }
package com.healerjean.proj.data.entity; import com.baomidou.mybatisplus.annotation.TableField; import com.baomidou.mybatisplus.annotation.TableName; import com.healerjean.proj.config.keycenter.one.CustomTypeHandler; import lombok.Data; import java.util.Date; @Data @TableName(autoResultMap = true) //有了這個BaseMapper查詢的結果才能解密 public class User { private Long id; private String name; private Integer age; //有了這個數據庫BaseMapper插入的時候才能加密 @TableField(typeHandler = CustomTypeHandler.class) private String telPhone; @TableField(typeHandler = CustomTypeHandler.class) private String email; private Date createDate; private Date createTime; }
若是不是mybatisPlus的 BaseMapper內部的方法,則須要咱們本身放入咱們自定義的
typeHandler
web
@Results({ @Result(column = "email", property = "email", typeHandler = CustomTypeHandler.class), @Result(column = "tel_phone", property = "telPhone", typeHandler = CustomTypeHandler.class)}) @Select("select * from user where id = #{id}") List<User> selectDncryptList(Long id);
User中的數據都是正常的 。不是密文。由於咱們只講入庫的數據設置了密文。並不會改變User對象自己spring
@Test public void encrypt(){ List<User> users = null ; //插入數據 User user = new User(); user.setName("name"); user.setAge(12); user.setEmail("healerjean@gmail.com"); user.setTelPhone("18841256"); userMapper.insert(user); //更新 user.setEmail("12456@gmail.com"); userMapper.updateById(user); //查詢 :列表查詢 users = userMapper.selectList(null); System.out.println(users); //查詢 :根據Id查詢 User user1 = userMapper.selectById(user.getId()); System.out.println(user1); //自定義sql查詢 users = userMapper.selectDncryptList(user.getId()); System.out.println(users); }
由於數據庫中是密文,因此查詢的時候,須要咱們先加密後才能查sql
// 根據敏感字段查詢 Wrapper<User> userWrapper = new QueryWrapper<User>().lambda() .select(User::getEmail) .eq(User::getEmail, keyCenterUtils.encrypt("healerjean@gmail.com")); users = userMapper.selectList(userWrapper); System.out.println(users);