JavaScript Editor Js editor     Website development 

Main Page

Previous Page
Next Page

Security Considerations

To this point in the chapter I have not delved too much into the security implications when it comes to using Perl to interact with MySQL. Security considerations are important but also diverse, so I'll highlight a few of the most important techniques here.

First up, the most important step to take is to validate the user input in some way, especially if that input will be used in a query. You can check for specific values when appropriate, use regular expressions to compare input to patterns, and so on. For numeric values, you can also cast them for protection.

After validation, with strings in particular it's a good idea to prevent errors that might occur if apostrophes or other problematic characters appear in user-submitted data. The quote() method will accomplish this nicely:

my $string = "Apostrophes aren't good.";
my $safe_string = $dbh->quote($string);

I'll implement some security techniques by rewriting the script.

To improve Perl script security:

Open (Script 8.5) in your text editor.

Replace the way the client's ID is read with this code (Script 8.7):

my $client_id = 0;
while ($client_id <= 0) {
  print "Enter the client ID: ";
  $client_id = <STDIN>;

Script 8.7. I've added some validation routines and applied quote() to the description string to improve the security of this script.

1     #!/usr/bin/perl -w
3     # Script 8.7 -
4     # This script adds an invoice to the
      invoices table.
5     # It relies upon user input.
7     # Use what needs to be used.
8     use strict;
9     use DBI;
11    # Print a message.
12    print "Use this program to add an
13    print "Client Name (Client ID):\n";
15    # Connect to the database.
16    my $dbh = DBI->connect("DBI:mysql:
      accounting:localhost", 'username',
      'password', {RaiseError => 1});
18    # Show the current clients with their
19    my $sql = "SELECT client_name, client_id
      FROM clients ORDER BY client_name ASC";
21    # Query the database.
22    my $sth = $dbh->prepare($sql);
24    if (defined($sth)) {
26        $sth->execute();
27        my @row;
28        while (@row = $sth->fetchrow_array())
29           print "$row[0] ($row[1])\n";
30        }
31        $sth->finish();
33    } else { # Print the error.
34        print "Error! MySQL said: " . $sth->errstr() . "\n";
35    }
37    # Get the information from the user.
38    my $client_id = 0;
39    while ($client_id <= 0) {
40     print "Enter the client ID: ";
41     $client_id = <STDIN>;
42    }
44    my $amount = 0;
45    while ($amount <= 0) {
46       print "Enter the invoice amount: ";
47       $amount = <STDIN>;
48    }
50    print "Enter the invoice description: ";
51    my $desc = <STDIN>;
53    # Watch for quotes.
54    $desc = $dbh->quote($desc);
56    # Query the database.
57    $sql = "INSERT INTO invoices (client_id,
      invoice_amount, invoice_description)
      VALUES ($client_id, $amount, $desc)";
58    my $affected = $dbh->do($sql);
60    # Report on the success of the query
61    if ($affected == 1) {
62        print "Invoice #" . $dbh->
          {'mysql_insertid'} . " has been
63    } else {
64        print "The invoice could not be
          created! \n";
65        print "Error! MySQL said: " .
          $sth->errstr() . "\n";
66    }
68    # Disconnect.
69    $dbh->disconnect;

This is a simple technique. First it initializes $client_id with an invalid value of 0. Then it checks to see if $client_id has an invalid value. If so, the user will be prompted. The loop will check the user input to see if it's invalid, in which case the user will be reprompted (Figure 8.30). This continues until a client ID with a value greater than 0 is entered.

Figure 8.30. The script now waits until a client ID greater than 0 is entered.

Replace the way the invoice amount is read with this code:

my $amount = 0;
while ($amount <= 0) {
  print "Enter the invoice amount: ";
  $amount = <STDIN>;

This is just a repeat of the system used to validate the client ID.

After reading in the description, add this line:

$desc = $dbh->quote($desc);

The quote() method does two things: first it escapes apostrophes within the string itself. It also wraps the string within its own single quotes, meaning that single quotes are not necessary for this input in the query.

Change the query to read:

$sql = "INSERT INTO invoices (client_id, invoice_amount, invoice_description) VALUES 
($client_id, $amount, $desc)";

The only difference is that $desc is no longer within quotes because wrapping single quotation marks have been added by $dbh->quote().

Save the script as, change the permissions (if necessary), and run the script (Figures 8.31 and 8.32).

Figure 8.31. Invalid input and apostrophes are no longer a problem for this script.

Figure 8.32. If the user enters valid information, they are only prompted once for each piece.

Make sure that you use some apostrophes and erroneous input to really test the script.


  • With the -w flag, this Perl script will complain if a user enters a string or character for the client ID or invoice amount. More important, though, the script will not attempt to run a query using those values.

  • If you wanted to be really sophisticated, you could confirm that the client ID entered by the user is actually a valid client ID (by querying the database).

Previous Page
Next Page

JavaScript Editor Js editor     Website development