Quote Marks

The variable that contains the database query is the $query string. Within that we have the problem of quotes. Perl likes double quotes if it is to interpolate a $ or @ value; MySQL likes quotes of some sort around a text variable. If we wanted to search for the person whose first name is in the Perl variable $xname, we could use the query string:

$query="select * from people where xname='$xname'";

This will work and has the advantage that you can test it by typing exactly the same string on the MySQL command line. It has the disadvantages that while you can, mostly, orchestrate pairs of '' and " ", it is possible to run out of combinations. It has the worse disadvantage that if we allow clients to type a name into their browser that gets loaded into $xname, the Bad Guys are free to enter a name larded with quotes of their own, which could do undesirable things to your system by allowing them to add extra SQL to your supposedly innocuous query.

Perl allows you to open up the possibilities by using the qq() construct, which has the effect of double external quotes:

$query=qq(select * from people where xname="$xname");

We can then go on to the following:

$sth=$dbm->prepare($query) || die $dbm->errstr;
$sth->execute($query);

But this doesn’t solve the problem of attackers planting malicious SQL in $xname.

A better method still is to use MySQL’s placeholder mechanism. (See perldoc DBI.) We construct the query string with a hole marked by ? for the name variable, then supply it when the query is executed. This has the advantage that no quotes are needed in the query string at all, and the contents of $xname completely bypass the SQL parsing, which means that extra SQL cannot be added via that route at all. (However, note that it is good practice always to vet all user input before doing anything with it.) Furthermore, database access runs much faster since preparing the query only has to happen once (and query optimization is often also performed at this point, which can be an expensive operation). This is particularly important if you have a busy web site doing lookups on different things:

$query=qq(select * from people where xname=?);
$sth=$dbm->prepare($query) || die $dbm->errstr;

When you want the database lookup to happen, you write:

$sth->execute($query,$xname);

This has an excellent impact on speed if you are doing the database accesses in a loop.

In the script script: first we print the HTTP header — more about this will follow. Then we print the HTML header, together with the caption of the table. Each line of the table is printed separately as we search the database, using the DBI function fetchrow_hashref to load the variable $ref. Finally, we close the table (easily forgotten, but things can go horribly wrong if you don’t) and close the HTML.

#! /usr/local/bin/perl -wT
use strict;
use DBI();

my ($ref,$mesg,$dbm,$query,$xname,$sname,$sth,$rows);

$xname="Anne Jane";
$sname="Beauregard";

# open a database
$dbm=DBI->connect("DBI:mysql:database=people;host=localhost",'webserv')
    or die "didn't connect to DB people";

#insert some more data just to show we can
# demonstrate qq()
$query=qq(insert into people (xname,sname) values ('$xname','$sname'));
$dbm->do($query);

# get it back
$xname="Anne";
#demonstrate DBI placeholder
$query=qq(select xname, sname from people where xname like ?);
$sth=$dbm->prepare($query) or die "failed to prepare $query: $!";
# $! is the Perl variable for the current system error message

#Now fill in the placeholder
$sth->execute($query,$xname);
$rows=$sth->rows;
print qq(There are $rows people with names matching '$xname'\n);
while ($ref=$sth->fetchrow_hashref)
    {
    print qq($ref->{'xname'} $ref->{'sname'}\n);
    }
$sth->finish;

# close the database connection
$dbm->disconnect;

This script produces a reasonable looking page. Once you get it working, development is much easier. You can edit it, save it, refresh from the browser, and see the new version straight away.

Use ./go 1 and browse to http://www.butterthlies.com to see a table of girls called “Anne.” This works because in the Config file we declared this script as the DirectoryIndex.

In this way we don’t need to provide any fixed HTML at all.