PHP脚本存入数据库Blob的文件无法下载，求解决方法

Got it, let's fix your code step by step—there are a couple of key issues keeping your file upload/download from working correctly:

1. Upload Logic Errors #

Your update query has incorrect variable usage, missing proper parameter binding, and doesn't handle Blob data correctly:

• You're hardcoding undefined variables like $myfile in your SQL instead of using secure placeholders.
• Your bindParam calls are out of order and don't map to any placeholders in the query.
• Blob data needs special handling with PDO::PARAM_LOB to ensure binary content is stored without corruption.

2. Download Link Mistake #

You can't directly use the Blob field's raw binary content as an href—browsers can't interpret unprocessed binary data as a downloadable link. Instead, you need a separate script to fetch the Blob from the database and send the correct HTTP headers to trigger a download.

Corrected Upload Page (e.g., index.php) #

<form method="post" enctype="multipart/form-data">
    <input type="file" name="myfile"/>
    <button name="btns">Incarca Schita</button>
</form>

<?php
// IMPORTANT: Define $id properly (e.g., from user session, URL parameter, or authenticated user data)
// Example: $id = $_SESSION['current_player_id']; or $id = (int)$_GET['player_id'];
$id = 1; // Replace with your actual ID logic

try {
    $dbh = new PDO("mysql:host=localhost;dbname=highmob_comenzi", "highmob", "PW");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Handle file upload only if no upload errors occurred
    if(isset($_POST['btns']) && $_FILES['myfile']['error'] === UPLOAD_ERR_OK) {
        $fileName = $_FILES['myfile']['name'];
        $fileType = $_FILES['myfile']['type'];
        $fileData = file_get_contents($_FILES['myfile']['tmp_name']);

        // Use prepared statements with placeholders to avoid SQL injection
        $stmt = $dbh->prepare("
            UPDATE players 
            SET data = :file_data, name = :file_name, mime = :file_type 
            WHERE id = :player_id
        ");
        // Bind Blob data with PARAM_LOB for proper binary handling
        $stmt->bindParam(':file_data', $fileData, PDO::PARAM_LOB);
        $stmt->bindParam(':file_name', $fileName);
        $stmt->bindParam(':file_type', $fileType);
        $stmt->bindParam(':player_id', $id);
        
        if($stmt->execute()) {
            echo "<p>File uploaded successfully!</p>";
        }
    }

    // Display download links for the player
    $fetchStmt = $dbh->prepare("SELECT id, name FROM players WHERE id = :player_id");
    $fetchStmt->bindParam(':player_id', $id);
    $fetchStmt->execute();

    while($row = $fetchStmt->fetch(PDO::FETCH_ASSOC)){
        // Link to the dedicated download script, passing the player ID
        echo "<a href='download.php?id=" . $row['id'] . "'>" . htmlspecialchars($row['name']) . "</a><br>";
    }
} catch(PDOException $e) {
    echo "<p>Error: " . $e->getMessage() . "</p>";
}
?>

Download Handler Script (download.php) #

Create this separate script to handle file downloads:

<?php
// Validate incoming player ID to prevent invalid requests
if(!isset($_GET['id']) || !is_numeric($_GET['id'])) {
    die("Invalid request: Missing or invalid player ID");
}
$playerId = (int)$_GET['id'];

try {
    $dbh = new PDO("mysql:host=localhost;dbname=highmob_comenzi", "highmob", "PW");
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Fetch file details from the database
    $stmt = $dbh->prepare("SELECT name, mime, data FROM players WHERE id = :player_id");
    $stmt->bindParam(':player_id', $playerId);
    $stmt->execute();
    $file = $stmt->fetch(PDO::FETCH_ASSOC);

    if(!$file) {
        die("File not found for this player");
    }

    // Set HTTP headers to tell the browser to download the file
    header("Content-Type: " . $file['mime']);
    header("Content-Disposition: attachment; filename=\"" . htmlspecialchars($file['name']) . "\"");
    header("Content-Length: " . strlen($file['data']));

    // Output the binary file data
    echo $file['data'];
    exit; // Stop script execution after sending the file
} catch(PDOException $e) {
    die("Error retrieving file: " . $e->getMessage());
}
?>

Key Improvements Explained #

• Secure Parameter Binding: Uses prepared statements to eliminate SQL injection risks, which is critical for user-uploaded content.
• Blob Handling: PDO::PARAM_LOB ensures binary data is stored and retrieved correctly without corruption.
• Proper Download Headers: The download.php script sends headers that instruct the browser to treat the response as a downloadable file, not a web page.
• Error Handling: Added exception catching to simplify debugging if something goes wrong with the database or upload process.
• XSS Protection: Uses htmlspecialchars() on filenames to prevent cross-site scripting attacks.

内容的提问来源于stack exchange，提问作者Pirjol Nelu