porDBle -- A Portable Web Database Solution

Kenneth Hess

As an independent consultant, I am often asked to provide samples of my work to prospective clients. More than 90 percent of my work is built for intranets and, therefore, is not viewable by the world at large. I am legally bound, in most cases, to keep it that way, and that creates a dilemma. Selling these services without examples is like trying to sell a car that people can't look at. I could register a domain name and put up some working samples of my work to demonstrate my design, database interaction, and advertise other services as well, and I have done so at http://www.webdbms.com. This approach works to some extent, but prospective clients often see this as marketing and not a "real-life" example of an application that is in use.

I researched and tested many different ways to give "live" demos of my Web, database, and scripting capabilities. I looked at proprietary solutions -- some very costly, some fairly cheap, and a few that were completely free. After almost giving up on the whole idea, I sat down and explicitly defined my criteria for this solution, which deviated somewhat from my original idea.

The Criteria

My original idea consisted of putting Apache, MySQL, PHP, and maybe Perl on a CD-ROM and using it as a marketing tool that would demo some working applications. I got that to work on Linux, as well as on Windows, but quickly realized that this solution was little more than what I could do just using static HTML. A CD-ROM is nice and portable, but it lacks the interactive read/write qualities of being able to input, delete, and update data in an application. I wanted the extra edge of a fully read/write capable application.

So, my new criteria for solving the problem are as follows:

1. Full read/write access to a live database.

2. A small, standalone Web server that doesn't need to be installed on the host computer.

3. A small, standalone database server that doesn't need to be installed on the host computer.

4. PHP or Perl for the database connectivity and Web application scripting.

5. The applications must be non-proprietary, free, and preferably open source.

6. The demo Web applications need to be fast enough to be usable to the client.

7. The Web and database server must be highly configurable.

8. Portable.

9. Easy to setup, even for a novice.

10. Inexpensive to distribute.

The first requirement is the most difficult, although you are probably thinking RAM disk, which would be okay, but the data would not be saved when the media are moved to a new computer. Satisfying criteria 2 through 7 is fairly easy. There are many good contenders in this area (see Resources).

Requirement number 8 puzzled me for a while, but I finally chose a USB pen drive, which satisfies number 10 as well. 32-MB pen drives can be found for less than $10.00. Number 9 is basically up to me to create startup and shutdown scripts or an application that handles the setup with minimal user interaction. Even though, in most cases, only root can mount file systems, I prefer to keep things simple.

After considering all of these criteria, my final solution consists of Fedora Core 3 (kernel 2.6.9-1.667, any Linux distribution should work), PHP 5.0.3 (which conveniently has SQLite 2.X embedded), thttpd 2.21b, and a 64-MB USB pen drive. I call this portable database solution porDBle (and I pronounce porDBle as "portable").

SQLite is a good choice for the database server, because where you have limited space, you need not even install SQLite as an executable. It is included with PHP 5.X, and it is free, fast, and open source.

The selection of thttpd was a little less obvious and may require some defense. However, it meets all of the criteria I set for a Web server. It is small, runs as a standalone server, is very fast even under heavy loads, works with PHP, is free, and is open source. Although I did not investigate the use of it, nanoweb -- a Web server written in PHP -- is interesting to me as you would only need to install PHP for everything to work.

Using these two applications made my job very simple. I have only two applications to install from source, and they are known to work together. Fedora Core 3 was my operating system of choice, because I wanted the updated USB support (and I have a love affair with Red Hat Linux that goes back to 1997). The 64-MB USB pen drive was the default choice simply because I have one that came with my laptop. My goal for the project is to use 32-MB drives for their low cost of distribution.

Installation

Next, I will go through the installation from source. After the executables are compiled, they should be distributable on the USB drive without issue.

Download thttpd version 2.21b from:

http://www.acme.com/software/thttpd/thttpd-2.21b.tar.gz

http://www.php.net/downloads.php

Insert your pen drive into an available USB slot on your Linux computer. You should hear the computer "sense" the new hardware being powered up. If you check dmesg, you will see that the SCSI subsystem has been initialized and the USB drive has been recognized by your system. Also check dmesg to see how this device is viewed by your system; mine sees it as sda. You then will need to create a usable file system on the pen drive. Although you can probably use FAT or FAT32, or even NTFS, you should go ahead and format with a Unix file system so that you can take full advantage of Unix permissions.

To create the new file system, I just used the defaults with:

# mke2fs /dev/sda1

# mount /dev/sda1 /mnt/usb
# cd /mnt/usb
# mkdir bin etc include lib log man sbin tmp www
# cd man
# mkdir man1 man8

Now we need to build the thttpd and PHP binaries. Cd to your source directory. Again, mine is /opt/source. Note that these steps must be completed in this order or thttpd and PHP will not work together:

# cd /opt/source
# tar zxvf thttpd-2.21b.tar.gz
# tar zxvf php-5.0.3.tar.gz
# cd php-5.0.3
# ./configure --prefix=/mnt/usb  --with-thttpd=../thttpd-2.21b
# make
# make install

# cd ../thttpd-2.21b

WEBDIR = /mnt/usb/www

# CONFIGURE: The group that the Web directory belongs to. This is 
# so that the makeweb program can be installed set-group-id to 
# that group, and make subdirectories.  If you're not going to use 
# makeweb, ignore this.

#WEBGROUP =      www

# CONFIGURE: Directory for CGI executables.

CGIBINDIR =     $(WEBDIR)

# ./configure --prefix=/mnt/usb
# make
# make install

You now have a working installation of PHP/SQLite and thttpd all on your USB pen drive. This installation should be about 13 MB in size at this point. You can trim it down if desired by removing some things like the man pages and binaries other than PHP and thttpd. You won't save much space because PHP and thttpd are just more than 5 MB each.

PHP looks for the php.ini file in the lib directory under your prefix directory (in our case, /mnt/usb/lib). Copy the php.ini-dist from your PHP source directory to /mnt/usb/lib and edit the file. Find the section called Error Handling and Logging and make the following changes:

These changes will force errors to be logged to a file and not to the browser. It is a potential security risk to have the errors sent to the browser, because paths, names, and code are all given away in the error message.

If you want or need to enable File Uploads, go to the File Uploads section and make the following changes:

Thttpd can be run from the command line with switches and options, or it can be run with reference to a config file. I prefer the config file method, because I can run multiple instances of thttpd on different ports, or as a different user, etc. by selecting a different config file.

For simplicity, I place my config files in /mnt/usb/etc. You can name them anything you want because you will be referencing them when you start thttpd. For now, I will show you a config file for a single thttpd. You will be able to see from the example how to implement multiple instances:

dir=/mnt/usb/www
chroot
user=nobody        # default
logfile=/mnt/usb/log/thttpd.log
pidfile=/mnt/usb/log/thttpd.pid
# This section _documents_ defaults in effect
port=81
# nosymlink# default = !chroot
# novhost
# nocgipat
# nothrottles
# host=0.0.0.0
# charset=iso-8859-1

To test the configuration, create a trivial PHP script /mnt/usb/www/info.php with the following contents:

<? phpinfo(); ?>

#  /mnt/usb/sbin/thttpd -C /mnt/usb/etc/thttpd.conf

http://<linux server>:81/info.php

Congratulations! You should now see the phpinfo for the installation of PHP on your Linux server. You now have a self-contained, fully capable Web server with a full-featured scripting language with database connectivity.

If you don't see the phpinfo page, you will need to go back through the installation process to see where you may have gone wrong. Read the rest of this article first, as there might be a permissions problem with your files. Also check whether thttpd is running:

ps  -ef  | grep thttpd

For manipulating data and SQLite databases, tables, etc., you will probably want to download SQLite either in binary or source form and install it on your development server. If you decide to include it with any distribution, it will only take up about 50 KB of space. You will need the 2.X version of SQLite as it is compatible with PHP. Databases created with SQLite are portable between platforms, so if you create a SQLite 2.X database on your Windows workstation, you can copy it to the USB pen drive for use with thttpd and PHP on Linux, and vice versa.

Startup and Shutdown of thttpd

Under the source directory for thttpd, you will find a nice startup/shutdown script, thttpd.init, that can be modified for your purpose. It is under thttpd-2.21b/contrib/redhat-rpm. You can edit the paths to match our/your install paths as you wish, or you can simply use the startup.sh and shutdown.sh scripts below. I place these in the root directory of the USB pen drive:

#!/bin/sh
# This is the startup.sh for thttpd
/mnt/usb/bin/thttpd -C /mnt/usb/etc/thttpd.conf

#!/bin/sh
# This is the shutdown.sh for thttpd
STOP='ps -ef | awk '/thttpd/ && !/awk/ {print $2}''
kill $STOP

To disconnect the device, first cd off it, then issue the umount command:

# umount  /mnt/usb

File Permissions, Security, and Distribution

The following information on file permissions is reprinted from the thttpd man page (see Resources). Thttpd is picky about file permissions. It wants data files (HTML, images) to be world-readable. Readable by the group that the thttpd process runs as is not enough -- thttpd checks explicitly for the world-readable bit. This is so that no one ever gets surprised by a file that's not set to be world-readable and yet somehow is readable by the HTTP server and therefore the whole world.

The same logic applies to directories. Like the standard Unix ls program, thttpd will only let you look at the contents of a directory if its read bit is on; but as with data files, this must be the world-read bit, not just the group-read bit.

Thttpd also wants the execute bit to be off for data files. A file that is marked executable but doesn't match the CGI pattern might be a script or program that accidentally got left in the wrong directory. Allowing people to fetch the contents of the file might be a security breach, so this is prohibited. Of course if an executable file does match the CGI pattern, then it just gets run as a CGI.

In summary, data files should be mode 644 (rw-r--r--), directories should be 755 (rwxr-xr-x) if you want to allow indexing and 711 (rwx--x--x) to disallow it, and CGI programs should be mode 755 (rwxr-xr-x) or 711 (rwx--x--x).

The SQLite database files must be in the /mnt/usb/www or a subdirectory of it because we have done a chroot to /mnt/usb/www. I keep mine separate from my PHP pages in a subdirectory of www called data.

SQLite permissions, or so I have found, can be very restrictive. I set mine at 600 or (rw-------) for the databases. If you allow PHP to create a database, the permissions will be the default set by the umask, which most likely will be 644 (rw-r-r--).

Once the read, write, and execute permissions are all set correctly, I do a global change to nobody or the user that the thttpd runs as:

cd /mnt
chown -R nobody:nobody *

While some would argue that placing data files in the same directory or subdirectory with your Web files is a non-secure method for a Web/database application, I think that due to the restrictions placed on us by thttpd, we haven't much choice. This solution, as described, is for use on an intranet and should be used as such. You should take reasonable security precautions when exposing any daemon to the Internet.

The biggest security advantage of this solution is that you only have a single daemon running, thttpd. The database is only accessible on the local file system with no daemon of its own, there is limited space on the device, and the thttpd is running as a non-root user. As long as your permissions are in order and your PHP coding is up to standards (preventing SQL injections, etc.), there should be little to worry about.

Distributing code causes many programmers/developers a little grief, but don't despair; there are code obfuscators out there for PHP. So, if you have a proprietary solution, protect yourself and your code by passing it through an obfuscator.

SQLite is a great database solution for a wide range of applications because it is ACID-compliant. However, there are limitations. If you write an application that requires frequent writes, you may want to consider a different database server, although I am confident that the developers of SQLite will continue to improve it.

Conclusion

I have attempted, in this article, to provide an overview of an interesting solution to allow your users access to a very capable Web database solution in a very small package. As media become less expensive, and with the use of USB hubs, you could potentially provide several Web database applications all running their own Web servers on different ports for different departments. Did I mention that it is portable?

Resources

Database servers -- http://www.linuxlinks.com/Software/Databases

Nanoweb -- http://nanoweb.si.kz

PHP 5.x -- http://www.php.net/downloads.php

Thttpd version 2.21 -- http://www.acme.com/software/thttpd/thttpd-2.21b.tar.gz

Thttpd man page -- http://www.acme.com/software/thttpd/thttpd_man.html

Web servers -- http://www.linuxlinks.com/Software/Internet/WebServers

Kenneth Hess is a Linux enthusiast and advocate. He began his obsession with Linux in 1995 and fervently defended his 1.2.13 kernel until he buried it in late 1999 along with his aspirations of becoming a famous actor. He can be reached for dialog at: kenneth.hess@gmail.com.