Command execution with MySQL UDF

Oussama Hammami, 2011-01-11

Switzernet

Modern database management systems are powerful applications: they provide several instruments to interact with the underlying operating system.

On MySQL it is possible to create a User-Defined Function to execute commands on the underlying operating system. Marco Ivaldi demonstrated that some years ago. His raptor_udf2.c works well, but it has two limitations:

It is not MySQL 5.0+ compliant because it does not follow the new guidelines to create a proper UDF.
It calls C function system() to execute the command and returns always integer 0.

These limitations make the UDF almost useless on recent MySQL server installations if the database administrator wants to get the exit status of the command as UDF output or the command standard output itself.

I recently came across an open repository of MySQL User-Defined Functions. One of their codes kept my attention: lib_mysqludf_sys (version 0.0.2) which implements three different functions to interact with the underlying environment::

sys_exec: executes an arbitrary command, and can thus be used to launch an external application.
sys_get : gets the value of an environment variable.
sys_set : create an environment variable, or update the value of an existing environment variable.

The first function can be used to execute operating system commands and has two advantages over raptor’s UDF:

It is MySQL 5.0+ compliant and it compiles on both Linux as a shared object and on Windows as a dynamic-link library.
It returns the exit status of the executed command.