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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料