数据库内部提供的摘要函数MD5/SHA/CRC与现有的AES/DES加密函数以及不能满足我们的需求,所以我们有必要开发外挂插件实现数据加密。
这里有一个例子,是我早年开发的 https://github.com/netkiller/mysql-safenet-plugin 这个UDF是链接 Safenet设备,实现数据库加密记录。
saftnet.h
my_bool safenet_encrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message); char *safenet_encrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); void safenet_encrypt_deinit(UDF_INIT *initid); my_bool safenet_decrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message); char *safenet_decrypt(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); void safenet_decrypt_deinit(UDF_INIT *initid); my_bool safenet_config_init(UDF_INIT *initid, UDF_ARGS *args, char *message); char *safenet_config(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); void safenet_config_deinit(UDF_INIT *initid);
safenet.c
/* Homepage: http://netkiller.github.io/ Author: netkiller<netkiller@msn.com> */ #include <mysql.h> #include <string.h> #include <stdio.h> #include <stdlib.h> #include <curl/curl.h> #include "safenet.h" #define SAFENET_URL "http://localhost/safe/interface" #define SAFENET_KEY "Web01-key" char *safe_url; char *safe_key; void get_safenet_env(){ if (getenv("SAFENET_URL")){ safe_url = getenv("SAFENET_URL"); }else{ safe_url = SAFENET_URL; } if (getenv("SAFENET_KEY")){ safe_key = getenv("SAFENET_KEY"); }else{ safe_key = SAFENET_KEY; } } /* CURL FUNCTION BEGIN*/ struct string { char *ptr; size_t len; }; void init_string(struct string *s) { s->len = 0; s->ptr = malloc(s->len+1); if (s->ptr == NULL) { fprintf(stderr, "malloc() failed\n"); exit(EXIT_FAILURE); } s->ptr[0] = '\0'; } size_t writefunc(void *ptr, size_t size, size_t nmemb, struct string *s) { size_t new_len = s->len + size*nmemb; s->ptr = realloc(s->ptr, new_len+1); if (s->ptr == NULL) { fprintf(stderr, "realloc() failed\n"); exit(EXIT_FAILURE); } memcpy(s->ptr+s->len, ptr, size*nmemb); s->ptr[new_len] = '\0'; s->len = new_len; return size*nmemb; } char * safenet(char *url, char *mode, char *key, char *in ) { CURL *curl; CURLcode res; char *fields; char *data; // curl_global_init(CURL_GLOBAL_ALL); /* get a curl handle */ curl = curl_easy_init(); if(curl) { struct string s; init_string(&s); asprintf(&fields, "mode=%s&keyname=%s&input=%s", mode, key, in); curl_easy_setopt(curl, CURLOPT_URL, url); curl_easy_setopt(curl, CURLOPT_USERAGENT, "safenet/1.0 by netkiller <netkiller@msn.com>"); curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, writefunc); curl_easy_setopt(curl, CURLOPT_WRITEDATA, &s); curl_easy_setopt(curl, CURLOPT_POSTFIELDS, fields); /* Perform the request, res will get the return code */ res = curl_easy_perform(curl); /* Check for errors */ if(res != CURLE_OK) fprintf(stderr, "curl_easy_perform() failed: %s\n", curl_easy_strerror(res)); asprintf(&data, "%s", s.ptr); //printf("Encrypt: %s\n", data); free(s.ptr); /* always cleanup */ curl_easy_cleanup(curl); } else{ strcpy(data,""); } return data; //curl_global_cleanup(); } /* CURL FUNCTION END*/ /* ------------------------ safenet encrypt ----------------------------- */ my_bool safenet_encrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args->arg_count != 1) { strncpy(message, "two arguments must be supplied: safenet_encrypt('<data>').", MYSQL_ERRMSG_SIZE); return 1; } get_safenet_env(); args->arg_type[0]= STRING_RESULT; return 0; } char *safenet_encrypt(UDF_INIT *initid, UDF_ARGS *args, __attribute__ ((unused)) char *result, unsigned long *length, __attribute__ ((unused)) char *is_null, __attribute__ ((unused)) char *error) { char *data; data = safenet(safe_url, "encrypt", safe_key, args->args[0]); *length = strlen(data); return ((char *)data); } void safenet_encrypt_deinit(UDF_INIT *initid) { return; } /* ------------------------ safenet decrypt ----------------------------- */ my_bool safenet_decrypt_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { if (args->arg_count != 1) { strncpy(message, "two arguments must be supplied: safenet_decrypt('<data>').", MYSQL_ERRMSG_SIZE); return 1; } get_safenet_env(); args->arg_type[0]= STRING_RESULT; return 0; } char *safenet_decrypt(UDF_INIT *initid, UDF_ARGS *args, __attribute__ ((unused)) char *result, unsigned long *length, __attribute__ ((unused)) char *is_null, __attribute__ ((unused)) char *error) { char *data; if(strlen(args->args[0]) != 512){ data = args->args[0]; }else{ data = safenet(safe_url, "decrypt", safe_key, args->args[0]); } *length = strlen(data); return ((char *)data); } void safenet_decrypt_deinit(UDF_INIT *initid) { return; } /* ------------------------ safenet config ----------------------------- */ my_bool safenet_config_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { get_safenet_env(); return 0; } char *safenet_config(UDF_INIT *initid, UDF_ARGS *args, __attribute__ ((unused)) char *result, unsigned long *length, __attribute__ ((unused)) char *is_null, __attribute__ ((unused)) char *error) { char *config; asprintf(&config, "SAFENET_URL=%s, SAFENET_KEY=%s", safe_url, safe_key); *length = strlen(config); return ((char *)config); } void safenet_config_deinit(UDF_INIT *initid) { return; }
CMakeLists.txt
cmake_minimum_required(VERSION 2.8) PROJECT(safenet) ADD_LIBRARY(safenet SHARED safenet.c) INCLUDE_DIRECTORIES(/usr/include/mysql) TARGET_LINK_LIBRARIES(safenet curl) INSTALL(PROGRAMS libsafenet.so DESTINATION /usr/lib64/mysql/plugin/)
Installation Plugin
yum install -y libcurl-devel cd src cmake . make make install cat > /etc/sysconfig/mysqld <<EOF export SAFENET_URL=http://host.localdomain/safe/interface export SAFENET_KEY=Web01-key EOF
Create Function
create function safenet_encrypt returns string soname 'libsafenet.so'; create function safenet_decrypt returns string soname 'libsafenet.so'; create function safenet_config returns string soname 'libsafenet.so';
Example
mysql> select safenet_encrypt('Helloworld!!!'); +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | safenet_encrypt('Helloworld!!!') | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 994BAB7BC417F0559A09ECE94EDCB695AC1D5705F7ABA9F3562158F5AFAC4720FA9B3E53F30DF65C1726E0F02A93A9CAE7E486349F41AE4F504DC2B49F809C5AF77FEF4DE49D03D8DEC4000B15F2F2A2296500AA6159491E65DEFDFE75FB2E79D31D9BF0CC67932ADA212C34C0B04BF30F222102FAD857F440404C0FE92B8626EA3126B0B5A4FA0B1D09F1CC9EF45EBB6A72123AE82D39F659C717A5AA4F7FB5BDBBC7977C7021F61BBC26B9DB78C9A8657C6BC291CAE5C07F9DF485D71A1E9CC8888793B03BB5AF2DDB57AAEFB6D2EA569226651092414F96BA0880B35B0D8A01A1F7B82C308A2316D07C0FD4E0A298ECB33F4E4EB9F1A1E53760B0BFBE7449 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.58 sec) mysql> select safenet_decrypt(safenet_encrypt('Helloworld!!!')); +---------------------------------------------------+ | safenet_decrypt(safenet_encrypt('Helloworld!!!')) | +---------------------------------------------------+ | Helloworld!!! | +---------------------------------------------------+ 1 row in set (0.31 sec) mysql> select safenet_config();
Drop Function
drop function safenet_encrypt; drop function safenet_decrypt; drop function safenet_config;
原文出处:Netkiller 系列 手札
本文作者:陈景峯
转载请与作者联系,同时请务必标明文章原始出处和作者信息及本声明。