How to handle file uploads

Xataface allows you to store file uploads in BLOB fields or on the file system.

Many applications need to be able to handle file uploads in some way shape or form, whether it be for uploading a logo to accompany a company profile or a PDF file as a resume for a job applicant. Xataface supports file uploads in 2 flavours:

Storing files in BLOB fields inside the database
Storing files on the file system and using a VARCHAR field to store the path to the file.

Method 1: Storing files the database (as a BLOB field)

I will describe by way of example. Suppose we wish to add a field called PDFDescription to the "Course" table in our FacultyOfWidgetry application (from the Getting Started with Xataface tutorial. We want this field to store a PDF version of the course description. We do the following:

Add the following fields to the Course table:
- PDFOutline LONGBLOB : This is the blob field that will actually store the PDF content.
- PDFOutline_mimetype VARCHAR(64) : This field will store the mimetype of the blob field.
- PDFOutline_filename VARCHAR(64) : This field will store the name of the file that is stored in the blob field.
Note that the PDFOutline_mimetype, and PDFOutline_filename fields are named based on the naming convention <filename>_mimetype, and <fieldname>_filename, where <fieldname> is the name of the Blob field. If you do not follow this naming convention and prefer to use different names for your mimetype and filename fields, you will need to specify these fields in the fields.ini file.
Open the application in your browser to see the changes. The PDFOutline field will appear as follows:

If there is already a file stored in the PDFOutline field then the widget will include a link to download the file (in a new window) as follows:

Accessing files stored in the database

One thing that may scare you about storing files in the database is that they may seem less accessible than if they were on the file system. In fact, Xataface makes it easy to access the files. One way is to click on the "View Field Content in new Window" link on the edit form for the record (as shown above). If you look at the url of this link, you will notice that you can access files directly from the URL. An example URL is:

http://powerbook.local/~shannah/FacultyOfWidgetry/index.php?-action=getBlob&-table=Course&-field=PDFOutline&CourseID=1

This URL will retrieve the contents of the PDFOutline field for the course with CourseID = 1.

Method 2: Storing files on the file system

Sometimes it may be more convenient to store the files in a folder on the file system and just store the names of the files in the database. This is also possible with Xataface. Follow these steps to implement the previous example with file system storage:

Create a new field in the "Course" table named PDFOutline of type VARCHAR. This will store the name of the file that is uploaded.
Add the following to the fields.ini file for the "Course" table:
```
[PDFOutline]
Type = container
widget:type = file
```
Pitfall: Make sure you enter Type = container and not type = container (note the capital 'T'). Xataface config files are case sensitive.
Create a directory named "PDFOutline" in the "tables/Course" directory and make it writable by the web server (e.g., chmod 777). This is where the uploaded files will actually be stored. Your directory structure for the FacultyOfWidgetry application will now look like:
Load up your application in a web browser and try it out:

From the web browser this looks no different than if the file was stored inside the database. However if you look on the file system inside the tables/Course/PDFOutline directory, you will also be able to see the files that have been uploaded.

NOTE: Allowing users to upload files to a directory of the web server opens up possible security holes. If you allow uploads, you should either disable script execution in that directory, limit file types that can be uploaded to exclude scripts, or use security and block direct access to the upload directory entirely.

Specifying a custom upload directory

If, for some reason, you don't want the files to be uploaded to the tables/<tablename>/<fieldname> directory, you can specify a different directory by adding the "savepath" and "url" attributes to the fields.ini file:

[PDFOutline]
Type = container
widget:type = file
savepath = /path/to/save_directory ; The file system save path to save the files
url = /web/url/path/to/save_directory ; The path from the document root to this 
                                          ; save directory (so that the files may be accessed online).

Note: make sure that the directory specified by "savepath" is writable by the web server. The current release of Xataface does not fail very gracefully if you forget to do this. You will just get a blank screen when you try to upload files if this directory is not writable. Future versions will provide a more descriptive error message, but for now, treat this as a warning.

Restricting mimetypes and extensions

For security reasons, it is a good idea to restrict the mimetypes and extensions that can be uploaded if you are storing files on the file system. For example: someone could upload a malicious PHP script and then access the script using their web browser causing the script to execute using the web server's permissions. For this reason it is a good idea to declare explicitly what mimetypes and file extensions are allowed to be uploaded into a particular field. This can be done using the following configuration directives in the fields.ini file:

allowed_mimetypes : A comma-delimited list of mimetypes that are allowed in this field. (e.g., image/gif)
disallowed_mimetypes : A comma-delimited list of mimetypes that are NOT allowed in this field. (e.g., image/gif)
allowed_extensions : A comma-delimited list of file extensions that are allowed in this field. (e.g., pdf)
disallowed_mimetypes : A comma-delimited list of file extensions that are NOT allowed in this field (e.g., pdf)

Example 1: File field allowing only pdf files based on mimetype: (snippet from fields.ini file)

[File]
Type = container
widget:type = file
allowed_mimetypes = application-x/pdf

Example 2: File field allowing pdf files based on extension.

[File]
Type = container
widget:type = file
allowed_extensions = pdf

Example 3: File field allowing files with pdf and ppt extensions (powerpoint and pdf)

[File]
Type = container
widget:type = file
allowed_extensions = pdf,ppt

Security Considerations

Allowing file uploads to the file system opens up some possible security holes. For example, it is possible that users could upload a PHP script to a directory, then run the PHP script through their web browser. This is a serious concern tha you need to take steps to block if you are going to allow file uploads to the file system (i.e. using the "Container" method described above).

Restrict Mimetypes and Extensions

One solution to this problem is to prevent users from uploading PHP scripts and the like by restricting mimetypes and extensions. Read more on this method.

Restricting Script Execution in Upload Directory

If you are using Apache web server you can also prevent PHP scripts (and other scripts) from being executed inside the uploads directory. This can be done by placing an .htaccess file inside your uploads directory with the following contents:

AddHandler cgi-script .php .pl .jsp .asp .sh .cgi
Options -ExecCGI

What this does is tells apache to use the cgi-script handler to deal with files of the listed extensions. It then tells Apache that CGI script execution should be disallowed.

Blocking Access To The Uploads Directory

Another method of securing uploads is to block access to the uploads directory altogether and use the secure directive to tell Xataface to serve the files through its get_blob action. This can be achieved by adding an .htaccess file to the uploads directory with the following contents:

Deny from all

Pages