Inserting and then retrieving a blob data from mysql database using php

PHP
1

I am having great difficulty in displaying an image that I had already inserted into a database. Below is my two script code to help in inserting and displaying. While using google chrome as my browser, I end up getting a broken image icon, but when i just recently used firefox, i got the error message that the file contains error!
Please what could be the problem in this case?

This is my first script: uploadFile.php:

<?php 

//Open a new connection to the MySQL server
$user="root";
$host="localhost";
$password="";
$database = "dynamic_data";

$cxn = mysqli_connect($host,$user,$password,$database);
// Check connection
if (mysqli_connect_errno()) {
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
}


?>



<?php

@$name = $_FILES['file']['name'];
$extension = strtolower(substr($name, strpos($name, '.') + 1));
@$tmp_name = $_FILES['file']['tmp_name'];
@$type = $_FILES['file']['type'];
@$size = $_FILES['file']['size'];
$max_size = 74752;




if(isset($name)){

  if(!empty($name)){
  	
    if(($extension == 'jpg' || $extension == 'jpeg')&& $type == 'image/jpeg' && $size <= $max_size){
		
	// Image submitted by form. Open it for reading (mode "r")
		$fp = fopen($_FILES['file']['tmp_name'], "r");
		
		// If successful, read from the file pointer using the size of the file (in bytes) as the length.
		if ($fp) {
			$content = fread($fp, $_FILES['file']['size']);
			fclose($fp);
		
			// Add slashes to the content so that it will escape special characters.
			// As pointed out, mysql_real_escape_string can be used here as well. Your choice.
			$content = addslashes($content);
			$content= mysqli_real_escape_string($cxn, $content);
			$name= mysqli_real_escape_string($cxn, $name);
			// Insert into the table "table" for column "image" with our binary string of data ("content")
			mysqli_query($cxn,"INSERT INTO uploaded (file_id, name, type, size, image, email) Values('','$name','$type', '$size','$content','goro@yahoo.com')") or 
			die("Couldn't execute query in your database!".mysqli_error($cxn));
			
			echo 'Data-File was inserted into the database!|';
			echo '<a href="showImages.php?id=1">view</a>';
		}
		
    else{
      echo 'There was an error!';
    }
  }
  else{
    echo 'File must be jpg/jpeg and must be 73 kilobyte or less! ';
  }

}

  else {
  echo 'Please select a file!';

  }
}

?>
<!DOCTYPE>
<html lang="en">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
	<title> File upload</title>
</head>
<body>
<form action = "uploadFile.php" method= "POST"  enctype = "multipart/form-data">
      <input type = "file" name = "file"><br><br>
      <input type = "submit" value = "Submit">
</form>
</body>
</html>

my second script to actually display the image is : showImages.php comes next


<?php
//Open a new connection to the MySQL server
$user="root";
$host="localhost";
$password="";
$database = "dynamic_data";

$cxn = mysqli_connect($host,$user,$password,$database);
// Check connection
if (mysqli_connect_errno()) {
	echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

if (isset($_GET['id'])) {
	$id= mysqli_real_escape_string($cxn, $_GET['id']);
	$mysql_run=mysqli_query($cxn, "SELECT * FROM uploaded WHERE file_id ='$id';");
	
	while ($row=mysqli_fetch_assoc($mysql_run)) {
		
		header("Content-type: image/jpeg");
		$name=$row['name'];
		$type=$row['type'];
		$size=$row['size'];
		//header("Content-length: $size");
		//header("Content-type: $type");
		//header("Content-Disposition: attachment; filename=$name");
		echo $image=$row['image'];
		
	}
	
	
	

}

else {
	echo 'Error!';
}

Please what could be possibly wrong?

2

I know this doesn’t fix any problem that might be in your code sample. It’s just a strategy suggestion.

Is there a reason why you want to upload the image to the database? Why don’t you upload the image to a folder, and just store the image location in the database?

3

I read about one big company that did that and when the hard drive crashed the support staff restored the database from the backup which left tens of thousands of broken image links as the images were not backed up.

Another reason for including images in the database is where they may be regularly being added and removed along with other database updates as a part of the same transaction where as separate files you could easily get the images out of step with the rest of the data if you needed to do a rollback.

There are both pros and cons to including images in the database or keeping them separate. I think this is one of the situations with respect to database design where the two alternatives are just about equal in which is better - depending on the requirements of the particular use.

In this particular instance applying mysqli_real_escape_string to the image code is probably breaking the image.

I’d also get rid of the fopen and fread of the image file and use the LOAD_FILE() call inside the SQL to transfer the image into the database directly.

See http://www.felgall.com/mysql06.htm for an article I wrote about the database calls for directly loading and retrieving files in database BLOB fields.

4

Okay, I will do just what you asked me. And saving the file on a folder

5

The problem is with your insert. You are corrupting your image data with addslashes() so effectively you are inserting altered data to your db. You cannot use both addslashes() and mysqli_real_escape_string(). Back in the old days addslashes() was used as a poor-man’s escaping function for db strings but it cannot be relied for this job so use only mysqli_real_escape_string() and there should be no more problems.

6

In my code, I later used the mysqli_real_escape_sequence function without using the add slashes, and still had the same result. I think for now, saving my files in a folder in my main project directory will be the best bet, since, there is no difficulty in viewing the uploaded file.
THANKS TO ALL YOU GUYS FOR EVERY LITTLE BIT OF YOUR ASSISTANCE

7

mysqli_real_escape is unnecessary in this situation - you simply validate the filename properly and then the call will be completely safe without needing to escape anything

For example:

INSERT INTO myimagetable (image_name, myimage) VALUES ('myimage.jpg', LOAD_FILE('images/myimage.jpg'));

substitutiong the filename for the image name string field and the filename including relative path for loading the image into the blob field using the LOAD_FILE mySQL function.

Retrieving it back into an image is just as easy:

SELECT myimage INTO DUMPFILE 'images/myimage.jpg' FROM myimagetable WHERE image_name = 'myimage.jpg';

The only reason you have had problems is all the unnecessary PHP file processing.