Inserting a file in to the SQL database and retrieving it

Question

My school’s web server does not allow us to upload files to it using move_uploaded_file(….). Therefore, I am trying to learn how can I insert a PDF file in to a SQL server database filed and retrieve it later. I was able to put the PDF file using the following code. But the retrieving file via the browser part does not work. It only prints the filed's values and does not save the file in to my computer. Any help would be appreciated

<?php
//Connection get established successfully
$connInfo = array(//code omitted);
$connect = sqlsrv_connect(//code omitted, $connInfo) or die(print_r(sqlsrv_errors(SQLSRV_ERR_ALL), true));

if(isset($_POST['upload'])) //This part works
{
    // extract file name, type, size and path
    $file_path=$_FILES['pdf']['tmp_name']; //pdf is the name of the input type where we are uploading files
    $file_type=$_FILES['pdf']['type'];
    $file_size=$_FILES['pdf']['size'];
    $file_name=$_FILES['pdf']['name'];

    // checks whether selected file is a pdf file or not
    if ($file_name != "" && $file_type == 'application/pdf')
    {//PDF file may contains, images, tables, etc.. 
        $data = base64_encode(file_get_contents($file_path));

        //SQL Data type is varchar(MAX). query to update file in database.
        $query="UPDATE TestTable SET Data='".$data."' WHERE ID=1";
        $result = sqlsrv_query($connection, $query); //query execution
        // Check if it was successful
        if($result)
            echo 'Success! Your file was successfully added!';
        else
            echo '<br>Error!:'.sqlsrv_errors();
    } 
    else 
    {
        echo 'Not a pdf file. Try again';
    }
}

if(isset($_POST['read'])) //Does not download the file!!
{
    //Query to fetch field where we are saving pdf file
    $sql = "SELECT Data FROM TestTable WHERE ID = '1'";
    $result2 = sqlsrv_query($connection, $sql);    // query execution
    $row = sqlsrv_fetch_object($result2); // returns the current row of the resultset
    $pdf_content = $row->Data; // Put contents of pdf into variable
    $fileName = time().".pdf"; // create the unique name for pdf generated
    //download file from database and allows you to save in your system
    header("Content-type: application/pdf");
    header("Content-disposition: attachment; filename=".$fileName);
    print $pdf_content;
}

?>

<form name="form" id="form" action="" method="post" enctype="multipart/form-data">
File: <input type="file" name="pdf" id="pdf" accept="application/pdf" title="Choose File" /><br />
<input type="submit" name="upload" id="upload" value="Upload" /><br />
<input type="submit" name="read" id="read" value="Read" />
</form>

I am saving the full data value of the file in to the database filed(not inserting, updating an existing row). Not trying to save the file's path or text content of it alone. This save the PDF file in to the database filed in base64_encode. If I look at the content of the database after running this code, I see the row has been updated with something similar to the following: JVBERi0xLjYNJeLjz9MNCjI0IDAgb2JqDTw8L0xpbmVhcml6ZWQgMS9MIDM1MTcyL08gMjYvRSAzMDI1Ni9OIDEvVCA .....

Answer 1

Next example demonstrates how to insert a PDF file into varbinary(max) column in SQL Server and then save this file to disk:

T-SQL:

CREATE TABLE [dbo].[VarbinaryTable] (
    [Data] varbinary(max) NULL
)

PHP:

<?php
# Connection
$server = 'server\instance,port';
$database = 'database';
$uid = 'user';
$pwd = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $uid,
    "PWD" => $pwd
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false)
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

# Insert PDF file
$pdf = file_get_contents('PDFFile.pdf');
$sql = "INSERT INTO VarbinaryTable ([Data]) VALUES (?)";
# In your case:
# $sql = "UPDATE VarbinaryTable SET [Data] = ? WHERE ID = 1"; 
$params = array(
    array($pdf, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY), SQLSRV_SQLTYPE_VARBINARY('max'))
);
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false) {
    echo "Error insert (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

# Get PDF file and save it again on disk. 
$sql = "SELECT [Data] FROM VarbinaryTable";
# In your case:
# $sql = "SELECT [Data] FROM VarbinaryTable WHERE ID = 1"; 
$stmt = sqlsrv_query($conn, $sql);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
if (sqlsrv_fetch($stmt)) {
    $pdf = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY));
    file_put_contents('PDFFile-fromDB.pdf', $pdf);
}

# Output the file
header('Content-Description: File Transfer');
header("Content-type: application/pdf");    
header("Content-disposition: attachment; filename=PDFFile-fromDB.pdf");
header('Content-Length: ' . filesize("PDFFile-fromDB.pdf"));
readfile("PDFFile-fromDB.pdf");
?>