I. MySQL UDF

This thing is called “MySQL user-definable function”, so what exactly does UDF do?

In a nutshell, it means: you can write some code to handle the data, then compile the code into a dynamic link library (so), and finally load it dynamically in MySQL and then the user can use it.

II. Solution

Since we have to check the database, but actually the review does not focus on each table or even the database details; so the simplest solution is to define a SM4 encryption algorithm to encrypt and decrypt the data dynamically when reading and writing through UDF, other details are not detailed here, this article mainly describes how to develop a simple UDF with Go and use it.

III. UDF Functions

Since UDF is officially supported in C/C++, you need to use CGO in Go; a UDF implementation usually contains two func.

1
2
3
4
5
6
7

func xxx_init(initid *C.UDF_INIT, args *C.UDF_ARGS, message *C.char) C.int {
    // ...
}

func xxx(initid *C.UDF_INIT, args *C.UDF_ARGS, result *C.char, length *C.ulong, is_null *C.char, error *C.char) *C.char {
    // ...
}

The xxx_init method is used for pre-checking, and xxx is the real logical implementation; the xxx_init method is used to pre-process the parameters and memory allocation before the xxx method is called.

Note: Starting from MySQL 8.0.1 the return value of xxx_init is changed from my_bool to int, many codes on the web write my_bool which will not compile; refer to https://bugs.mysql.com/bug.php?id=85131

IV. Go implementation of UDF

Once you know the method signature, let’s get started.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

package main

// #include <stdio.h>
// #include <sys/types.h>
// #include <sys/stat.h>
// #include <stdlib.h>
// #include <string.h>
// #include <mysql.h>
// #cgo CFLAGS: -D ENVIRONMENT=0 -I/usr/include/mysql -fno-omit-frame-pointer
import "C"

import (
    "github.com/tjfoc/gmsm/sm4"
)

//export xsm4_enc_init
func xsm4_enc_init(initid *C.UDF_INIT, args *C.UDF_ARGS, message *C.char) C.int {
    if args.arg_count != 1 {
        msg := "xsm4_enc() supports only a single string parameter\n"
        C.strcpy(message, C.CString(msg))
        return 1
    }

    return 0
}

//export xsm4_enc
func xsm4_enc(initid *C.UDF_INIT, args *C.UDF_ARGS, result *C.char, length *C.ulong, is_null *C.char, error *C.char) *C.char {
    // Converting a C pointer to a Go type
    var str = C.GoString(*args.args)

    var resp string
    enc, err := sm4.Sm4Ecb([]byte("1234567890abcdef"), []byte(str), true)
    if err != nil {
        resp = err.Error()
    } else {
        resp = string(enc)
    }

    // Converts the result to a C type
    var res = C.CString(resp)

    // Set output parameters
    *length = C.ulong(len(resp))
    *is_null = 0

    // Return Results
    return res
}

func main() {}

The xsm4_enc_init method does a check, currently only supports a single field parameter, xsm4_enc does a simple SM4 encryption of the incoming field via the open source gmsm library and returns it; in a real environment you need to call the encryption machine to implement the relevant encryption, here we only demonstrate the direct use of the open source library + fixed password.

V. Compile and load

Save the above code as xsm4_enc.go, then compile it on the server with MySQL headers installed using the following command.

1

go build -o xsm4_enc.so -buildmode=c-shared xsm4_enc.go

If there is no problem, a xsm4_enc.so file will be generated, if it says C.xxx type not found and other problems, the header file is not loaded, check or modify -I/usr/include/mysql location.

After generating the so file, copy it to MySQL’s plugins directory (the plugins directory can be queried by SHOW VARIABLES LIKE 'plugin_dir';).

1

cp xsm4_enc.so /usr/lib/mysql/plugin/

Finally, create the UDF in MySQL.

1
2
3
4
5

# CREATE
CREATE FUNCTION xsm4_enc RETURNS STRING SONAME 'xsm4_enc.so';

# DROP
DROP FUNCTION xsm4_enc;

VI. Use of UDF

It’s easy to use, just call it by name in the query.

1

SELECT id, xsm4_enc(username), username FROM users;

Similarly, you can also create a decrypted UDF, but of course these UDFs will eventually work with the view or something to do what and how to use it without going into too much detail.

VII. Ref

  • https://mritd.com/2023/05/12/write-mysql-udf-in-golang/