mariadb : sys_exec : trigger call shell command Hint

# yum install mariadb-devel

# cd 

# wget https://github.com/mysqludf/lib_mysqludf_sys/archive/master.zip

# mv master.zip lib_mysqludf_sys-master.zip

# unzip lib_mysqludf_sys-master.zip

# mv lib_mysqludf_sys-master /usr/include/mysql/

 

# cd /usr/include/mysql/lib_mysqludf_sys-master/

# gcc -m64 -fPIC -Wall -I/usr/include/mysql/server -I. -I/usr/include/mysql/server/private  -shared lib_mysqludf_sys.c -o /usr/lib64/mariadb/plugin/lib_mysqludf_sys.so

 

# ll /usr/lib64/mariadb/plugin/lib_mysqludf_sys.so

cat /usr/include/mysql/lib_mysqludf_sys-master/lib_mysqludf_sys.sql

DROP FUNCTION IF EXISTS lib_mysqludf_sys_info;

DROP FUNCTION IF EXISTS sys_get;

DROP FUNCTION IF EXISTS sys_set;

DROP FUNCTION IF EXISTS sys_exec;

DROP FUNCTION IF EXISTS sys_eval;

 

CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';

CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';

CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';

CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';

CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';

 

# mysql -uroot -p mysql

 

SQL> CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';

SQL> CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';

SQL> CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';

SQL> CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';

SQL> CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';

 

SQL> select * from mysql.func;

+-----------------------+-----+---------------------+----------+

| name                  | ret | dl                  | type     |

+-----------------------+-----+---------------------+----------+

| lib_mysqludf_sys_info |   0 | lib_mysqludf_sys.so | function |

| sys_get               |   0 | lib_mysqludf_sys.so | function |

| sys_set               |   2 | lib_mysqludf_sys.so | function |

| sys_exec              |   2 | lib_mysqludf_sys.so | function |

| sys_eval              |   0 | lib_mysqludf_sys.so | function |

+-----------------------+-----+---------------------+----------+

 

SQL> select sys_eval('pwd') from dual;

 

 

/* ------------------------------------------------------------------------ */

lib_mysqludf_sys

/* ------------------------------------------------------------------------ */

DELIMITER @@

CREATE TRIGGER test_trigger 

AFTER INSERT ON test_table 

FOR EACH ROW 

BEGIN

 DECLARE cmd CHAR(255);

 DECLARE result int(10);

 SET cmd=('touch /path/file');

 SET result = sys_exec(cmd);

END; @@

DELIMITER ;

/* ------------------------------------------------------------------------ */