Retrieve image data from mssql with php (utf-8)

Question

From a php script (PHP 5.3.10-1 on Ubuntu3.6) I connect to an MSSQL Server and I would like to retrive image data from image field type. And I want to print it.

I can get the data from MSSQL but I can't print/echo it as a valid image. How can I print/echo/save it?

$db= new PDO('odbc:MYODBC', '***', '***');
$stmt = $db->prepare("USE database");
$stmt->execute();
$tsql = "SELECT image 
         FROM Pics 
         WHERE id = 12";
$stmt = $db->prepare($tsql);
$stmt->execute();

$stmt->bindColumn(1, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);

header("Content-Type: image/jpg");

echo($lob); //not an image: 424df630030000000000360000002800 ...

imagecreatefromstring($lob);  // Data is not in a recognized format ...

$lob = fopen('data://text/plain;base64,' . base64_encode($lob), 'r'); //Resource
fpassthru($lob); //not an image: 424df63003000000000036000000280000 ...

PHP script encoding: UTF-8.

In /etc/freetds/freetds.conf

[MYODBC]
host = myhost.com
client charset = UTF-8
tds version = 7

((With sqlsrv on the server of MSSQL I could use this:

$image = sqlsrv_get_field( $stmt, 0, 
                      SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
header("Content-Type: image/jpg");
fpassthru($image);

))

UPDATE:

echo base64_decode($lob); //Not an image: γnτΣ}4ΣM4ΣM4ί4ΣM4ΫΝ4ΣM4s...

Don't forget that you have to store your image in base64 then decode it on retrieval. It seems you are not decoding the data. Did you encode it? — Robert Seddon-Smith, Jun 13 '13 at 09:17
The database made by others. So it's possible that it is not encoded. (But with sqlsrv it worked for me on the server.) In the database it seems to start with: 0x424DF630030000000000360000002800000... (Microsoft SQL Server Management Studio Express) — borazslo, Jun 13 '13 at 09:23

Stephan · Answer 1 · 2013-06-13T09:21:12.223

1

Try adding the following headers :

Content-Disposition
Content-Transfer-Encoding
Content-Length

In PHP code :

header('Content-Type: image/jpg');
header('Content-Disposition:attachment; filename="my_file.jpg"');// Set the filename to your needs
header('Content-Transfer-Encoding: binary');
header('Content-Length: 12345');// Replace 12345 with the actual size of the image in bytes

edited Jun 13 '13 at 09:21

answered Jun 13 '13 at 09:14

Stephan

41,764
65
238
329

Not. :( With "'Content-Length: '.strlen($lob)"... It returns the same as with the first header only. :( – borazslo Jun 13 '13 at 09:31
But It should be something wrong with the "binary" thing in my code. As in the workin sqlsrv version there is an "SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY)" option. How should I implement it to PDO? – borazslo Jun 13 '13 at 09:32
If your sqlsrv version works, sniff (via Google Chrome Dev Toolbar for instance) the headers sent to the browser then compare those headers to the ones sent by your PDO version. – Stephan Jun 13 '13 at 09:36

score 0 · Answer 2 · answered Apr 29 '16 at 20:08

I was recently fighting with a similar storage issue. It turned out that I was quoting my binary image data before insert into the database table. So, make sure you are not adding quotes and turning it into a string - like I accidentally did.

Here's the prep work that is done on an existing local file to get the proper data to store into your database. Also, make sure you have bin2hex() available or get a replacement version of the function.

function prepareImageDBString($filepath) {

    $out = 'null';
    $handle = @fopen($filepath, 'rb');
    if ($handle) {
        $content = @fread($handle, filesize($filepath));
        // bin2hex() PHP Version >= 5.4 Only!
        $content = bin2hex($content); 
        @fclose($handle);
        $out = "0x" . $content;
    }
    return $out;
}

I hope this helps someone.

Thank you for sharing your experience. It does not resolve the original problem, as I had the database filled up by others. But your experience could have helped for "reverse engineering". I hope this will be helpful for someone. — borazslo, Apr 30 '16 at 20:49

Retrieve image data from mssql with php (utf-8)

2 Answers2