PHP/MySQL/Linux: File upload, database import and access right problems

Posted on by

On an (Apache) web server one should establish a policy regarding access rights to the files of hosted (virtual) domains. Especially with respect to files transferred by FTP or by uploads for a browser. It was an interesting experience for me that uploading files during an Ajax communication with a PHP program and moving them to target directories with PHP’s “move_uploaded_file” can collide with such policies and lead to unexpected results.

The problem

I recently tested Ajax controlled csv file transfers from a browser to a web server with a subsequent loading of the file contents to a database via PHP/MySQL. The database import was initiated by PHP by executing the SQL command “LOAD DATA INFILE” on the MySQL server. This chain of processes worked very well:

The uploaded csv file is moved from PHPs $_FILES superglobal array (which works as an input buffer for uploaded files) to a target directory on the web server by the means of the PHP function “move_uploaded_file”. My PHP program – the Ajax counterpart on the server – afterwards triggers a special MySQL loader procedure via the “LOAD DATA INFILE” command. MySQL then loads the data with very high speed into a specified database table. It is clear that the import requires sufficient database and table access rights which have to be specified by the PHP program when opening the database connection via one of PHP’s MySQL interfaces.

The overall success of the upload and database import sequence changed, however, in a surprising way when I wanted to transfer my (rather big) files in a zip-compressed form from the browser to the web server.

So I compressed my original csv file into a zip container file. This zip file was transferred to the server by using the same a web site formular and Ajax controls as before. On the server my PHP program went through the following steps to make the contents of the zip container available for further processing (as the import into the database) :

  • Step 1: I used “unlink” to delete any existing files in the target directory.
  • Step 2: I used “move_uploaded_file” to save the zip file into the usual target directory.
  • Step 3: I used the “ZipArchive” class and its methods from PHP to uncompress the zip-file content within the target directory.

Unfortunately, the “LOAD DATA INFILE” command failed under these conditions.

However, everything still worked well when I transferred uncompressed files. And even more astonishing:
style=”margin-left:20px;”>When I first uploaded the uncompressed version and then tried the same with the zip-version BUT omitted Step 1 above (i.e. did not delete the existing file in the target directory) “LOAD DATA” also worked perfectly.

It took me some time to find out what happened. The basic reason for this strange behavior was a peculiar way of how file ownership and access rights are handled by “move_uploaded_file” and by the method ZipArchive::extractTo. And in a wiggled way it was also due to some naivety on my side regarding my established access right policy on the server.

Note in addition: The failures took place although I used SGID and setfacl policies on the target directory (see below).

User right settings on my target directory

On some of my web servers – as the one used for the uploads – I restrict access rights to certain directories below the root directories of some virtual web domains to special user groups. One reason for this is the access control of different developer groups and FTP users. In addition I enforce automatic right and group settings for newly created files in these directories by ACL (setfacl) and SGID settings.

The Apache process owner becomes a member of the special group(s) owning these special directories. Which is natural as PHP has to work with the files.

Such an access policy was also established for the target directory of my file uploads. Let us say one of these special groups would be “devops” and our target directory would be “uploads”. Normally, when a user of “devops” (e.g. the Apache/PHP process)

  • creates a file in the directory “uploads”
  • or copies a file to the directory “uploads”

the file would get the group “devops” and “-rw-rw—-” rights – the latter due to my ACL settings.

What does “move_uploaded_file” do regarding file ownership and access rights ?

The first thing worthwhile to note is that the politics for what this PHP function does regarding ownership settings and access rights changed at some point in the past. It furthermore seems to depend on the fact whether the target directory resides on a different (mounted) file system. See the links given at the bottom of this file for more information.

Some years ago the right settings for the moved file in the target directory were “0600”. This obviously has changed :

  • The right settings of moved files today are “0644” (independent of file system questions).

However, what about the owner and the group of the moved file? Here “move_uploaded_file” seems to have it’s very own policy:

  • It sets the file owner to the owner of the Apache web server process (on my Opensuse Linux to “wwwrun“).
  • It sets the group always to “www” – and it does so
    independent of

    • whether the SGID sticky bit is set for the target directory or not (!) ,
    • whether the Apache web server process owner really is a member of “www” (!) ,
    • whether a file with the same name is already existing in the target directory with a maybe different group !

Funny, isn’t it? Test it out! I find the last 3 facts really surprising. They do not reflect a standard copy policy. As a result in my case the uploaded file always gets the following settings when saved to the file system by “move_uploaded_file”:

owner “wwwrun” and group “www” and the access rights “0644”.

So, after the transfer to the server, my target file ends up with being world readable!

What does ZipArchive do with respect to ownership and access rights ?

As far as I have tested “ZipArchive::extractTo” I dare say the following: It respects my sticky bit SGID and ACL settings. It behaves more or less like the Linux “cp” command would do.

So, when ZipArchive has done it’s extraction job the target file will have very different settings:

owner “wwwrun”, BUT group “devops” and the access rights “0660”.

However, this is only the case if the target file did not yet exist before the extraction !
If a file with the same name already existed in the target directory “ZipArchive::extractTo” respects the current owner/group and access rights settings (just as the cp command would have done!). Test it out!

The impact on MySQL’s “LOAD DATA INFILE …”

The MySQL process has its own owner – on my system “mysql”. When PHP issues the SQL command “LOAD DATA INFILE” by one of it’s MySQL interfaces the MySQL engine uses an internal procedure to access the (csv) file and loads it’s content into a database table. You may rightfully conclude that the opening of the file will be done by a process with “mysql” as the owner.

nSo, it becomes clear that not only the rights of the Apache/PHP process or database access rights are important in my scenario described above:

The MySQL (sub-) process itself must have access rights to the imported file!
 
But as we have learned: This is not automatically the case when the ZipArchive extraction has finalized – if “mysql” is not by accident or purpose a member of the group “devops”.

Now, take all the information given above together – and one understands the strange behavior:

  • When I uploaded the uncompressed file it got rights such that it was world readable and it’s contents could therefore be accessed by “mysql”. That guaranteed that “UPLOAD DATA INFILE” worked in the first standard scenario without a zip file.
  • If the target file is not deleted and exists before a zip extraction process it will be rewritten without a change of it’s properties. That makes the “LOAD DATA INFILE” work also after a zip extraction as long as we do not delete a previously existing target file from a standard upload with the same name and world readability.
  • However, in case of an emptied target directory the extraction process respects the SGID and ACL settings – and then “mysql” has no read access right for the file!

A conclusion is that I had stumbled into a problem which I had partially created by myself:

I had established a policy for group and right settings which was respected when extracting zipped files. This policy collided with required file access rights for the MySQL processes. Stupid me! Should have taken that into account !

Accidentally, my politics was not respected when uploading and handling standard files directly without encapsulating it in a zip container. Due to the failure of my policy and the disregard of special right settings by “move_uploaded_file” the subsequent MySQL process could use “LOAD UPLOAD INFILE”. Not what I wanted or had expected – but the policy violation almost went undetected due to the overall success.

Conclusions

I hope that the points discussed in this article have made it clear that the access rights of uploaded and/or zip-extracted files are something to take good care of on a Linux web server:

Do not rely on any expectations regarding of how PHP functions and objects may handle user/group ownerships and access rights! In case of file uploads (with and without zip containers) test it out, check the behavior of your programs and set the rights with available PHP file handling functionality explicitly to what to want to achieve.

The consequences of the peculiar rights settings of “move_uploaded_file” and it’s ignorance of SGID/ACL policies must be taken into account. Other PHP routines may handle access rights of copied/moved/extracted files differently than “move_uploaded_file”. Furthermore, if you want to load data from uploaded (csv) files into the database take into account that the “mysql” user needs read access rights for your file.

Solutions for my scenario with zip files

Let us assume that I follow my advice above and set the rights for files saved by “move_uploaded_file” explicitly, namely such that my policy with the group “devops” and the “0660” rights is respected. Than – without further measures – no uploaded file could be handled by the MySQL “UPLOAD DATA INFILE” mechanism. In my special case one could think about several possible solutions. I just name 3 of them:

Solution 1: If – for some reasons – you do not even temporarily want to break your SGID/ACL policies for the uploaded and extracted file in your target directory, you could make “mysql” a member of your special group (here “devops”). Note that this may also have some security
implications you should consider carefully.

Solution 2: You may set up another target directory with special access rights only to “mysql” and “wwwrun” by defining a proper group. This directory would only be used to manage the data load into the database. So, copy or move your uploaded/extracted file there, set proper rights if necessary and then start the “UPLOAD DATA” process for this target file. And clean the special directory afterwards – e.g. by moving your upload file elsewhere (for example to an archive).

Solution 3: You may change the access rights and/or the group of the file temporarily by the PHP program before initiating the MySQL “LOAD DATA” procedure. You may reset rights after the successful database import or you may even delete or move the file to another directory (by rename) – and adjust its rights there to whatever you want.

Whatever you do – continue having fun with Ajax and PHP controlled file uploads. I shall come back to this interesting subject in further articles.

Links

New access right settings of move_uploaded_file
https://blog.tigertech.net/posts/php-upload-permissions/
Old access right settings by move_uploaded_file and ignorance of SGID sticky bit
http://de1.php.net/manual/en/function.move-uploaded-file.php#85149
cp and access rights (especially when a file already exists in the target directory)
http://superuser.com/questions/409226/linux-permissions-and-owner-being-preserved-with-cp
The use of SGID
http://www.library.yale.edu/ wsg/docs/ permissions/ sgid.htm