I am encountering an error of “broken file icon retrieving image from database php and MySQL.

Question

Actually, I want to upload and retrieve an image from a database, I can store the image in the database but can’t display it later. I have written the following code to retrieve from the database.

 $result1=mysql_query("INSERT INTO userdata(id, username, firstname, lastname, imageType, image)VALUES('', '" . $_SESSION['username'] . "', '" . $_SESSION['firstname'] . "', '$lastname','{$image_size['mime']}','{$imgData}')") or die("Invalid query: " . mysql_error());

if($result1)

{

echo "</br>";

echo "Registration successful";

echo "</br>";

echo $lastid=mysql_insert_id();//get the id of the last record

echo "uploaded image is :"; ?>

<img src="imageView.php?image_id=<?php echo $lastid; ?>" /><br/>

<?php

echo "</br>";     

}#if result1into db successful

else 

{

echo $result1;

echo "Problem in database operation";

imageView.php code:

   <?php

    $conn = mysql_connect("localhost", "root", "");

    mysql_select_db("wordgraphic") or die(mysql_error());

         if(isset($_GET['id'])) {

        $sql = "SELECT imageType,image FROM userdata WHERE id=". $_GET['image_id'];

        $result = mysql_query("$sql") or die("<b>Error:</b> Problem on Retrieving Image BLOB<br/>" . mysql_error());

        $row = mysql_fetch_array($result);

        header("Content-type: " . $row["imageType"]);

        echo $row["image"];

         }

    mysql_close($conn);

?>

What is wrong with this code? Whenever I run imageView.php with a static id image is getting displayed. So I think the error is in passing the variable in this code:

echo "uploaded image is :"; ?>

<img src="imageView.php?image_id=<?php echo $lastid; ?>" /><br/>

<?php

Kindly help me out.

Answer 1

The code you provided contains several issues that need to be addressed. I'll point out the main problems and suggest some corrections:

1. Deprecated MySQL Functions: The code is using deprecated MySQL functions (`mysql_query`, `mysql_error`, `mysql_fetch_array`, etc.), which are no longer supported in recent PHP versions. Instead, you should switch to MySQLi or PDO for database operations. I'll provide an example using MySQLi.

2. SQL Injection Vulnerability: The code is vulnerable to SQL injection attacks since it directly concatenates variables into the SQL query. To prevent this, you should use prepared statements with parameter binding. I'll show you an example using prepared statements.

3. Image Display: The code for displaying the image should be corrected to ensure that the `image_id` parameter is properly passed.

Here's an updated version of your code addressing the above issues:

imageUpload.php:

<?php

$conn = new mysqli("localhost", "root", "", "wordgraphic");

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

$statement = $conn->prepare("INSERT INTO userdata (username, firstname, lastname, imageType, image) VALUES (?, ?, ?, ?, ?)");

$statement->bind_param("sssss", $_SESSION['username'], $_SESSION['firstname'], $lastname, $imageType, $imgData);

$result = $statement->execute();

if ($result) {

    echo "Registration successful<br/>";

    $lastid = $conn->insert_id;

    echo "uploaded image is:<br/>";

    echo '<img src="imageView.php?image_id=' . $lastid . '" /><br/>';

} else {

    echo "Problem in database operation";

}

$statement->close();

$conn->close();

?>

imageView.php:

<?php

$conn = new mysqli("localhost", "root", "", "wordgraphic");

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

if (isset($_GET['image_id'])) {

    $statement = $conn->prepare("SELECT imageType, image FROM userdata WHERE id = ?");

    $statement->bind_param("i", $_GET['image_id']);

    $statement->execute();

    $result = $statement->get_result();

    $row = $result->fetch_assoc();

    header("Content-type: " . $row["imageType"]);

    echo $row["image"];

    $statement->close();

} else {

    echo "Invalid image ID";

}

$conn->close();

?>

Please note that the above code assumes you have the necessary error handling and security measures in place. It's important to sanitize user input, validate file uploads, and apply proper security practices to prevent potential vulnerabilities.

Answer 2

Please apply this  small rectification in your code

<img src="imageView.php?image_id=<?php echo $lastid; ?>" />

instead of this src = src path where you save the image file lets say in images folder that would be here $imagedata store the file name in the DB so that you can retrieve it from the image folder

<img src="images/$imgData" width="your wish" height="your wish"/>

This is the simple solution to your problem.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.

Answer 3

The code you provided contains deprecated MySQL functions and is vulnerable to SQL injection attacks. It's recommended to use modern alternatives like MySQLi or PDO for database operations and to properly sanitize user input.

To address the issue you're facing, you need to ensure that the `image_id` parameter is correctly passed to the `imageView.php` script. Here are the modifications you need to make:

1. Update the insertion query to store the last inserted ID in a variable before using it:

   $result1 = mysql_query("INSERT INTO userdata (id, username, firstname, lastname, imageType, image) VALUES ('', '" . $_SESSION['username'] . "', '" . $_SESSION['firstname'] . "', '$lastname', '{$image_size['mime']}', '{$imgData}')") or die("Invalid query: " . mysql_error());

   if ($result1) {

     echo "</br>";

     echo "Registration successful";

     echo "</br>";

     $lastid = mysql_insert_id(); // Store the last inserted ID

     echo "Uploaded image is: ";

     ?>

     <img src="imageView.php?image_id=<?php echo $lastid; ?>" /><br/>

     <?php

     echo "</br>";     

   } else {

     echo $result1;

     echo "Problem in database operation";

   }

2. Modify the `imageView.php` code to use the correct parameter name:

   <?php

   $conn = mysql_connect("localhost", "root", "");

   mysql_select_db("wordgraphic") or die(mysql_error());

   if (isset($_GET['image_id'])) { // Check for 'image_id' instead of 'id'

     $sql = "SELECT imageType, image FROM userdata WHERE id = " . $_GET['image_id'];

     $result = mysql_query($sql) or die("<b>Error:</b> Problem on Retrieving Image BLOB<br/>" . mysql_error());

     $row = mysql_fetch_array($result);

     header("Content-type: " . $row["imageType"]);

     echo $row["image"];

   }

   mysql_close($conn);

   ?>

Please note that the code provided above is using the deprecated MySQL functions for consistency with the code you shared. However, it's strongly recommended to update your code to use MySQLi or PDO for database operations and to implement proper security measures to prevent SQL injection.

Answer 4

The code you provided has a few issues that may be causing problems in retrieving and displaying the image. Here are some suggestions to fix the code:

1. Deprecated MySQL Functions: The code you shared is using deprecated MySQL functions (`mysql_query`, `mysql_select_db`, `mysql_fetch_array`, etc.). It is recommended to use MySQLi or PDO instead. In this case, I will provide an example using MySQLi.

2. SQL Injection: Your code is vulnerable to SQL injection attacks. It is crucial to use prepared statements or properly sanitize user inputs before incorporating them into SQL queries.

3. Missing id in the URL: In the `imageView.php` code, you're checking for `$_GET['id']`, but in the URL, you are passing `$_GET['image_id']`. Make sure the parameter name in the URL matches the one you're checking in the code.

Here's an updated version of your code that addresses the above issues using MySQLi and prepared statements:

uploadImage.php:

<?php

$conn = mysqli_connect("localhost", "root", "", "wordgraphic");

if (isset($_SESSION['username']) && isset($_SESSION['firstname']) && isset($_POST['image'])) {

    $username = $_SESSION['username'];

    $firstname = $_SESSION['firstname'];

    $image = $_POST['image'];

    $stmt = $conn->prepare("INSERT INTO userdata (username, firstname, imageType, image) VALUES (?, ?, ?, ?)");

    $stmt->bind_param("ssss", $username, $firstname, $image['type'], $image['data']);

    $stmt->execute();

    $lastId = $stmt->insert_id;

    echo "Registration successful<br />";

    echo "Uploaded image is: <br />";

    echo "<img src=\"imageView.php?image_id=" . $lastId . "\" /><br />";

    $stmt->close();

} else {

    echo "Problem in database operation";

}

mysqli_close($conn);

?>

imageView.php:

<?php

$conn = mysqli_connect("localhost", "root", "", "wordgraphic");

if (isset($_GET['image_id'])) {

    $imageId = $_GET['image_id'];

    $stmt = $conn->prepare("SELECT imageType, image FROM userdata WHERE id = ?");

    $stmt->bind_param("i", $imageId);

    $stmt->execute();

    $stmt->bind_result($imageType, $imageData);

    $stmt->fetch();

    header("Content-type: " . $imageType);

    echo $imageData;

    $stmt->close();

} else {

    echo "<b>Error:</b> Problem on retrieving image BLOB";

}

mysqli_close($conn);

?>

Make sure to update the database connection details (`localhost`, `root`, `wordgraphic`) in the code to match your configuration.

Note: This code assumes that the image is uploaded as `$_POST['image']`, which should be an array containing the image type (`type`) and image data (`data`). Adjust the code accordingly if you're using a different method for image upload.