Handling Binary Data with PDO

I’m a great fan of the PDO database access library in PHP 5 and use it for all of my database work in PHP. I love its’ clean, object oriented syntax and great support for prepared statements. I also like the fact that it supports most of the most common database engines. Although all of my dev work in PHP so far has been with MySQL I like the fact that if I needed to use MS SQL Server, Oracle or any of the other big RDBMS’s I could use the same PDO syntax to access them rather than learning a new database access library. However, there do seem to be some bugs in PDO  according to what I’ve read on the web. While I haven’t encountered most of them and can’t comment on them I’d like to write about one that I ran into the other day and how I worked around it.

I have a project that I’m working on where I’m storing some images in a database as binary data. PDO allows you to bind a file handle to a parameter in a prepared statement and when the statement is executed the contents of the file are slurped into the database. This works perfectly but the problem comes when getting the image out of the database again to display it. According to the PHP manual the following code should work:


<?php
$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

header("Content-Type: $type");
fpassthru($lob);
?>

Binding a column from a result set to a variable using PDO::PARAM_LOB is supposed to return a stream resource into the variable when PDO::fetch() is called. This stream can then be operated on using any PHP function that handles files. Unfortunately there’s a bug which means that instead of returning a stream into $lob PDO returns a string containing the binary data. When this is then passed to fpassthru() an error is triggered. Fortunately there’s a simple fix for displaying the image: replace the call to fpassthru() with echo or print. Since the browser is expecting an image after the call to header() writing the binary data using echo or print has the same effect as calling fpassthru(). In my code I’ve added the following just in case this bug is fixed in a future release:


if (is_string($lob)) {

echo $lob;

} else {

fpassthru($lob);

}

This neatly gets around the problem if you just want to send the binary data back to the browser to be displayed. Anything more requiring the use of any file functions or image editing functions would need quite a few contortions in the code. The information from the database would probably need to be written to a temporary file to allow it to be operated on. This bug was first reported almost three years ago in PHP 5.2.6 and it’s still not fixed today in the most recent version, 5.3.1. It would be great if this bug was finally taken care of.

Edit: Joshua Johnston has posted a comment below that explains how to convert a string of data into a stream using the data stream wrapper. I’ve tried it out and it works very well. I think it gives a cleaner solution to the problem and allows the data returned from the database to be manipulated with file functions.

11 thoughts on “Handling Binary Data with PDO

  1. You can also try this code to convert your string data into a stream first then continue accessing it as if it were a string. I haven’t tested if the base64 data will output correctly or not but that is the basics of turning any in-memory data source into a stream

    if (is_string($lob)) {
       // $lob is now a resource pointing to a stream composed of the data at hand
       $lob = fopen('data://text/plain;base64,' . base64_encode($lob), 'r');
    }
    
    fpassthru($lob);
    
  2. Pingback: abcphp.com
  3. In reply to Josh: The whole idea of handling LOBs as streams is to greatly reduce memory load. While your workaround will work, it will keep the whole LOB in memory, and that LOB could be a few megs picture on a pretty busy site…

  4. In reply to Dennis: I think you have a very valid point but Josh’s solution is the only one I’ve seen so far that completely gets around this bug. For my purposes simply writing the binary data to the output was enough as all I needed to do was to display the image. For anything else I’d probably need a stream, which is where Josh’s solution comes in. I completely agree with you in that it’s not optimal from a memory consumption point of view. The only true solution would be for this bug to be fixed I guess.

  5. Hello,

    i am having a big problem with this PDO LOB bug. I have alof of files stored in DB2 and currently the only solution i can use is to do this…


    $stmt = $db->prepare($QSql);
    $stmt->bindColumn(1, $Podat, PDO::PARAM_LOB);
    $stmt->bindColumn(2, $FileName, PDO::PARAM_STR);
    $stmt->execute();
    $stmt->fetch(PDO::FETCH_BOUND);

    for ($i = 0; $i < $noc; $i = $i+2)
    {
    $PodatPret .=chr(hexdec(substr($Podat, $i, 2)));
    }

    fwrite($Datot, $PodatPret);
    fclose($Datot);

    This solution is good for small images… but it is very bad for larger files since it takes alot of time to convert to scroll over the file doing substr, hexdec and chr.

    Anyone can help?

    Contact me by mail ( kozy_ligh at yahoo dot com ) or answer here please.

    Thank you!

    With regards,

    Marko

  6. Update,

    since i cannot get this to work any other way i did this…


    function hextostr($x) {
    $s='';
    foreach(explode("\n",trim(chunk_split($x,2))) as $h) $s.=chr(hexdec($h));
    return($s);
    }

    this way my data gets changed but it consumes alot of memory so large files are sitll a big problem.

    Any recomendation or a hint how to implement:


    $lob = fopen('data://text/plain;base64,' . base64_encode($lob), 'r');

    I just cant get it to work.

    With regards,
    Marko

Leave a Reply