A Shell Exec function for mysql 5.5

Shell control from mysql context

This code patch will let you execute shell commands with Mysql statements.

mysql> select system_exec("ls -l /");
+-----------------------------------------------------------------------------------+
| system_exec("ls -l /");                                                           |
+-----------------------------------------------------------------------------------+
| total 52                                                                          |
| drwx------   3 cvsd           cvsd            4096 2007-05-10 16:51 cvsd          |
| drwx------   4 dnyanesh                  504  4096 2008-03-10 12:08 dnyanesh      |
| drwx------   3 james          cvsuser         4096 2008-03-05 12:52 james         |
| drwx------ 125 kapil          kapil          12288 2010-04-24 14:36 kapil         |
| drwx------  23 kapilbedarkar  kapilbedarkar   4096 2007-05-10 14:43 kapilbedarkar |
| drwx------   4 Kapil.Bedarkar Kapil.Bedarkar  4096 2009-04-15 12:57 Kapil.Bedarkar|
| drwx------   4 kb             cvsuser         4096 2008-03-13 12:58 kb            |
| drwx------   3 pradeep        pradeep         4096 2007-05-10 17:16 pradeep       |
| drwxrwxr-x  11 kapil          kapil           4096 2007-10-09 11:38 shared-files  |
+-----------------------------------------------------------------------------------+

This can be very useful if you want to do something like doing file search inside Mysql procedure or even moving files form mysql procedure.



Even trigger few shell scripts inside mysql procedure!!

But this can introduce serious security threats too. be careful with user permissions. By default MySQL server is invoked using mysql user, which is good. So you have to keep in mind what damage that user can cause to system using shell.
Please do not start MySQL Server with root permissions as there is *NO UNDO*.

Using the Patch

This should work, suppose you have save following patch content in file called diff-mysql.

$ tar -xzf mysql-5.5.4-m3.tar.gz
$ patch -p0 mysql-5.5.4-m3/ < diff-mysql
$ cd mysql-5.5.4-m3
.. Then follow the compilation steps! (Read man pages! man/ma'am)

Code Patch

diff -ur -x '*~' mysql-5.5.4-m3/sql/item_create.cc mysql-5.5.4-m3.new/sql/item_create.cc
--- mysql-5.5.4-m3/sql/item_create.cc 2010-04-10 03:31:18.000000000 +0530
+++ mysql-5.5.4-m3.new/sql/item_create.cc 2010-04-23 20:29:22.000000000 +0530
@@ -2293,6 +2293,19 @@
   virtual ~Create_func_year_week() {}
 };
 
+// [HACK]
+class Create_func_system_exec : public Create_func_arg1
+{
+public:
+  virtual Item *create(THD *thd, Item *arg1);
+
+  static Create_func_system_exec s_singleton;
+
+protected:
+  Create_func_system_exec() {}
+  virtual ~Create_func_system_exec() {}
+};
+
 
 /*
 =============================================================================
@@ -4777,6 +4790,15 @@
   return func;
 }
 
+//[HACK]
+Create_func_system_exec Create_func_system_exec::s_singleton;
+
+Item*
+Create_func_system_exec::create(THD *thd, Item *arg1)
+{
+  return new (thd->mem_root) Item_func_system_exec(arg1);
+}
+
 
 struct Native_func_registry
 {
@@ -5004,6 +5026,8 @@
   { { C_STRING_WITH_LEN("X") }, GEOM_BUILDER(Create_func_x)},
   { { C_STRING_WITH_LEN("Y") }, GEOM_BUILDER(Create_func_y)},
   { { C_STRING_WITH_LEN("YEARWEEK") }, BUILDER(Create_func_year_week)},
+  /*HACK */
+  { { C_STRING_WITH_LEN("SYSTEM_EXEC") }, BUILDER(Create_func_system_exec)},
 
   { {0, 0}, NULL}
 };
@@ -5215,3 +5239,4 @@
   }
   return res;
 }
+
diff -ur -x '*~' mysql-5.5.4-m3/sql/item_strfunc.cc mysql-5.5.4-m3.new/sql/item_strfunc.cc
--- mysql-5.5.4-m3/sql/item_strfunc.cc 2010-04-10 03:31:20.000000000 +0530
+++ mysql-5.5.4-m3.new/sql/item_strfunc.cc 2010-04-24 14:41:14.000000000 +0530
@@ -3622,3 +3622,41 @@
   strmov(s+18, clock_seq_and_node_str);
   return str;
 }
+
+//[HACK]
+String *Item_func_system_exec::val_str(String *str)
+{
+ char *readHead;
+ char *exec_line;
+ FILE *fpcmd;
+ size_t readSize;
+ uint32 sizeToRead;
+ uint32 bufferSize;
+ 
+ String *sptr = args[0]->val_str(str);
+
+ if(sptr)
+ {
+  bufferSize = 0;
+  sizeToRead = 256;
+  fpcmd = popen(sptr->ptr(), "r");
+  do
+  {
+   if (tmp_value.realloc(bufferSize + sizeToRead))
+       goto err;
+   readHead = ((char*)tmp_value.ptr()) + bufferSize;
+   bzero(readHead, sizeToRead);
+   readSize = fread(readHead, sizeToRead, 1, fpcmd);
+   bufferSize += sizeToRead;
+  }while(feof(fpcmd) == 0);
+  pclose(fpcmd);
+  
+  tmp_value.realloc((bufferSize - sizeToRead) + strlen(readHead));
+  tmp_value.length((bufferSize - sizeToRead) + strlen(readHead));
+     return &tmp_value;
+ }
+ 
+err:
+ null_value= 1;
+ return 0;
+}
diff -ur -x '*~' mysql-5.5.4-m3/sql/item_strfunc.h mysql-5.5.4-m3.new/sql/item_strfunc.h
--- mysql-5.5.4-m3/sql/item_strfunc.h 2010-04-10 03:31:20.000000000 +0530
+++ mysql-5.5.4-m3.new/sql/item_strfunc.h 2010-04-23 20:29:18.000000000 +0530
@@ -16,6 +16,8 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
+//[HACK]
+#include <stdio.h>
 
 /* This file defines all string functions */
 
@@ -877,4 +879,15 @@
   String *val_str(String *);
 };
 
+//[HACK]
+class Item_func_system_exec :public Item_str_func
+{
+  String tmp_value;
+public:
+  Item_func_system_exec(Item *a) :Item_str_func(a) {}
+  void fix_length_and_dec() { maybe_null= 1; max_length = MAX_BLOB_WIDTH; }
+  const char *func_name() const { return "system_exec"; }
+  String *val_str(String *);
+};
+
 #endif /* ITEM_STRFUNC_INCLUDED */