admin 发布时间:2022-02-21 分类:记事 阅读:4661次 添加评论
需求:需要将现在的用户关键信息在数据库层面做加密。
--安装pgcrypto扩展
create extension pgcrypto; select * from pg_available_extensions --查看pgcrypto是否安装成功
--加密
select encode(encrypt('17817719973'::bytea,'0000000ctsdev123','aes-ecb'),'base64')
--解密
select convert_from(decrypt(decode('gGBEj3ScUIF1Ow08cftkpA==','base64'),'0000000ctsdev123','aes-ecb'),'SQL_ASCII');
--为了以后方便直接建立一个加密函数(cts_encrypt) 参数1为明文,参数2为密钥
CREATE or replace FUNCTION cts_encrypt(var1 VARCHAR,var2 varchar(16)) RETURNS VARCHAR as $$ BEGIN RETURN (SELECT encode(encrypt(var1::bytea,var2 :: bytea,'aes-ecb'),'base64')); END; $$ language plpgsql;
测试加密:
select cts_encrypt('18700000000','0000000ctsdev123')
--解密函数cts_decrypt 参数1为密文,参数2为密钥
CREATE or replace FUNCTION cts_decrypt(var1 VARCHAR,var2 varchar(16)) RETURNS VARCHAR as $$ BEGIN RETURN (select convert_from(decrypt(decode(var1,'base64'),var2 :: bytea,'aes-ecb'),'SQL_ASCII')); END; $$ language plpgsql;
测试解密:
select cts_decrypt('XyFXGdD/gt8Fjc+lsjWLKg==','0000000ctsdev123')
备份用户信息表:
create table base_user_detail as ( select * from base_user); ALTER TABLE base_user_detail ADD PRIMARY KEY (id) ;
加密用户表里面的手机号信息
update base_user_detail set mobile=cts_encrypt(mobile,'0000000ctsdev123')
对应的Java应用层面的加解密:
private static String MODEL = "AES/ECB/PKCS5Padding"; private static String useKey ="0000000ctsdev123"; public static String encrypt(String content) { if(StringUtils.isEmpty(content)){ return content; } String result = content; try { byte[] contentBytes = content.getBytes("UTF-8"); SecretKeySpec skeySpec = new SecretKeySpec(useKey.getBytes("UTF-8"), "AES"); Cipher cipher = Cipher.getInstance(MODEL); cipher.init(Cipher.ENCRYPT_MODE, skeySpec); byte[] encryptResult = cipher.doFinal(contentBytes); result = Base64.encodeBase64String(encryptResult); //替换\r \n result = result.replace("\n", "").replace("\r", ""); } catch (Exception ex) { throw new RuntimeException(ex); } return result; } public static String decrypt(String content){ if(StringUtils.isEmpty(content)){ return content; } String result = content; byte[] contentBytes =null; try{ if(content.length()%4==0){ contentBytes =Base64.decodeBase64(content); }else{ throw new RuntimeException("字符串"+content+"不是base64编码过的字符串!"); } } catch (Exception ex) { throw new RuntimeException(ex); } if(contentBytes!=null){ try{ SecretKeySpec skeySpec = new SecretKeySpec(useKey.getBytes("UTF-8"), "AES"); Cipher cipher = Cipher.getInstance(MODEL); cipher.init(Cipher.DECRYPT_MODE, skeySpec); byte[] decryptResult = cipher.doFinal(contentBytes); if (decryptResult != null) { result = new String(decryptResult, "UTF-8"); } } catch (Exception ex) { throw new RuntimeException(ex); } } return result; }
发表评论:
◎欢迎您的参与讨论。