Beginning Perl Web Development - From Novice To Professional (2006)

60C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

my @mysqlhosts;

while (my $hostname = $sth->fetchrow_array()) { push (@mysqlhosts,$hostname);

}

while (<@mysqlhosts>) { if ($_ =~ /%/) {

print "Wildcard host found: $_\n";

}

}

$dbh->disconnect();

Most of the early portion of this example is the same as Listing 3-3, but notice that the query itself is different, this time retrieving only the host from the MySQL user table. Instead of printing the output directly while fetching, it is pushed into an array, @mysqlhosts, for later use. The later use comes next, when that array is expanded and each entry is examined for the offending percent sign.3

My output looks like this:

Wildcard host found: %

Wildcard host found: 192.168.1.%

Your output may vary if you don’t have any wildcard hosts on your server.

Another use might be to take the results and build another query based on the data retrieved. The possibilities are without limit and entirely dependent on the needs of your program.

Dumping the Results

In some cases, you may not want to build the loop for fetching the results from a query. In such instances, you can use the DBI’s dump_results() method on the statement handle. Using dump_results() enables you to quickly see if a query was successful. It returns the rows themselves, followed by the number of rows returned. Listing 3-5 shows the program in Listing 3-4 altered to use dump_results() instead of the fetchrow_array() method.

Listing 3-5. Dumping a Query’s Results

#!/usr/bin/perl

use DBI; use strict;

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:mysql:192.168.1.10";

3.It would be better practice to place the if{} test within the database fetch itself, in order to save memory. I guess you’ll have to wait for version 2 of the software!

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

61

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT user,host FROM mysql.user");

$sth->execute() or die "Cannot execute sth: $DBI::errstr";

print $sth->dump_results();

$dbh->disconnect();

The output is as follows:

'postfix', 'localhost' 'root', 'localhost' '', 'netserver' 'testuser', 'localhost' 'user', 'localhost'

3 rows

Finish()-ing the Statement

It is a good idea to be aware of the result set that you’re working with from a given statement handle. If you don’t retrieve all of the rows from a query, the result set will still hold data. This can mean extra memory usage for the database server and can also result in warnings when you attempt to execute the disconnect() method. Therefore, if you won’t be retrieving all of your results, be sure to use the finish() method on the statement handle to flush the results.

In the examples shown, all of the results were retrieved by iterating through them using fetchrow_array() or dump_results(). However, if this hadn’t been the case, I would have used the finish() method, like this:

$sth->finish();

Using the Quote Method for Dynamic Statements

In the examples shown so far, there’s no reason why you couldn’t substitute a valid variable within the SQL statement. In other words, instead of merely using this:

SELECT host FROM mysql.user;

you could, assuming a variable of $username, use this:

SELECT host FROM mysql.user WHERE user = '$username';

In this example, the variable $username is interpolated, and whatever is in $username will be sent with the query. This interpolation of $username is as opposed to parameterizing or binding for dynamic statements, as you’ll see in the “Binding Parameters” section, coming up soon.

A popular vector for attackers exploiting database connectivity (particularly in web applications) is to include characters or other anomalies in an attempt to get the program, and therefore the database server, to execute additional commands. These types of attacks are made possible

62C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

when input is allowed unchecked or the program executes a SQL statement without first properly sanitizing it for the database’s consumption. The DBI contains an aptly titled quote() method to properly escape or sanitize SQL statements for you.

You should use the quote() method for any statement that will use parameters or other variables or input that could possibly be dirty. The quote() method belongs to a database handle, since nearly every database server has its own set of rules for quoting. Consider this code, assuming a database handle of $dbh has already been created:

my $dirtystring = "This is some %really% \"weird\" \\* input";

my $cleanstring = $dbh->quote( $dirtystring);

my $sth = $dbh->prepare("SELECT * from tablename where something = $cleanstring");

So, the example shown earlier in this section might look like this:

my $usernamein = "suehring";

my $sth = $dbh->prepare("SELECT	host FROM mysql.user WHERE user = " .
$dbh->quote($usernamein . "	");

While it may seem like a hassle to need to clean up input and other parameters before using them in a statement, the trade-off is well worth the extra typing. I could go into a story akin to the age-old “I used to have to walk 18 miles a day to school” of how life was prior to the quote() method, but rest assured that using quote() is much easier and simpler than needing to do the same function manually against all input.

Executing Other SQL Statements

Not all statements must go through the prepare() and execute() methods, or even create a statement handle prior to being run against the database server. The database handle’s do() method executes a statement immediately against the database. This is useful for performing actions like DELETE, INSERT, and UPDATE, which don’t actually retrieve any results from the database, but merely perform an action against the database.

The do() method is used in the context of a database handle. Assuming a database handle of $dbh with a table called table, you might use do() like this:

my $rows = $dbh->do("DELETE from table where id = '4'");

The $rows variable would contain the number of rows affected by this statement. If the statement executes successfully, regardless of the number of rows deleted, the do() method will return true. In other words, the rows affected could still be zero, even though the statement executed successfully.

Binding Parameters

As you saw earlier, you can use the quote() method to create dynamic SQL statements. However, another method exists for creating such statements, namely parameters. Parameters are also known by a few other names or concepts, such as binding or placeholders. When you hear one of these terms, it’s referring to the concepts described in this section.

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

63

You may be asking why you would use binding instead or interpolated value queries. Binding speeds up the execution of the same SQL statement. Therefore, if you’re going to be executing the same statement but with different values, you can gain speed by using parameters. Whether or not you should use parameters also depends on the database that you’re using; not all databases implement binding.

Recall a previous example that used variable interpolation to build the statement handle for a dynamic query:

my $sth = $dbh->prepare("SELECT	host FROM mysql.user WHERE user = " .
$dbh->quote($usernamein . "	");

Using parameters, that statement handle would look like this:

my $sth = $dbh->prepare("SELECT host FROM mysql.user WHERE user = ?"); $sth->bind_param(1, $username);

The bind_param() method on the statement handle accepts an index value, beginning with 1, to specify the order in which the parameters should be bound, and it then accepts the value itself, this time in the variable $username.

Notice that the quote() method is not used here. Since bind_param() hands off the parameters to the database separate from the SQL statement, the quote() method isn’t necessary.

You can (and many times will) bind multiple values. Consider this example:

my $sth = $dbh->prepare("SELECT host FROM mysql.user WHERE user = ? AND host = ?"); $sth->bind_param(1, $username);

$sth->bind_param(2, $hostname);

It’s important to understand that not all portions of a statement can be parameterized. For example, most databases won’t allow you to parameterize the entire WHERE clause of a SQL statement. However, binding the values used within that WHERE clause is valid on many databases.

Rather than using bind_param() to bind parameters to their values, you can also send the values when you call execute(). You must specify the values in the same order in which they appear in the SQL statement. So, based on the SQL statement in this example:

my $sth = $dbh->prepare("SELECT host FROM mysql.user WHERE user = ? AND host = ?");

username should be specified first and host second. Therefore, this would be correct:

$sth->execute($username,$hostname);

and this would be incorrect:

$sth->execute($hostname,$username);

Inserting Data into a Database

Inserting rows into a database is largely the same syntactically as retrieving rows from it, with the obvious changes to the SQL syntax itself. For example, you could use the do() method or the prepare() method for inserting into a database. You create the database handle as you would for any other database operation, create the statement handle the same way, and call execute() if you’ve used prepare().

64 C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

Assume that this INSERT statement would execute against a database table:

INSERT INTO urls VALUES ('','http://www.braingia.org/','suehring',unix_timestamp(),'query words');

The equivalent DBI code is shown in Listing 3-6.

Listing 3-6. Inserting into a Database

#!/usr/bin/perl

use DBI; use strict;

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:goo:192.168.1.10";

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $sth = $dbh->prepare("INSERT INTO urls VALUES ('','http://www.braingia.org/','suehring',unix_timestamp(),'query words')");

$sth->execute() or die "Cannot execute sth: $DBI::errstr";

$dbh->disconnect();

As you can see, much of this code is the same as previous examples, with the exception being the INSERT syntax for the actual statement. You can also bind parameters with INSERT statements and use the do() method.

One common task when inserting data into a table is to retrieve the value for an autoincremented index ID for the newly inserted row. The MySQL DBD includes a function for retrieving this value. Note that this is dependent on the database itself and also on the data layout. If there is no auto-incremented field in the database table, this value might be meaningless. Additionally, some databases don’t implement this. Check the Perl documentation for your DBD.

In the case of the example in Listing 3-6, the first field was an auto-incremented field. Therefore, it’s possible to add some code to the example to retrieve the value for that ID field. The new code is shown in Listing 3-7.

Listing 3-7. Retrieving an Index ID

#!/usr/bin/perl

use DBI; use strict;

my $username = "dbuser";

my $password = "dbpassword";

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

65

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $sth = $dbh->prepare("INSERT INTO urls VALUES ('','http://www.braingia.org/','suehring',unix_timestamp(),'query words')");

$sth->execute() or die "Cannot execute sth: $DBI::errstr";

my $insertid = $dbh->{'mysql_insertid'}; print "$insertid\n";

$dbh->disconnect();

Notice the two new lines in Listing 3-7:

my $insertid = $dbh->{'mysql_insertid'}; print "$insertid\n";

In this case, the output from each run should increment, assuming that no one else is inserting data into the table at the same time! For example, I called this program insert2.pl. Running the program from the command line outputs the $insertid variable:

netserver% ./insert2.pl 41

Running the program again reveals that the value is indeed incrementing:

netserver% ./insert2.pl 42

Interacting with the Web

Thus far, this chapter has looked at only the basics of the DBI, and with good reason. Understanding how the DBI works enables you to write powerful web applications. What you do with the data contained in the database is entirely up to you.

Outputting to HTML

You could simply output the statements to plain HTML by combining the DBI functions with what you’ve learned about CGI programming.

Reworking the example from Listing 3-4 into a plain web page yields the result shown in Listing 3-8.

Listing 3-8. Creating a Web Page Integrated with SQL Data

#!/usr/bin/perl

use DBI; use strict;

use CGI qw/:standard/;

66C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:mysql:192.168.1.10";

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $hosttolookup = "%";

my $sth = $dbh->prepare("SELECT host FROM mysql.user WHERE host LIKE ?");

$sth->execute($hosttolookup)

or die "Cannot execute sth: $DBI::errstr";

my @mysqlhosts;

while (my $hostname = $sth->fetchrow_array()) { if ($hostname =~ /%/) {

push (@mysqlhosts,$hostname);

}

}

print header,

start_html('MySQL Hosts Using Wildcards');

my $count = @mysqlhosts; if ($count == 0) {

print p("No Hosts Using Wildcards");

}

else {

while (<@mysqlhosts>) {

print p("Host Wildcard: $_");

}

}

print end_html;

$dbh->disconnect();

Essentially, the program is largely the same as those previously shown. The main difference is that, instead of printing to STDOUT, this program outputs HTML. The core of the “HTMLized” portion of the program is here:

print header,

start_html('MySQL Hosts Using Wildcards');

my $count = @mysqlhosts; if ($count == 0) {

print p("No Hosts Using Wildcards");

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

67

else {

while (<@mysqlhosts>) {

print p("Host Wildcard: $_");

}

}

print end_html;

None of this code should be new to you after going through the previous chapters on CGI programming. If there are no hosts using wildcards, the program will output a message indicating that. If there are hosts using wildcards, that list is sent to the HTML instead, as shown in Figure 3-1.

Figure 3-1. The output from the script indicating that hosts were found with wildcards

Building HTML Tables

The output from Listing 3-8 is nice for a simple program, but when you’re building a more complex CGI program, you’ll likely find that you want to use HTML tables for the output. You can build a table manually or by using the CGI’s table() method. The example in Listing 3-9 uses the table() method to print each user and host on a MySQL server in tabular format.

Listing 3-9. Creating an HTML Table

#!/usr/bin/perl

use DBI; use strict;

use CGI qw/:standard/;

68C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

my $username = "dbuser";

my $password = "dbpassword";

my $dsn = "dbi:mysql:mysql:192.168.1.10";

my $dbh = DBI->connect($dsn,$username,$password)

or die "Cannot connect to database: $DBI::errstr";

my $sth = $dbh->prepare("SELECT host,user FROM mysql.user");

$sth->execute()

or die "Cannot execute sth: $DBI::errstr";

print header,

start_html('MySQL Hosts and Users'), table({-border=>1}),

Tr({-align=>'CENTER',-valign=>'TOP'},

[

th(['User','Host'])

]);

while (my ($hostname,$username) = $sth->fetchrow_array()) { if ($hostname eq "") {

$hostname = "<b>undef</b>";

}

print Tr({-align=>'CENTER',-valign=>'TOP'}, [td(["$username","$hostname"])

]);

}

print end_html;

$dbh->disconnect();

Running this program shows that the data has been placed into an HTML table, as shown in Figure 3-2.

In Listing 3-9, first the table is created using CGI.pm’s table() method:

print header,

start_html('MySQL Hosts and Users'), table({-border=>1}),

Tr({-align=>'CENTER',-valign=>'TOP'},

[

th(['User','Host'])

]);

C H A P T E R 3 ■ D ATA B A S E S A N D P E R L

69

Figure 3-2. Data from a SQL query has been placed into an HTML table.

Next, the results are iterated through and the table dynamically created on the fly:

while (my ($hostname,$username) = $sth->fetchrow_array()) { if ($hostname eq "") {

$hostname = "<b>undef</b>";

}

print Tr({-align=>'CENTER',-valign=>'TOP'}, [td(["$username","$hostname"])

]);

}

Each row of the table must be created within the while() loop in order for it to make sense in an HTML table format.

You could also place values retrieved into a hash within the while() loop, or you could use other methods, including fetchrow_hashref() and others. See the DBI documentation (type perldoc DBI from a terminal window) for more information about other methods.