Use the links below to share Saving a File Directly Into a MySQL Database on your favorite social networks.
Back to the Article >>
Saving a File Directly Into a MySQL Database
October 15th, 2008 in Programming & IT
by: Matthew Griffin
In cases where file security isn't an issue, it's easy to save web form file uploads into a public directory on your server where they can be accessed by all. You can do this with just a few lines of PHP code, and it works great for public photo galleries, or avatar uploaders, etc. But what if you need to limit access to the files your users are uploading? Let's say, for example, you have a client that needs to be able to upload sensitive company files that can only be accessed by certain employees. This is where the MySQL longblob field comes in handy. In this tutorial, I'll show how to get a file from a web form into a MySQL database and out again.
Preparing the MySQL Database
If you don't already know how to create a new MySQL database on your server, you'll need to get acquainted with that process before any of this will be helpful. Most LINUX hosting packages come with a visual MySQL manager such as PHP MyAdmin by default. With a tool like that It shouldn't take long to figure out what's what. In this example, I'm going to assume you have a database called "MyDatabase" with a table called "Files". The Files table will have a number of fields, the most important of which is the File_Content field. The File_Content field will be a longblob field. The longblob field type isn't one you use every day. It can house large amounts of binary information that make up a file. You can run this SQL code on your database to create the new table automatically.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
CREATE TABLE IF NOT EXISTS `Files` (
`ID` int(25) NOT NULL auto_increment,
`Title` varchar(250) NOT NULL default '',
`File_Name` varchar(250) NOT NULL default '',
`File_Type` varchar(15) NOT NULL default '',
`File_Size` varchar(45) NOT NULL default '',
`File_Content` longblob NOT NULL,
`File_Extension` varchar(10) NOT NULL default '',
PRIMARY KEY (`ID`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
Setting Up Your HTML Form
Now that the database is ready to receive file uploads, the next step is to build an HTML form that presents the user with all of the necessary fields and options. The code below will get you up and running. Pay special attention to the enctype attribute in the opening <form> tag. Notice that it's set to "multipart/form-data". Without this attribute, your file will never make it to the server.
<form action="<?=$PHP_SELF;?>" method="POST" enctype="multipart/form-data" name="upload_form" id="upload_form">
<label>Title:</label><input name="title" type="text" id="title" value="<?=htmlentities(stripslashes($_POST['title']));?>" size="35">
<label>File:</label<input type="file" name="file">
<input name="action" type="hidden" id="action" value="add_document">
</form>
Writing Some PHP to Handle the File
Now, normally it's best to write your PHP code in an object oriented system. For our purposes, though, I'm going to combine all the code into one script so you can more easily see how it all works. The code below has detailed comments to help keep you from getting lost. First the script will check that a file has been uploaded. Next it will break the file apart, connect to the MySQL database and insert the file.
// Connect to the database
// replace "user_name" and "password" with your real login info
$dbh = mysql_connect("localhost","user_name","password") or die("There was a problem with the database connection.");
$dbs = mysql_select_db("MyDatabase", $dbh) or die("There was a problem selecting the categories.");
// Set up a list of acceptable file extensions.
// This keeps out malicious files
$acceptable_extensions[0] = "pdf";
$acceptable_extensions[1] = "jpg";
$acceptable_extensions[2] = "gif";
$acceptable_extensions[3] = "doc";
$acceptable_extensions[4] = "ppt";
$acceptable_extensions[5] = "xls";
$acceptable_extensions[6] = "xsl";
$acceptable_extensions[7] = "PDF";
$acceptable_extensions[8] = "JPG";
$acceptable_extensions[9] = "GIF";
$acceptable_extensions[10] = "DOC";
$acceptable_extensions[11] = "PPT";
$acceptable_extensions[12] = "XLS";
$acceptable_extensions[13] = "XSL";
$acceptable_extensions[14] = "txt";
$acceptable_extensions[15] = "TXT";
$acceptable_extensions[16] = "csv";
$acceptable_extensions[17] = "CSV";
$acceptable_extensions[18] = "docx";
$acceptable_extensions[19] = "DOCX";
// Check the uploaded file to make sure it's a valid file
$validated = 1;
if($_FILES && $_FILES['file']['name']){
//make sure the file has a valid file extension
$file_info = pathinfo($_FILES['file']['name']);
$acceptable_ext = 0;
for($x = 0; $x < count($acceptable_extensions); $x++){
if($file_info['extension'] == $acceptable_extensions[$x]){
$acceptable_ext = 1;
}
}
if(!$acceptable_ext){
$validated = 0;
}
}else{
$validated = 0;
}
//Now that we're sure we have a valid file,
//we'll add it into the database
if($validated){
// Get important information about the file and put it into variables
$fileName = $_FILES['file']['name'];
$tmpName = $_FILES['file']['tmp_name'];
$fileSize = $_FILES['file']['size'];
$fileType = $_FILES['file']['type'];
// Slurp the content of the file into a variable
$fp = fopen($tmpName, 'r');
$content = fread($fp, filesize($tmpName));
$content = addslashes($content);
fclose($fp);
if(!get_magic_quotes_gpc()){
$fileName = addslashes($fileName);
}
$file_info = pathinfo($_FILES['file']['name']);
$sql = "INSERT INTO Files SET
Title = "".htmlentities(stripslashes($_POST['title']))."",
File_Name = '".$fileName."',
File_Type = '".$fileType."',
File_Size = '".$fileSize."',
File_Content = '".$content."',
File_Extension = '".$file_info['extension']."'";
$result = mysql_query($sql);
// If the query was successful, give success message
if(!$result){
echo "Could not add this file.";
exit;
}
else{
echo "New file successfully added.";
}
}else{
echo "Invalid file.";
exit;
}
Make sure to test this script with a small file. If you're having trouble getting this to work, check out the "Important Notes and Pitfalls" section at the end of this tutorial. More than likely you'll find your answer there.
Getting Your File Back Out of the MySQL Database
Okay, we've successfully uploaded a file directly into a longblob field in a MySQL database. But that's not the end. Now we need to pull it back out. This process is actually quite simple. Unfortunately, I didn't have a comprehensive tutorial like this when I was figuring it out so it took a lot of trial and error. Essentially, we are going to create a PHP script that accepts a file ID through a $_GET variable called id, pulls the corresponding file out of the database and sends it to the browser with the correct headers so the user can open it. The script should look something like this:
// We'll start out again by connecting to the database
// replace "user_name" and "password" with your real login info
$dbh = mysql_connect("localhost","user_name","password") or die("There was a problem with the database connection.");
$dbs = mysql_select_db("MyDatabase", $dbh) or die("There was a problem selecting the categories.");
// Now, We'll check to make sure our $_GET variable is a number
if(!is_numeric($_GET['id']){
echo "Invalid file chosen.";
exit;
}
// Next, we'll run a query on the database to get the file out
$sql = "SELECT * FROM Files
WHERE Files.ID = ".$_GET['id'];
$result = mysql_query($sql);
// If the query was invalid or failed to return a result, an error is thrown
if(!$result || !mysql_num_rows($result)){
echo "Invalid file chosen.";
exit;
}
// Finally, we will send the file to the browser
$curr_file = mysql_fetch_assoc($result);
$size = $curr_file['File_Size'];
$type = $curr_file['File_Type'];
$name = $curr_file['File_Name'];
$content = $curr_file['File_Content'];
header("Content-length: ".$size."");
header("Content-type: ".$type."");
header('Content-Disposition: attachment; filename="'.$name.'"');
echo $content;
// That's it. We're finished.
Important Notes and Pitfalls
To start, there are two very important server settings that can completely ruin your upload script. The first is the "upload_max_filesize" variable in your PHP.ini file. If this is set too low, large files just flat out won't make it up onto the server. The second is the MySQL server variable called "max allowed packet". Most of the time, hosts like to set this pretty low by default. If you don't have direct access to this setting, you may need to contact your host to have them increase it. If you upload a file that's larger than the "max allowed packet" setting, it won't make it into the database.
Also, I didn't include a password protection script in this tutorial but you should be able to find a free one floating around the internet somewhere. It's important that you have good security on pages that allow visitors to upload files. I won't insult you by listing the reasons why.
- 22 Comments
- 15370 Views