A number of options are available for Perl developers who want to reduce the amount of work and tedium involved in writing Perl CGI scripts that interact with a database server. Some of these alternatives include CGI::Application and Class::DBI with the Template toolkit. However, these solutions may not be appropriate for every situation. Web sites hosted at an ISP may not include these Perl modules or allow customers to install these modules within the Perl directory hierarchy. These solutions still do require some programming and therefore may not be suitable for entry-level developers.

This article describes how Perl is used to generate Perl CGI code using the multi-platform CGIScripter application. The resulting output code automates SQL table creation commands (in this example, for a MySQL database), HTML pages and Perl code. Web security issues, data validation and image handling functionality are incorporated into the resulting Perl code. By automating the development of Perl CGI scripts, even entry-level developers can create CGI scripts that contain most of the commonly requested features in a short period of time--without manually writing any code.

This article does not cover the installation or configuration of Perl, Apache, MySQL or DBI/DBD software on a Linux server. You can find information on these topics in a variety of places on the Web, within several well written books and in the documentation included with each Perl module.

The CGIScripter graphical interface has been implemented in the Revolution development environment from Runtime Revolution Ltd. The graphical interface allows the user to enter/change information, load/save files and provides data validation on a field by field basis. The heavy lifting, however, is accomplished by a Perl program that reads configuration files written by the graphical interface and then generates the Perl, HTML, database table creation SQL and instructions files (see Figure 1).

Figure 1. Code Generation Process

Revolution is based on ideas originally incorporated within Apple Computer´s HyperCard in 1987. Revolution, though, is a built-from-scratch implementation that contains no Apple code. The Revolution IDE not only runs on Linux, it also compiles standalone graphical applications for over a dozen different operating systems. Many modern programming language features found in Java and Perl, such as object orientation, runtime compilation, automatic memory management and garbage collection, were implemented within Apple´s HyperTalk. Runtime Revolution has named its development language Transcript, but it is HyperTalk-compatible and contains a significant number of language enhancements. The development environment uses an object-oriented message passing architecture in which the code for an object actually is stored within the object being programmed. This means that if I want to add functionality to a button on the GUI, I generally place that code within the button itself. Applications developed in Revolution look and work identically across each operating system (see Figures 2, 3 and 4).

Figure 2. CGIScripter on Mac OS X

Figure 3. CGIScripter on Linux

Figure 4. CGIScripter on Windows

Four folder tabs appear across the top of the CGIScripter window; we concentrate on updating them first. Update the default contents of each field on the General and Other folder tabs as shown in Figures 5 and 6. We don't need to update any fields on the Oracle folder tab.

Figure 5. General Parameters

Figure 6. MySQL Database Parameters

Click on the CGI tab and three more tabs become visible under the first group of folder tabs (see Figure 7). These three tabs enable you to enter parameters specific to the Web Site, each Web form and the fields located on each Web form. The Web Server menu defaults to UNIX, which we do not need to change, although code also can be generated for Windows IIS Web servers.

Figure 7. Web Site Parameters

The Max Submit Size determines the total number of bytes that can be submitted to all fields of the form at one time. This parameter is directly translated into the POST_MAX parameter used by the CGI.pm module. Limiting the amount of data accepted by the Web form helps prevent one type of denial of service attack that can be launched against a web server. For this example, the default value of 10,000 bytes has been increased to 100,000 bytes, because we are going to allow the uploading of images to the database.

The Web site URL is used to construct form submission URLs and documentation within the generated scripts. For example purposes, we can use the IP address of the Web server being used here for testing, which is 10.1.0.25. This value then can be changed to an actual production Web server URL and the scripts regenerated after testing has been completed locally. Because the resulting output code is re-created accurately by CGIScripter, we don't have to worry about making a typo while making manual changes to the scripts.

A new Web form is added by clicking the Add button to the right of the Forms field (see Figure 8). Each form is given a default name of form??, where the ?? is a numeric value. The name of the form within the Form Name field can be changed at any time, so let's change it to pictures_form1.

Figure 8. Adding a Form

Form-specific parameters include the HTML header/footer, success URL or success Text that should be displayed at the completion of a successful form submission.

The security of this form may be left at the default value of None. Selecting the Secure option from this menu prefixes URLs with https instead of http.

Clear the URL within the Submit Success URL field, unless you want to create a static HTML page to take its place. Clearing this field causes cgiscripter.pl to generate files that dynamically display the text listed within the Submit Success Text field, instead of performing an HTTP redirect to the Submit Success URL. Change the Submit Success Text to The image has been successfully uploaded.

The example database table being created for this article is a picture database table. This example utilizes a wide range of column types in order to show how they can be handled automatically by the generated code. One use for a table similar to this design might be for cataloging digital images for a Web site.

A new field is added to a Web form by clicking on the Add button to the right of the Fields field. The new field is added to the form currently highlighted within the Forms field. Clicking on the name of a form or field within the Forms or Fields fields displays information for the form or field clicked. The first field we want to add to the form is the ID field. Click the Add button to add this field, then change the default name from field1 to ID (see Figure 9).

The name we display for a field may differ from the actual name used for the database table. As business requirements may change in the future, we don't want to have to make changes to column names within database tables simply because the name of a field has been changed on a Web form. We also may have the need to display a name for a field that is not a valid name for a database column, such as those that include spaces or special characters. The Display As field gives us the means by which we can define the display name for a field, so let's change the display name to be Image Number.

Figure 9. ID Field Parameters

Every database table should have a primary key column in order to maintain the integrity of the data. The ID column is the primary key for the pictures table, therefore, it should be set to a Field Type of Number with an Auto-Enter attribute of Serial Number. The data validation requirement for this field should be set to Not Empty because it is going to be the primary key for the table. Setting the Auto-Enter attribute to Serial Number along with the Not Empty data validation option results in the table creation code designating this column as the primary key with the auto-increment attribute.

Next, we create the image field to store/display our image information. This field should be set as a Field Type of Image with a data validation attribute of Not Empty, because we always want to insure that an image is uploaded for every new form submission.

The used_status field makes use of an Auto-Enter feature that puts the static text value of "unused" into the field. Each image initially has its status set to the unused status when the record is created. The Data menu selection should be selected for the Auto-Enter parameter, and the text unused should be entered into the Result = field. Finish adding the rest of the fields according to the attributes specified in Table 1.

Now that the form and fields have been specified, click on the Generate CGI button to create the scripts. If you want to monitor the file-generation process, select the Status Window item from the File menu. The Status Window shows the STDOUT results from generating the Perl CGI scripts. If any problems occur, you can use the error messages in this window for troubleshooting.

Either list the contents of the output directory from the command line or open the folder in a graphical window. You should see a list of files similar to those listed in Figure 10.

Figure 10. List of Generated Files

Use the picture_form1_create_table1.sql file to create the pictures table within the MySQL database.

$ cd pictures_scripts
$ mysql webdb1 -u webuser1 -pweb_12y#! < picture_form1_create_table1.sql

You can use the MySQL Control Center software to verify that the table has been created (see Figure 11).

Figure 11. MySQL Control Center Showing Pictures Table

Now that the database table has been created, we can move the HTML and Perl CGI scripts into the Web server directories. The following commands move these files into the proper locations; they also make the Perl scripts executable by the Web server. It can be helpful to put these commands into a shell script so you don't have to do all of this typing every time you regenerate the scripts. Because it is easy to make iterative changes and then regenerate the scripts, you most likely will want to perform this task more than once.

$ mv -f picture_form1_submit1.html /var/www/html
$ mv -f picture_form1_submit1 /var/www/cgi-bin
$ mv -f picture_form1_update1 /var/www/cgi-bin

$ mv -f picture_form1_update1.html /var/www/html
$ mv -f picture_form1_display_record1 /var/www/cgi-bin
$ mv -f picture_form1_update_record1 /var/www/cgi-bin
$ mv -f picture_form1_update_record_validate1 /var/www/cgi-bin

$ chmod +x /var/www/cgi-bin/*

Now we're ready to use the Web application we have created. The first script we use is the Submit Form so we can input some data into the database. Use your browser to go to the HTML submit page, which has the URL http://10.1.0.25/picture_form1_submit1.html/ . You should notice that no Image Number field is listed on the submit form. This field is specified as an Auto-Enter Serial Number field; therefore it has been omitted from the form, because its value is supplied by the MySQL database. For the image field, select a GIF or JPEG image containing less than 100K bytes from your hard drive. Fill in the remaining fields as shown in Figure 12 and then press the Submit button.

Figure 12. Submit Form

Let's next test some of the data validation features of the submit form processing code. Submit another record without selecting an image for the image field. You should receive an error message telling you that this field must contain a value.

Now that we have some data stored in the pictures table, we can test the query and record display features of our new scripts. Use your browser to go to the HTML update page, http://10.1.0.25/picture_form1_update1.html/ . Select ocean from the Type menu to find all of the image records that contain ocean images. If the search results include more than 30 records, you can test the result set paging feature. Clicking on the image number for any record in the list displays the entire record, including the image uploaded into the database. The image stored within the database is displayed in the Web browser if it is a GIF or JPEG image. Any type of binary file, such as a StarOffice document, a PDF file or a binary executable file, may be inserted into a LongBLOB column within the MySQL database table. These other types of files, however, aren't displayed within the Web browser window. An image may be replaced within a record by selecting the Replace menu item and browsing to select the file to be uploaded, as shown in Figure 13.

Figure 13. Update Record

The file generation process is implemented within the cgiscripter.pl program. Each output file is generated by a particular subroutine within the program, which is given a name that reflects its function. For instance, the create_submit_std subroutine creates what I call the standard version of the code used for processing submit forms. This naming methodology is implemented in order to plan for the addition of new functionality. Future functionality could involve adding new versions of each of these subroutines, which would implement the new features without affecting the existing code.

Database-specific functionality is isolated in the get_database_info, create_table_mysql and create_table_oracle subroutines. The get_database_info subroutine is updated to support new databases, while a new version of the create_table subroutine is created for each new supported database server. Each of the subroutines is called from within the main section of code that directly follows the variable definitions.

Developers who wish to change the file generation process may want to simply copy and modify existing subroutines and then call the modified versions of those subroutines within the main section of the code. This method of implementation allows changes to be more portable to future versions of the cgiscripter.pl code. Because the CGIScripter GUI application rewrites the cgiscripter.pl file every time scripts are generated, the modified version of cgiscripter.pl needs to be copied into place and executed every time scripts are generated. A shell script can be used to automate this process.

Perl CGI programming of database-backed Web applications is a challenging process. Developers need to understand Perl programming, HTML coding, Web server CGI execution and debugging, along with SQL database technology. Each field included in a CGI Web application requires programming for auto-enter values and data validation of user-entered data. This validation process also needs to consider the security implications of file uploading and overall Web security best practices. The automation of this programming process with graphical applications such as CGIScripter can provide a significant reduction in development time and programming errors. Using a Perl program as the core technology for this process offers the advantage of enabling experienced developers to tailor easily the code generation process to their specific needs.