Not able to show a pdf / image from binary data in MSSQL using PDO LOB

Question

I go MSSQL database which contains attachments - most of them are PDFs. I want to create simple website, that will display attachment after clicking a button. I got main website prepared (there are records with attachments' titles and a link fo the other page, that is supposed to just show the attachment in the new tab).

I've been using PDO as I heard that is secure and pretty simple.

My query '$sql' for sure returns one row (I have checked that by simply doing var_dump() of the FetchAll()).

 $stmt = $pdo->prepare($sql);
    $stmt->execute([$id]);

    $stmt->bindColumn(10,$lob, PDO::PARAM_LOB);
    $stmt->bindColumn(11, $attachment, PDO::PARAM_STR, 256); 
    $stmt->fetch(PDO::FETCH_BOUND);
    $lobContent = stream_get_contents($lob);


    $ext = substr(strrchr($attachment, '.'),1); 



      
       header("Content-Type: Application/pdf");
     
        // fpassthru($lob) shows some weird stuff that looks like memory leak
         echo $lobContent; 

Any ideas what Am I doing wrong here? If I try to use fpassthru() it show me some garbage, I mean it looks like memory leak or something, there are some parts of the code, that is not on my website and is not in the database...

I have checked if the query result isn't empty, tried to just fetchAll() the data I got but as told above, got very unexpected behaviour.

Answer 1

I have managed to solve my problem. It turned out that weird output isn'tany memory leak - it was just the contnent of the document that I was getting the binary from. After finding it out I have tried few things and the milestone was changing the PDO connection to

$conn = sqlsrv_connect($server, $conninfo); 
$stmt = sqlsrv_query($conn, $sql, $params); 
if ( sqlsrv_fetch( $stmt ) )  
{ 
$attachment = sqlsrv_get_field( $stmt, 10,   
SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));  

header("Content-Type: Application/pdf");  

fpassthru($attachment);
  
}  

I was able to open the pdf and images from databse in my browser, using binaries saved in the db!

Hope this helps anyone in the future tho I know, it is a workaround.