A More Complex Example - Perl

Remember the complex query we did previously in this chapter when we tried to find out the instruments played by Lenny Kravitz? It was a three-step process: first, we found his player_id from the musicians table. Then we used that player_idto read theinst_ids from thewhat_they_playtable. For each of thoseinst_ids, we read theinstrumentname from theinstrumentstable. Here is how we might do this in Perl andDBI:

#!/usr/bin/perl -w
# showinstruments1.pl

use strict;
use DBI;

my($who, $player_id, $inst_id);

print "Enter name of musician and I will show you his/her instruments: ";
chomp($who = <STDIN>);

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

# first, grab the musicians player_id
my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = '$who'")
             or die "prepare failed: " . $dbh->errstr();

$sth->execute() or die "execute failed: " . $sth->errstr();

($player_id) = $sth->fetchrow();

die "player_id not found" unless defined $player_id;

# given the player_id, grab their inst_ids from what_they_play
$sth = $dbh->prepare("SELECT inst_id FROM what_they_play
                             WHERE player_id = $player_id")
             or die "prepare failed: " . $dbh->errstr();

$sth->execute() or die "execute failed: " . $sth->errstr();

# foreach inst_id, grab the instrument name from the
# instruments table and print it
while (($inst_id) = $sth->fetchrow()) {
    my $sth = $dbh->prepare("SELECT instrument FROM instruments
                                     WHERE inst_id = $inst_id")
             or die "prepare failed: " . $dbh->errstr();

    $sth->execute() or die "execute failed: " . $sth->errstr();

    my($instrument) = $sth->fetchrow();
    print "    $instrument\n";

    $sth->finish();
}

$sth->finish();

$dbh->disconnect();

Let’s look at each step of this process. After connecting to the database as we have been doing in the previous examples and asking for the user to enter the name of a musician, we construct and execute a query to obtain that musician’splayer_id.

# first, grab the musicians player_id
my $sth = $dbh->prepare("SELECT player_id FROM musicians WHERE name = '$who'")
             or die "prepare failed: " . $dbh->errstr();

$sth->execute() or die "execute failed: " . $sth->errstr();

($player_id) = $sth->fetchrow();

die "player_id not found" unless defined $player_id;

Notice how the SQL query is constructed using the variable$who. The outer double quotes of the query string are needed to take the value of$who. The SQL query needs to quote the name since it is a string, so wrapped around$whowithin the query are single quotes.

The query is then executed. This should return only one row (it could return zero rows if the musician is not found in the table) so we only have to callfetchrow()once—we take the return value of that method, a list of one value, and assign it to the assignable list($player_id). The result is that$player_idwill be theplayer_idof the musician that the user entered at standard input orundefif the musician was not found. The programdie()s if$player_idis not defined.

Then we use$player_idand construct a query asking for theinst_ids:

# given the player_id, grab their inst_ids from what_they_play
$sth = $dbh->prepare("SELECT inst_id FROM what_they_play
                             WHERE player_id = $player_id")
             or die "prepare failed: " . $dbh->errstr();

$sth->execute() or die "execute failed: " . $sth->errstr();

Notice how the query string contains$player_id, the value we just read out of the database. Since theplayer_idis an integer, it does not need to be quoted within the SQL query. When executed, this should return back all theinst_ids for thatplayer_id. We then loop through the result, each row at a time:

# foreach inst_id, grab the instrument name from the
# instruments table and print it
while (($inst_id) = $sth->fetchrow()) {
    my $sth = $dbh->prepare("SELECT instrument FROM instruments
                                    WHERE inst_id = $inst_id")
       or die "prepare failed: " . $dbh->errstr();

    $sth->execute() or die "execute failed: " . $sth->errstr();

    my($instrument) = $sth->fetchrow();
    print "    $instrument\n";

    $sth->finish();
}

As we loop through each row of output from the previous query, weprepare()another query to read the name of the instrument from theinstrumentstable. Notice that within thewhileloop,$sthwill receive the return value fromprepare()and that we have declared this variable with amy(). Themy()here is very important—it creates a new copy of$sthwithin thewhileloop so we will not clobber the previous value of$sthoutside thewhileloop (the result of the query of thewhat_they_playtable). If we had not declared$sthwith amy(), that previous query would have been overwritten and we would have only processed one row of output from the query ofwhat_they_play. An alternative to declaring$sthwithmy()would have been selecting a different variable name, which would have been fine, but sticking with$sthas the state handler variable is the convention (besides, that new variable would have to have beenmy()ed as well).

Executing the program looks like this:

$ perl showinstruments1.pl
Enter name of musician and I will show you his/her instruments: Roger Waters
    guitar
    vocals
$

Excellent! But, not so fast. There is a problem with this program, demonstrated with this example:

$ perl showinstruments1.pl
Enter name of musician and I will show you his/her instruments: Chris O'Rourke DBD::mysql::st execute failed: You have an error in your SQL syntax near
'Rourke'' at line 1 at showinstruments1.pl line 20, <STDIN> line 1.
execute failed: You have an error in your SQL syntax near 'Rourke'' at line 1 at showinstruments1.pl line 20, <STDIN> line 1. $

Can you see what the problem is? The query that uses$who, the name entered, looks like this:

$dbh->prepare("select player_id from musicians where name = '$who'")

Since$whois single-quoted in the SQL query string, the single quote in the name “Chris O’Rourke” makes SQL think that the string it is comparing tonameis “Chris O”. SQL then sees “Rourke”, which is totally out of place—this is a syntax error.

You may think the answer is to escape the single quote and turn$whointo “Chris O\’Rourke”. This is possible, and would work, but there is a better way.