Uploading and Inserting Files into Databases

Example 6-2 and Example 6-3 also show how files can be uploaded from a web browser to a web server, the file data inserted into a database, and the data then retrieved and displayed as part of a web page. In the examples, the file uploaded is a GIF image, but the techniques can be applied to any file or content type.

Files are transferred using the <form> encoding type multipart/form-data and the POST method. Most modern browsers-such as Netscape and Internet Explorer-support this encoding type and the <input> of type file. The <input> of type file displays a widget into which the user can enter a filename; it also displays a Browse button that displays a file dialog for finding files. Therefore, the following fragment from Example 6-2 is all that is needed for a user to select a file and for it to be transferred from a browser to a server:

<form enctype="multipart/form-data" action="example.6-2.php" method="post">
<br>Region map (GIF format):
<input name="userfile" type="file">
<br><input type="submit">

The uploaded file and information about it can be accessed directly at the web server using PHP. Assuming the <input> widget of type file has a name=userfile, the name of the file on the web server can be accessed as $userfile. The original name of the file on the browser can also be accessed as $userfile_name, the file size as $userfile_size, and the type of the file as $userfile_type.

The following fragment from Example 6-3 checks if a file has been uploaded and, if so, reads the contents of the file into the variable $fileContents:

     // Was an image file uploaded?
     if (is_uploaded_file($userfile))
       // Open the uploaded file
        $file = fopen($userfile, "r");
       // Read in the uploaded file
        $fileContents =
          fread($file, filesize($userfile));
       // Escape special charcters in the file
        $fileContents = AddSlashes($fileContents);
        $fileContents = NULL;

The library function is_uploaded_file( ) should always be used to make sure the file being processed was actually uploaded to the web server. Without the check, a security problem can arise if the user supplies the filename of a file on the web server as a value for userfile using a GET or POST request.

The function fopen( ) opens a file on disk; in this example, it opens the file in read mode by supplying the r flag as the second parameter. The function fread( ) reads the contents of a file, in this case into the variable $fileContents. In this example, the number of bytes read from the file is the file size, determined by using the function filesize( ). After reading the file, any special characters are escaped by adding slashes using the AddSlashes( ) function. It's necessary to do this before the content of the file can be added to the database.

The file data in $fileContents is then inserted in the same way as any other data into the region table:

// Insert region data, including the image file
$insertQuery = "INSERT INTO region VALUES
               (NULL, " .
               "\"" . $regionName . "\", " .
               "\"" . $description . "\", " .
               "\"" . $fileContents . "\")";

The end result is that a new region has a name, a textual description, and an associated GIF image stored as the map attribute.

Displaying images from a database is straightforward. The script shown in Example 6-4 retrieves a map image from the region table and outputs the image using the echo statement. The region_id of the required image is supplied as a parameter using the GET method. A header is output to the browser that defines the MIME type of the image, in this case image/gif, and the data follows.

Example 6-4. A script to retrieve GIF images from the region table map attribute
  include '';
  include '';
  $region_id = clean($region_id, 2);
  if (empty($region_id))
  // Connect to the MySQL DBMS
  if (!($connection = @ mysql_pconnect($hostName,
     die("Could not connect to database");
  if (!mysql_select_db($databaseName, $connection))
     showerror(  );
  $query = "SELECT map FROM region
            WHERE region_id = $region_id";
  // Run the query on the DBMS
  if (!($result = @ mysql_query ($query,$connection)))
  $data = @ mysql_fetch_array($result);
  if (!empty($data["map"]))
    // Output the GIF MIME header
     header("Content-Type: image/gif");
    // Output the image
     echo $data["map"];

The script in Example 6-4 is requested by an embedded <img> tag in Example 6-3:

echo "\n<br>Region map : " .
     "<img src=\"example.6-4.php?region_id=" .
     $regionId . "\">";

The result is that when the user views the receipt page in Example 6-3, the uploaded image from the database is displayed.

The techniques we have described work for small files such as most GIF images. Several additional configuration steps are required if files larger than a few megabytes are to be uploaded:

  • As in Example 6-2, an additional hidden <form> field must be added to specify the maximum allowed upload file size, such as:

    <input type="hidden" name="MAX_FILE_SIZE" value="100000">
  • The memory limit of a PHP script should be greater than the maximum file size. This can be set by adjusting the memory_limit parameter in the php.ini file, which was copied to /usr/local/lib/ in the installation instructions in Appendix A.

  • The maximum file upload size should be set by modifying the upload_max_filesize parameter in the php.ini file.

  • The maximum POST size should be set to be greater than the maximum file size by modifying the post_max_size parameter in the php.ini file.

  • The maximum execution time for a PHP script should set to an appropriate value to allow the upload to complete. The default value is 30 seconds. The parameter can be changed by modifying the max_execution_time parameter in the php.ini file.

  • The web server must be restarted after any changes, so that the php.ini configuration file is reread. This can be done by executing the command apachectl restart in the directory /usr/local/apache/bin/, assuming the installation instructions in Appendix A were followed.