Spring-data-jpa中用@ColumnTransformer注解加密，可是解密后返回的数据为null

Question

问题描述

Springboot + Spring-data-jpa中用@ColumnTransformer注解将password字段加密，可是解密后返回的数据为null

相关代码

实体类：
package top.inger.JpaDemo.domain;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.ColumnTransformer;
import org.hibernate.validator.constraints.Length;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Entity
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value = {"adminRegTime", "adminModTime"}, allowGetters = true)
@Table(name = "WINES_ADMIN")
public class Admin {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private int adminId;

@Column(name = "username")
@NotBlank(message = "管理员用户名不能为空!")
@Length(min = 4, max = 8)
private String adminUsName;

@Column(name = "password")
@NotBlank(message = "管理员密码不能为空!")
@Length(min = 3, max = 6)
@ColumnTransformer(
        write = "HEX(AES_ENCRYPT(?, 'password'))",
        read = "AES_DECRYPT(UNHEX(password),'password')"
)
private String adminPassword;

@Column(name = "name")
@NotBlank(message = "管理员姓名不能为空!")
@Length(min = 2, max = 20)
private String adminName;

@Column(name = "phone")
@NotBlank(message = "手机号不能为空!")
@Length(min = 11,max = 11)
private String adminPhone;

@Column(name = "status")
private Byte adminStatus=0;

@CreatedDate
@Temporal(TemporalType.TIMESTAMP)
@Column(nullable = false, updatable = false,name = "regTime")
private Date adminRegTime;

@LastModifiedDate
@Temporal(TemporalType.TIMESTAMP)
@Column(nullable = false,name = "lastModTime")
private Date adminLastModTime;

}

具体字段使用方法：

@Column(name = "password")
@NotBlank(message = "管理员密码不能为空!")
@Length(min = 3, max = 6)
@ColumnTransformer(
        write = "HEX(AES_ENCRYPT(?, 'password'))",
        read = "AES_DECRYPT(UNHEX(password),'password')"
)
private String adminPassword;

repository：
package top.inger.JpaDemo.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.stereotype.Repository;
import top.inger.JpaDemo.domain.Admin;

@Repository
public interface AdminRepository extends JpaRepository<Admin, Integer>, JpaSpecificationExecutor<Admin> {
}

controller：
@RestController
@RequestMapping("/admin")
public class AdminController {

private final AdminRepository adminRepository;

@Autowired
public AdminController(AdminRepository adminRepository) {
    this.adminRepository = adminRepository;
}

/**
 * 创建一个用户  —>  POST:  /admin/create
 */
@PostMapping("/create")
public Admin createAdmin(@RequestBody @Valid Admin admin) {
    return adminRepository.saveAndFlush(admin);
}

/**
 * 查询某个id的用户  —>  GET:  /admin/findById/{adminId}
 */
@GetMapping("/findById/{adminId}")
public Optional<Admin> findAdminById(@PathVariable(value = "adminId") int id) {
    return adminRepository.findById(id);
}

你期待的结果是什么？实际看到的错误信息又是什么？

创建一个管理员：

查找id为3的管理员：

idea中日志打印：
Hibernate: select admin0_.id as id1_0_0_, admin0_.lastModTime as lastModT2_0_0_, admin0_.name as name3_0_0_, AES_DECRYPT(UNHEX(admin0_.password),'admin0_.password') as password4_0_0_, admin0_.phone as phone5_0_0_, admin0_.regTime as regTime6_0_0_, admin0_.status as status7_0_0_, admin0_.username as username8_0_0_ from WINES_ADMIN admin0_ where admin0_.id=?

应该返回为sdfsd的密码，可是返回结果为null。

望大神帮忙！！！

Answer 1

@ColumnTransformer注解里补充一下forColumn值试试呢

@Column(name = "password")
@NotBlank(message = "管理员密码不能为空!")
@Length(min = 3, max = 6)
@ColumnTransformer(
        forColumn = "password",
        write = "HEX(AES_ENCRYPT(?, 'password'))",
        read = "AES_DECRYPT(UNHEX(password),'password')"
)
private String adminPassword;

Answer 2

目前数据操作基于 hibernate

HEX(AES_ENCRYPT(?, 'password'))

这段代码里的password 是加密的盐吧。

从安全角度考虑，将加密盐设置到环境变量。

编辑 application.yml 文件

databaseslat: ${databaseSlat}

@Configuration
@EnableAutoConfiguration
public class DataBaseSlatConfig {
    public static String DATABASE_SLAT;
    @Value("${databaseSlat}")
    public void setDatabaseSlat(String databaseSlat) {
        DATABASE_SLAT = databaseSlat;
    }
}

我目前遇到问题是: write 不能传入变量？例如

HEX(AES_ENCRYPT(?, DataBaseSlatConfig.DATABASE_SLAT))

基于该方案是否有其他写法或者有其他方案

Answer 3

我就想问下，大佬解决了吗