Tiny PNGs bloating up in size when stored as blob

Question

I have a PHP script attempting to store images into a MySQL table as blobs. The script runs just fine and does its job as expected. However, when trying to view the stored image from the table, I notice only the top half is getting stored; the remaining is just cut off! Here's what I mean:

Also, I noticed that the image size is getting padded up for no apparent reason while being stored as blob. The image in question (left) is originally 46kB but when downloaded off the table (right), it shows 36kB (I know BLOBs can take up to 64kB so that shouldn't be a problem). This is happening to smaller images as well.

Even funnier is the fact that the file size doesn't look consistent in PHPMyAdmin. Take a look:

This is in table-view:

This is in insert view:

What's going on? Am I doing something wrong? How is a 46kB file turning into 36 in one view and 90 in another?

As for my max_allowed_packet size, it's currently defaulted to 4194304, way above what I'm trying to store.

UPDATE Here's the PHP snippet that's inserting the image to the db:

foreach($files as $fname) {
      if($fname != '.' && $fname != '..') {
          rename($oldfolder.$fname, $newfolder.$fname);
          $imgname = array_shift(explode(".",$fname)); //Individual image file name, without extension
          echo "\n".$imgname;
          $thumbph = fopen($newfolder.$fname, 'rb');
          $sql = 'UPDATE distilled_contacts SET THUMB = ? WHERE PHONE = ?';
          $connect = dbconn(PROJHOST,PROJDB,PROJDBUSER,PROJDBPWD);
          $query = $connect->prepare($sql);
          $query->bindParam(1, $thumbph, PDO::PARAM_LOB);
          $query->bindParam(2, $imgname);
          $query->execute();
          $query = NULL;
          $sql = NULL;
          $connect = NULL;
      }
  }

The dbconn() function looks like this:

function dbconn($strHost, $strDB, $strUser, $strPass) {
      try {
          $conn = new PDO("mysql:host=".$strHost.";dbname=".$strDB.";charset=utf8", $strUser, $strPass);
          $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $conn->setAttribute(PDO::ATTR_PERSISTENT, true);
          return $conn;
          }
      catch(PDOException $e) {
          die("Could not connect to the database $dbname :" . $e->getMessage());
          return false;
          }

  }

UPDATE: On Ryan's request, here's the table structure:

ID int(10) unsigned, auto-increment
PHONE varchar(20), utf8_unicode_ci
POPULARNAME varchar(60), utf8_unicode_ci
PREFERREDNAME varchar(60), utf8_unicode_ci
LATITUDE float(8,6)
LONGITUDE float(9,6)
LASTSEEN datetime
THUMB blob

The default collation for the database is utf8_unicode_ci which I cannot change as I need to support non European languages.

PHP version: 5.5.20, MySQL version: 5.6.24

Answer 1

You're using utf-8 in your connection string. Could this be the problem? You might have to remove that and use "SET NAMES utf8".

See this: Is a BLOB converted using the current/default charset in MySQL?

Also look at the comments here: http://php.net/manual/en/pdo.lobs.php

Specifically:

I spend a lot of time trying to get this to work, but no matter what I did PDO corrupted my data.

I finally discovered that I had been using:

$pdo->exec('SET CHARACTER SET utf8');

in the TRY part of my connection script.

This off course doesn't work when you feed binary input to PDO using the parameter lob.

Update
Like this

         function dbconn($strHost, $strDB, $strUser, $strPass) {
      try {
          $conn = new PDO("mysql:host=".$strHost.";dbname=".$strDB, $strUser, $strPass);
          $conn->exec('SET CHARACTER SET utf8');
          $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
          $conn->setAttribute(PDO::ATTR_PERSISTENT, true);
          return $conn;
          }
      catch(PDOException $e) {
          die("Could not connect to the database $dbname :" . $e->getMessage());
          return false;
          }
      }

Answer 2

As requested as a 'method' to ensure 'upload' and 'display' of image files work:

When 'things' get confusing - i believe in 'back to first principles' to ensure the 'basics' work.

I have scripts that 'work' here with your data. I would like you to ensure that these scripts work as expected.

I have uploaded your image into a 'blob' of the correct size and displayed it correctly.

These are not 'pretty' - they need to work and be easy to check.

Scripts: Upload Image:

Q30212477_uploading-image.php

<?php  // Q30212477_uploading-image.php
  session_start();

  DEFINE ('BIGGEST_FILE', 64 * 1024);

/*
Table: distilled_contacts

Field        Type         Collation        Null    Key     Default  Extra           Privileges            Comment
-----------  -----------  ---------------  ------  ------  -------  --------------  --------------------  ---------
id           int(11)      (NULL)           NO      PRI     (NULL)   auto_increment  select,insert,update
PHONE        varchar(20)  utf8_general_ci  NO              (NULL)                   select,insert,update
POPULARNAME  varchar(60)  utf8_general_ci  NO              (NULL)                   select,insert,update
LATITUDE     float        (NULL)           NO              (NULL)                   select,insert,update
LONGITUDE    float        (NULL)           NO              (NULL)                   select,insert,update
THUMB        mediumblob   (NULL)           NO              (NULL)                   select,insert,update
*/
?>

<?php if (!empty($_FILES)): // process the form... ?>

<?php // process the input files...

// start file processing...
/* debug */ // var_dump($_FILES); // show what we got as files...

// database connection...
$dsn = 'mysql:host=localhost;dbname=testmysql';
$username = 'test';
$password = 'test';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$connection = new PDO($dsn, $username, $password, $options);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if (is_uploaded_file($_FILES['thumb']['tmp_name'])) {
   $imgThumb  = fopen($_FILES['thumb']['tmp_name'], 'rb');

   $stmt = $connection->prepare("INSERT INTO distilled_contacts (`PHONE`, `POPULARNAME`,
                                `LATITUDE`, `LONGITUDE`,
                                `THUMB`)
                                VALUES (?, ?, ?, ?, ?)");

   $stmt->bindValue(1, $_POST['phone'], PDO::PARAM_STR);
   $stmt->bindValue(2, $_POST['popularname'], PDO::PARAM_STR);
   $stmt->bindValue(3, $_POST['latitude'], PDO::PARAM_STR);
   $stmt->bindValue(4, $_POST['longitude'], PDO::PARAM_STR);
   $stmt->bindParam(5, $imgThumb, PDO::PARAM_LOB);

   $connection->beginTransaction();
   $stmt->execute();
   $connection->commit();

   fclose($imgThumb);
}
else
   echo "Error uploading image";

unset($connection);
?>
<?php endif; // processed the form? ?>

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Upload images</title>
  </head>

  <body>

    <form action="" method="POST" enctype="multipart/form-data">
      <input type="hidden" name="MAX_FILE_SIZE" value="<?php echo BIGGEST_FILE ?>">

      <label for="phone">Phone Number</label>
      <input type="text" name="phone"  id="phone" value="1-800-TESTFILE"><br>

      <label for="popularname">Popular Name</label>
      <input type="text" name="popularname"  id="popularname" value="Jane Doe"><br>

      <label for="latitude">Latitude</label>
      <input type="text" name="latitude"  id="latitude" value="29.9792"><br>

      <label for="latitude">Longitude</label>
      <input type="text" name="longitude"  id="longitude" value="31.1344"><br>

      <label for="">Thumb image</label>
      <input type="file" name="thumb"  id="thumb" value="test_image_Q30212477.png"><br>

      <input type="submit" value="Upload file" />
    </form>
  </body>
</html>

<?php if (empty($_FILES)) {
  exit; // leave this script...
} ?>

And: Display Image:

index.php - display image

<?php  // index.php

       // https://stackoverflow.com/questions/30212477/tiny-pngs-bloating-up-in-size-when-stored-as-blob#30212477

 // !!! Change this id for your record!!!
 $ID = '1';

// database connection...
$dsn = 'mysql:host=localhost;dbname=testmysql';
$username = 'test';
$password = 'test';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
$connection = new PDO($dsn, $username, $password, $options);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);


// query
   $stmt = $connection->prepare("SELECT `PHONE`, `POPULARNAME`,
                                `LATITUDE`, `LONGITUDE`,
                                `THUMB`
                                FROM
                                    `distilled_contacts` dc
                                WHERE
                                    dc.id = ?");


   $result = array('PHONE' => "", 'POPULARNAME' => "",
                    'LATITUDE' => "", 'LONGITUDE' => "",
                    'THUMB' => null);


   $stmt->bindColumn(1, $result['PHONE']);
   $stmt->bindColumn(2, $result['POPULARNAME']);
   $stmt->bindColumn(3, $result['LATITUDE']);
   $stmt->bindColumn(4, $result['LONGITUDE']);
   $stmt->bindColumn(5, $result['THUMB']);

   // which row to return
   $stmt->bindValue(1, $ID);

   $allOK = $stmt->execute();

   // this returns an arry of results
   $allResults = $stmt->fetchAll(PDO::FETCH_ASSOC);

   /* debug */ // var_dump($result);

   $currentRow = current($allResults);
?>

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <title>Upload images</title>
  </head>

  <body>
      <ul>
          <li>Phone: <?= $currentRow['PHONE'] ?></li>
          <li>Name: <?= $currentRow['POPULARNAME'] ?></li>
          <li>Lat:  <?= $currentRow['LATITUDE'] ?></li>
          <li>Long: <?= $currentRow['LONGITUDE'] ?><li>
          <li>
              <div style="border: red solid 1px;">
                 <img src="<?= 'data:image/'. 'png' .';base64,'. base64_encode($currentRow['THUMB']); ?>">
              </div>
          </li>
      </ul>


  </body>
</html>

You need to change the $ID variable for your image id.