MySQL UDF 一例
目录
1. 功能
2. des_encrypt.cc 代码
3. 使用动态库编译
4. 复制到 MySQL 插件目录
5. 重启 MySQL
6. 创建函数并调用执行
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. 重启 MySQL
mysqladmin -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;