达梦数据库SM4加解密函数编写

1.前言

首先确保是最新版本达梦数据库

2.我的算法

模式CBC ，填充PKCS#7
密钥：Zm9vYmFyMTIzNDU2Nzg5MA==
iv：MTIzNDU2Nzg5MGFiY2RlZg==
都是Base64格式

加密示范

明文：你好
加密后的密文：RBv5XeM4iVvTkGcyYn1UVQ==

在线测试：https://tool.hiofd.com/sm4-encrypt-online/

3.查询达梦数据库函数信息

SELECT * FROM V$CIPHERS

我们要用66

4.加解密SQL编写

加密

SELECT
  UTL_RAW.CAST_TO_VARCHAR2(
    UTL_ENCODE.BASE64_ENCODE(
      SF_ENCRYPT_VARBINARY2(
        66,
        UTL_I18N.STRING_TO_RAW('你好', 'UTF8'), --要加密的明文
        UTL_ENCODE.BASE64_DECODE(CHARTOBIN('Zm9vYmFyMTIzNDU2Nzg5MA==')),  -- 密钥 Base64 解码
        UTL_ENCODE.BASE64_DECODE(CHARTOBIN('MTIzNDU2Nzg5MGFiY2RlZg=='))   -- IV Base64 解码
      )
    )
  )
FROM DUAL;

解密

SELECT
  BINTOCHAR(
    SF_DECRYPT_VARBINARY2(
      66,
      UTL_ENCODE.BASE64_DECODE(CHARTOBIN('RBv5XeM4iVvTkGcyYn1UVQ==')), --要解密的明文
      UTL_ENCODE.BASE64_DECODE(CHARTOBIN('Zm9vYmFyMTIzNDU2Nzg5MA==')),  -- 密钥 Base64 解码
      UTL_ENCODE.BASE64_DECODE(CHARTOBIN('MTIzNDU2Nzg5MGFiY2RlZg=='))   -- IV Base64 解码
    )
  )
FROM DUAL;

5.封装成函数

加密函数：FN_SM4_ENCRYPT

CREATE OR REPLACE FUNCTION FN_SM4_ENCRYPT(
    P_PLAINTEXT IN VARCHAR2   -- 明文
) RETURN VARCHAR2
AS
    V_KEY    CONSTANT VARCHAR2(4000) := 'Zm9vYmFyMTIzNDU2Nzg5MA==';
    V_IV     CONSTANT VARCHAR2(4000) := 'MTIzNDU2Nzg5MGFiY2RlZg==';
    V_RESULT VARCHAR2(4000);
BEGIN
    SELECT UTL_RAW.CAST_TO_VARCHAR2(
             UTL_ENCODE.BASE64_ENCODE(
               SF_ENCRYPT_VARBINARY2(
                 66,
                 UTL_I18N.STRING_TO_RAW(P_PLAINTEXT, 'UTF8'),
                 UTL_ENCODE.BASE64_DECODE(CHARTOBIN(V_KEY)),
                 UTL_ENCODE.BASE64_DECODE(CHARTOBIN(V_IV))
               )
             )
           )
    INTO V_RESULT
    FROM DUAL;

    RETURN V_RESULT;
END;
/

解密函数：FN_SM4_DECRYPT

CREATE OR REPLACE FUNCTION FN_SM4_DECRYPT(
    P_CIPHERTEXT IN VARCHAR2  -- Base64 密文
) RETURN VARCHAR2
AS
    V_KEY    CONSTANT VARCHAR2(4000) := 'Zm9vYmFyMTIzNDU2Nzg5MA==';
    V_IV     CONSTANT VARCHAR2(4000) := 'MTIzNDU2Nzg5MGFiY2RlZg==';
    V_RESULT VARCHAR2(4000);
BEGIN
    SELECT BINTOCHAR(
             SF_DECRYPT_VARBINARY2(
               66,
               UTL_ENCODE.BASE64_DECODE(CHARTOBIN(P_CIPHERTEXT)),
               UTL_ENCODE.BASE64_DECODE(CHARTOBIN(V_KEY)),
               UTL_ENCODE.BASE64_DECODE(CHARTOBIN(V_IV))
             )
           )
    INTO V_RESULT
    FROM DUAL;

    RETURN V_RESULT;
END;
/

使用函数

SELECT FN_SM4_ENCRYPT('你好') from dual;
SELECT FN_SM4_DECRYPT(FN_SM4_ENCRYPT('你好')) from dual;