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 .....

It's also unclear if you're wanting to save the path to the file, or as a BLOB. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 6, 2018 at 22:02
Seeing the edit: What is the column's type? This needs to be a BLOB. You can @Funk me or @someone_else here. We can't keep looking at the question here. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 6, 2018 at 22:10
Right you are; use varbinary / varbinary(MAX) instead. My SQL-server skills aren't like MySQL. — Funk Forty Niner
– Funk Forty Niner, Commented Dec 6, 2018 at 22:25
Hmm..., I'd try and lose the base64_encode() and use a prepared statement, as in this Q&A. and/or stackoverflow.com/q/33630714/1415724 and stackoverflow.com/questions/43071537/… — Funk Forty Niner
– Funk Forty Niner, Commented Dec 6, 2018 at 22:39

Zhorov · Accepted Answer · 2024-07-02 07:08:46Z

0

The following example demonstrates how to insert a PDF file into a 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");
?>

edited Jul 2, 2024 at 7:08

answered Dec 7, 2018 at 7:02

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

D P. Over a year ago

Thank you for showing me with an example. If I were to echo the result of $pdf = file_get_contents('PDFFile.pdf'); I get some text similar to the following: %PDF-1.6 %�� 24 0 obj <> endobj 31 0... But when I go to the data Data filed in the data base, that filed shows the following value: <Binary data> and nothing else in it. If I try to print the value of $pdf = sqlsrv_get_field($stmt, 0, SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY)); $pdf output the following result: Resource id #12. By the way, my intention is to save this retrieved data on a user’s windows desktop.

Zhorov Over a year ago

@DP. Updated answer. If you want to force a download, use readfile().

Collectives™ on Stack Overflow

Inserting a file in to the SQL database and retrieving it

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related