MySQL SM4 UDF 安装与使用文档
本文档详细介绍了将SM4加解密添加到MySQL插件中，可以在MySQL命令直接进行加解密。
一、功能概述
本 UDF 提供以下四个函数，用于在 MySQL 中进行 SM4 加解密操作：
注意：如果返回文字不是字符串就使用cast
| 函数名 | 功能说明 |
|---|---|
cast(sm4_ecb_encrypt(msg, key) as char) |
使用 SM4-ECB 模式加密明文，返回 Base64 编码的密文 |
sm4_ecb_decrypt(b64_ciphertext, key) |
解密 Base64 编码的 SM4-ECB 密文，返回明文 |
cast(sm4_sic_encrypt(msg, key, iv) as char) |
使用 SM4-SIC（CTR）模式加密明文，返回 Base64 编码的密文 |
sm4_sic_decrypt(b64_ciphertext, key, iv) |
解密 Base64 编码的 SM4-SIC 密文，返回明文 |
✅ 特点：
- 支持标准 SM4 算法（符合 GM/T 0002-2012）
- ECB 模式使用 PKCS#7 填充
- SIC 模式为流式加密，无需填充
- 所有输出均经 Base64 编码便于存储和传输
- 支持 128 位密钥（16 字节）
二、环境要求
- 操作系统：Linux（如 CentOS、Ubuntu 等）
- MySQL 版本：5.7 或 8.0（需支持 UDF）
- 开发工具：
gcc编译器make工具mysql-devel或libmysqlclient-dev包
- 权限：具备 root 或 sudo 权限以安装插件
三、编译与安装
1. 准备源码文件
将以下代码保存为 sm4_udf.c：
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdint.h>
#include <stdbool.h>
#include <mysql.h>
#include <ctype.h>
/* ===== SM4 constants ===== */
static const uint8_t SM4_SBOX[256] = {
0xd6,0x90,0xe9,0xfe,0xcc,0xe1,0x3d,0xb7,0x16,0xb6,0x14,0xc2,0x28,0xfb,0x2c,0x05,
0x2b,0x67,0x9a,0x76,0x2a,0xbe,0x04,0xc3,0xaa,0x44,0x13,0x26,0x49,0x86,0x06,0x99,
0x9c,0x42,0x50,0xf4,0x91,0xef,0x98,0x7a,0x33,0x54,0x0b,0x43,0xed,0xcf,0xac,0x62,
0xe4,0xb3,0x1c,0xa9,0xc9,0x08,0xe8,0x95,0x80,0xdf,0x94,0xfa,0x75,0x8f,0x3f,0xa6,
0x47,0x07,0xa7,0xfc,0xf3,0x73,0x17,0xba,0x83,0x59,0x3c,0x19,0xe6,0x85,0x4f,0xa8,
0x68,0x6b,0x81,0xb2,0x71,0x64,0xda,0x8b,0xf8,0xeb,0x0f,0x4b,0x70,0x56,0x9d,0x35,
0x1e,0x24,0x0e,0x5e,0x63,0x58,0xd1,0xa2,0x25,0x22,0x7c,0x3b,0x01,0x21,0x78,0x87,
0xd4,0x00,0x46,0x57,0x9f,0xd3,0x27,0x52,0x4c,0x36,0x02,0xe7,0xa0,0xc4,0xc8,0x9e,
0xea,0xbf,0x8a,0xd2,0x40,0xc7,0x38,0xb5,0xa3,0xf7,0xf2,0xce,0xf9,0x61,0x15,0xa1,
0xe0,0xae,0x5d,0xa4,0x9b,0x34,0x1a,0x55,0xad,0x93,0x32,0x30,0xf5,0x8c,0xb1,0xe3,
0x1d,0xf6,0xe2,0x2e,0x82,0x66,0xca,0x60,0xc0,0x29,0x23,0xab,0x0d,0x53,0x4e,0x6f,
0xd5,0xdb,0x37,0x45,0xde,0xfd,0x8e,0x2f,0x03,0xff,0x6a,0x72,0x6d,0x6c,0x5b,0x51,
0x8d,0x1b,0xaf,0x92,0xbb,0xdd,0xbc,0x7f,0x11,0xd9,0x5c,0x41,0x1f,0x10,0x5a,0xd8,
0x0a,0xc1,0x31,0x88,0xa5,0xcd,0x7b,0xbd,0x2d,0x74,0xd0,0x12,0xb8,0xe5,0xb4,0xb0,
0x89,0x69,0x97,0x4a,0x0c,0x96,0x77,0x7e,0x65,0xb9,0xf1,0x09,0xc5,0x6e,0xc6,0x84,
0x18,0xf0,0x7d,0xec,0x3a,0xdc,0x4d,0x20,0x79,0xee,0x5f,0x3e,0xd7,0xcb,0x39,0x48
};
static const uint32_t FK[4] = {0xa3b1bac6,0x56aa3350,0x677d9197,0xb27022dc};
static const uint32_t CK[32] = {
0x00070e15,0x1c232a31,0x383f464d,0x545b6269,0x70777e85,0x8c939aa1,0xa8afb6bd,0xc4cbd2d9,
0xe0e7eef5,0xfc030a11,0x181f262d,0x343b4249,0x50575e65,0x6c737a81,0x888f969d,0xa4abb2b9,
0xc0c7ced5,0xdce3eaf1,0xf8ff060d,0x141b2229,0x30373e45,0x4c535a61,0x686f767d,0x848b9299,
0xa0a7aeb5,0xbcc3cad1,0xd8dfe6ed,0xf4fb0209,0x10171e25,0x2c333a41,0x484f565d,0x646b7279
};
/* ===== helpers ===== */
static uint32_t rotl(uint32_t x,int n){return (x<<n)|(x>>(32-n));}
static uint32_t bytes_to_uint32(const uint8_t *b){return ((uint32_t)b[0]<<24)|((uint32_t)b[1]<<16)|((uint32_t)b[2]<<8)|(uint32_t)b[3];}
static void uint32_to_bytes(uint32_t v,uint8_t *b){b[0]=(v>>24)&0xFF;b[1]=(v>>16)&0xFF;b[2]=(v>>8)&0xFF;b[3]=v&0xFF;}
static uint32_t sbox_transform(uint32_t x){uint8_t b[4];uint32_to_bytes(x,b);b[0]=SM4_SBOX[b[0]];b[1]=SM4_SBOX[b[1]];b[2]=SM4_SBOX[b[2]];b[3]=SM4_SBOX[b[3]];return bytes_to_uint32(b);}
static uint32_t linear_transform(uint32_t x){return x^rotl(x,2)^rotl(x,10)^rotl(x,18)^rotl(x,24);}
static uint32_t round_function(uint32_t x0,uint32_t x1,uint32_t x2,uint32_t x3,uint32_t rk){return x0^linear_transform(sbox_transform(x1^x2^x3^rk));}
/* ===== key schedule ===== */
static void key_schedule(const uint8_t *key,uint32_t *rk){
int i;
uint32_t k[4];
k[0]=bytes_to_uint32(key)^FK[0];
k[1]=bytes_to_uint32(key+4)^FK[1];
k[2]=bytes_to_uint32(key+8)^FK[2];
k[3]=bytes_to_uint32(key+12)^FK[3];
for(i=0;i<32;i++){
uint32_t t=k[1]^k[2]^k[3]^CK[i];
t=sbox_transform(t);
t=t^rotl(t,13)^rotl(t,23);
rk[i]=k[0]^t;
k[0]=k[1];k[1]=k[2];k[2]=k[3];k[3]=rk[i];
}
}
/* ===== block encrypt/decrypt ===== */
static void sm4_encrypt_block(const uint8_t *in,const uint32_t *rk,uint8_t *out){
int i;
uint32_t x[4];
x[0]=bytes_to_uint32(in);x[1]=bytes_to_uint32(in+4);x[2]=bytes_to_uint32(in+8);x[3]=bytes_to_uint32(in+12);
for(i=0;i<32;i++){uint32_t tmp=round_function(x[0],x[1],x[2],x[3],rk[i]);x[0]=x[1];x[1]=x[2];x[2]=x[3];x[3]=tmp;}
uint32_to_bytes(x[3],out);uint32_to_bytes(x[2],out+4);uint32_to_bytes(x[1],out+8);uint32_to_bytes(x[0],out+12);
}
static void sm4_decrypt_block(const uint8_t *in,const uint32_t *rk,uint8_t *out){
uint32_t rk_rev[32];
int i;
for(i=0;i<32;i++) rk_rev[i]=rk[31-i];
sm4_encrypt_block(in,rk_rev,out);
}
/* ===== PKCS7 padding ===== */
static int pkcs7_pad(const uint8_t *in,int in_len,uint8_t *out){
int pad_len=16-(in_len%16);
memcpy(out,in,in_len);
for(int i=0;i<pad_len;i++) out[in_len+i]=pad_len;
return in_len+pad_len;
}
static int pkcs7_unpad(uint8_t *data,int len){
if (len<=0) return 0;
int pad=(int)data[len-1];
if(pad>0 && pad<=16) return len-pad;
return len;
}
/* ===== SIC/CTR mode (encrypt/decrypt identical) ===== */
static void sm4_sic_crypt(const uint8_t *in,int len,const uint8_t *key,const uint8_t *iv,uint8_t *out){
uint32_t rk[32];key_schedule(key,rk);
uint8_t counter[16],stream[16];int i,j;
memcpy(counter,iv,16);
int blocks=(len+15)/16;
for(i=0;i<blocks;i++){
sm4_encrypt_block(counter,rk,stream);
int block_size=(i==blocks-1)?(len-i*16):16;
for(j=0;j<block_size;j++) out[i*16+j]=in[i*16+j]^stream[j];
for(j=15;j>=0;j--){ if(++counter[j]) break; }
}
}
/* ===== ECB mode wrapper (with PKCS7 padding) ===== */
static void sm4_ecb_encrypt_impl(const uint8_t *in,int len,const uint8_t *key,uint8_t *out){
uint32_t rk[32];key_schedule(key,rk);
uint8_t block[512]; /* large buffer to be safe for UDF use */
int padded_len = pkcs7_pad(in,len,block);
for(int i=0;i<padded_len;i+=16) sm4_encrypt_block(block+i,rk,out+i);
}
static void sm4_ecb_decrypt_impl(const uint8_t *in,int len,const uint8_t *key,uint8_t *out){
uint32_t rk[32];key_schedule(key,rk);
for(int i=0;i<len;i+=16) sm4_decrypt_block(in+i,rk,out+i);
int new_len=pkcs7_unpad(out,len);
if(new_len>=0) out[new_len]='\0';
}
/* ===== base64 ===== */
static const char base64_table[]="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
static char* base64_encode(const uint8_t *in,int len){
int out_len = ((len + 2) / 3) * 4;
char *out = (char*)malloc(out_len + 1);
if(!out) return NULL;
int i,j=0;
for(i=0;i<len;i+=3){
int val=0,count = (len - i >= 3) ? 3 : (len - i),k;
for(k=0;k<count;k++) val=(val<<8)|in[i+k];
val <<= (3-count)*8;
out[j++]=base64_table[(val>>18)&0x3F];
out[j++]=base64_table[(val>>12)&0x3F];
out[j++]= (count>1) ? base64_table[(val>>6)&0x3F] : '=';
out[j++]= (count>2) ? base64_table[val&0x3F] : '=';
}
out[j]='\0'; return out;
}
static uint8_t decode_table[256];
static void build_decode_table(){
static int inited=0;
if(inited) return; inited=1;
memset(decode_table,0x80,sizeof(decode_table));
for(int i=0;i<64;i++) decode_table[(unsigned char)base64_table[i]] = i;
decode_table[(unsigned char)'='] = 0;
}
static uint8_t* base64_decode(const char *in,int *out_len){
build_decode_table();
int len = (int)strlen(in);
uint8_t *out = (uint8_t*)malloc(len*3/4 + 1);
if(!out) return NULL;
int i,j=0;
for(i=0;i<len;i+=4){
uint32_t a = decode_table[(unsigned char)in[i]];
uint32_t b = decode_table[(unsigned char)in[i+1]];
uint32_t c = (i+2 < len) ? decode_table[(unsigned char)in[i+2]] : 0;
uint32_t d = (i+3 < len) ? decode_table[(unsigned char)in[i+3]] : 0;
uint32_t val = (a<<18) | (b<<12) | (c<<6) | d;
out[j++] = (val>>16)&0xFF;
if(in[i+2] != '=') out[j++] = (val>>8)&0xFF;
if(in[i+3] != '=') out[j++] = val&0xFF;
}
*out_len = j;
return out;
}
/* safe strdup */
static char* safe_strdup(const char *s){
if(!s) return NULL;
size_t l = strlen(s)+1;
char *p = (char*)malloc(l);
if(p) memcpy(p,s,l);
return p;
}
/* ===== UDF functions =====
* Names must be e.g. sm4_ecb_encrypt_init / sm4_ecb_encrypt etc.
* */
/* sm4_ecb_encrypt_init */
bool sm4_ecb_encrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message){
if(args->arg_count != 2){ strcpy(message,"expected 2 arguments"); return true; }
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = STRING_RESULT;
initid->maybe_null = 0;
return false;
}
char* sm4_ecb_encrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length,
char *is_null, char *error){
(void)initid; (void)is_null; (void)error;
const char *msg = (const char*)args->args[0];
const char *key = (const char*)args->args[1];
if(!msg || !key) return NULL;
int mlen = (int)strlen(msg);
int blocks = ((mlen + 15) / 16) * 16;
uint8_t *cipher = (uint8_t*)malloc(blocks);
if(!cipher) return NULL;
sm4_ecb_encrypt_impl((const uint8_t*)msg, mlen, (const uint8_t*)key, cipher);
char *b64 = base64_encode(cipher, blocks);
free(cipher);
if(!b64) return NULL;
*length = strlen(b64);
return b64; /* caller (MySQL) will not free; typical pattern returns malloc'ed ptr */
}
/* sm4_ecb_decrypt_init */
bool sm4_ecb_decrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message){
if(args->arg_count != 2){ strcpy(message,"expected 2 arguments"); return true; }
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = STRING_RESULT;
initid->maybe_null = 0;
return false;
}
char* sm4_ecb_decrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length,
char *is_null, char *error){
(void)initid; (void)is_null; (void)error;
const char *b64 = (const char*)args->args[0];
const char *key = (const char*)args->args[1];
if(!b64 || !key) return NULL;
int clen = 0;
uint8_t *cipher = base64_decode(b64, &clen);
if(!cipher) return NULL;
/* clen must be multiple of 16 */
uint8_t *plain = (uint8_t*)malloc(clen + 1);
if(!plain){ free(cipher); return NULL; }
sm4_ecb_decrypt_impl(cipher, clen, (const uint8_t*)key, plain);
int new_len = pkcs7_unpad(plain, clen);
if(new_len < 0) new_len = 0;
plain[new_len] = '\0';
free(cipher);
char *ret = safe_strdup((char*)plain);
free(plain);
if(!ret) return NULL;
*length = strlen(ret);
return ret;
}
/* sm4_sic_encrypt_init */
bool sm4_sic_encrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message){
if(args->arg_count != 3){ strcpy(message,"expected 3 arguments"); return true; }
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = STRING_RESULT;
args->arg_type[2] = STRING_RESULT;
initid->maybe_null = 0;
return false;
}
char* sm4_sic_encrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length,
char *is_null, char *error){
(void)initid; (void)is_null; (void)error;
const char *msg = (const char*)args->args[0];
const char *key = (const char*)args->args[1];
const char *iv = (const char*)args->args[2];
if(!msg || !key || !iv) return NULL;
int mlen = (int)strlen(msg);
uint8_t *cipher = (uint8_t*)malloc(mlen);
if(!cipher) return NULL;
sm4_sic_crypt((const uint8_t*)msg, mlen, (const uint8_t*)key, (const uint8_t*)iv, cipher);
char *b64 = base64_encode(cipher, mlen);
free(cipher);
if(!b64) return NULL;
*length = strlen(b64);
return b64;
}
/* sm4_sic_decrypt_init */
bool sm4_sic_decrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message){
if(args->arg_count != 3){ strcpy(message,"expected 3 arguments"); return true; }
args->arg_type[0] = STRING_RESULT;
args->arg_type[1] = STRING_RESULT;
args->arg_type[2] = STRING_RESULT;
initid->maybe_null = 0;
return false;
}
char* sm4_sic_decrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length,
char *is_null, char *error){
(void)initid; (void)is_null; (void)error;
const char *b64 = (const char*)args->args[0];
const char *key = (const char*)args->args[1];
const char *iv = (const char*)args->args[2];
if(!b64 || !key || !iv) return NULL;
int clen=0;
uint8_t *cipher = base64_decode(b64,&clen);
if(!cipher) return NULL;
uint8_t *plain = (uint8_t*)malloc(clen + 1);
if(!plain){ free(cipher); return NULL; }
sm4_sic_crypt(cipher, clen, (const uint8_t*)key, (const uint8_t*)iv, plain);
plain[clen] = '\0';
free(cipher);
char *ret = safe_strdup((char*)plain);
free(plain);
if(!ret) return NULL;
*length = clen;
return ret;
}
2. 编译共享库
执行以下命令编译生成 .so 插件文件：
gcc -std=c99 -fPIC -shared -O2 -Wall -fvisibility=default \
$(mysql_config --cflags) \
-o sm4_udf.so sm4_udf.c \
$(mysql_config --libs)
⚠️ 注意：
mysql_config必须在 PATH 中，否则需手动指定路径。mysql_config 在MySQL的bin目录下
3. 复制插件到 MySQL 插件目录
cp sm4_udf.so $(mysql_config --plugindir)/
确认复制成功：
ls -l $(mysql_config --plugindir)/sm4_udf.so
4. 验证符号导出（可选）
检查是否包含所需的函数符号：
nm -D $(mysql_config --plugindir)/sm4_udf.so | grep sm4
应看到如下符号：
sm4_ecb_encrypt
sm4_ecb_decrypt
sm4_sic_encrypt
sm4_sic_decrypt
...
四、在 MySQL 中注册 UDF 函数
登录 MySQL（需有 SUPER 权限或 CREATE FUNCTION 权限）：
mysql -u root -p
执行以下 SQL 注册函数：
-- 删除旧函数（如果存在）
DROP FUNCTION IF EXISTS sm4_ecb_encrypt;
DROP FUNCTION IF EXISTS sm4_ecb_decrypt;
DROP FUNCTION IF EXISTS sm4_sic_encrypt;
DROP FUNCTION IF EXISTS sm4_sic_decrypt;
-- 创建新函数
CREATE FUNCTION sm4_ecb_encrypt RETURNS STRING SONAME 'sm4_udf.so';
CREATE FUNCTION sm4_ecb_decrypt RETURNS STRING SONAME 'sm4_udf.so';
CREATE FUNCTION sm4_sic_encrypt RETURNS STRING SONAME 'sm4_udf.so';
CREATE FUNCTION sm4_sic_decrypt RETURNS STRING SONAME 'sm4_udf.so';
✅ 成功后，这些函数即可在 SQL 中使用。
五、使用示例
1. SM4-ECB 模式加解密
-- 加密，加密必须使用cast转换为字符串，as不是别名，是类型，必须char。
select cast(sm4_ecb_encrypt('Hello World', '1234567890abcdef') as char) as encrypted;
-- 示例输出：
-- '9iCY9HhI0qQT+geIqZ8rcw=='
-- 解密
select sm4_ecb_decrypt('9iCY9HhI0qQT+geIqZ8rcw==', '1234567890abcdef') as decrypted;
-- 输出：Hello World
🔐 注意：
- 密钥必须为 16 字节（128 位），不足或超过都会导致错误行为。
- ECB 不推荐用于敏感数据批量加密（模式不安全）。
2. SM4-SIC（CTR）模式加解密
-- 加密（需要 IV，也必须是 16 字节）
select cast(sm4_sic_encrypt('Hello World', '1234567890abcdef', 'fedcba0987654321') as char) as encrypted;
-- 示例输出：
-- 'EVpRnWZdV0qpdHY='
-- 解密（必须使用相同的 key 和 iv）
select sm4_sic_decrypt('EVpRnWZdV0qpdHY=', '1234567890abcdef', 'fedcba0987654321') asdecrypted;
-- 输出：Hello World
✅ SIC/CTR 模式优势：
- 可并行加密
- 不需要填充
- 更适合大数据量加密
六、参数说明
| 参数 | 类型 | 要求 |
|---|---|---|
msg / b64_ciphertext |
VARCHAR 或 TEXT |
明文或 Base64 密文字符串 |
key |
CHAR(16) 或 VARCHAR |
必须为 16 字节长度的密钥 |
iv |
CHAR(16) 或 VARCHAR |
初始向量，SIC 模式必需，建议随机且唯一 |
❗ 重要提示：
- 所有密钥和 IV 都应为 精确 16 字节。
- 若使用字符串作为密钥，请确保其长度为 16 字符（如十六进制需先解码）。
七、卸载 UDF
当不再需要时，可执行：
DROP FUNCTION IF EXISTS sm4_ecb_encrypt;
DROP FUNCTION IF EXISTS sm4_ecb_decrypt;
DROP FUNCTION IF EXISTS sm4_sic_encrypt;
DROP FUNCTION IF EXISTS sm4_sic_decrypt;
然后删除 .so 文件：
sudo rm $(mysql_config --plugindir)/sm4_udf.so
结语
本 UDF 实现了轻量级、高效、标准兼容的 SM4 加解密能力，适用于需要在数据库层面进行国密加密的场景（如字段加密、日志脱敏等）。请结合业务需求合理使用加密模式，并加强密钥安全管理。