PHP/MySQL：图片成功存入数据库但无法显示，上传提示异常

Hey there! Let's break down the issues in your code that are causing those frustrating problems:

1. Incorrect insertion success check #

Your current condition if($sql->rowCount() > 1 ) is the root cause of the false "Failed to upload!" message. When you insert a single image, rowCount() will return 1, not a value greater than 1. You need to check if it's equal to 1 (or greater than 0) to properly confirm a successful insertion.

2. Missing base64 image prefix & wrong data access #

To display a base64-encoded image in an <img> tag, you must prepend a MIME type prefix like data:image/jpeg;base64, — otherwise the browser won't recognize the string as an image. Additionally, since you're using PDO::FETCH_ASSOC, your result is an associative array, so you need to access the data with $row['encoded_image'] instead of the object syntax $row->encoded_image.

3. SQL injection vulnerability #

You're directly concatenating the $encoded_image value into your INSERT query, which exposes your code to SQL injection risks. Using PDO parameter binding is the safer, industry-standard approach.

Here's the fixed version of your code with all these issues addressed:

<body>
<div class="wrapperDiv">
    <form action="" method="post" id="form" enctype="multipart/form-data">
        Upload image : <input type="file" name="uploadFile" value="" />
        <input type="submit" name="submitBtn" value="Upload" />
    </form>
    <?php
    $last_insert_id = null;
    include('db2.php');

    if(isset($_POST['submitBtn']) && !empty($_POST['submitBtn'])) {
        if(isset($_FILES['uploadFile']['name']) && !empty($_FILES['uploadFile']['name'])) {
            // Allowed file types
            $allowed_extensions = array("jpg","jpeg","png","gif");
            // Get file extension
            $ext = strtolower(pathinfo($_FILES['uploadFile']['name'], PATHINFO_EXTENSION));
            
            // Validate file extension
            if(in_array($ext, $allowed_extensions)) {
                // Get correct MIME type for the image
                $mime_type = mime_content_type($_FILES['uploadFile']['tmp_name']);
                // Encode image with proper base64 prefix
                $encoded_image = 'data:' . $mime_type . ';base64,' . base64_encode(file_get_contents($_FILES['uploadFile']['tmp_name']));
                
                // Use parameter binding to avoid SQL injection
                $query = "INSERT INTO tbl_images (encoded_image) VALUES (:encoded_image)";
                $sql = $conn->prepare($query);
                $sql->bindParam(':encoded_image', $encoded_image);
                $sql->execute();

                echo "File name : " . $_FILES['uploadFile']['name'];
                echo "<br>";

                // Correct success check logic
                if($sql->rowCount() == 1 ) {
                    echo "Status : Uploaded";
                    $last_insert_id = $conn->lastInsertId();
                } else {
                    echo "Status : Failed to upload!";
                }
            } else {
                echo "File not allowed";
            }
        }
    }

    if($last_insert_id) {
        // Use parameter binding for SELECT query too
        $query = "SELECT encoded_image FROM tbl_images WHERE id= :id";
        $sql = $conn->prepare($query);
        $sql->bindParam(':id', $last_insert_id);
        $sql->execute();

        if($sql->rowCount() == 1 ) {
            $row = $sql->fetch(PDO::FETCH_ASSOC);
            echo "<br><br>";
            echo '<img src="' . $row['encoded_image'] . '" width="250">';
        }
    }
    ?>
</div>
</body>

Key changes made:

• Updated the insertion success check to $sql->rowCount() == 1 to correctly detect a single inserted record
• Added the MIME type prefix to the base64 string so the browser can render the image
• Switched to PDO parameter binding for both INSERT and SELECT queries to eliminate SQL injection risks
• Fixed the data access syntax from object-style to associative array style
• Used mime_content_type() to automatically get the correct MIME type (more reliable than relying solely on file extensions)

内容的提问来源于stack exchange，提问作者Radamel Falcao