Web Reporting with MySQL, CSS and Perl

Extending the Maypole Soccer Club Database System with a simple Web-based reporting mechanism.
Security: Protection from User Tampering

When it comes to tampering with the CGI script and SQL query files, the problem is—by default—all of the files can be read by any user logged in to the system that runs the Web server; a simple cat or less command would do the trick. Any user can look inside runquery.cgi and display the user ID and password used in accessing the database, which is not good.

The User and Group directives in the Apache httpd.conf configuration file indicate which user and group the Apache Web server runs under. On my computer, this user and group is set to apache. Knowing this, I issued the following commands to ensure that the contents of my CGI script and SQL query files are owned by the apache user and that they can be read from and written to only by the same apache user. This stops any other user—except root, of course—from examining their contents:

cd  /var/www/cgi-bin
chown  apache:apache  *
chmod  600  *
chmod  700  *.cgi

The first chmod command ensures the files in the cgi-bin directory can be read from and written to solely by the apache user. The second chmod command switches on the executable bit for any CGI scripts, but only for the owner of the file. With these simple precautions, my solution is now safe from user tampering.

Security: Protection from the CGI Script

The above chmod command lines protect the files from other users logged in to the system, but my solution still is vulnerable. Unfortunately, it is open to exploitation by any user with access to the Web server by way of any Web browser. For example, consider what happens if the following URL is sent to the CGI script:

http://localhost/cgi-bin/runquery.cgi?            \
title=Ha!&query=conditions.sql | cat runquery.cgi

The contents of the CGI script appear in the browser, and the issuer easily can read the database name, the user ID and password contained within the file. This is bad enough, but imagine if the cat runquery.cgi pipe in the above URL is replaced with this:

cat  /etc/passwd

or the potentially disastrous:

rm  -rf  /

The problem with the CGI script as written is it blindly trusts the issuer not to fiddle with the URL. By simply adding the pipe symbol and any other shell command line to the URL, the issuer exploits this poorly designed CGI script, effectively executing other commands of the issuer's choosing on the Web server. By passing the query string unaltered to the operating system to execute, the CGI script makes it far too easy for such a vulnerability to be exploited.

Thankfully, Perl has a special mode of operation that can help, and it is called taint mode. Most any book on Perl describes taint mode, and the second edition of Christiansen and Torkington's Perl Cookbook provides a handy primer (recipe 19.4, page 767). By turning on taint mode, the Perl interpreter is instructed not to trust data that originates outside the script. As the data is not trusted—it's “tainted”—Perl won't let you use the data in an unsafe way without raising a run-time exception.

I can turn on Perl's tainting technology by changing the first line of my CGI script to include the taint mode switch:

#! /usr/bin/perl -wT

When I reload the fiddled-with URL, the resulting HTML page is empty and Apache's error_log has been appended with an insecure dependency error. This is Perl's way of telling me that the script failed due to tainting errors. Obviously, with the script failing in this way, it is no longer a security threat to the system. However, it also is no longer doing what it was designed to do, which makes it all but unusable. To make the script usable again, we need to untaint the input data using Perl's regular expression technology. The idea is straightforward: by defining a pattern representing safe data, the pattern can be applied to the tainted data and—assuming the pattern matches—any results are untainted and considered safe. With the CGI script, there are two data inputs, query and title. I added the following regular expressions to the CGI script to untaint the input data:

$query =~ /^([-\w]+\.sql)$/;
$query = $1;

$title =~ /^([\w:.?! ]+)$/;
$title = $1;

The first regular expression matches on a string that has any combination of hyphens or word characters, followed by a period and the letters s, q and l. Anything else doesn't match and is considered suspect. If a match does occur, Perl remembers the match in the $1 match-variable, which then is assigned back to the now untainted $query. With titles, the pattern allows any combination of word characters and those characters included within the square brackets of the regular expression. Again, the $title variable is untainted when a successful match occurs.

As the CGI script executes an external executable—namely, the MySQL client—the environment's path also needs to be untainted. This is accomplished by setting the PATH variable within the environment to a safe list of directories, as follows:

$ENV{'PATH'} = "/usr/bin";

With these changes made to the runquery.cgi script, it is usable once more. As well as being quick-and-dirty and safe from user tampering, my solution is no longer a potential security threat to my system.



Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re:Web Reporting with MySQL, CSS and Perl

Clement Yap's picture

Hi Paul,

I thank you for your generous tip on how we can convert the mySQl results into html in a breezy way.

However I would like to know how I can produce results in html that looks much like command line client, that is the results is bordered by plus, pipe and dash symbols.

Pls advise
Thanks in advance


ayesha's picture

hy its realy v informative..i actually have to create web interface using perl..have made a database of hospitalsin SQL and now have to create web interface using perl and html..could u plz help me in that


Tommy Burle's picture

Hi Paul

Tommy Burke from your 2004 Networking class here. Stumbled across this when re-searching a current reporting project for work. It has greatly helped me in getting a headstart on the code and the above makes it a hell of a lot easier to work with the query results rather than parsing through each rown and formatting them into a html page.. :-)

Now if i could only get these damn admins to install CGI,DBI and DBD:Mysql faster!!

Once more an excellent piece of work by you


Web Reporting with MySQL, CSS and Perl

Ashok's picture

Dear Barry,
The teaching style is simply marvelous.
You have consistently simplified a complex task by breaking it into small easy to follow steps.
Though I have no intentions of implementing such an arrangement, I read through the entire article as it easy easy to understand.


Nice comments, thanks!

barryp's picture

Shucks ... I'm blushing. Very pleased you liked it.

Paul Barry
IT Carlow, Ireland

Paul Barry