Building a Download Manager for Your WebSite, Using ISAPI, ADO and - of course - Delphi 5

By marc hoffman, elitedevelopments software

Last August, we radically redesigned our website, elitedev.com, moving away from static HTML to a more active and dynamic design. The main focus of the new site was to offer our customers the ability to log on to the site, and to provide every customer with a personalized view of the data they need. For example, our webhosting customers can view their site's statistics, our software customers can access private downloads of their latest software versions, etc.

One of the key elements in offering private downloads is a custom ISAPI .dll that controls each and every download from our site. It is this .dll, developed in Delphi 5, that we will be taking a closer look at for the remainder of this article.

Setting the requirements

The first job for every development is to decide on the exact set of features needed. So lets summarize what our ISAPI .dll need to do:

1. Let the user download a file from our site
2. Control which files can be downloaded by which users

The database

I'm aware that database structures are not a very exiting topic, but one can't write code to access a database if one doesn't understand it's structure. That's why we will have to take a small excursion into elitedev.com's database structure, before we can get our hands dirty. I'll try to make it brief, I promise.

Information about available downloads will - like all other data on the site - be stored in a database, in our case residing on a Microsoft SQL Server 7.0. Layouting the entire database structure would exceed the scope of this article, so i will try to focus on the information that is relevant to our project, the download manager. (A detailed view of the database structure is available here)

First of all, we have of course a table containing all Downloads. Each entry contains a unique DownloadID, the local filename (relative to a common point in the server's file system), plus additional information like description, creation date and flags whether the file is available for public or limited-user download.

Secondly, we have tables containing the user base. For our purpose, all we need to know is that we have a list of Users, and a list of Groups. Users can be member of many groups, linked via a third relational table, UserGroups.

Downloads again can be linked to either Users (for example a private download for just a single customer) or entire Groups (for example the latest product beta, available for all members of the beta test). These links are also managed by two relational tables, namely UserDownloads and GroupDownloads.

A user can download a file, when at least one of three conditions is met:

1. The file is marked as public
2. The user as direct access to the Download (by entry in UserDownloads)
3. The user is member of a Group that has access to the Download (indirectly linked thru UserGroups and GroupDownloads)

Luckily, we have a stored procedure available that will help us decide whether a user may download a certain file, or not.

Getting going

Ok, enough theory, let's launch Delphi and start coding our ISAPI extension. Delphi 5 offers a great encapsulation of the ISAPI framework thru its WebBroker technology, and many great articles have been written about is, so I won't dwell on the basics. Select File|New|Web Server Application, decide on ISAPI when asked, and Delphi will automatically generate all the framework we need to start coding.

We want our ISAPI .dll to be called (for example from an asp page) with the following syntax:

where XXX is the unique id of the file to download. We create a new Action, name it wac_Download (wac for WebACtion) and set it's Default property to true. So whenever a request to download.dll reaches the web server, it will be processed by this newly created Action object.

We create an event handler for the OnAction event, and because working with our database is what we are mostly going to do, the first this we do is open the connection to our data source:

procedure TDownloadWebModule.wac_DownloadsAction(...);
begin
  try
    con_SiteDb.ConnectionString := fConnectionString;
    con_SiteDb.Open();
    try
 
      { ToDo: add rest of code to handle this Action }
 
    finally
      con_SiteDb.Close();
    end;
  except
    on E:Exception do begin
      Response.SendRedirect(fExceptionAsp+'?class='+
                            E.ClassName+'&message='+E.Message);
    end;
  end;
  Handled := true;
end;

What this code does is opening a connection to out database server, and making sure this connection is properly closed when we're done (by placing the call to Close() inside a finally block).

Also, we place an try/except block around the entire code, so we can catch any unexpected exeptions and handle them properly. Be default, an Execption inside a Delphi ISAPI causes an ugly white page stating the exception name and message. While this is ok for debugging purposes, you would not want your customers to ever see this. So what we are doing is we're redirecting the request to an external ASP page, passing if the details about the exception. This ASP page could for example display a user-friendly message like "we are experiencing technical difficulties, please try again or check back in a few hours", and send out a message to the site admin via email or pager.

Who am I?

Now, what exactly do we need to do to handle the request? First of all, we need to check who the current user is, and whether he is allowed to access the requested file. If the file is public, this is trivially easy: every (even an anonymous) user may download the file. But if access to the file is limited, how do we find out which user has logged on?

This is not quite as trivial as it might appear at first. Our site - which uses ASP - keeps tracked of the current user by writing his UserID into the session object. So the ASP code always knows which user is logged on. But since ISAPI extensions live one layer above (or below, if you will) ASP in the IIS hierachy, we unfortunately do not have access to our Session data.

The second option that comes to mind - passing the UserID via the URL - has to be discarded, as well. A malevolent user cannot write a UserID into our ASP Session object, so it's safe for ASP code to assume the UserID to always be valid. But any user could fiddle with the URL and pass a random UserID to our .dll. And eventually he would find one that would grant him access to the file he wants to download. So this is definitely not an option.

What we chose to do was to have our ASP code store both UserID and password inside a temporary cookie when the user logs on. This way our ISAPI .dll can do the user check itself, simply by evaluating the cookie.

And this is what we are going to do now, adding the following code to the event hander we created above:

UserID := StrToInt(Request.CookieFields.Values['UserID']);
Password := Request.CookieFields.Values['Password'];
DownloadID := StrToInt(Request.QueryFields.Values['id']);
 
if (UserID > 0) and (Password <> '') then begin
 { ToDo: we have a user authorization data, check his data }
end
else begin
 { ToDo: we have an anonymous user, may download only public files }
end;

The the first case we will have to first check whether the supplied credentials are valid for the user. We simply look up the users record in the User table and check his password:

qry_User.Parameters.ParamByName('UserID').Value := UserID;
qry_User.Open();
try
  // do we have exactly one record, and does the password match? 
  // if yes we had valid credentials.
  if (qry_User.RecordCount = 1) and
     (qry_User.FieldByName('Password').Value = Password) then begin
 
    { ToDo: user is ok, now check if he may download this file }
 
  end
  else
    Error(Response,ERROR_INVALID_USER);
finally
  qry_User.Close();
end;

If the password was ok, we can assume the user to be authenticated, and we can go on with the download. If not, we generate an error. We will take a closer look at how this error gets reflected back to the user, further down.

May I download it?

Once the user's credentials have been verified, we can now check if this user has access to the requested file. Thanks to our stored procedure, this is a piece of cake:

sp_IsUserDownload.Parameters.ParamByName('@UserID').Value := UserID;
sp_IsUserDownload.Parameters.ParamByName('@DownloadID').Value := DownloadID;
sp_IsUserDownload.ExecProc();
if (sp_IsUserDownload.Parameters.ParamByName('RETURN_VALUE').Value > 0) then begin
   
  {ToDo: user may download file. Now send it to him }
 
end
else 
  Error(Response,ERROR_INVALID_DOWNLOAD);

Our stored procedure returns a positive value, if the user has access to the file, and zero if he has not. We only need to set the appropriate parameters, namely UserID and DownloadID, execute the stored procedure, and check the return value. If it's larger that zero, we have a Go, and we can finally lookup the download's source from the Downloads table and have the file send to the user via the Response object:

qry_AllDownloads.Parameters.ParamByName('DownloadID').Value := DownloadID;
qry_AllDownloads.Open();
try
  if (qry_AllDownloads.RecordCount = 1) then begin
    SendFile(Response,qry_AllDownloads.FieldByName('FilePath').AsString);
  end
  else
    Error(ERROR_INVALID_DOWNLOAD)
finally
  qry_AllDownloads.Close();
end;

The second case - having an anonymous downloader - is basically the same as the last step of the previous scenario. We lookup the file in the Downloads table, but only send it back if it's Public flag is set. The code looks exactly as the block above, except that we use the qry_PublicDownloads Query instead of qry_AllDownloads. Sending the file back to the user What's left now is actually getting the requested file back to the user. This is handled by the SendFile method, which is called in both scenarios and looks like this:

procedure TDownloadWebModule.SendFile(iResponse: TwebResponse; iFilename:string);
var FileStream:TFileStream;
begin
  Replace(iFilename,'/','');
  FileStream := TFileStream.Create(fFilebase+iFilename, 
                                   fmOpenRead and fmShareDenyWrite);
  iResponse.ContentStream := FileStream;
  iResponse.ContentType := 'application/x-zip-compressed';
  iResponse.SetCustomHeader('Content-Disposition',
                            'filename='+ExtractFilename(iFilename));
end;

What we do is we expand the relative filename that came from the database by appending the path to the file store (which we could for example read from the registry on startup), and create a TFileStream. We can assign this stream to the Respose object's ContentStream property, and the Delphi ISAPI framework will automatically send the entire contents of the file back to the client.

Now we only need to make sure the client knows how to handle the data. We do this by setting the content type to 'application/x-zip-compressed' (even if the file in question should be an .exe or some other binary file, the client will be able to cope with it), and setting the desired filename that the client should suggest for storing the file. The Content-Disposition header makes sure the client site appliaction - for example Internet Explorer or some download manager like GetRight - will know the correct name of the file it is downloading.

Last but not least, we used a method named Error in several places above, to propagate errors back to the user. This method basically does the same as we have already seen in the except bock of our event handler above: it redirectes the request to an ASP page that handles displaying the appropriate information to the user:

procedure TDownloadWebModule.Error(iResponse: TWebResponse; iCode:integer);
begin
  iResponse.SendRedirect(fErrorAsp+'?code='+IntToStr(iCode));
end;

That's it?

Yes, that's it. Well, there's of course some minor maintenance work to do, like actually reading a few settings from the Registry, etc., but basically that's all. You now have a full featured download manager for your site, which allows you to control who is allowed to download which file, and who is not.

Is it perfect? Most certainly it's not. There are a few features that might be added, for example we could log which files are being downloaded how often (or even by which authorized users). Or we could have the .dll pass the request back to ASP for anonymous users to request further information (as we are actually doing on our site).

Also, there is one small issue that's not solved very well, yet. As you might have noticed, we are opening and closing a connection to the database each and every time a user tries to download a file. But opening a database connection is generally regarded to be an "expensive" operation. While this will not be a problem as long as users are downloading maybe a few hundred files per day, it might soon become a problem if - or when - your site scales up to have several thousand or ten thousand users requesting files every day.

Several options for removing this bottleneck come to mind, the most sensible being to move the database logic off to an MTS/COM+ component. MTS/COM+ will cache database connections for us. This is clearly an interesting topic that wants to be explored in a followup article.

Another related topic worth exploring are techniques for testing and Debugging ISAPI extensions and MTS objects.

So hopefully, I will see you back soon. In the meantime, feel free to contact me with any feedback or question you might have regarding this article. Just drop me a mail to mh@elitedev.com.

The full package including the source code, the SQL script to generate the database and everything else can be downloaded here (yes, using our download.dll ;-) or from Code Central.

take care,
marc hoffman
elitedevelopments software