Getting BLOB Data into the Database

First, we need to decide exactly what we want to store. A logo for the page headers can go into the Auction Table, and an item_image can go into the Items Table. Also, we need to store a MIME type for each image so that we know what

type of image we are storing. The image type isn't important when storing the data, but when we want to display it, we need to tell the client what type of data we are sending.

Table structure

The new table structure for the Auction Table looks like this:

mysql> describe auction; +---------------+-------------+------+-----+---------+----------------+ | Field         | Type        | Null | Key | Default | Extra          | +---------------+-------------+------+-----+---------+----------------+ | auction_id    | int(11)     |      | PRI | NULL    | auto_increment | | name          | varchar(50) | YES  |     | NULL    |                | | start_bidding | datetime    | YES  |     | NULL    |                | | stop_bidding  | datetime    | YES  |     | NULL    |                | | logo          | mediumblob  | YES  |     | NULL    |                | | mime          | varchar(50) | YES  |     | NULL    |                | +---------------+-------------+------+-----+---------+----------------+

Notice that we've added the logo and mime fields. The data-type of mediumblob for the logo depends on your database. Various databases store binary data in different ways. In MySQL, using a data-type of mediumblob should be sufficient for just about any image file.

The new Items Table looks like this:

mysql> describe item; +-------------+--------------+------+-----+---------+----------------+ | Field       | Type         | Null | Key | Default | Extra          | +-------------+--------------+------+-----+---------+----------------+ | item_id     | int(11)      |      | PRI | NULL    | auto_increment | | name        | varchar(50)  | YES  |     | NULL    |                | | description | varchar(255) | YES  |     | NULL    |                | | donor_id    | int(11)      | YES  |     | NULL    |                | | value       | double       | YES  |     | NULL    |                | | min_bid     | double       | YES  |     | NULL    |                | | min_incr    | double       | YES  |     | NULL    |                | | auction_id  | int(11)      | YES  |     | NULL    |                | | item_image  | mediumblob   | YES  |     | NULL    |                | | mime        | varchar(50)  | YES  |     | NULL    |                | +-------------+--------------+------+-----+---------+----------------+

Again, this table is identical to the tables used in Chapter 7, except for the item_image field and the MIME field.

You can add these fields to the existing database tables, or you can make a new database; then, when you make the tables, you can add these fields. Here, we've simply made a backup of the MySQL auction database by using the mysqldump function and edited the output to add these fields. From there, we have used the file that mysqldump created and have made a new database with it.

HTML forms

The HTML forms we'll use for getting the data to the database are extremely simple.

You should be familiar with working with HTML forms, but there is one important change you must make to your form tag. File uploading uses multipart encoding, which is not the default, so you must set it explicitly.

Also, for file uploading you must use the POST method rather than GET.

Here is what your form tag might look like:

<form enctype="multipart/form-data" method="post"   action="/cgi-bin/auction_up.cgi">

Notice that we set the encoding type (enctype) to multipart/form-data.

The other thing that may be new to you is the input type of file. To upload a file, use this input type; simply typing a file's location in a text field does not work.

Here is what your file might look like:

<input type="file" name="logo" size="40" />

Note 

For security reasons, an input field of type file cannot be automatically populated with a value attribute. This prevents people from writing malicious programs that trick you into sending your files from your system, among other things.

The HTML form is very plain; it looks like the form in Figure 8-2. This just provides auction administrators with a simple way of creating a new auction. You can find the HTML for this page on this book's companion Web site.

Figure 8-2: Add new auction page

The code for parsing this data is what we are really interested in here. Let's take a look at how we handle the data sent from this HTML form.

01: #!/usr/bin/perl -wT 02: # auction_up.cgi 03: use strict; 04: use DBI; 05: use CGI qw(:standard); 06: print header;

Line 1 tells the system where to find Perl and turns on warnings and taint checking.

Line 2 is simply a comment about this program.

Line 3 loads the strict module. This forces the programmer to adhere to stringent programming rules and helps eliminate common programming mistakes.

Line 4 loads the DBI module so that we can have database access.

Line 5 loads the CGI module, which is a great help when dealing with HTML forms and especially with file uploads.

Line 6 prints the standard HTTP header. The header function is part of the CGI module's standard functions.

07: my ($image_data, $data, %fd); 08: my $image  = param(‘logo'); 09: die unless $image; 10: my $mime   = CGI::uploadInfo($image)->{‘Content-Type'}; 11: my @fields = qw(auction_name start_bid stop_bid);

Line 7 declares a couple of variables we'll be using shortly.

Line 8 uses CGI.pm's param function to get the value passed in the logo field on the HTML form. This is the field in which the file was passed. CGI.pm handles files as well, so working with file uploads is easy!

Line 9 calls die if $image contains no data.

Line 10 uses uploadInfo from the CGI module to get the MIME type of the file that was just uploaded.

Line 11 declares a new array named @fields that contains the names of the HTML form fields we will be working with.

12: for(@fields){ $fd{$_} = param($_); }

Line 12 takes the values in the @fields array and stores them into a hash named %fd (for Form Data). Reading the data this way gives you a nice, easy hash to deal with, containing only the HTML form data you want.

13: my $dbh = DBI->connect("dbi:mysql:auction_img",                             "bookuser", "testpass") 14:     or die("Error! $!\nAborting");

Line 13 uses the connect method from the DBI to initiate a connection to the database. We use the mysql driver to connect to the auction_img database; our username is bookuser, with a password of testpass.

Line 14 prints an error message if there is a problem connecting to the database; then it terminates the program.

15: while( read($image, $data, 2048) ) { 16:     $image_data .= $data; 17: }

Line 15 begins a while loop that reads the uploaded file 2048 bytes at a time and stores the data in the $image_data variable via the appendination operator.

Note 

The .= operator is named appendination. The .= does not have a formal name. This is a fit because .= both appends and concatenates in the same operation.

Each time through the loop, the read function is executed. When it executes, the first argument is the file handle; the second argument is the temporary storage variable, and the last argument is the number of bytes to read in at a time. read returns true each time it finds data; once it runs out, it returns false so the while loop can exit. If there are less than 2048 bytes left to read, Perl will read in the remaining data and do the right thing. You won't cause any out-of-bounds errors or anything like that. The right thing is that Perl just reads what is left and returns that data.

Line 16 uses the appendination operator to concatenate the data in the $data variable onto the end of the $image_data variable.

Line 17 ends the while loop.

18: $image_data = $dbh->quote($image_data); 19: $mime       = $dbh->quote($mime); 

Lines 18-19 use the quote function from the DBI to quote the strings of data in $image_data and $mime. This should really be done on all variables to make sure no weird data is trying to get sent to the database. These are the only two variables we'll worry about in this administrative program, though.

Note 

Using the quote function on all data being passed to a query is recommended when you are getting data from unknown people. If you do not quote your data properly and if someone sends a quote in a query, it might cause the query to fail - sometimes these query-failure messages contain more information about the database server than you may want the users to see.

20: my $sql = qq{INSERT INTO auction 21:   (name, start_bidding, stop_bidding, logo, mime) 22:    VALUES  23:   (?, ?, ?, $image_data, $mime) };

Lines 20-23 create the SQL statement needed to add the new record to the database. Notice on Line 22 that although we have placeholders, we also have regular variables. Placeholders don't work very well with binary data - extra data is sent. For the image_data field and mime field, using variables makes sense.

24: my $sth   = $dbh->prepare($sql); 25: $sth->execute($fd{auction_name},$fd{start_bid},                   $fd{stop_bid})  26:   or die("Error: $DBI::errstr \nAborting"); 27: print "SUCCESS!!!\nMIME type: $mime\n";

Line 24 calls the prepare method to get the SQL statement ready to run by the database.

Line 25 calls the execute method and sends the data for the placeholders to the database.

Line 26 causes the program to abort and to send an error message to the screen if there is a problem executing the query.

Line 27 prints a message to the user, informing him or her that the actions have been successful.

That is it for the program that adds a new auction to the database table. There isn't a whole lot to this program; it simply takes a few form fields and adds the appropriate data to a database table.

The next program adds items to the database. Again, this program contains a simple interface because auction administrators will use it. We need a few more fields for the items because we need to enter more data about each item. Take a look at Figure 8-3 to see what this page looks like. Again, the HTML for this page can be found at this book's Web site.

Figure 8-3: Add auction item form

 01: #!/usr/bin/perl -wT 02: # upload.cgi 03: use strict; 04: use DBI; 05: use CGI qw(:standard); 06: print header;

Line 1 tells the system where to find Perl and turns on warnings and taint checking.

Line 2 is a comment about this program.

Line 3 loads the strict module.

Line 4 loads the DBI module for database access.

Line 5 loads the CGI module and imports its :standard functions.

Line 6 prints the results of a call to CGI.pm's header function. This simply prints the HTTP header.

07: my ($image_data, $data, %fd); 08: my $image_handle = param(‘image1'); 09: my $mime  =          CGI::uploadInfo($image_handle)->{‘Content-Type'}; 

Line 7 simply declares a few scalar variables.

Line 8 creates a new variable named $image_handle and sets it to the value returned by a call to the param function. This is simply reading in the data that was passed from the HTML form in the image1 field.

Line 9 uses the CGI::uploadInfo function to determine the content-type of the file that was just uploaded.

10: my @fields  = qw(item_name description donor_id  11:                  value min_bid min_incr auction_id); 12: for(@fields){ $fd{$_} = param($_); }

Lines 10-11 create a new array named @fields and use the qw (quote word) function to populate the new array with the values of the fields we want to work with.

Line 12 takes the newly created array and traverses through it. While traversing through the array, this line reads in each value using the param function and stores the results in the %fd hash. (The fd stands for Field Data.)

13: my $dbh = DBI->connect("dbi:mysql:auction_img",        "bookuser", "testpass") 14:     or die("Error! $!\nAborting");

Line 13 uses the connect method from the DBI module to initiate a connection to the database.

Line 14 prints the error message and aborts the program if there is a problem connecting to the database.

15: while( read($image_handle, $data, 2048) ) { 16:     $image_data .= $data; 17: }

Line 15 begins a while loop that reads data from the $image_handle filehandle until there is no more data left to read. The read function uses the $image_handle variable as a filehandle and uses the $data variable as the temporary storage location. 2048 is the number of bytes read each time through the loop.

Line 16 uses the appendination operator, .=, to take the data in $data and to append it to the end of the $image_data variable.

Line 17 closes the while loop.

18: $image_data = $dbh->quote($image_data); 19: $mime       = $dbh->quote($mime);

Lines 18-19 uses the quote function to quote the data in $image_data and $mime.

Note 

To be as portable as possible, use the quote function; it is designed to work properly for the specific database that the DBI is connected to.

20: my $sql = qq{INSERT INTO item 21:   ( name, description, donor_id, value, min_bid,  22:     min_incr, auction_id, item_image, mime) 23:   VALUES  24:     (?, ?, ?, ?, ?, ?, ?, $image_data, $mime) };

Lines 20-24 make up the SQL to insert an auction item into the database table. More items are in this SQL statement than in the SQL statement for our former program, but the concept is the same. The $image_data and $mime are sent as data right in the SQL statement, because of the "?" bug with BLOBs that we mentioned earlier. The $mime item, since it is just text, is probably acceptable, but we're being cautious.

25: my $sth   = $dbh->prepare($sql);

Line 25 calls the prepare function and passes it $sql. The result of the call to prepare is stored in $sth, the statement handle.

26: $sth->execute( @fd{ qw(item_name description donor_id  27:                     value min_bid min_incr auction_id) })  28:     or die("Error: $DBI::errstr \nAborting");

Lines 26-27 use a hash slice to send the appropriate %fd hash values to the execute function.

Note 

A slice is a very useful way to access a portion of an array or hash. Although it may look like an array because of the @, it can be either a portion of an array or a hash.

You can recognize an array or hash slice by the {} or [] symbols. @fd{} indicates a hash slice of %fd. @fd[] indicates an array slice.

The qw inside of this slice simply takes the place of quotes around the strings and the commas separating the strings. That is, qw(a b c) is just shorthand for (‘a', ‘b', ‘c').

Line 28 causes the program to exit if there is a problem executing the statement.

29: print "Success!\n"; 30: print "MIME type: $mime\n";

Lines 29-30 print a simple message so that the user knows the task has been successful.

The next program is used in the HTML image tags to display an image. To use the program, simply put something like this in your HTML file:

<img src="/books/2/889/1/html/2//cgi-bin/display_image.cgi?item_id=34">

The preceding line of code displays the image for item 34. As you can see, this is exactly like a regular image link, except we point to a program that returns an image rather than pointing to an image file itself. One key benefit here is that you can easily vary the item_id dynamically and can change images without changing code.

One thing we'll use in this program is a 1x1 pixel graphic. If the image data is not present in the database for the particular item we are looking for, the program will send the 1x1 graphic instead. This prevents the broken image graphic from displaying as would happen if a HTTP request for an image didn't actually successfully return an image file. So, if you had <img src='/books/2/889/1/html/2/foo' /> and foo returned HTML saying ‘404 Not Found', then you'd just see a broken image.

Let's take a look at our program; it is actually very simple.

01: #!/usr/bin/perl -wT 02: # display_image.cgi 03: use strict; 04: use DBI; 05: use CGI qw(:standard);

Line 1 tells the system where to find Perl and turns on warnings and taint checking.

Line 2 is simply a comment about this program.

Line 3 loads the strict module.

Line 4 loads the DBI module for database access.

Line 5 loads the CGI module and its :standard functions.

06: my $a_id    = param("a_id"); 07: my $item_id = param("item_id"); 08: my ($sth, $sql, $item);

Lines 6-7 declare a scalar variable and read in the value passed from the HTML form via the param function. The a_id is an item from the Auction Table, the logo. The item_id is an item from the Item Table. Only one of these should be passed at a time.

Line 8 declares some scalar variables that we'll use in a bit.

09: my $dbh = DBI->connect("dbi:mysql:auction_img",          "bookuser", "testpass") 10:     or die("Error! $DBI::errstr\nAborting");

Line 9 creates a connection to the database and stores a handle to that connection in $dbh.

Line 10 causes the program to abort and to display an error message if there is a problem connecting to the database.

11: if($a_id) { 12:     die "No valid a_id?" unless $a_id =~ m/\A\d+\z/; 13:     $sql = qq{SELECT logo, mime FROM auction                    WHERE auction_id = ? }; 14:     $item   = $a_id; 15: }

Line 11 begins an if..else block. This part checks to see if there is any data in $a_id. If there is, a value is passed via a_id to this variable, so the user is looking for the auction logo. If something is in $a_id, this block of code gets entered.

Line 12 calls the die function and displays an error message if the value in $a_id contains anything other than numbers.

Line 13 simply sets the $sql variable to the SQL statement needed to select the logo for the current auction from the Auction Table.

Line 14 sets the scalar variable $item equal to the value in $a_id.

Line 15 ends this block of the if.else statement.

16: else { 17: die "No valid item_id?"        unless $item_id and $item_id =~ m/\A\d+\z/; 18:     $sql = qq(SELECT item_image, mime FROM item WHERE item_id = ?); 19:     $item   = $item_id; 20: }

Line 16 begins the else portion of this if..else block. If no data is passed into $a_id, we enter this block and use it for handling the default case.

Line 17 calls the die function and displays an error message if $item_id is empty or contains anything other than numbers.

Line 18 is the SQL for selecting the item_image and mime values from the Item Table.

Line 19 sets the scalar variable $item equal to the value in $item_id.

Line 20 ends this if..else block.

21: $sth = $dbh->prepare($sql); 22: $sth->execute($item); 23: my ($image, $mime) = $sth->fetchrow_array;

Line 21 prepares the SQL statement stored in $sql. $sth holds a handle to this prepared statement.

Line 22 executes the SQL statement and passes the value of $item to the SQL statement so that it can take the place of the placeholder (?).

Line 23 calls the fetchrow_array method on the $sth handle. This returns one row of data in a list. By putting the parenthesis around the $image and $mime values, we make them into a list context - so they are populated with the first and second items returned. We know that we will get no more than one record returned to us because the field that we are basing our search of the database table on is a primary key.

24: if($mime) { 25:     print header(-type => $mime); 26:     print $image; 27: }

Line 24 checks to see if the $mime variable contains anything. If it is not empty, data is returned from our SQL call, and we need to handle the output accordingly - so we enter this part of the if..else block.

Line 25 uses the CGI module's header function to print an HTTP header with the proper MIME type for this image.

Line 26 prints the data in $image.

Line 27 closes this part of the if..else block.

28: else { 29:     $mime = "image/jpeg"; 30:     print header(-type => $mime);

Line 28 is entered if there is not a value in $mime.

Line 29 sets the value of $mime to image/jpeg. This line can actually be skipped, the text substituted into Line 30, where we have the variable; it remains, in this case, for consistency of the code - so that it looks like the header function in the preceding block.

Line 30 prints the HTTP header with the appropriate MIME type.

31:     open(PX, "templates/pixel.jpg")              or die("Error: $!\nAborting"); 32:         binmode(PX); 33:     while( read(PX, my $data, 1024) ) { 34:          print $data; 35:     } 36: }

Line 31 opens the 1 x 1 pixel image at the location specified. This is used to display a blank area so that you don't get a broken-image picture if nothing is returned from the database.

Line 32 calls the binmode function to ensure that binary mode is used on this filehandle.

Line 33 reads the file in, using the $data variable as the temporary storage.

Line 34 prints the data.

Line 35 closes the while loop that begins on Line 33.

Line 36 ends this program.

See, in just 36 lines of code, we have a program that can return graphical images from a database!