joungkyun / lib_mysqludf_aes256

Support AES-256 encrypt and decrypt functions on mysql and mariadb

lib_mysqludf_aes256

Support AES 128/192/256 encrypt and decrypt on MySQL and Maraidb with User Defined Function.

License

Copyright (c) 2020 JoungKyun.Kim <http://oops.org> All rights reserved. This program is under GPL v2

Requirements

MySQL <= 5.7
Mariadb <= 10.5

From the version MySQL 5.6.17 and later, you can encode to AES-256 with AES_ENCRYPT function using the block_encryption_mode global variable. see also http://mysqlblog.fivefarmers.com/2014/03/27/mysql-5-6-17-now-with-better-encryption/. And from version 5.7.4 and later, you can encode to AES-256 with AES_ENCRYPT using 32byte key lentgh. MariaDB does not yet support thie features.

Usage

• AES 128 encrypt and decrypt
  • key length : 16byte
  • If the length of the key is 16byte, AES256_ENCRYPT will operate in the same way as AES_ENCRYPT.

mysql> select HEX(AES256_ENCRYPT('strings', '0123456789abcdef'));
mysql> select AES256_DECRYPT(UNHEX('encrypted_hash_string'), '0123456789abcdef');

• AES 192 encrypt and decrypt
  • key length : 24byte

mysql> select HEX(AES256_ENCRYPT('strings', '0123456789abcdef01234567'));
mysql> select AES256_DECRYPT(UNHEX('encrypted_hash_string'), '0123456789abcdef01234567');

• AES 256 encrypt and decrypt
  • key length : 32byte

mysql> select HEX(AES256_ENCRYPT('strings', '0123456789abcdef0123456789abcdef'));
mysql> select AES256_DECRYPT(UNHEX('encrypted_hash_string'), '0123456789abcdef0123456789abcdef');

Installation

• Build and installation

[root@host lib_mysqludf_aes256]$ ./configure \
        --with-mysql=@MYSQL_PREFIX@ \
        --with-mysql-config=@MYSQL_PREFIX@/bin/mysql_config
[root@host lib_mysqludf_aes256]$ make
[root@host lib_mysqludf_aes256]$ make install

Before 1.0.4, you must use --with-mysql-plugins-dir instead of --with-mysql-config options.

[root@host lib_mysqludf_aes256]$ ./configure \
        --with-mysql=@MYSQL_PREFIX@ \
        --with-mysql-plugins-dir="$(mysql_config --plugindir)"

If you want to check installed files, you can test install as follow.

[root@host lib_mysqludf_aes256]$ make test-install
[root@host lib_mysqludf_aes256]$ # OR
[root@host lib_mysqludf_aes256]$ make install DESTDIR=$(pwd)/z
[root@host lib_mysqludf_aes256]$ # chcek test-install files
[root@host lib_mysqludf_aes256]$ tree z # or find ./z

• Regist AES256_ENCRYPT/AES256_DECRYPT UDF

[root@host lib_mysqludf_aes256]$ mysql < docs/aes256_install.sql

• Unregist AES256_ENCRYPT/AES256_DECRYPT UDF

[root@host lib_mysqludf_aes256]$ mysql < docs/aes256_uninstall.sql

After installation, aes256_install.sql and aes256_uninstall.sql are located in PREFIX/share/doc/lib_mysqludf_aes256-@VERSION@/.

Language API

This UDF function will operate in the same way with follow apis:

• javascript mysqlAES package
• PHP mysqlAES class

Credits

JoungKyun.Kim

lib_mysqludf_aes256's Issues

version update 1.0.5

change version 1.0.5 on master tree

build failure uner mariadb 10.3 and after

my_sys.h check fails when executing ./configure in mariadb 10.3 or later versions.

[root@host mysql-aes256]# ./configure --with-mysql-config=/usr/bin/mysql_config
... blah blah
checking for ANSI C header files... (cached) yes
checking for stdlib.h... (cached) yes
checking for string.h... (cached) yes
checking mysql.h usability... yes
checking mysql.h presence... yes
checking for mysql.h... yes
checking my_global.h usability... yes
checking my_global.h presence... yes
checking for my_global.h... yes
checking my_sys.h usability... no
checking my_sys.h presence... no
checking for my_sys.h... no
configure: error: You must need my_sys.h file to build this udf.

No such file or directory about m_string.h

From mariadb 10.3.24, 10.4.14 or later versions, m_string.h was not found.

[root@host src]# make
... blah blah
In file included from mysql_aes256.c:40:0:
/usr/local/mysql/include/mysql/my_sys.h:3:2: warning: #warning This file should not be included by clients, include only <mysql.h> [-Wcpp]
 #warning This file should not be included by clients, include only <mysql.h>
  ^
mysql_aes256.c:42:47: fatal error: m_string.h: No such file or directory
   #include <m_string.h>  /* To get strmov() */
                                               ^
compilation terminated.
make[2]: *** [lib_mysqludf_aes256_la-mysql_aes256.lo] Error 1
make[2]: Leaving directory `/home/maria/lib_mysqludf_aes256-master/src'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/home/maria/lib_mysqludf_aes256-master/src'
make: *** [all-recursive] Error 1

build failure with mysql 5.7

when build with mysql 5.7, follow errors occurs:

In file included from mysql_aes256.c:40:
In file included from /usr/include/mysql/my_sys.h:26:
/usr/include/mysql/mysql/psi/mysql_thread.h:64:10: fatal error:  'pfs_thread_provider.h' file not found
#include "pfs_thread_provider.h"
         ^
1 error generated.

when execute configure, auto search mysql plugin directory with mysql_config

Remove --mysql-plugins-dir option. And search mysql_config and get mysql plugin dir with --plugindir option.

first, use --with-mysql and if set --with-mysql-config, use value of --with-mysql-config

for i in $MYSQL_PREFIX/bin $MYSQL_PREFIX/sbin /usr/bin /usr/sbin /bin /sbin /usr/local/bin /usr/local/sbin
do
    if test -f "$i/mysql_config"; then
        MYSQL_CONFIG="$i/mysql_config"
        break
    fi
done

if test "x$with_mysql_config" != "xno"; then
    if test -x "$with_mysql_config"; then
        MYSQL_CONFIG="$with_mysql_config"
    fi
fi

master tree version update to 1.0.7

change version to 1.0.7 on master tree

can not found my_global.h on configure

If mysql prefix is not /usr, can not found my_global.h

[root@host lib_mysqludf_aes256-1.0.3]$ ./configure --with-mysql=/opt/mysql-5.5.40
...
checking mysql.h usability... yes
checking mysql.h presence... yes
checking for mysql.h... yes
checking my_global.h usability... no
checking my_global.h presence... no
checking for my_global.h... no
configure: error: You must need my_global.h file to build this udf.

This issue is reported at https://oops.org/SERVICE/jsboard/read.php?table=jsboard_oopsQnA&no=53450 by 김대홍 <srpro3 AT paran.com>

Error when CREATE FUNCTION aes256_encrypt

Hello @Joungkyun,

I had an issue when I'm trying to install this library at the last step :

CREATE FUNCTION aes256_encrypt RETURNS string SONAME 'lib_mysqludf_aes256.so';
ERROR 1126 (HY000): Can't open shared library 'lib_mysqludf_aes256.so' (errno: 11, undefined symbol: rijndaelEncrypt)

I made first steps like this :

cd lib_mysqludf_aes256
./configure --with-mysql=/usr --with-mysql-config=/usr/bin/mysql_config
make
make install

I'm on a Debian server with a MariaDB 10.1.48.

I did not find any error when configuring the lib before these error at the creation of the function.
I don't understand why there is this error while "src/rijndael.c" and "src/rijndael.h" exists.

Do you have any idea where the problem comes from ?
Thanks again for your time
Antoine

compile warning on gcc 4.8

mysql_aes256.c: In function 'aes256_encrypt_init':
mysql_aes256.c:140:2: warning: implicit declaration of function 'my_aes256_get_size' [-Wimplicit-function-declaration]
  initid->max_length = my_aes256_get_size (args->lengths[0]);
  ^
mysql_aes256.c: In function 'aes256_encrypt':
mysql_aes256.c:174:2: warning: implicit declaration of function 'my_aes256_encrypt' [-Wimplicit-function-declaration]
  len = my_aes256_encrypt (
  ^
mysql_aes256.c: In function 'aes256_decrypt_init':
mysql_aes256.c:231:4: warning: format '%d' expects argument of type 'int', but argument 6 has type 'long unsigned int' [-Wformat=]
    );
    ^
mysql_aes256.c:257:3: warning: format '%d' expects argument of type 'int', but argument 3 has type 'long unsigned int' [-Wformat=]
   );
   ^
mysql_aes256.c:204:6: warning: unused variable 'blocks' [-Wunused-variable]
  int blocks, i;
      ^
mysql_aes256.c: In function 'aes256_decrypt':
mysql_aes256.c:299:3: warning: format '%d' expects argument of type 'int', but argument 5 has type 'long unsigned int' [-Wformat=]
   );
   ^
mysql_aes256.c:309:2: warning: implicit declaration of function 'my_aes256_decrypt' [-Wimplicit-function-declaration]
  len = my_aes256_decrypt (
  ^
mysql_aes256.c: At top level:
mysql_aes256.c:56:24: warning: 'LOCK_hostname' defined but not used [-Wunused-variable]
 static pthread_mutex_t LOCK_hostname;

check support mariadb 10.5 GA version

check support mariadb 10.5 GA version

travis ci test environment

test target

• language : gcc, clang
• mysql : 5.5, 5.6, 5,7
• mariadb: 5.5, 10.0, 10.1, 10.2

version update 1.0.6

change version 1.0.6 on master tree