mysql UDF : fopen = permission denied

EDIT: I've re-written this question, as I got no answer and I'm currently trying to narrow the problem.

I'm trying to create a mysql UDF function checking if a file exists on the server side. This function calls "open/close". It doesn't work even when the file are readeable.

I put the code for this function below:

#include <mysql.h>
#include <string.h>
#include <errno.h>
#include <stdio.h>
#include <sys/types.h>
#include <sys/stat.h>
#include <unistd.h>
#include <fcntl.h>

/* The initialization function */
my_bool fileExists_init(
        UDF_INIT *initid,
        UDF_ARGS *args,
        char *message
        )
  {
  /* check the args */
  if (!(args->arg_count == 1 &&
        args->arg_type[0] == STRING_RESULT
        ))
    {
    strncpy(message,"Bad parameter expected a string",MYSQL_ERRMSG_SIZE);
    return 1;
    }
  initid->maybe_null=1;
  initid->ptr= NULL;
  return 0;
  }

/* The deinitialization function */
void  file开发者_如何学CExists_deinit(UDF_INIT *initid)
    {
    }

#define MAX_RESULT_LENGTH 250
char *fileExists(
    UDF_INIT *initid, UDF_ARGS *args, char *result,
    unsigned long *length, char *is_null, char *error)
 {
 //bad filename
 if(args->args[0]==NULL  || args->lengths[0]==0 || args->lengths[0] >= FILENAME_MAX )
    {
    strncpy(result,"#BAD_INPUT",MAX_RESULT_LENGTH);
    }
  else
    {
          char  filename[FILENAME_MAX+1];
      int err;
      int in;
      //create a NULL terminated string
      memcpy(filename,args->args[0],args->lengths[0]);
      filename[args->lengths[0]]=0;
      errno=0;
      in=open(filename,O_RDONLY|O_NDELAY);
      err=errno;
      if(in<0)
        {
        snprintf(result,MAX_RESULT_LENGTH,"#ERR:\"%s\":\"%s\".",strerror(err),filename);    
        }
      else
        {
        close(in);
        snprintf(result,MAX_RESULT_LENGTH,"OK:\"%s\".",filename);   
        }
    }
  *length=strlen(result);
  return result;
  }

Make:

gcc -Wall -DMYSQL_VERSION -fPIC -shared `mysql_config --cflags` -o `mysql_config --plugindir`/libfileexists.so udffileexists.c  `mysql_config --libs `

Test:

ok, mysql can open some files:

mysql>  create function fileExists RETURNS STRING SONAME 'libfileexists.so'; select fileExists("/etc/mysql/my.cnf"); drop function fileExists;
Query OK, 0 rows affected (0.00 sec)

+---------------------------------+
| fileExists("/etc/mysql/my.cnf") |
+---------------------------------+
| OK:"/etc/mysql/my.cnf".         | 
+---------------------------------+
1 row in set (0.00 sec)



mysql>  create function fileExists RETURNS STRING SONAME 'libfileexists.so'; select fileExists("/tmp/file.txt"); drop function fileExists;
Query OK, 0 rows affected (0.00 sec)

+-------------------------------+
| fileExists("/tmp/file.txt")   |
+-------------------------------+
| OK:"/tmp/file.txt".           | 
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

OK, no problem, it works, I can open() file.txt in /tmp/

ls -la /tmp 
drwxrwxrwt 16 root    root       4096 2010-05-28 15:45 .
-rw-r--r--  1 lindenb lindenb       0 2010-05-28 15:25 file.txt

But when I want to test a file in /data:

ls -la /data
drwxrwxrwx  4 root    root        4096 2010-05-28 16:11 .
-rw-r--r--  1 lindenb lindenb        0 2010-05-28 15:25 file.txt

I got:

mysql>  create function fileExists RETURNS STRING SONAME 'libfileexists.so'; select fileExists("/data/file.txt"); drop function fileExists;
Query OK, 0 rows affected (0.00 sec)

+--------------------------------------------+
| fileExists("/data/file.txt")               |
+--------------------------------------------+
| #ERR:"Permission denied":"/data/file.txt". | 
+--------------------------------------------+
1 row in set (0.00 sec)

Any idea ?

Thanks !

mysqld was protected by apparmor.

AppArmor represents one of several possible approaches to the problem of restricting the actions that installed software can take.

I added

/data/** r,

at the end of

/etc/apparmor.d/usr.sbin.mysqld

apparmor was restarted:

/etc/init.d/apparmor restart

and now my UDF works fine ! :-)

Why are you using the file open on it? Perhaps it might be better to do it this way:

char *fileExists(
    UDF_INIT *initid, UDF_ARGS *args, char *result,
    unsigned long *length, char *is_null, char *error)
{
 //bad filename
 if(args->args[0]==NULL  || args->lengths[0]==0 || args->lengths[0] >= FILENAME_MAX )
 {
   strncpy(result,"#BAD_INPUT",MAX_RESULT_LENGTH);
 }
 else
 {
   char  filename[FILENAME_MAX+1];
   int err;
   int in;
   struct stat statbuffer;
   //create a NULL terminated string
   memcpy(filename,args->args[0],args->lengths[0]);
   filename[args->lengths[0]]=0;
   errno=0;
   if (!stat(filename, &statbuffer)){
     if (S_ISREG(statbuffer.st_mode)){
        snprintf(result,MAX_RESULT_LENGTH,"OK:\"%s\".",filename);
     }else{
        snprintf(result,MAX_RESULT_LENGTH,"#ERR:\"%s\":\"%s\".",strerror(err),filename);
     }
   }else{
     snprintf(result,MAX_RESULT_LENGTH,"#ERR:\"%s\":\"%s\".",strerror(err),filename);
   }
 }
 *length=strlen(result);
 return result;
}

Instead of opening the file, use the stat, this would seem to be the reliable way of checking if the file exists, and also, it would be easier to home in on exactly why would the stat function fail.