Executing Simple Queries

Now that you know how to handle queries that return results, let's go back a step and perform the simpler task of executing simple queries. In this section, I'll show how to execute simple queries using the do() method.

$sth = $dbh->do("QUERY STATEMENT");

The do() function is run using the data-base handler created when connecting to the database (e.g., $dbh). It will normally return the number of rows affected by a query, if applicable.

To demonstrate this function, I'll write a Perl script that will add a new login account to the accounting database's logins table. This table was added to this database in Chapter 6, "MySQL Functions," for the purpose of assigning login accounts to clients. It makes use of both the SHA1() and AES_ENCRYPT() functions. See Chapter 6 or the MySQL manual for more on these and their syntax.

To execute simple queries:

Create a new Perl script in your text editor with the standard beginning lines of code (Script 8.4).

#!/usr/bin/perl -w
use strict;
use DBI;

Print some introductory text.

print "Use this program to add a new login account.\n\n";
print "Client Name (Client ID):\n";

Establish a connection to the database.

[View full width]my $dbh = DBI->connect("DBI:mysql: accounting:localhost", 'username', 'password',
 {RaiseError => 1});

In this script the database, accounting, is hard-coded, not taken from user input.

Script 8.4. This script `add_user.pl` takes inputted information to add records to the database.

1 #!/usr/bin/perl -w 2 3 # Script 8.4 - add_user.pl 4 # This script adds a user to the logins table. 5 # It relies upon user input. 6 7 # Use what needs to be used. 8 use strict; 9 use DBI; 10 11 # Print a message. 12 print "Use this program to add a new login account.\n\n"; 13 print "Client Name (Client ID):\n"; 14 15 # Connect to the database. 16 my $dbh = DBI->connect("DBI:mysql: accounting:localhost", 'username', 'password', {RaiseError => 1}); 17 18 # Show the current clients with their IDs. 19 my $sql = "SELECT client_name, client_id FROM clients ORDER BY client_name ASC"; 20 21 # Query the database. 22 my $sth = $dbh->prepare($sql); 23 24 if (defined($sth)) { 25 $sth->execute(); 26 my @row; 27 while (@row = $sth->fetchrow_array()) { 28 print "$row[0] ($row[1])\n"; 29 } 30 } 31 $sth->finish(); 32 33 # Get the information from the user. 34 print "Enter the client ID: "; 35 my $client_id = <STDIN>; 36 print "Enter the login username: "; 37 my $name = <STDIN>; 38 print "Enter the login password: "; 39 my $pass1 = <STDIN>; 40 print "Confirm the login password: "; 41 my $pass2 = <STDIN>; 42 43 # Make sure the passwords match. 44 while ($pass1 ne $pass2) { 45 print "The passwords you entered did not match! Try again!\n"; 46 47 # Re-request the password. 48 print "Enter the login password: "; 49 $pass1 = <STDIN>; 50 print "Confirm the login password: "; 51 $pass2 = <STDIN>; 52 53 } # End of WHILE loop. 54 55 # Query the database. 56 $sql = "INSERT INTO logins (client_id, login_name, login_pass) VALUES ($client_id, AES_ENCRYPT('$name', 'w1cKet'), SHA1('$pass1'))"; 57 my $affected= $dbh->do($sql); 58 59 # Report on the success of the query attempt. 60 if ($affected== 1) { 61 print "The login account has been added! \n"; 62 } else { 63 print "The login account could not be added! \n"; 64 } 65 66 # Disconnect. 67 $dbh->disconnect;

List the current clients with their client IDs.

my $sql = "SELECT client_name, client_id FROM clients ORDER BY client_name ASC";
my $sth = $dbh->prepare($sql);
if (defined($sth)) {
   $sth->execute();
   my @row;
   while (@row = $sth->'fetchrow_array()) {
      print "$row[0] ($row[1])\n";
   }
}
$sth->finish();

Because one of the pieces of information needed to create a login account is the client ID, that information needs to be presented to the user. To do so, a simple query is run and the results are printed (Figure 8.19). Most of this syntax matches what was taught in the preceding section of this chapter.

Figure 8.19. The script begins by listing the clients along with their respective ID numbers.

Prompt for the client's ID.

print "Enter the client ID: ";
my $client_id = <STDIN>;

This script will prompt for three things (Figure 8.20): the client's ID, the username, and the password. These correspond to the fields in the clients table. The value keyed in at this first prompt will be assigned to the $client_id variable.

Figure 8.20. When you run the script, you will be prompted three times for information (plus a confirmation of the password), which will then be added to the database to make a new user.

Prompt for the username and password.

print "Enter the login username: ";
my $name = <STDIN>;
print "Enter the login password: ";
my $pass1 = <STDIN>;
print "Confirm the login password: ";
my $pass2 = <STDIN>;

To make sure there are no errors in the inputted password, it will be requested twice and then compared.

Confirm that the passwords match.

while ($pass1 ne $pass2) {
      print "The passwords you entered did not match! Try again!\n";
      print "Enter the login password: ";
      $pass1 = <STDIN>;
      print "Confirm the login password: ";
      $pass2 = <STDIN>;
}

This loop checks to see if the first entered password matches the second, confirmed one. If it does not, it will print an error message and give another chance to re-enter both (Figure 8.21). This process will be repeated until the passwords match.

Figure 8.21. If the two passwords entered do not match, you'll be prompted to re-enter these values.

Create the INSERT query and send it to the database.

[View full width]$sql = "INSERT INTO logins (client_id, login_name, login_pass) VALUES ($client_id,
 AES_ENCRYPT|('$name', 'w1cKet'), SHA1('$pass1'))";
my $affected= $dbh->do($sql);

This query is nearly identical to that from Chapter 6 except that it uses the values submitted by a user rather than hard-coded ones. The final query will be something like

[View full width]INSERT INTO logins (client_id, login_name, login_pass) VALUES (7, AES_ENCRYPT('sophie',
 'w1cKet'), SHA1('isabella'))

Once the query has been created, it is run through the do() function.

Print a message indicating the success of the query.

if ($affected== 1) {
    print "The login account has been added! \n";
} else {
    print "The login account could not be added! \n";
}

The do() function in Step 8 will return the number of affected rows for queries such as ALTER, UPDATE, and DELETE. The number of affected rows was assigned to the $sth variable, so if it is equal to 1, the query worked.

10.

Close the database connection.

$dbh->disconnect;

11.

Save the file as add_user.pl, change the permissions (if necessary), and run the script (Figure 8.22).

Figure 8.22. A login account has been created by this Perl script.

Tips

To increase the security of this script, you could use regular expressions to check the values entered by the script's user.
At the end of the chapter you'll learn how to improve the security and error reporting when executing queries. As it stands, one problem is that apostrophes in the submitted values will break the query (Figure 8.23).

Figure 8.23. This version of the Perl script does not safeguard against apostrophes in the user-submitted data. This could lead to problems and will be remedied in time.

[View full size image]