[PB life extension 02] It feels a bit messy. Let’s sort it out and encapsulate an Oracle package. Put encryption and decryption together. The package name is BLM_CRYPTO.
1 Baotou
create or replace package blm_crypto is
/*
-- Author: GROMS
-- Created : 2023-10-20 17:24:25
-- Purpose: Encryption and decryption toolkit
--Public type declarations
type <TypeName> is <Datatype>;
--Public constant declarations
<ConstantName> constant <Datatype> := <Value>;
--Public variable declarations
<VariableName> <Datatype>;
--Public function and procedure declarations
function <FunctionName>(<Parameter> <Datatype>) return <Datatype>;
*/
--MD5 encryption================================================ ================================
function md5(plaintext varchar2) return varchar2;
/*MD5() application example
select blm_crypto.md5('5217') from dual
--Result after encryption: 5A7B238BA0F6502E5D6BE14424B20DED
*/
--Aes + Base64 encryption============================================== ==========================
FUNCTION EnAes64(as_ysj IN VARCHAR2,as_key In Varchar2) RETURN VARCHAR2;
--Aes + Base64 decryption
FUNCTION DeAes64(as_ysj IN VARCHAR2, as_key IN VARCHAR2) RETURN VARCHAR2;
/*Aes application example, the key can only be 16 bits
select blm_crypto.enaes64('白龙马5217','blma5217blma5217') from dual;
--Encrypted result: mV4aaEnbaEllRvpH5WSiyw==
select blm_crypto.deaes64('mV4aaEnbaEllRvpH5WSiyw==','blma5217blma5217') from dual;
--Decrypted result: Bailongma 5217
*/
--Aes192 encryption================================================ ==============================
FUNCTION ENAES192( V_STR VARCHAR2 , V_KEY VARCHAR2) RETURN VARCHAR2;
--Aes192 decryption
FUNCTION DEAES192(V_STR VARCHAR2, V_KEY VARCHAR2) RETURN VARCHAR2;
/*Aes192 application example, the key can only be 24 bits
select blm_crypto.enaes192('白龙马5217','blma5217blma5217blma5217') from dual;
--Encrypted result: 347F4960FA347FE45F91B97B72114F76
select blm_crypto.deaes192('347F4960FA347FE45F91B97B72114F76','blma5217blma5217blma5217') from dual;
--Decrypted result: Bailongma 5217
*/
--DES encrypted string============================================ ==============================
function ENDES_STRING(input8x in varchar2,key8x in varchar2) return varchar2;
--DES decrypt string
function DEDES_STRING(encrypted_str in varchar2,key8x in varchar2) return varchar2;
/*DES application example, encrypt/decrypt string, the plaintext and key length of DES encryption and decryption algorithm must be a multiple of 8
select blm_crypto.endes_string('BLMa5217','blma5217') from dual;
--Encrypted result: D8BB696289F6177F
select blm_crypto.dedes_string('D8BB696289F6177F','blma5217') from dual;
--Decrypted result: BLMa5217
*/
--DES encrypts RAW. RAW is a variable-length data type. When transmitted on different platforms, all binary information is transmitted. Even if the character sets are different, no conversion is required.
function ENDES_RAW(input8x in varchar2,key8x in varchar2) return varchar2;
--DES decrypt RAW
function DEDES_RAW(encrypted_raw in varchar2,key8x in varchar2) return varchar2;
/*DES application example, encrypt/decrypt RAW, the plaintext and key length of DES encryption and decryption algorithm must be a multiple of 8
select blm_crypto.endes_raw('BLMa5217','blma5217') from dual;
--Encrypted result: D8BB696289F6177F
select blm_crypto.dedes_raw('D8BB696289F6177F','blma5217') from dual;
--Decrypted result: BLMa5217
*/
end blm_crypto;
2 Package body
create or replace package body blm_crypto is
/*
--Private type declarations
type <TypeName> is <Datatype>;
--Private constant declarations
<ConstantName> constant <Datatype> := <Value>;
--Private variable declarations
<VariableName> <Datatype>;
-- Function and procedure implementations
function <FunctionName>(<Parameter> <Datatype>) return <Datatype> is
<LocalVariable> <Datatype>;
begin
<Statement>;
return(<Result>);
end;
*/
--MD5 encryption function
function md5(plaintext varchar2) return varchar2
as --md5 encryption function
begin
return utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => plaintext));
end;
--Aes + Base64 encryption
FUNCTION EnAes64(as_ysj IN VARCHAR2,as_key In Varchar2) RETURN VARCHAR2
IS
encrypted_raw RAW (2000); -- store encrypted bytes
key_bytes_raw RAW (16); -- store the secret key 128 bits (16 bits)
--The Aes encryption and decryption methods here are: AES/ECB/PKCS5Padding. You can freely combine them by yourself, such as: AES/ECB/PKCS5Padding, etc.
encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_PKCS5;
iv_raw RAW (16); -- vector
BEGIN
-- Secret key
key_bytes_raw := UTL_I18N.STRING_TO_RAW(as_key,'AL32UTF8'); -- DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
-- vector
iv_raw := UTL_I18N.STRING_TO_RAW('9iuj87y2hbi5wxl1','AL32UTF8'); -- DBMS_CRYPTO.RANDOMBYTES (16);
-- Encryption process
encrypted_raw := DBMS_CRYPTO.ENCRYPT
(
src => UTL_I18N.STRING_TO_RAW (as_ysj, 'AL32UTF8'),
typ => encryption_type,
key => key_bytes_raw,
iv => iv_raw
);
-- Bas64 encryption, if you only need aes and no base64, just skip this step.
encrypted_raw := utl_encode.base64_encode(r => encrypted_raw);
return UTL_I18N.RAW_TO_CHAR (encrypted_raw, 'AL32UTF8');
END;
--Aes + Base64 decryption
FUNCTION DeAes64(as_ysj IN VARCHAR2, as_key IN VARCHAR2) RETURN VARCHAR2
IS
decrypted_raw RAW (2000); -- store decrypted bytes
key_bytes_raw RAW (16); -- store the secret key 128 bits (16 bits)
--The Aes encryption and decryption methods here are: AES/ECB/PKCS5Padding. You can freely combine them by yourself, such as: AES/ECB/PKCS5Padding, etc.
encryption_type PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES + DBMS_CRYPTO.CHAIN_ECB + DBMS_CRYPTO.PAD_PKCS5;
iv_raw RAW (16); -- vector
temp varchar2(1000); -- Temporary, the result of AES encryption and Base64
BEGIN
-- Secret key
key_bytes_raw := UTL_I18N.STRING_TO_RAW( as_key,'AL32UTF8'); -- DBMS_CRYPTO.RANDOMBYTES (num_key_bytes);
-- vector
iv_raw := UTL_I18N.STRING_TO_RAW('9iuj87y2hbi5wxl1','AL32UTF8'); -- DBMS_CRYPTO.RANDOMBYTES (16);
--Convert the encrypted string to row
decrypted_raw := utl_raw.cast_to_raw(c => as_ysj);
--Base64 decryption, if the source string is only aes encrypted and not base64 encoded, just skip this step.
decrypted_raw := utl_encode.base64_decode(r => decrypted_raw);
-- Aes decryption
decrypted_raw := DBMS_CRYPTO.DECRYPT
(
src => decrypted_raw,
typ => encryption_type,
key => key_bytes_raw,
iv => iv_raw
);
temp := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
RETURN temp;
END;
--Aes192 encryption, key is 24 bits
FUNCTION ENAES192( V_STR VARCHAR2 , V_KEY VARCHAR2) RETURN VARCHAR2
AS
V_KEY_RAW RAW(24);
V_STR_RAW RAW(2000);
V_RETURN_STR VARCHAR2(2000);
V_TYPE PLS_INTEGER;
BEGIN
V_KEY_RAW := UTL_I18N.STRING_TO_RAW(V_KEY,'UTF8') ;
V_STR_RAW := UTL_I18N.STRING_TO_RAW(V_STR,'UTF8') ;
V_TYPE := DBMS_CRYPTO.ENCRYPT_AES192 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
V_STR_RAW := DBMS_CRYPTO.ENCRYPT(SRC => V_STR_RAW, typ => V_TYPE, key => V_KEY_RAW);
V_RETURN_STR := RAWTOHEX(V_STR_RAW);
RETURN V_RETURN_STR;
END;
--Aes192 decryption, the key is 24 bits
FUNCTION DEAES192(V_STR VARCHAR2, V_KEY VARCHAR2) RETURN VARCHAR2
AS
V_KEY_RAW RAW(24);
V_STR_RAW RAW(2000);
V_RETURN_STR VARCHAR2(2000);
V_TYPE PLS_INTEGER;
BEGIN
V_KEY_RAW := UTL_I18N.STRING_TO_RAW(V_KEY,'UTF8') ;
V_STR_RAW := HEXTORAW(V_STR);
V_TYPE := DBMS_CRYPTO.ENCRYPT_AES192 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5;
V_STR_RAW := DBMS_CRYPTO.DECRYPT(SRC => V_STR_RAW, typ => V_TYPE, key => V_KEY_RAW);
V_RETURN_STR := UTL_I18N.RAW_TO_CHAR(V_STR_RAW,'UTF8');
RETURN V_RETURN_STR;
END;
--DES encrypted string
function ENDES_STRING(input8x in varchar2,key8x in varchar2) return varchar2
is
RetuenResult varchar2(4000);
encrypted_str VARCHAR2(4000);
begin
--The length of input_string and key_string must be a multiple of 8
dbms_obfuscation_toolkit.desencrypt(input_string => input8x, key_string => key8x, encrypted_string => encrypted_str);
RetuenResult := utl_raw.cast_to_raw(encrypted_str);
return(RetuenResult);
end;
--DES decrypt string
function DEDES_STRING(encrypted_str in varchar2,key8x in varchar2) return varchar2
is
-- encrypted_str is the return value of FN_ENDES_STRING()
--The length of key_string must be a multiple of 8
RetuenResult varchar2(4000);
decrypted_str VARCHAR2(4000);
begin
dbms_obfuscation_toolkit.desdecrypt(
input_string => utl_raw.cast_to_varchar2(encrypted_str),
key_string => key8x,
decrypted_string => decrypted_str);
RetuenResult := decrypted_str;
return(RetuenResult);
end;
--DES encrypted RAW,
function ENDES_RAW(input8x in varchar2,key8x in varchar2) return varchar2
is
--The length of input8x and key8x must be a multiple of 8
RetuenResult varchar2(4000);
encrypted_raw RAW(4000);
begin
dbms_obfuscation_toolkit.desencrypt
(input => utl_raw.cast_to_raw(input8x),
key => utl_raw.cast_to_raw(key8x),
encrypted_data => encrypted_raw);
RetuenResult := rawtohex(encrypted_raw);
return(RetuenResult);
end ;
--DES decrypt RAW
function DEDES_RAW(encrypted_raw in varchar2,key8x in varchar2) return varchar2
is
-- encrypted_raw is the return value of FN_ENDES_RAW()
--The length of key8x must be a multiple of 8
RetuenResult varchar2(4000);
decrypted_raw RAW(4000);
begin
dbms_obfuscation_toolkit.desdecrypt
(input => encrypted_raw,
key => utl_raw.cast_to_raw(key8x),
decrypted_data => decrypted_raw);
RetuenResult := utl_raw.cast_to_varchar2(decrypted_raw);
return(RetuenResult);
end ;
end blm_crypto;