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.
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.
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
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.
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.
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
After generating the so file, copy it to MySQL’s plugins directory (the plugins directory can be queried by
SHOW VARIABLES LIKE 'plugin_dir';).
Finally, create the UDF in MySQL.
VI. Use of UDF
It’s easy to use, just call it by name in the query.
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.