Updating Images in MySQL with PHP

Question

I am on my way to create the feature for users to upload their profile photo to the database. I manage to upload my photo to database in binary form (BLOB) but I am having problem while trying to display it.

upload form code:

<?php //get the posted image when the submit button is clicked 
$username = "MentorMenteeData"; 
$password = "mentormenteedata"; 
$host = "localhost"; 
$database = "mentormenteesystem"; 

// Make the connect to MySQL or die 
// and display an error. 
$link = mysql_connect($host, $username, $password); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 

// Select your database 
mysql_select_db ($database);         

   if (isset($_FILES['image']) && $_FILES['image']['size'] > 0) { 

      // Temporary file name stored on the server 
      $tmpName  = $_FILES['image']['tmp_name'];   

      // Read the file 
      $fp      = fopen($tmpName, 'r'); 
      $data = fread($fp, filesize($tmpName)); 
      $data = addslashes($data); 
      fclose($fp); 

      $student_id=$row_student['student_id']; 

      // Create the query and insert 
      // into our database. 
      $query = "UPDATE student SET student_img='$data' WHERE student_id ='$student_id'"; 
      $query .= "(image) VALUES ('$data')"; 
      $results = mysql_query($query, $link); 

      // Print results 
      print "Thank you, your file has been uploaded."; 

} 
else { 
   print "No image selected/uploaded"; 
} 

// Close our MySQL Link 
mysql_close($link); 
?> 

<form action="" method="post" enctype="multipart/form-data" name="changer"> 

<strong style="color: #FFD700;">Upload your image:</strong><br /> 
<input name="MAX_FILE_SIZE" value="102400" type="hidden"><br /><br /> 
<input namge="image" accept="image/jpeg" type="file"> 
<input type="submit" value="Submit"> 
</form>

Code to display image:

<?php 

$username = "MentorMenteeData"; 
$password = "mentormenteedata"; 
$host = "localhost"; 
$database = "mentormenteesystem"; 

mysql_connect($host, $username, $password) or die("Can not connect to database: ".mysql_error()); 

mysql_select_db($database) or die("Can not select the database: ".mysql_error()); 

$id = $_REQUEST['student_id']; 

if(!isset($id) || empty($id) || !is_int($id)){ 
     die("Please select your image!"); 
}else{ 

$query = mysql_query("SELECT * FROM student WHERE student_id='".$id."'"); 
$row = mysql_fetch_array($query); 
$content = $row['image']; 
} 

header('Content-type: image/jpeg'); 
     echo $content; 
?>

I could see my database table for the image column containing some bits but I just cant seems to display it. Please advise.

Don't store the actual image in the database... store it in the filesystem, and just hold the path to that file in the database — Mark Baker, Feb 25 '12 at 15:18

Michael Berkowski · Answer 1 · 2012-02-25T15:37:43.887

2

Your code to retrieve and display the image looks like it is correct, however I'm guessing you are never getting to the query due to this filtering line:

$id = $_REQUEST['student_id']; 

if(!isset($id) || empty($id) || !is_int($id)){ 
     die("Please select your image!"); 
}

is_int() tests if the type of a value is an integer, and values coming from $_GET, $_POST, $_REQUEST are always strings. , so your condition is always false. You can test it instead with ctype_digit() or is_numeric(), or intval($id) == $id. Also, empty() calls isset() implicitly, so isset() isn't needed.

// ctype_digit() method...
if (empty($id) || !ctype_digit($id)) { 
     die("Please select your image!");
}

// intval() method...
if (empty($id) || (intva($id) != $id)) { 
  die("Please select your image!"); 
}

edited Feb 25 '12 at 15:37

answered Feb 25 '12 at 15:19

Michael Berkowski

267,341
46
444
390

So, how could I get the image to be visible? what happen if I delete this line "if(!isset($id) || empty($id) || !is_int($id)){ die("Please select your image!"); }" ? Will this help? – Steve87 Feb 25 '12 at 15:42
@Steve87 Just replace that line with either one of my two suggestions. Don't delete it, as you need it to properly validate your `$id` against SQL injection. Leave the `else {}` that follows it intact. – Michael Berkowski Feb 25 '12 at 15:59

score 1 · Answer 2 · edited May 23 '17 at 10:34

Without any errors, it's hard to say, but if it's not the ID problem as suggested by Michael, it might [also] be corruption of your image data on upload. Have you tried downloading the directly (eg wget that URL) and opening it locally? Is the JPEG header there?

Some general comments:

Are you sure you want to store images in the database? It's generally preferred to store filename / URL fragments, and leave the binary data on disk, especially if they're larger images. See php:Store image into Mysql blob, Good or bad? for a discussion.
Either way that upload code is asking for trouble. addslashes() is not sufficient to fix the escaping problem, use a specific one like mysqli::real escape-string to safeguard against SQL Injection attacks - this post explores some differences.
If your MySQL DB is hosted on the same box as your webserver, you could even save effort (and increase speed) by using the MySQL LOAD_FILE function, but this isn't very scalable in the long term.
Consider moving all your login details to a separate file, out of the webroot for security.

Updating Images in MySQL with PHP

2 Answers2