MySQL Bugs: #72027: LOAD_FILE() does not work on dynamic files

Bug #72027	LOAD_FILE() does not work on dynamic files
Submitted:	12 Mar 2014 23:05	Modified:	17 Dec 2018 19:18
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.6, *	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
LOAD_FILE() will not be able to read files off /proc/ or various FUSE'd or dynamic partitions, as it uses stat() information to allocate buffers and commence reads, thus it will return an empty string, e.g.:

How to repeat:
mysql> select load_file('/proc/self/stat');
+------------------------------+
| load_file('/proc/self/stat') |
+------------------------------+
|                              |
+------------------------------+
1 row in set (0.09 sec)

Suggested fix:
stat() should be treated as advisory, reads chunked by some properly sized buffer, etc

do note, it is actually incorrect value instead of a NULL or something that would indicate anything wrong

code in question:

  if (!mysql_file_stat(key_file_loadfile, path, &stat_info, MYF(0)))
    goto err;
...
  if (tmp_value.alloc(stat_info.st_size))
    goto err;
  if ((file= mysql_file_open(key_file_loadfile,
                             file_name->ptr(), O_RDONLY, MYF(0))) < 0)
    goto err;
  if (mysql_file_read(file, (uchar*) tmp_value.ptr(), stat_info.st_size,
                      MYF(MY_NABP)))
  {
    mysql_file_close(file, MYF(0));
    goto err;
  }

Hello Domas,

Thank you for the bug report and test case.
Verified as described.

Thanks,
Umesh

is DML category correct? LOAD_FILE does not edit any data :)

Hello Domas,

I agree, but due to some technical reasons(Server and Server:General are not really supported in the UI for mapping to internal BugDB) I had used closest one. :)

Thanks,
Umesh

Hi Domas,

we discussed this case internally and decided that allowing LOAD_FILE to load file from any directory, except specified by secure_file_priv option, is dangerous and can lead to serious security issues.

However we think that your request is still can be useful and can be implemented as new function, UDF function or plugin.

In order for us to do such a function more useful could you please explain why you need such possibility, how you are going to use (or store) "files off /proc/ or various FUSE'd or dynamic partitions".

Please also let us know if UDF or plugin will work for you.

Thanks in advance!

Well, this is awkward. The bug was not about LOAD_FILE() security, it was about very limited scope, where it failed due to not actually trying to read the file. 

Yes, secure_file_priv is an important limiter, and people should use it, albeit it does not mean that features should not be fixed because someone can run with insecure setup. 

What name would you suggest for new function or UDF or plugin, LOAD_FILE_THAT_WORKS() ? LOAD_FILE_BUT_BETTER()? LOAD_FILE_IS_BROKEN_BETTER_USE_THIS() ?

By telling me to use UDF you just told me to **** myself. I understand that lots of broken behaviors can be worked around with UDF. 
Type problems? Use UDF to compare/set/handle arbitrary data, why bother using such overburdened operators such as '='

Why would this be useful?

I would be able to use MySQL to access server-data such as /proc/stat or /proc/uptime or /proc/self/stat or /proc/self/statm because MySQL does not export any of that data through any of its existing interfaces at the moment. And thats just /proc/, there're lots of various useful things one can do by reading various files.

Hi Domas,

thank you for the feedback.

Got your point:

mysql> select @@secure_file_priv;
+--------------------+
| @@secure_file_priv |
+--------------------+
| /proc/5930/        |
+--------------------+
1 row in set (0.00 sec)

mysql> select load_file('/proc/5930/stat');
+------------------------------+
| load_file('/proc/5930/stat') |
+------------------------------+
|                              |
+------------------------------+
1 row in set (0.00 sec)

But what the issue which developers have with such kind of files is: "Some of these non-files are having no end (e.g. the process on the other side keeps adding). So it's a bit unclear ... how to convert these into runtime strings." Therefore implementing this request means we need to think about ways how to store such files in the database. For example, LOAD_FILE in case of such a file can run forever. Thus the idea of implementing new function for this purpose.

And we need to know a little bit more about your original use case to implement this properly.

For example, I can elaborate a use case of parsing such files, then storing result in the database. Something similar to processing piped logs in Apache HTTPD server (http://httpd.apache.org/docs/1.3/logs.html#piped), but by the database means. Could you please provide couple examples from your side?

Oh, apparently this went nowhere without me showing original use cases. My original use case was reading from "/proc/self/stat", like I showed in the initial description.

Posted by developer:
 
Fixed in 8.0.15.

The LOAD_FILE() function could fail for files for which stat() should
be considered only advisory, such as files under /proc.