SQL UDF – mysql-udf

測試環境 CentOS8 – MariaDB 10.3.28

在 SQL Shell 下面是可以直接透過 system 指令去執行 Linux Shell 的程式,不過這個 system 指令基本安全考量才不能使用在 Stored Routines (Procedure , Function 或是 Trigger ) 上,不過可以透過 User Define Function (UDF) 來做.
如 mysql-udf – https://github.com/topics/mysql-udf 這個 UDF 就提供了去執行 Linux Shell 程式的功能.

下載 mysql-udf

[root@localhost ~]# git clone https://github.com/retanoj/mysql_udf.git
[root@localhost ~]# cd mysql_udf/

so 檔案

將 UDF 的 so 檔案移至 MariaDB plugin_dir 路徑並更名,plugin_dir 路徑可以透過以下方式來查詢.

MariaDB [(none)]> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| plugin_dir    | /usr/lib64/mariadb/plugin/ |
+---------------+----------------------------+
1 row in set (0.001 sec)

[root@localhost mysql_udf]# mv udf_mysql5.6.25_redhat_x64.so /usr/lib64/mariadb/plugin/lib_mysqludf_sys.so

安裝

在 MariaDB 安裝 sys_eval 與 sys_exec 這兩個 UDF.

• sys_eval
  sys_eval 執行指定指令,並回傳執行結果.

  MariaDB [(none)]> CREATE FUNCTION sys_eval RETURNS STRING SONAME 'lib_mysqludf_sys.so';
  

• sys_exec
  sys_exec 執行指定指令,並回傳結束碼.

  MariaDB [(none)]> CREATE FUNCTION sys_exec RETURNS STRING SONAME 'lib_mysqludf_sys.so';
  

測試

• sys_eval

  MariaDB [(none)]> SELECT sys_eval('ls /boot/')\G
  *************************** 1. row ***************************
  sys_eval('ls /boot/'): config-4.18.0-305.19.1.el8_4.x86_64
  config-4.18.0-305.3.1.el8.x86_64
  efi
  grub2
  initramfs-0-rescue-587b3daa9a6842e0b945cb026c9de3f2.img
  initramfs-4.18.0-305.19.1.el8_4.x86_64.img
  initramfs-4.18.0-305.19.1.el8_4.x86_64kdump.img
  initramfs-4.18.0-305.3.1.el8.x86_64.img
  initramfs-4.18.0-305.3.1.el8.x86_64kdump.img
  loader
  System.map-4.18.0-305.19.1.el8_4.x86_64
  System.map-4.18.0-305.3.1.el8.x86_64
  vmlinuz-0-rescue-587b3daa9a6842e0b945cb026c9de3f2
  vmlinuz-4.18.0-305.19.1.el8_4.x86_64
  vmlinuz-4.18.0-305.3.1.el8.x86_64
  
  1 row in set (0.004 sec)
  

• sys_exec

  MariaDB [(none)]> SELECT sys_exec('ls /boot/')\G
  ERROR 2006 (HY000): MySQL server has gone away
  No connection. Trying to reconnect...
  Connection id:    10
  Current database: *** NONE ***
  
  *************************** 1. row ***************************
  sys_exec('ls /boot/'): NULL
  1 row in set (0.005 sec)
  

SQL Procedure

寫成 Procedure 也可以使用.

DROP PROCEDURE IF EXISTS simpleproc;
DELIMITER //
CREATE PROCEDURE simpleproc ()
BEGIN
  SELECT sys_eval('ls /boot/');
END//
DELIMITER ;

執行

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [testdb]> DROP PROCEDURE IF EXISTS simpleproc;
DURE simpleproc ()
BEGIN

SQuery OK, 0 rows affected, 1 warning (0.006 sec)

MariaDB [testdb]> DELIMITER //
MariaDB [testdb]>
MariaDB [testdb]> CREATE PROCEDURE simpleproc ()
    -> BEGIN
    ->
    -> SELECT sys_eval('ls /boot/');
    ->
    -> END//
Query OK, 0 rows affected (0.002 sec)

MariaDB [testdb]>
MariaDB [testdb]> DELIMITER ;

MariaDB [testdb]> CALL simpleproc();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sys_eval('ls /boot/')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| config-4.18.0-305.19.1.el8_4.x86_64
config-4.18.0-305.3.1.el8.x86_64
efi
grub2
initramfs-0-rescue-587b3daa9a6842e0b945cb026c9de3f2.img
initramfs-4.18.0-305.19.1.el8_4.x86_64.img
initramfs-4.18.0-305.19.1.el8_4.x86_64kdump.img
initramfs-4.18.0-305.3.1.el8.x86_64.img
initramfs-4.18.0-305.3.1.el8.x86_64kdump.img
loader
System.map-4.18.0-305.19.1.el8_4.x86_64
System.map-4.18.0-305.3.1.el8.x86_64
vmlinuz-0-rescue-587b3daa9a6842e0b945cb026c9de3f2
vmlinuz-4.18.0-305.19.1.el8_4.x86_64
vmlinuz-4.18.0-305.3.1.el8.x86_64
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.009 sec)

Query OK, 0 rows affected (0.009 sec)