Secure download-upload script in Php

Recently I have written a blog post on how to insert a file in mysql database as a blob data type. In this post I will explain how you can upload user submitted file securely to your server and make it available for download. I personally prefer this method as work load on a file system will be better than on a database system. But we'll still be using a mysql database to record details of an uploaded file. For a more visited site this database should also be recording user's ip and browser etc. But we'll just go through with a simple database schema. Rest is upto you.

So Our schema goes like this,

First create the table mydata by executing belowgiven query:

CREATE TABLE `mydb`.`mydata` (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`save_name` VARCHAR( 255 ) NOT NULL ,
`orig_name` VARCHAR( 255 ) NOT NULL ,
`upload_date` DATETIME NOT NULL ,
`download_count` INT NOT NULL DEFAULT '0'

) ENGINE = InnoDB

save_name is the name of the file with which it will be saved on your web site's server.
orig_name is the actual name of the file with which the user will download and have uploaded it.
We are keeping it with a different name to keep away from name ambiguity in case two users submit file with same name ... and even we don't want people on the server to interfere with file .. to keep user's file secure..
We'll also keep track of Date of upload and how many times it has been downloaded.

Let us start our upload file php code.

upload_file.php

<?php
if ( isset($_POST['submit']))
{
$upload_dir = "uploads/";
if ( $_FILES['file']['error'] === 0 )
{
//Here is the place where you can check for your desired file extension eg jpg,zip etc
//For this example i am assuming any type of file can be uploaded

//A unique 32 char name for the uploaded file
$file_name = md5(uniqid(microtime(),true));

//Upload the file there is no error and type is also fine
if ( move_uploaded_file($_FILES['file']['tmp_name'],$upload_dir.$file_name))
{
$conn = mysql_connect("localhost","root","");
mysql_select_db("mydb",$conn);
$query = "insert into mydata (save_name,orig_name,save_date)
VALUES('{$file_name}','{$_FILES['file']['name']}',NOW())";
if (mysql_query($query,$conn))
echo "File added successfully";
else
echo "Query error";
mysql_close($conn);
}
else
echo "File upload error";
}
else
{
echo "File upload error : {$_FILES['file']['error']}";
}
}
?>

Before using this script you'll have to create the "uploads" directory in the same directory where this script is saved.

Now we come to the download part.

download_file.php
<?php
$id = (int)$_GET['id'];
$upload_dir = "uploads/";
$conn = mysql_connect("localhost","root","");
mysql_select_db("mydb",$conn);
$query = "select * from mydata where id = {$id}";
if ( $result = mysql_query($query,$conn) )
{
if ( $row = mysql_fetch_array($result) )
{
$query = "update mydata set download_count = download_count + 1
where id = {$id}";
mysql_query($query,$conn);
$file_name = $upload_dir.$row['save_name'];
$file = file_get_contents($file_name);
$size = filesize($file_name);
$orig_name = $row['orig_name'];
header("Content-Type: application/octet-stream");
header("Content-Disposition: inline; filename={$orig_name}");
header("Content-Length: {$size}");
echo $file;
}
else
echo "No file found with given id";
}
else
echo "error in query";
?>

A secure file upload/download script is ready now. You'll have to take care of the allowable types which I haven't cater for the example and ensure that when a user enter uploads directory url in his browser he should get a 403 error ie set appropriate permissions. Ok thats all now u are secure :)



Inserting Binary data into MySQL database using Php

A simple script which can help to store any binary data to you MySQL database. You can add any type of binary data eg word document, Excel spreadsheet, PDF file , an image or a video in through it. I will use MySQL's LONGBLOB data type. it support at most a data of 4GB.

schema

CREATE TABLE `mydata` (

`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

`data` LONGBLOB NOT NULL,

`fname` VARCHAR(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE = InnoDB

create the above table before using the script

upload_file.php

<?php

if($_FILES['myfile']['tmp_name']!="")

{

if(mysql_connect("localhost","root",""))

{

if(mysql_select_db("mydb"))

{

$fname=$_FILES['myfile']['name'];

$tname = $_FILES['myfile']['tmp_name'];

$fsize = $_FILES['myfile']['size'];

$data=file_get_contents($tname);

//to escape all binary data which can make mysql mad

$data = mysql_real_escape_string($data);

if(mysql_query("Insert into mydata (data,fname) values('$data','$fname')"))

{

echo "File inserted successfully";

}

else

{

echo "Error occured ".mysql_error();

}

mysql_close();

}

}

}

?>

<form action="" method="post" enctype="multipart/form-data">

<input type="file" name="myfile">

<input type="submit" value="UPLOAD">

</form>

usage: upload_file.php

download_file.php

if(mysql_connect("localhost","root",""))

{

if(mysql_select_db("mydb"))

{

$id = (int)$_GET['id'];

$result=mysql_query("Select * from mydata where id = {$id}");

if($row=mysql_fetch_array($result))

{

Header( "Content-type: application/octet-stream");

Header( "Content-Disposition: inline; filename={$row['fname']}");

echo ($row['data']);

}

else

{

echo "File doesn't exist with above id";

}

}

}

?>

usage: download_file.php?id=2

enjoyy ... for any problem with the script leave your comments!

Prevent your Php MySQL scripts from SQL injections

If your site uses data from user and operates its database functionalities over it then you are not safe. SQL injections are the most comman type of attack which a site faces. I would like to mention some common types of attacks and how to save your script from these attacks.

Escape your input strings

Consider a query

"select * from users where username = '{$userid}' "

input : $userid = " 1'; delete from users"

so the query would become 

"select * from users where username = '1'; delete from users"

A query is injected to delete all the data in users table. But luckily mySql doesn't support query stacking hence more than one queries can't be executed. But this attack can occur in other databases like pgsql and ms sql which supports query stacking.

But still the input is not save . consider another input

$userid = " 1' OR '1' = '1' "

query becomes :

"select * from users where username = '1' OR '1' = '1' "

since 1 = 1 always the attack is successfull. So you must escape quotations from user input. Standard way of escaping will be:

function escape($data)

{

if ( get_magic_quotes_gpc() )

$data = stripslashes($data);

return mysql_real_escape_string($data);

}

Magic quotes should be checked for .. as if they are 'on' then your data will be escaped twice .. You might ask if you can use addslashes in place of mysql_real_escape_string as both escapes special characters .. but cases have been found in which addslashes failed against sql injections .. I won't go into much details of it .

mysql_real_escape_string escapes characters:  \x00, \n, \r, \, ', " and \x1a .

When quotations are not used

What if your query doesn't contain a quotation. Example

select * from users where id = $userid

and $userid = 1 OR 1 = 1

which will become

"select * from users where id = 1 OR 1 = 1"

But we have escaped quote .. and since simple int doesn't require to be quoted your query again became vulnerable.. what we can do in this case is to cast an input ..

$userid = (int)1 OR 1 = 1 //$userid = 1

or in case the input is a float we can cast it as a float to ensure that the data coming is of required type.

% and _ are not escaped still

Consider this query:

select * from users where date LIKE '{$date}%'

$date = "%" // we expected something like 2008-12

an attacker can use % and _ too ... they must also be escaped.. php provides a custom escaping function addcslashes.. we can further escape our data like this

$data = escape($data); // above escape function

addcslashes($data,"%_");

And all % and _ will also be escaped which culd attack on LIKE based queries.

Prevent your schema details from user

if you do query something like 

mysql_query ($query) or die(mysql_error());

the function mysql_error() can reveal certain details about your tables and field names. Try some other approach. Maybe you could store errors in a private log file which only you can read.

These were only some of the tips. Users of your web site are your evil enemies. Gmail, wikipedia and other big names all were attacked like this. Also keep your script save from these attacks by using above tips and more if you can. Good luck !