2

I've been pulling my hair out for a couple of days about the issue i'm having.

Trying to read a PDF that is stored as binary blob data in a SQL database (not mysql) varbinary field.

If I use a program like Navicat, and save the raw blob data directly as a PDF, the file works.

When I try and read the data through PHP from a mssql query and create a PDF file - 

$file = "file.pdf";
$data = $row['FileData'];
file_put_contents($file, $data);

It creates the PDF, however it is corrupt and will not open.

I have difficulty in knowing exactly what format the data is stored as in the database. When I view the BLOB data in Navicat, it renders it like this:

%PDF-1.4
%Çì¢
5 0 obj
<</Length 6 0 R/Filter /FlateDecode>>
stream
xœÅY[o·F_÷Wð%èlðÞûX¸-8èCÐy-Éjt±´qÚüŒö÷#‡Crv¹Y§ÞÔ`qÈÃÃsùÎ…Ô“#    ±ñglïW_çÄÍn•¦ÅwɃç›ÕÓÊ*þKíx{/þxAR‹‹ë•CZN\I8ŠßF8FcI\ܯ†ß­/þ±b3jlx?â?    ¶rÔÊ‚áÅÛ¼}3Ïm2£y¡·ÿO«WÐB
'ÉŽÖ‰û•Æ—Ï»é[¢¸$”•ViGÅ^<_­®¿\Ù‘øçÊ‹oAûÍj£a\’¡ÙBÉÞ¿´q×ë$TÆqRúI<–3weÆúežwÕ
9q–a›¢W¯~¢«ÕHŒ^£l\Ìÿê7–‹H^–

It's hard to show you what the raw data in the database looks like, as binary data as I believe can't be viewed properly, and what you see is just the interpretation of what it really is.

Any help would be appreciated. Thank you.

Nicholas Morley
  • 43
  • 6
  • You may need external libraries to fully manipulate pdf from BLOB: http://stackoverflow.com/questions/467793/how-do-i-convert-a-pdf-document-to-a-preview-image-in-php – Vladimir Ramik Mar 10 '15 at 03:07
  • what is the php that retrieves the data from the table? your db method might be screwing up the data by trying to sanitize it or something – chiliNUT Mar 10 '15 at 03:13
  • @VladimirRamik you shouldnt need an external library to pull binary data from a database and write it to a file. OP is not manipulating pdf data, they are just trying to pull it and write it to disk – chiliNUT Mar 10 '15 at 03:15
  • I would avoid using any and all external libraries if possible. Was simply sharing a good link I've used before. – Vladimir Ramik Mar 10 '15 at 03:17
  • Hi @chiliNUT ` $q = "SELECT FileData FROM MessageAttachment WHERE MessageAttachmentID=1234"; $result = mssql_query($q); $row = mssql_fetch_array($result); $data = $row['FileData'];` – Nicholas Morley Mar 10 '15 at 03:29
  • @chiliNUT is correct, Vladimir Ramik, I am not trying to manipulate, i'm trying to get it and write to disk. – Nicholas Morley Mar 10 '15 at 03:32
  • @NicholasMorley hey, I'm not really sure, since all of the code you posted is of course extremely standard stuff. Its weird that navicat has no issue dumping to a pdf. I wonder if you are running into a weird line break issue? windows / *nix handle them differently and there are known (albeit only tangentially related) bugs (http://pear.php.net/bugs/bug.php?id=16118) with php's mssql adapter which can break blob data, but I'm just spitballing. sorry I can't be more help! – chiliNUT Mar 10 '15 at 03:40
  • also, something with pdf analyzing capabilities like ghostscript or acrobat might be able to look at the output file and give you an idea of just how corrupt it is, certain readers like firefox's built in reader or vanilla adobe reader may give you false negatives since they can be overly sensitive to less than perfect pdf data – chiliNUT Mar 10 '15 at 03:42
  • 1
    Thanks @chiliNUT - it's super frustrating as it normally would just work. I'm not doing anything complicated, and I know the data is binary stored in the database. I appreciate your input. – Nicholas Morley Mar 10 '15 at 03:49

2 Answers2

1

Solved the issue by adding the following to my .htaccess file

php_value mssql.textlimit 10000000
php_value mssql.textsize 10000000

Basically it was writing the files, but it was stopping 63kb (64,512 bytes) into the file, so increasing this limit enabled the script to finish writing the remainder of the file.

Hope this helps someone else with the same issue.

Nicholas Morley
  • 43
  • 6
0

Have you tried this?

select hex(pdf) from table;

Use the above query and use file_put_contents(); to store your contents onto a file.

PHP does a great job providing easy conversions between binary to hex and hex to binary.

Gururaju Hiddenx
  • 199
  • 1
  • 4
  • 19
  • I did, however I receive the error: [Err] 42000 - [SQL Server]'HEX' is not a recognized built-in function name. I am using SQL server here, not mysql. – Nicholas Morley Mar 10 '15 at 03:27