Friday, December 26, 2008

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 :)

Free web hosts with php mail() function enabled

It is difficult to find a web host that is free and support php's mail() function. Following are my two favorite web hosts that freely supports php's mail() function.

1)FreeWebHostingArea.com
Features apart from mail include :
  • Quad-processor server with 4GB RAM
  • Unmetered traffic
  • 500 MB Webspace
  • PHP 5 (v5.2.4) with GD2 library, Zend Optimizer, php curl, php magickwand (support for ImageMagick 6.3), php sockets, php xml, xsl, php soap etc!!
  • Zend Optimizer support
  • Ioncube loader support
  • MySQL 5 support (v5.0.45) - one free db per account; database size is not limited!!!
  • SSI Support
  • Free Technical Support
  • Free Website Tools, preinstalled phpMyAdmin
  • Custom error page
  • Full FTP Access
2)0fees.net
Other features includes:
  • 300 MB disk space
  • 10 GB Monthly transfer
  • PHP with MySQL databases
  • 5 Add-on domains
  • 5 Sub domains
  • PHP Flags manager
  • FTP account
  • File manager
  • Webmail, POP3 email
  • MYSQL, Php MyAdmin
I have used both the hosting services. I like freewebhostingarea because of its space,bandwith and quick mail() function. But 0fees is getting improved day by day. I also like 0fees because it displays no ads on your pages while the later fwha shows its ads on your pages.

Tuesday, December 23, 2008

Ajax is so simple using JQuery

I recently started using JQuery and found that it has made JavaScript so simple and easy. For those of you who use Ajax simply , first you have to check if XmlHttpRequest object is available. If not then u try some ActiveX object untill to attain cross browser compatibility. This is all a very tiresome and repitative work.

JQuery on the other hand handles all the issue and make our script cross browser compatible. It supports IE 6.0+, Firfox 2.0+,Safari 2.0+ and Opera 9.0+.

Let us take an example. I want to get a list of US cities from a php page.

php code: cities.php
<?php

$cities = array(
'Annapolis MD',
'Atlanta GA',
'Augusta ME',
'Austin TX',
'Baton Rouge LA',
'Bismarck ND',
'Boise ID',
'Boston MA',
'Carson City NV',
'Charleston WV',
'Cheyenne WY',
'Columbia SC',
'Columbus OH',
'Concord NH',
'Denver CO',
'Des Moines IA',
'Dover DE',
'Frankfort KY',
'Harrisburg PA',
'Hartford CT',
'Helena MT',
'Honolulu HI',
'Indianapolis IN',
'Jackson MS',
'Jefferson City MO',
'Juneau AK',
'Lansing MI',
'Lincoln NE',
'Little Rock AR',
'Madison WI',
'Montgomery AL',
'Montpelier VT',
'Nashville TN',
'Oklahoma City OK',
'Olympia WA',
'Phoenix AZ',
'Pierre SD',
'Providence RI');

$count = isset($_POST['limit'])?$_POST['limit']:count($cities);
//$count = count($cities);
for ( $i = 0 ; $i < $count; $i++ ){
echo $cities[$i];
if ( $count-1 > $i )
echo "|";
}

//outputs city1 | city2 | city3 ...... | city n
>

Now we come to out html page in which we'll call the above page through Ajax.

first we'll have to include the latest version of JQuery . You can download it from its homepage jquery.com

html page: main.html

First we include jquery library which we have just downloaded.

<script type="text/javascript" src="jquery.js"></script>

We create an empty div in html part with id city in which we'll dump our data coming through Ajax.

<div id="a1"></div>

Get Ready for the action :P we are going to make our first Ajax request using Jquery's $.get method.

Get Method
prototype : $.get(url,data,callback)
url is cities.php in our case, we won't send any data now , callback function is called when data is received successfully .

$.get("cities.php",function(data){
//We get an array of citites
var d = data.split("|");
for ( i = 0 ; i < d.length ; i++ )
{
$("<li>" + d[i] + "</li>").appendTo("#a1");
}
});

And voila ! Our data is received and set into div with id a1, in a list.

So simple, Now we do it with a post request and send a variable limit so that only limit number of cities should be recieved and you'll see how beautifully it is done through jQuery.

Post Method:
prototype : $.post(url,data,callback,type)
url is cities.php in our case, we'll send limit = 3 which will be a key value paired data , callback function is called when data is received successfull and type could be either json or xml but we won't specify here as its optional.

$.post("cities.php",{limit:3},function(data){
//We get an array of citites
var d = data.split("|");
for ( i = 0 ; i < d.length ; i++ )
{
$("<li>" + d[i] + "</li>").appendTo("#a1");
}
});

This time you'll see that we get only 3 cities. Thats because in php code we specified that if $_POST['limit'] is set than use it rather than the length of actual array.

So you see how simple was it to send a post data without any headache to a page and get response.

One additional method that I would like to discuss is load(url,data,callback)

Load Method:

In most of the cases we just want to dump whole response into one div or a span of a particular id. This helpfull method does all that for you.

$("#a1").load("cities.php",{limit:3},function(data){
alert("Only top 3 cities have been received and loaded into id a1");
});

And that was all the Ajax using JQuery. I hope you have found it easy to go through JQuery's way of Ajax as I have.

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!

Monday, December 22, 2008

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 !