Apache: The Definitive Guide, 3rd Edition

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 ...