3

I have a trigger which calls a stored procedure with parameters which calls SET result = sys_exec(cmd);. But it gives the error "function sys_exec does not exist".

I don't know what to do, on Tuesday I have presentation and because of this code line my project won't work. The codes which I try to work.

DELIMITER $$
CREATE PROCEDURE push_message
(p1   int,
 p2   int,
 p3 varchar(20))
BEGIN
 DECLARE cmd CHAR(255);
 DECLARE result CHAR(255);
 SET cmd = CONCAT('curl https://pubsub.pubnub.com/publish/demo/demo/0/mysql_triggers/0/%22',p1, ',' ,p2, ',' ,p3,'%22');
 SET result = sys_exec(cmd);
END$$; 


CREATE TRIGGER push_message_trigger AFTER INSERT ON your_table_name_here
FOR EACH ROW
CALL push_message(NEW.id, NEW.num, NEW.name);
miken32
  • 42,008
  • 16
  • 111
  • 154
earlymorningtea
  • 508
  • 1
  • 9
  • 20
  • try execute or exec if you want to execute proc and see what it has to say. – SMA Dec 21 '14 at 06:56
  • Have you created functions, has run the file [lib_mysqludf_sys.sql](https://github.com/mysqludf/lib_mysqludf_sys/blob/master/lib_mysqludf_sys.sql)? – wchiquito Dec 21 '14 at 12:41

3 Answers3

8

Try:

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.5.35-1ubuntu1 |
+-----------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ | -- copy 'lib_mysqludf_sys.so' here
+---------------+------------------------+
1 row in set (0.01 sec)

mysql> DROP FUNCTION IF EXISTS lib_mysqludf_sys_info;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP FUNCTION IF EXISTS sys_get;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP FUNCTION IF EXISTS sys_set;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP FUNCTION IF EXISTS sys_exec;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP FUNCTION IF EXISTS sys_eval;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME 'lib_mysqludf_sys.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys.so';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT sys_exec('curl http://stackoverflow.com/');
+--------------------------------------------+
| sys_exec('curl http://stackoverflow.com/') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.12 sec)

For more details, visit: 24.3.2.5 Compiling and Installing User-Defined Functions.

wchiquito
  • 16,177
  • 2
  • 34
  • 45
4

Try Once.

Steps to be followed:

  • Get the files from https://github.com/mysqludf/lib_mysqludf_sys

  • Go to the folder path in terminal.

  • Execute:

    gcc -DMYSQL_DYNAMIC_PLUGIN -fPIC -Wall -m64 -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o lib_mysqludf_sys.so

    NOTE: If 32-bit OS replace -m64 with -m32 in the above command.

  • Execute the following in mysql shell:

    SHOW VARIABLES LIKE 'plugin_dir';

    This is an example of successful output:

    +---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+

  • Copy the lib_mysqludf_sys.so file to the above path.

  • Execute the following in mysql shell

    CREATE FUNCTION sys_exec RETURNS INT SONAME 'lib_mysqludf_sys.so';
galoget
  • 722
  • 9
  • 15
Aslam Shaik
  • 1,629
  • 1
  • 11
  • 10
0

sys_exec is not a MySql built-in function, you should use an User define function (UDF). Reference here https://dev.mysql.com/doc/refman/8.0/en/create-function-udf.html#:~:text=A%20user%2Ddefined%20function%20is,ABS()%20or%20CONCAT()%20.&text=function_name%20is%20the%20name%20that,of%20the%20function's%20return%20value.

If your OS is Windows, use this (there are .so file for Linux and .dll file for Windows) https://github.com/rapid7/metasploit-framework/tree/master/data/exploits/mysql

Download file and copy to your ../MySQL/lib or ../MySQL/lib/plugin directory.

Open MySQL, copy and execute this :

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_64.dll';
CREATE FUNCTION sys_get RETURNS string SONAME 'lib_mysqludf_sys_64.dll';
CREATE FUNCTION sys_set RETURNS int SONAME 'lib_mysqludf_sys_64.dll';
CREATE FUNCTION sys_exec RETURNS int SONAME 'lib_mysqludf_sys_64.dll';
CREATE FUNCTION sys_eval RETURNS string SONAME 'lib_mysqludf_sys_64.dll';

Then you can call an .exe file by :

DECLARE cmd CHAR(255);
DECLARE result int(10);
SET cmd = "../<ProjectApp>/bin/Debug/netcoreapp3.1/<appName>.exe";
SET result = sys_exec(cmd);
Quan Truong
  • 193
  • 4
  • 11