Skip to content
Tutorials Center
My learning

Topics

Lesson 138 of 147 ยท Projects

Project: Upload Manager with Database Records

Build a PHP upload manager in four stages - normalize multiple uploads, validate size and real MIME type, store files and metadata in MySQL with PDO, then list, download and delete them.

A plain upload form saves a file somewhere and forgets about it. A real application needs to remember each file: who uploaded it, the original name, its size and type, when it arrived, and where it lives, so that it can be listed, downloaded, searched and deleted later. That is the job of an upload manager, and it is the point where file uploads meet databases.

You will build one in four stages: (1) read multiple files from one form, (2) validate each of them properly, (3) store the files on disk and their metadata in MySQL through PDO (and learn when a BLOB column is the better choice), and (4) list, download and delete them. The editor cannot receive real browser uploads and has no MySQL server, so the programs simulate $_FILES with temporary files and use an in-memory SQLite database, which PDO treats almost identically; the MySQL version of each statement is shown next to it.

Before you start, read File Uploads, Prepared Statements, CRUD with PDO and, for downloads, the File Downloader project.

The plan

flowchart TD
  A["Form: input type=file multiple"] --> B["Normalize $_FILES into a list"]
  B --> C{"For each file: valid?"}
  C -->|"no"| D["Collect an error message"]
  C -->|"yes"| E["Move to storage under a random name"]
  E --> F["INSERT metadata row"]
  F -->|"fails"| G["Delete the stored file"]
  F -->|"ok"| H["List page: SELECT rows"]
  H --> I["Download by id / delete by id"]

The database row and the stored file must stay in step: never keep one without the other.

Stage 1: reading several files from one form

A form with <input type="file" name="docs[]" multiple> sends several files under one name. PHP shapes $_FILES in an awkward way: instead of a list of files, you get one array per property, each holding all the values.

HTML
<form method="post" enctype="multipart/form-data">
  <input type="file" name="docs[]" multiple>
  <button>Upload</button>
</form>

The enctype="multipart/form-data" is mandatory; without it the browser sends only the file names. To make the data easy to work with, convert it into a list where each element describes one file. The function also skips the "no file chosen" entry (error code 4).

PHP Stage 1: normalizing $_FILES

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Notice the type entry: it is whatever the browser claims, and a malicious client can claim anything. We never trust it in the next stage.

Stage 2: validating each file

Validation has five checks, in this order:

  1. Upload error code. UPLOAD_ERR_OK is 0. Codes such as UPLOAD_ERR_INI_SIZE (the file exceeded upload_max_filesize in php.ini) deserve readable messages.
  2. Own size limit. A business rule such as 5 MB, independent of php.ini.
  3. is_uploaded_file(). Confirms the temporary file really came from this request's upload and was not injected by an attacker pointing at /etc/passwd. On a real server it is automatic when you use move_uploaded_file().
  4. Real MIME type. Use finfo to inspect the file's content, not its name or the browser's claim.
  5. Extension whitelist. The stored extension must match the detected type, from a map you control.
PHP Stage 2: validating uploads

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The file called cute.jpg is really a PHP script, and finfo sees through the name. This is why the stored extension comes from the detected type and never from the visitor's file name. Servers can also limit uploads globally (upload_max_filesize, post_max_size, max_file_uploads); a form that exceeds post_max_size arrives with an empty $_FILES and $_POST, which you should detect with $_SERVER['CONTENT_LENGTH'].

Stage 3: store the file and its metadata

For each valid file we do three things: choose a random stored name (the visitor's name is kept only as data), move the file into a storage folder, and insert a row. The stored name avoids collisions and prevents someone from overwriting index.php. If the insert fails, the file is deleted again so no orphans stay behind.

The table in MySQL:

SQL
CREATE TABLE uploads (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  original_name VARCHAR(255)  NOT NULL,
  stored_name   VARCHAR(64)   NOT NULL UNIQUE,
  mime_type     VARCHAR(100)  NOT NULL,
  size_bytes    BIGINT UNSIGNED NOT NULL,
  sha256        CHAR(64)      NOT NULL,
  uploaded_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (uploaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The SQLite version in the program below differs only in the types. The PHP code is the same for both; only the connection string changes (mysql:host=127.0.0.1;dbname=app;charset=utf8mb4).

PHP Stage 3: storing files and records

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The visitor's name ../../Q4 report.pdf is reduced to Q4 report.pdf and stored only as text. The file on disk has a name nobody can guess or abuse. Hashing with sha256 also lets you detect duplicates: look for an existing row with the same hash before storing again.

Disk or BLOB?

You can also store the file inside the database in a BLOB column (use MEDIUMBLOB up to 16 MB or LONGBLOB up to 4 GB in MySQL). The code is a binary parameter:

PHP
$stmt = $pdo->prepare('INSERT INTO uploads (original_name, mime_type, data) VALUES (?, ?, ?)');
$stmt->bindValue(1, $name);
$stmt->bindValue(2, $mime);
$stmt->bindValue(3, file_get_contents($tmpName), PDO::PARAM_LOB);   // or fopen() a stream
$stmt->execute();
Files on disk + metadata BLOB in the database
Backups two things to back up in step one backup holds everything
Transactions file and row can drift apart file and row commit together
Speed / size fast, served by the web server database grows, memory pressure
Replication, scaling needs shared storage or object storage travels with the database
Best for most sites, large files, media small files (avatars, signatures), strict consistency

Most projects keep files on disk or in object storage (such as S3) and metadata in the database, which is what this project does.

Stage 4: list, download and delete

The list page selects the metadata, never the file contents, and prints every value escaped. A download looks up the row by id and streams the stored file with the original name as the suggested file name (the headers are explained in the downloader project). Delete removes the row and the file, inside a transaction so that a failure leaves nothing half-done.

PHP Stage 4: list, download and delete

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Notice what is missing on purpose: access control. Real pages must check that the logged-in user owns the file before listing, downloading or deleting it (add a user_id column and a WHERE user_id = ? condition), and the delete action must be a POST protected by a CSRF token.

Ideas for extending the project

  • Add a user_id column and show each user only their own files.
  • Generate thumbnails for images with the tools from Image Processing.
  • Reject duplicates by looking up the SHA-256 hash first.
  • Move the files to object storage and keep only the key in the database.
  • Show an upload progress bar with a little JavaScript (AJAX).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does $_FILES for a multiple-file input need to be normalized?

    With name="docs[]", $_FILES['docs']['name'] is a list of names, ['type'] a list of types and so on. Converting it to a list of file arrays makes it simple to loop over.
  2. 2. Why should the stored file name be random and its extension come from the detected type?

    The visitor controls the original name and can lie about the type. A random name plus an extension from a whitelist removes both risks.
  3. 3. What does finfo(FILEINFO_MIME_TYPE) have over $_FILES['x']['type']?

    The type in $_FILES is sent by the client and can be anything. finfo reads the file's bytes.
  4. 4. What should happen to the stored file if the INSERT fails?

    Disk and database must agree. A file with no row can never be listed or deleted by the application.
  5. 5. When is a BLOB column a good choice for uploaded files?

    BLOBs keep file and record together but make the database large. For big media prefer disk or object storage.

Summary

  • Use enctype="multipart/form-data" and name="files[]" multiple; normalize $_FILES into a list, skipping UPLOAD_ERR_NO_FILE.
  • Validate the error code, your size limit, is_uploaded_file(), the real MIME type with finfo and a whitelist of extensions.
  • Store files under random names with move_uploaded_file(); keep the original name, type, size and a hash in a table, inserted with prepared statements.
  • Remove the stored file when the insert fails, and delete the row and file together.
  • List only metadata, escape it, and serve downloads by id with Content-Disposition.
  • Choose between files on disk (default) and BLOB columns (small files, one backup); always add ownership checks and CSRF protection.

Next: PHP Cheat Sheet