MySQL BLOB with PHP: Secure File Upload and Display

Storing an image in MySQL sounds simple until the first upload contains the wrong MIME type, exceeds the server limit, or returns a broken image icon. The database is rarely the difficult part. Handling the file safely is where most of the work lives.

This tutorial builds a complete PHP 8.2+ example using MySQLi. It validates an uploaded image or PDF, stores the binary content and metadata in a MySQL BLOB, lists the saved files, and returns each file through a controlled PHP endpoint.

Quick answer

To store a file in a MySQL BLOB with PHP:

1. Create a table containing a BLOB, MEDIUMBLOB, or LONGBLOB column.
2. Validate the upload error and file size in PHP.
3. Detect the MIME type from the temporary file instead of trusting $_FILES['type'].
4. Insert the binary data with a MySQLi prepared statement.
5. Retrieve the BLOB by a validated numeric ID.
6. Send the correct Content-Type, Content-Length, and Content-Disposition headers before outputting the data.

The example uses send_long_data() to transfer the uploaded content to MySQL in chunks. It also stores the original filename, detected MIME type, file size, and a SHA-256 checksum.

Should files be stored in MySQL?

A BLOB is useful when the file must be stored, backed up, and controlled as part of the same database record. It can be a practical choice for small documents, private attachments, signatures, or images that require database-level access control.

For a large public image library, storing files in object storage or the filesystem and keeping only their paths in MySQL is usually more efficient. Large BLOB collections increase database size, backup time, memory use, and replication traffic.

This project limits each upload to 5 MB. That keeps the example practical while still showing the complete upload and retrieval flow.

Choose the appropriate BLOB type

The project uses the LONGBLOB column type, but the application still enforces its own 5 MB limit. A large column capacity is not permission to accept unlimited uploads. PHP settings such as upload_max_filesize and post_max_size, along with MySQL’s max_allowed_packet, must also be large enough for the configured limit.

Create the MySQL table

The table stores the file content and the metadata required to return it correctly. Keeping the original name, MIME type, and size outside the BLOB also lets the gallery list files without loading their binary content.

CREATE DATABASE IF NOT EXISTS php_blob_demo
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE php_blob_demo;

CREATE TABLE IF NOT EXISTS stored_files (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size BIGINT UNSIGNED NOT NULL,
    sha256 CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    file_data LONGBLOB NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_stored_files_created_at (created_at)
) ENGINE=InnoDB;

The sha256 column is not required for displaying the file. This project uses it to verify that the retrieved BLOB still matches the uploaded content.

The application accepts only 5 MB per upload even though LONGBLOB supports much larger values. Keeping a separate application limit reduces memory pressure and makes failures easier to control.

Configure the upload rules

Place the size limit and MIME allowlist in config.php. A MIME allowlist is safer than trying to block a growing list of unwanted file types.

<?php

declare(strict_types=1);

const MAX_UPLOAD_BYTES = 5 * 1024 * 1024;

const ALLOWED_MIME_TYPES = [
    'image/jpeg' => 'JPEG image',
    'image/png' => 'PNG image',
    'image/gif' => 'GIF image',
    'image/webp' => 'WebP image',
    'application/pdf' => 'PDF document',
];

The keys are the MIME types accepted by the server. The application will detect the type from the uploaded file content. It will not trust the value supplied by the browser.

Connect to MySQL with MySQLi

The database connection is kept in db.php so the upload page and retrieval endpoint can use the same configuration.

<?php

declare(strict_types=1);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$database = new mysqli(
    getenv('DB_HOST') ?: '127.0.0.1',
    getenv('DB_USER') ?: 'root',
    getenv('DB_PASSWORD') ?: '',
    getenv('DB_NAME') ?: 'php_blob_demo',
    (int) (getenv('DB_PORT') ?: 3306)
);

$database->set_charset('utf8mb4');

Strict MySQLi error reporting converts database failures into exceptions. That makes it possible to log the technical error while displaying a safe message to the visitor.

The example reads credentials from environment variables and uses local development defaults when they are absent. On a production server, use a dedicated database account with only the permissions the application needs. Do not place a privileged MySQL password in a public project download.

Validate the uploaded file

The upload handler begins in index.php. It starts a session for the CSRF token, loads the configuration and database connection, and handles only POST requests.

<?php

declare(strict_types=1);

session_start();

require __DIR__ . '/config.php';
require __DIR__ . '/db.php';

if (!isset($_SESSION['csrf_token'])) {
    $_SESSION['csrf_token'] = bin2hex(random_bytes(32));
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    try {
        $token = (string) ($_POST['csrf_token'] ?? '');
        if (!hash_equals($_SESSION['csrf_token'], $token)) {
            throw new RuntimeException(
                'The form expired. Refresh the page and try again.'
            );
        }

        if (!isset($_FILES['upload']) || !is_array($_FILES['upload'])) {
            throw new RuntimeException('Choose a file to upload.');
        }

        $upload = $_FILES['upload'];
        $error = (int) ($upload['error'] ?? UPLOAD_ERR_NO_FILE);

        if ($error !== UPLOAD_ERR_OK) {
            $messages = [
                UPLOAD_ERR_INI_SIZE => 'The file exceeds the server upload limit.',
                UPLOAD_ERR_FORM_SIZE => 'The file exceeds the form upload limit.',
                UPLOAD_ERR_PARTIAL => 'The file was only partly uploaded.',
                UPLOAD_ERR_NO_FILE => 'Choose a file to upload.',
            ];

            throw new RuntimeException(
                $messages[$error] ?? 'The upload failed.'
            );
        }

        $temporaryPath = (string) ($upload['tmp_name'] ?? '');
        $size = (int) ($upload['size'] ?? 0);

        if (!is_uploaded_file($temporaryPath)) {
            throw new RuntimeException(
                'The uploaded file could not be verified.'
            );
        }

        if ($size < 1 || $size > MAX_UPLOAD_BYTES) {
            throw new RuntimeException(
                'The file must be between 1 byte and 5 MB.'
            );
        }

        $mimeType = (new finfo(FILEINFO_MIME_TYPE))
            ->file($temporaryPath);

        if (
            !is_string($mimeType)
            || !array_key_exists($mimeType, ALLOWED_MIME_TYPES)
        ) {
            throw new RuntimeException(
                'Upload a JPEG, PNG, GIF, WebP, or PDF file.'
            );
        }

        $originalName = str_replace(
            '\\',
            '/',
            (string) ($upload['name'] ?? 'upload')
        );
        $originalName = basename($originalName);
        $originalName = preg_replace(
            '/[\x00-\x1F\x7F]/u',
            '',
            $originalName
        ) ?? '';

        if ($originalName === '' || strlen($originalName) > 255) {
            throw new RuntimeException(
                'The file name is empty or longer than 255 bytes.'
            );
        }

The accept attribute on an HTML file input is useful for the visitor, but it is not a security check. A request can be created without the form, and the browser-provided $_FILES['upload']['type'] value can be inaccurate or manipulated.

finfo examines the temporary file and reports its MIME type. The result must still be compared with an explicit allowlist because detecting a type does not decide whether the application should accept it.

Insert the file into the BLOB column

After validation, calculate the checksum and prepare the insert. The BLOB parameter uses the MySQLi b type, and the send_long_data() method sends its content in 8 KB chunks.

        $checksum = hash_file('sha256', $temporaryPath);

        if ($checksum === false) {
            throw new RuntimeException(
                'The file checksum could not be calculated.'
            );
        }

        $statement = $database->prepare(
            'INSERT INTO stored_files (
                original_name,
                mime_type,
                file_size,
                sha256,
                file_data
             ) VALUES (?, ?, ?, ?, ?)'
        );

        $blob = null;
        $statement->bind_param(
            'ssisb',
            $originalName,
            $mimeType,
            $size,
            $checksum,
            $blob
        );

        $stream = fopen($temporaryPath, 'rb');

        if ($stream === false) {
            throw new RuntimeException(
                'The uploaded file could not be read.'
            );
        }

        try {
            while (!feof($stream)) {
                $chunk = fread($stream, 8192);

                if ($chunk === false) {
                    throw new RuntimeException(
                        'The uploaded file could not be read.'
                    );
                }

                if ($chunk !== '') {
                    $statement->send_long_data(4, $chunk);
                }
            }
        } finally {
            fclose($stream);
        }

        $statement->execute();

        $_SESSION['flash'] = [
            'type' => 'success',
            'text' => 'File uploaded successfully.',
        ];
    } catch (RuntimeException $exception) {
        $_SESSION['flash'] = [
            'type' => 'error',
            'text' => $exception->getMessage(),
        ];
    } catch (mysqli_sql_exception) {
        error_log('BLOB upload database error');

        $_SESSION['flash'] = [
            'type' => 'error',
            'text' => 'The file could not be saved.',
        ];
    }

    header('Location: index.php', true, 303);
    exit;
}

The fifth bound value has index 4 because send_long_data() numbers parameters from zero. This detail is easy to miss when the BLOB is not the first placeholder.

The redirect implements the Post/Redirect/Get pattern. Refreshing the result page will not submit the same file again, and the upload message survives for the redirected request through the session.

Create the upload form

After the upload handler, read and remove the flash message. Then query only the metadata needed by the gallery. The BLOB column is intentionally absent from this query.

$flash = $_SESSION['flash'] ?? null;
unset($_SESSION['flash']);

$files = $database->query(
    'SELECT id, original_name, mime_type, file_size, created_at
     FROM stored_files
     ORDER BY id DESC'
);

function escape(string $value): string
{
    return htmlspecialchars(
        $value,
        ENT_QUOTES | ENT_SUBSTITUTE,
        'UTF-8'
    );
}

function formatBytes(int $bytes): string
{
    return $bytes >= 1024 * 1024
        ? number_format($bytes / (1024 * 1024), 1) . ' MB'
        : number_format($bytes / 1024, 1) . ' KB';
}

Selecting file_data for the gallery would transfer every stored file from MySQL even though the page only needs names, types, sizes, and IDs. Fetch each BLOB separately when the browser requests it.

The upload form includes the session CSRF token and uses multipart/form-data, which is required for file uploads.

<section class="panel">
    <h1>Store files in a MySQL BLOB</h1>

    <p class="intro">
        Upload an image or PDF up to 5 MB.
        The file content and its metadata are stored in MySQL.
    </p>

    <?php if (is_array($flash)): ?>
        <div
            class="message <?= escape((string) $flash['type']) ?>"
            role="status"
        >
            <?= escape((string) $flash['text']) ?>
        </div>
    <?php endif; ?>

    <form method="post" enctype="multipart/form-data">
        <input
            type="hidden"
            name="csrf_token"
            value="<?= escape($_SESSION['csrf_token']) ?>"
        >

        <input
            type="hidden"
            name="MAX_FILE_SIZE"
            value="<?= MAX_UPLOAD_BYTES ?>"
        >

        <label for="upload">
            Choose a JPEG, PNG, GIF, WebP, or PDF
        </label>

        <input
            id="upload"
            name="upload"
            type="file"
            accept="image/jpeg,image/png,image/gif,image/webp,application/pdf"
            required
        >

        <button type="submit">Upload file</button>
    </form>
</section>

The hidden MAX_FILE_SIZE field may let PHP reject an oversized upload earlier, but a visitor can change or remove it. The server-side comparison with MAX_UPLOAD_BYTES remains the authoritative limit.

List the stored images and files

The gallery uses each database ID to build a URL for file.php. Images are loaded through that endpoint for previews. PDFs receive a simple document placeholder and are available as downloads.

<section class="panel">
    <h2>Stored files</h2>

    <?php if ($files->num_rows === 0): ?>
        <p class="empty">No files have been uploaded yet.</p>
    <?php else: ?>
        <div class="file-grid">
            <?php while ($file = $files->fetch_assoc()): ?>
                <article class="file-card">
                    <?php if (
                        str_starts_with($file['mime_type'], 'image/')
                    ): ?>
                        <a
                            class="preview"
                            href="file.php?id=<?= (int) $file['id'] ?>"
                            target="_blank"
                            rel="noopener"
                        >
                            <img
                                src="file.php?id=<?= (int) $file['id'] ?>"
                                alt="<?= escape($file['original_name']) ?>"
                                loading="lazy"
                            >
                        </a>
                    <?php else: ?>
                        <div class="document-icon" aria-hidden="true">
                            PDF
                        </div>
                    <?php endif; ?>

                    <h3>
                        <?= escape($file['original_name']) ?>
                    </h3>

                    <p>
                        <?= escape($file['mime_type']) ?>
                        ·
                        <?= escape(
                            formatBytes((int) $file['file_size'])
                        ) ?>
                    </p>

                    <a
                        class="download"
                        href="file.php?id=<?= (int) $file['id'] ?>&amp;download=1"
                    >
                        Download
                    </a>
                </article>
            <?php endwhile; ?>
        </div>
    <?php endif; ?>
</section>

Every filename and metadata value is escaped before it is inserted into HTML. A filename comes from the visitor, so it must be treated as untrusted text even after the file itself passes MIME validation.

Files uploaded to and retrieved from a MySQL BLOB with PHP

Retrieve the BLOB with PHP

Create file.php to retrieve one record by its numeric ID. This endpoint returns only the binary response, so it must not print HTML, spaces, warnings, or debugging output before sending the headers.

<?php

declare(strict_types=1);

require __DIR__ . '/config.php';
require __DIR__ . '/db.php';

$id = filter_input(INPUT_GET, 'id', FILTER_VALIDATE_INT, [
    'options' => ['min_range' => 1],
]);

if (!is_int($id)) {
    http_response_code(400);
    exit('Invalid file ID.');
}

$statement = $database->prepare(
    'SELECT original_name, mime_type, file_size, sha256, file_data
     FROM stored_files
     WHERE id = ?'
);

$statement->bind_param('i', $id);
$statement->execute();

$file = $statement->get_result()->fetch_assoc();

if ($file === null) {
    http_response_code(404);
    exit('File not found.');
}

$data = $file['file_data'];

if (
    !is_string($data)
    || strlen($data) !== (int) $file['file_size']
    || !hash_equals($file['sha256'], hash('sha256', $data))
) {
    error_log(
        'Stored BLOB failed integrity verification for ID ' . $id
    );

    http_response_code(500);
    exit('The stored file failed its integrity check.');
}

$mimeType = array_key_exists(
    $file['mime_type'],
    ALLOWED_MIME_TYPES
)
    ? $file['mime_type']
    : 'application/octet-stream';

$download = isset($_GET['download'])
    || !str_starts_with($mimeType, 'image/');

$disposition = $download ? 'attachment' : 'inline';

$name = str_replace(
    ["\r", "\n"],
    '',
    $file['original_name']
);

$asciiName = preg_replace(
    '/[^A-Za-z0-9._-]/',
    '_',
    $name
) ?: 'download';

header('Content-Type: ' . $mimeType);
header('Content-Length: ' . strlen($data));

header(
    'Content-Disposition: '
    . $disposition
    . '; filename="'
    . $asciiName
    . '"; filename*=UTF-8\'\''
    . rawurlencode($name)
);

header('X-Content-Type-Options: nosniff');

header(
    "Content-Security-Policy: default-src 'none'; sandbox"
);

header('Cache-Control: private, no-store');

echo $data;

The validated integer ID and prepared statement prevent the URL parameter from becoming part of the SQL query. If no matching row exists, the endpoint returns a 404 Not Found response.

The checksum comparison provides an integrity check before any content is sent. If the stored bytes no longer match the SHA-256 value recorded during upload, the endpoint logs the record ID and returns an error instead of serving damaged content.

Display an image BLOB

An allowlisted image can use file.php

<img
    src="file.php?id=<?= (int) $file['id'] ?>"
    alt="<?= escape($file['original_name']) ?>"
>

When the request does not contain the download parameter, an image receives an inline content disposition. Its stored MIME type becomes the response Content-Type, allowing the browser to display the returned bytes.

Download a stored file

Add download=1

<a href="file.php?id=42&amp;download=1">Download</a>

PDF files are always returned as attachments in this example, even without that parameter. Only allowlisted image types may be displayed inline.

The ASCII filename fallback keeps the basic header safe and compatible. The additional filename* value preserves a UTF-8 filename for browsers that support it. Carriage returns and line feeds are removed before either value is used.

X-Content-Type-Options: nosniff tells the browser not to reinterpret the declared type. The restrictive Content Security Policy adds another boundary around inline responses, while Cache-Control: private, no-store avoids leaving uploaded files in shared caches.

Security considerations for BLOB uploads

Prepared statements protect the database query, but secure file handling needs several separate checks. No single validation step covers the entire upload and retrieval process.

• Validate the upload status. Check the PHP upload error before reading the temporary file.
• Set a server-side size limit. Do not rely on the HTML MAX_FILE_SIZE value.
• Detect the MIME type. Use Fileinfo instead of trusting $_FILES['upload']['type'] or the filename extension.
• Use an allowlist. Accept only the file types the application is designed to handle.
• Protect the form from CSRF. A valid session token should be required for each upload request.
• Escape filenames in HTML. The original filename is visitor-controlled text.
• Control the response headers. Set the content type, disposition, length, caching policy, and nosniff header explicitly.
• Add authorization when files are private. A hard-to-guess URL is not an access-control system.

Storing a file inside MySQL prevents the web server from executing it as a PHP script from an upload directory. That does not make every stored file safe. The application must still validate uploads and control how their content is returned.

The tutorial permits images and PDFs because the interface is built for those types. If the application only needs images, remove application/pdf from the allowlist instead of accepting it without a use case.

Common errors and fixes

The file exceeds the server upload limit

PHP may reject the request before the application can inspect the file. Check the PHP upload settings in the active php.ini:

upload_max_filesize = 6M
post_max_size = 7M

post_max_size must be larger than upload_max_filesize because the request also contains form boundaries and other fields. Restart the web server or PHP service after changing the configuration.

The MySQL server reports that the packet is too large

The uploaded BLOB and prepared statement must fit within MySQL’s max_allowed_packet limit. Inspect the current server value with:

SHOW VARIABLES LIKE 'max_allowed_packet';

If it is smaller than the application’s maximum upload plus statement overhead, increase it in the MySQL server configuration. Do not increase the application limit simply because the BLOB column can hold more data.

The browser shows a broken image

Open the image URL directly and inspect its response. A PHP warning, HTML error page, incorrect MIME type, or output sent before header() will corrupt the binary response.

Keep file.php free of templates and debugging output. Log internal errors on the server instead of printing them into the file content.

The retrieved file is empty

Confirm that the upload size is greater than zero and that send_long_data() uses the correct parameter index. In this project, the BLOB is the fifth placeholder, so its zero-based index is 4.

Also verify that PHP can read the temporary upload before the request ends. The temporary file is removed automatically after PHP finishes handling the request.

Fileinfo is unavailable

The project requires PHP’s Fileinfo extension. Check whether it is enabled:

php -m

Look for fileinfo in the output. If the command-line and web server PHP installations use different configuration files, confirm the extension in the web environment as well.

MySQLi cannot connect to the database

Confirm the host, port, database name, username, and password supplied through the environment variables. MySQL may treat localhost and 127.0.0.1 differently because one can use a Unix socket while the other uses TCP.

In production, log the database exception privately. Do not display credentials, SQL details, or server paths in the browser.

Run the complete project

Import schema.sql

mysql -u root -p < schema.sql

Set the database environment variables if the local defaults do not match your setup:

export DB_HOST=127.0.0.1
export DB_PORT=3306
export DB_NAME=php_blob_demo
export DB_USER=root
export DB_PASSWORD=your_password

Start PHP’s local development server from the same directory:

php -S 127.0.0.1:8000

Open http://127.0.0.1:8000 and upload an allowlisted image or PDF. The page will save the file in MySQL and add it to the gallery.

The PHP development server is convenient for local testing. Use a properly configured web server with HTTPS for a public application.

Project structure

mysql-blob-using-php/
├── README.md
├── config.php
├── db.php
├── file.php
├── index.php
├── schema.sql
└── style.css

• index.php validates uploads, inserts BLOB data, and displays the file gallery.
• file.php retrieves a BLOB and returns it as an inline image or download.
• config.php defines the upload limit and MIME allowlist.
• db.php creates the MySQLi connection.
• schema.sql creates the database and table.
• style.css provides lightweight styling for the form, messages, and gallery.
• README.md contains setup and configuration instructions.

Developer FAQ

Should binary files be converted to Base64 before storing them?

No. A BLOB column stores binary data directly. Base64 increases the stored size and adds unnecessary encoding and decoding work. Send the original bytes through a prepared BLOB parameter.

Can the project store files other than images and PDFs?

Yes, but each additional type should have a real application use case. Add its verified MIME type to ALLOWED_MIME_TYPES and decide whether the retrieval endpoint should display it inline or force a download.

How can several files be uploaded at once?

Add the multiple attribute to the file input and use an array-style field name such as upload[]. PHP will then provide arrays inside $_FILES['upload']. Validate and insert every file independently, including its upload error, size, MIME type, and filename.

How do I update an existing BLOB?

Use the same validation and streaming process with an UPDATE statement:

UPDATE stored_files
SET original_name = ?,
    mime_type = ?,
    file_size = ?,
    sha256 = ?,
    file_data = ?
WHERE id = ?;

Validate the record ID and authorize the operation before replacing its content.

Why not include the BLOB in the gallery query?

A metadata page does not need every file’s binary content. Selecting only the ID, name, MIME type, size, and creation time keeps the initial query smaller. The browser requests an individual BLOB only when it loads a preview or follows a download link.

Is this project ready for private user files?

The upload and retrieval mechanics are secure starting points, but the tutorial does not include user accounts or file ownership. Add authentication and check that the current user may access the requested record before returning private content.

Download the PHP MySQL BLOB project

The complete PHP 8.2+ project includes the MySQL schema, MySQLi upload and retrieval code, MIME validation, checksum verification, light CSS, and setup instructions.

Download the PHP MySQL BLOB example project