MySQL UDF 一例

目录1. 功能2. des_encrypt.cc 代码3. 使用动态库编译4. 复制到 MySQL 插件目录5. 重启 MySQL6. 创建函数并调用执行1. 功能在 MySQL 中实现 DES/ECB/PKCS5Padding 加密算法。2. des_encrypt.cc 代码#include stdio.h #include stdlib.h #include string.h #include mysql.h #include openssl/des.h #include openssl/md5.h #ifndef my_bool #define my_bool unsigned char #endif extern C { my_bool direct_checksum_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void direct_checksum_deinit(UDF_INIT *initid); char *direct_checksum(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); } my_bool direct_checksum_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args-arg_count ! 4) { strcpy(message, 需要4个参数: userID, diamondCount, updateTime, key); return 1; } initid-max_length 32; return 0; } void direct_checksum_deinit(UDF_INIT *initid) {} char *direct_checksum(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error) { if (!args-args[0] || !args-args[1] || !args-args[2] || !args-args[3]) { *is_null 1; return NULL; } // 获取参数 long long userID *((long long*)args-args[0]); long long diamondCount *((long long*)args-args[1]); long long updateTime *((long long*)args-args[2]); char *key args-args[3]; // 1. 构建字符串 char plaintext[256]; snprintf(plaintext, sizeof(plaintext), %lld,%lld,%lld, userID, diamondCount, updateTime); // 2. DES加密 DES_key_schedule schedule; DES_cblock key_block; memset(key_block, 0, sizeof(key_block)); strncpy((char*)key_block, key, 8); DES_set_key_unchecked(key_block, schedule); int text_len (int)strlen(plaintext); int pad_len 8 - (text_len % 8); if (pad_len 0) pad_len 8; int total_len text_len pad_len; // 分配内存 unsigned char *padded_input (unsigned char*)malloc(total_len); unsigned char *des_output (unsigned char*)malloc(total_len); memcpy(padded_input, plaintext, text_len); for (int i 0; i pad_len; i) { padded_input[text_len i] (unsigned char)pad_len; } // ECB模式加密 for (int i 0; i total_len; i 8) { DES_ecb_encrypt((DES_cblock*)(padded_input i), (DES_cblock*)(des_output i), schedule, DES_ENCRYPT); } // 3. 转16进制字符串 char hex_str[1024]; for (int i 0; i total_len; i) { sprintf(hex_str i * 2, %02x, des_output[i]); } hex_str[total_len * 2] \0; // 4. MD5加密 MD5_CTX md5_ctx; unsigned char md5_digest[16]; MD5_Init(md5_ctx); MD5_Update(md5_ctx, hex_str, (unsigned long)strlen(hex_str)); MD5_Final(md5_digest, md5_ctx); // 5. 关键直接写入result缓冲区作为字符串 for (int i 0; i 16; i) { sprintf(result i * 2, %02x, md5_digest[i]); } result[32] \0; // null终止 // 6. 告诉MySQL这是32个字符的字符串 *length 32; // 清理内存 free(padded_input); free(des_output); return result; }3. 使用动态库编译g -fPIC -shared -o des_encrypt.so des_encrypt.cc \ -I/home/mysql/mysql-8.0.22/include \ -I/usr/include/openssl \ -L/home/mysql/mysql-8.0.22/lib \ -Wl,-rpath,/home/mysql/mysql-8.0.22/lib \ -lmysqlclient -lssl -lcrypto4. 复制到 MySQL 插件目录cp des_encrypt.so /home/mysql/mysql-8.0.22/lib/plugin/ chmod 755 /home/mysql/mysql-8.0.22/lib/plugin/des_encrypt.so5. 重启 MySQLmysqladmin -uroot -p123456 -S /data/3306/mysqldata/mysql.sock shutdown mysqld_safe --defaults-file/home/mysql/my.cnf 6. 创建函数并调用执行use test; create function direct_checksum returns string soname des_encrypt.so; select UserID,DiamCount,UpdateTimeJava, cast(direct_checksum(UserID,DiamondCount,UpdateTimeJava, gnbihUyr) as char) CheckSum from t1 limit 20;