Re: Ejecutar aplicacion externa desde mysql

[Miguel Perez]

Quizás esto te pueda servir. Es una UDF de pruebas que estuve haciendo. Sirve para borrar archivos desde MySQL (es una interfaz a unlink). Elimina archivos siempre dentro de un directorio especificado y hardwireado en el código fuente, y no deja escapar a directorios de arriba con "..", con lo que hay que definir el directorio donde irán los archivos en el fuente de la función e incluso si comprometen MySQL no podrían utilizar esta función para hacer unlink fuera de ese directorio.

Esta función no está probada porque la he sacado de otro sistema que tenemos y para que te pudiera funcionar he tenido que hacerle alguna modificación, pero si no funciona inmediatamente, por lo menos será un buen punto de partida.


Este es el archivo fuente de la función en C:

--8<--------------------------------------------------

/****************************************************************************\
 * UDF FUNCTIONS FOR MYSQL                                                  *
 * Warning: This is highly experimental code. It was abandoned, and it has  *
 * been stripped of some functionality and it has not been properly tested. *
 *                                                                          *
 * This program provides User Defined Functions (UDFs) for MySQL 5 that     *
 * serve certain special purposes. Currently, the only function defined is  *
 * DeleteFile, which will delete files specified by their absolute name     *
 * inside UDF_BASE_DIRECTORY. Check the function doc for more information.  *
 * Functions defined here:                                                  *

	DeleteFile(filename) -> success
		Deletes a file within the configured UDF_BASE_DIRECTORY,
		returning 1 for successfully deleted files, 0 for files which were
		not found or unable to be deleted.

\****************************************************************************/

//Configuration: set the prefix for all file operations, can be a symlink
//Important: terminate it in a slash unless you know what you're doing.
#define UDF_BASE_DIRECTORY "/Software/Your_App/udfdir/"

//Configuration: define to forbid "." in names (note ".." is always forbidden)
#define UDF_NO_DOT_NAMES

/* This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
   the Free Software Foundation; either version 2 of the License, or
   (at your option) any later version.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */

/* After compiling, define in MySQL like:
	CREATE FUNCTION DeleteFile RETURNS INT SONAME "udfs.so";
   You can undefine it like:
    DELETE FUNCTION DeleteFile;
*/

#ifdef STANDARD
/* STANDARD is defined, don't use any mysql functions */
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;	/* Microsofts 64 bit types */
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#if defined(MYSQL_SERVER)
#include <m_string.h>		/* To get strmov() */
#else
/* when compiled as standalone */
#define strmov(a,b) strcpy(a,b)
#define bzero(a,b) memset(a,0,b)
#define memcpy_fixed(a,b,c) memcpy(a,b,c)
#endif
#endif
#include <mysql.h>
#include <ctype.h>

static pthread_mutex_t LOCK_hostname;

#ifdef HAVE_DLOPEN





/* These must be right or mysqld will not find the symbol! */

my_bool DeleteFile_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
longlong DeleteFile(UDF_INIT *initid, UDF_ARGS *args, char *result,
	       unsigned long *length, char *is_null, char *error);
void DeleteFile_deinit(UDF_INIT *initid);




/*************************************************************************
** Example of init function
** Arguments:
** initid	Points to a structure that the init function should fill.
**		This argument is given to all other functions.
**	my_bool maybe_null	1 if function can return NULL
**				Default value is 1 if any of the arguments
**				is declared maybe_null.
**	unsigned int decimals	Number of decimals.
**				Default value is max decimals in any of the
**				arguments.
**	unsigned int max_length  Length of string result.
**				The default value for integer functions is 21
**				The default value for real functions is 13+
**				default number of decimals.
**				The default value for string functions is
**				the longest string argument.
**	char *ptr;		A pointer that the function can use.
**
** args		Points to a structure which contains:
**	unsigned int arg_count		Number of arguments
**	enum Item_result *arg_type	Types for each argument.
**					Types are STRING_RESULT, REAL_RESULT
**					and INT_RESULT.
**	char **args			Pointer to constant arguments.
**					Contains 0 for not constant argument.
**	unsigned long *lengths;		max string length for each argument
**	char *maybe_null		Information of which arguments
**					may be NULL
**
** message	Error message that should be passed to the user on fail.
**		The message buffer is MYSQL_ERRMSG_SIZE big, but one should
**		try to keep the error message less than 80 bytes long!
**
** This function should return 1 if something goes wrong. In this case
** message should contain something usefull!
**************************************************************************/
my_bool DeleteFile_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
	if (args->arg_count != 1 || args->arg_type[0] != STRING_RESULT) {
		strcpy(message, "UDF DeleteFile: usage: DeleteFile(filename) -> success");
    	return 1;
	}
	return 0;
}


/****************************************************************************
** Deinit function. This should free all resources allocated by
** this function.
** Arguments:
** initid	Return value from xxxx_init
****************************************************************************/
void DeleteFile_deinit(UDF_INIT *initid __attribute__((unused)))
{
}


/***************************************************************************
** UDF string function.
** Arguments:
** initid	Structure filled by xxx_init
** args		The same structure as to xxx_init. This structure
**		contains values for all parameters.
**		Note that the functions MUST check and convert all
**		to the type it wants!  Null values are represented by
**		a NULL pointer
** result	Possible buffer to save result. At least 255 byte long.
** length	Pointer to length of the above buffer.	In this the function
**		should save the result length
** is_null	If the result is null, one should store 1 here.
** error	If something goes fatally wrong one should store 1 here.
**
** This function should return a pointer to the result string.
** Normally this is 'result' but may also be an alloced string.
***************************************************************************/

#ifdef __WIN__
//Let's pray this works
#include <io.h>
#else
#include <unistd.h>
#endif

longlong DeleteFile(UDF_INIT *initid __attribute__((unused)),
  UDF_ARGS *args, char *result, unsigned long *length, char *is_null,
  char *error __attribute__((unused))) {

  	//Get the string passed as parameter into my own buffer, prefixed with the
  	//base directory, and ensure ASCIIZ.
  	char *src = args->args[0];
  	int srclen = args->lengths[0];

  	char *filename = malloc(strlen(UDF_BASE_DIRECTORY) + srclen + 1);
	char *dst = filename + strlen(UDF_BASE_DIRECTORY);

	//Copy prefix
	strcpy(filename, UDF_BASE_DIRECTORY);

	//Copy filename
	while (srclen-- > 0) {
		*dst++ = *src++;
	}
	*dst = 0; //Final 0

	//Security: check that there are no ".." or "."s in the name
#ifdef UDF_NO_DOT_NAMES
	//Check against "."
	if (strstr(filename, ".")) {
#else
	//Check against ".."
	if (strstr(filename, "..")) {
#endif
		free(filename);
		return 0;
	}

	//Delete file
	int retval = unlink(filename);

	free(filename);

	return retval == 0;
}




#endif /* HAVE_DLOPEN */

--8<--------------------------------------------------


Para compilarlo, puedes utilizar este script de sh (antes edita las variables a lo que sea apropiado y descomenta la línea de compilación para 32 o 64 bits según necesites):

--8<--------------------------------------------------

#!/bin/sh
#Run as root
#Requires sh - Windows users could try GNUWin32 or Cygwin

#Specify where's your mysql_config executable from your current MySQL server
#installation
CFG=/Software/mysql5/bin/mysql_config

#Specify your library directory: the directory where you want to install the
#library. For most machines, it'll be /usr/lib. For 64 bit Linuces running on
#AMD Athlon 64 and Opteron processors, it'll be /usr/lib64. For Windows, it
#simply can be the same directory as the mysqld executable.
LIBDIR=/usr/lib64

echo "Note: It's possible that you'll see several \"incompatible implicit declaration of built-in function\" warnings during compilation. This is ok."

#---FOR 32 BIT MACHINES---
#If your operating system doesn't use .so files, replace the extension by whatever you need
#gcc -m32 -O2 `$CFG --cflags` -shared -o udfs.so udfs.c

#---FOR 64 BIT MACHINES---
#If your operating system doesn't use .so files, replace the extension by whatever you need
gcc -m64 -fPIC -O2 `$CFG --cflags` -shared -o udfs.so udfs.c

mv udfs.so $LIBDIR/udfs.so
chown root:root $LIBDIR/udfs.so
chmod 755 $LIBDIR/udfs.so

--8<--------------------------------------------------


Suerte

Un saludo,

Miguel Pérez
Afina Sistemas - Partner de MySQL en España