PHP: file functions (filesize, readile, unlink) don't work for file created by mysql

Question

I have the following query (apache, php-7.4.33, mysql):

SELECT [blah] from [blah] INTO OUTFILE $tmpfile FIELDS TERMINATED BY ',' FIELDS TERMINATED BY '\n';

I am trying to download this from the server using this code:

header("Pragma: public"); 
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Cache-Control: private",false);
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=\"contacts.csv\";" );
header("Content-Transfer-Encoding: binary");
header("Content-Length: ".filesize( $tmpfile ));
readfile( $tmpfile );
unlink( $tmpfile );

I get error messages for filesize(), readfile() and unlink() and the uploaded file is empty eg:

PHP Warning:  filesize(): stat failed for /tmp/contacts-25178.csv in /[blah]/index.php

The file exists if I go to the command line:

$ ls -l /tmp/contacts-25178.csv
-rw-r--r-- 1 mysql mysql 151253 Jan 24 21:29 /tmp/contacts-25178.csv

Can anybody explain this for me?

Answer 1

-rw-r--r--

This says everyone should be able to read the file, but that only applies of they can read the path (i.e. each of the directories) where the file resides.

/tmp/

On a Unix systems everyone usually has access to /tmp, but if you ls -ld /tmp, you should see something like this for the permissions:

drwxrwxrwt

See that 't' at the end? That means something special. But again, it should not prevent reading of the file.

So based on the information you have provided there is no reason why your PHP code can't read the file. But there are 2 other possiblities:

1. Your PHP service is running in a chroot environment - it can't reach the /tmp directory

2. There is Mandatory Access Control system preventing access. This might be SELinux on RHEL/derivatives, Apparmor on Debian and others or it could be implemented by the systemd filesystem namespace (either blocking or re-mapping). See https://serverfault.com/questions/847156/is-there-a-way-to-control-filesystem-access-with-systemd

(for completeness, I should mention facls here - but that is unlikely to apply here)

Mentioning what OS/distribution this is running on and how you sourced / deployed the PHP runtime would have cut down on the possibilites.

You can use an explicit path in SELECT...INTO OUTFILE... but bear in mind that mysqld might be restricted in a similar (or even a different maneer) as to where it can write files.