数据库内部提供的摘要函数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 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。
