Download file from mySQL database (blob)

[wei123]

I have 3 files:

config.php

<?php

$db = new PDO("mysql:host=localhost;dbname=datarepo", "root", "");

?>

data.php (the data repository which displays the table of files from my database)

<?php

<table class ="table">
<thead>

<tr>
<th>#</th>
<th>File</th>
<th>Action</th>
</tr>

</thead>

<tbody>
<?php
include('config.php');

$stmt = $db -> prepare("SELECT * FROM files");
$stmt->execute();


while($row = $stmt->fetch()){
?>

<tr>
 <td> <?php echo $row["id"]?></td>
 <td> <?php echo $row["name"]?></td>
 <td><a href="download.php?id=<?php echo $row["id"]?>" class="btn btn-primary">Download</a></td>
</tr>

<?php
}
?>

</tbody>

</table>

?>

download.php

<?php

include "config.php";
if(isset($_GET['id'])){
    
    $id = $_GET['id'];
    $stmt = $db -> prepare("select * from files where id=?");
    $stmt->bindParam(1,$id);
    $stmt->execute();
    $data = $stmt -> fetch();
    
    $file = 'dpo'.$data['name'];

    if(file_exists($file)){
        header('Content-Disposition: name="'.basename($file).'"');
        header('Content-Type:'.$data['mime']);
        header ('Content-Length:'.filesize($file));
        readfile($file);
        exit;
        
    }
      
}
?>

When I try to download a file from the data repository (data.php):

I will be brought to any empty page but no file will be downloaded, with the url: http://localhost/dpo/download.php?id=1

Here is a sample of the 'files' table from the 'datarepo' database in mySQL:

So my question is, how do I get the file to download?

[Barand]

try content-disposition : attachment

header('Content-Disposition: attachment; filename="'.basename($file).'"');

 

[wei123]

7 minutes ago, Barand said:

try content-disposition : attachment

header('Content-Disposition: attachment; filename="'.basename($file).'"');

  

no luck, download.php still gives a white page without downloading the file.

For reference, here's my upload file (please ignore the file sanitation for now) :

<?php

include('config.php');

if(isset($_POST["submitfile"])) {
  
	$fileName = $_FILES['file']['name'];
	$fileSize = $_FILES['file']['size'];
	$fileError = $_FILES['file']['error'];
	$fileType = $_FILES['file']['type'];
	$data = file_get_contents($_FILES['file']['tmp_name']);

	$fileExt = explode('.', $fileName);
	$fileActualExt = strtolower(end($fileExt));
	
	$allowed = array('csv','doc','docx','jpg','pdf','png');
	
	
	if (in_array($fileActualExt, $allowed)){
		if ($fileError == 0){
			if ($fileSize < 5000000){
				$stmt = $db ->prepare("insert into files values('',?,?,?,?)");
				$stmt ->bindParam(1,$fileName);
				$stmt ->bindParam(2,$fileType);
				$stmt ->bindParam(3,$fileSize);
				$stmt ->bindParam(4,$data);
				$stmt ->execute();

				echo "File uploaded successfully.";
			}
			
			else {
				echo "File size is too big.";
			}
		} else {
			echo "There were was an error uploading the file.";
		}
		
	} else {
		echo "File format not supported.";
		
	}
	
}
?>

 

Edited November 10, 2018 by wei123

[mac_gyver]

a) it is generally not a good idea to store files in a database (getting large data into and out of the database is a problem. and sql dumps are twice the size since they are done in hexadecimal.) databases are for storing data. file systems are for storing files.

b) if you do store files in a database, you must store all of the file. the blob type you have used only holds 64K bytes. the file size your code allows is 5M bytes (and would take special handling to store since the default data packet size is 1M bytes.) the actual size of your test file is 102k bytes, which was then cut off at 64K when it was stored.

c) the php code in download.php is trying to check the existence of and read the file as though it is stored in the file system, not in the database.

Edited November 10, 2018 by mac_gyver

[wei123]

32 minutes ago, mac_gyver said:

a) it is generally not a good idea to store files in a database (getting large data into and out of the database is a problem. and sql dumps are twice the size since they are done in hexadecimal.) databases are for storing data. file systems are for storing files.

b) if you do store files in a database, you must store all of the file. the blob type you have used only holds 64K bytes. the file size your code allows is 5M bytes (and would take special handling to store since the default data packet size is 1M bytes.) the actual size of your test file is 102k bytes, which was then cut off at 64K when it was stored.

c) the php code in download.php is trying to check the existence of and read the file as though it is stored in the file system, not in the database.

a) Yeah I'm well aware of that but my project requires me to store the files into the database so I don't have a choice here.

b) Is this alright? I've changed the data type from 'blob' to 'longblob', so now the blob size matches the file size

c) I've remove the if else block so download.php now looks like this:

<?php
include "config.php";
if(isset($_GET['id'])){
	
	$id = $_GET['id'];
	$stmt = $db -> prepare("select * from files where id=?");
	$stmt->bindParam(1,$id);
	$stmt->execute();
	$data = $stmt -> fetch();
	$file = $data['name'];

		header('Content-Disposition: attachment; filename="'.basename($file).'"');
		header('Content-Type:'.$data['mime']);
		header ('Content-Length:'.filesize($file));
		readfile($file);
		exit;
	
}


?>

But now whenever I try to download something, I will always end up with a 322B file.

Edited November 10, 2018 by wei123

[ginerjm]

"your project requires" that you store files in a db?  Who is making this a requirement?  Not a teacher I hope!

[Barand]

The functions filesize() and readfile() both require the file to be stored in the file system.

If you are storing the file in the database, that is where your filesize and filedata need to be downloaded from. (And as mac_gyver pointed out, a BLOB column only holds 64K. Change it to MEDIUMBLOB (16MB) )

Better still, store the uploaded files on the server and store the metadata only in the database.

[Barand]

A couple of pointers regarding the first piece of code in your original post (to list the  file names)

Your query is

$stmt = $db -> prepare("SELECT * FROM files");

1. There is no point in preparing a query of that nature as there are no user-provided items of data.
2. Don't use SELECT STAR. Especially as you are storing large blob items. You are now retrieving every column of every row (including the unnecessary blobs). This just wastes time and resources. Always specify the columns you need. In this case

$stmt = $db->query("SELECT id, name FROM files");

 

[wei123]

Updated download.php:

<?php
include "config.php";
if(isset($_GET['id'])){
	
	$id = $_GET['id'];
	$stmt = $db->query("SELECT id, name,mime,filesize FROM files");
	$stmt->bindParam(1,$id);
	$stmt->execute();
	$data = $stmt -> fetch();
	$file = $data['name'];
	$type = $data['mime'];
	$size = $data['filesize'];
	
	// echo $data['name']."<br>";
	// echo $data['mime']."<br>";
	// echo $data['filesize']."<br>";
	
	
	header("Content-Disposition: attachment; filename=$file");
	header("Content-Type: $type");
	header("Content-Length:$size");
	
	exit;
	
	
}

?>

 

Right now, the files I download are only 264 bytes in size.

 

Updated table:

 

Echo messages give me:

datarepo.PNG
image/png
28282

 

Edited November 10, 2018 by wei123
code edit

[Barand]

You should also think about outputting the contents of the file too. All you do is send headers.

The query I gave you was the one where you list the files for selection. You cannot use the same query every time you access the database. This time you need to select the required record (id = ?) and get all the fields (including the blob column).

I recommend you turn on php's error reporting - you should have got errors telling you that indexes mime and filesize do not exist (as you didn't select those columns)

[benanamen]

1 hour ago, wei123 said:

my project requires me to store the files into the database so I don't have a choice here

Something smells amiss here.

[Barand]

As you are too lazy to specify column names in your SELECT and INSERT statements, some of this is guesswork. But it should look something like this

$stmt = $db->prepare("SELECT name
                           , size
                           , mime
                           , data
                      FROM files
                      WHERE id = ?     
                      ");
$stmt->execute([ $_GET['id'] ]);
$file = $stmt->fetch();
   
        header('Content-Disposition: attachment; filename="'.basename($file['name']).'"');
        header('Content-Type: '.$file['mime']);
        header ('Content-Length: '.$file['size']);
        echo $file['data'];