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 ...
Get Apache: The Definitive Guide, 3rd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.