Now that we have seen some basic uses of MySQL from within Perl, let's spend some time thinking about how we can integrate the use of MySQL into a CGI program. While this might seem like overkill for some small jobs, database servers are so much more reliable and efficient at this sort of task than our CGI programs that it is almost always worth using such a server, assuming one is available.
By using a database server, we can be sure that our data is stored more reliably than with text files. As an added bonus, the information is available using SQL, which is more efficient and flexible than text files.
How can we use a database server from within our CGI programs? The simple answer is that it is actually no different from connecting to a database server from within non-CGI programs. We still create the Mysql object, use its methods to send an SQL query and retrieve results. The differences are in our ability to modify our query based on input sent to us in an HTML form and the necessity of sending our output to the user's browser using a recognized content type (usually HTML). Such a program, which I have called cgi-sql-test.pl is shown in Listing 2.
While cgi-sql-test.pl is longer than the program on which it is based, it is not much more complicated.
First, we fire up the CGI module for Perl, which you can get via the Comprehensive Perl Archive Network (CPAN) at http://www.perl.com/CPAN. After creating an instance of CGI, we send an HTTP Content-type header to the user's browser indicating that we will be returning results of type text/html, i.e., HTML-formatted text.
Following our initialization of the CGI environment, we go ahead with what we had done in the non-CGI version of the program, namely connecting to the database, sending our query and retrieving the results.
This is where the big difference lies. Rather than printing the results to standard output, we send them in HTML format to the user's browser, so that we can use all sorts of nifty HTML formatting techniques to display the results.
In this particular example, I decided to put the results of the telephone list in an HTML table, which is attractive and makes it easy to understand the results. The <tr> tag introduces a table row, while the <td> tag introduces a column within a row. Because each iteration through the while loop represents a new record in the database, we can start a new HTML row at the top of each loop, ending it at the bottom of each loop.
We will continue to explore the interaction between SQL and CGI in the next few installments, but before I conclude this month's column, I want to show at least one example of how we can modify the SQL queries based on the user's input. For the sake of simplicity, we modify our program such that it will ask the database server to return only those rows whose name column matches what we enter in the query string. Thus, if we are interested in finding out Gil's telephone number, we can go to:
And if we are interested in finding out Andy's telephone number, we can go to:
/cgi-bin/cgi-sql-test.pl?Andywhich produces only that listing.
But what happens if someone invokes our program without entering a name in the query string? Well, our program cleverly notices it and produces a very small page of HTML in response. This small page of HTML asks the user to enter a name for which to search and then uses the <isindex> tag to create a text field in the page of HTML.
The <isindex> tag has generally fallen out of favor, since HTML forms are more flexible and useful. When a user enters information into an <isindex> field and presses enter, the URL in which the <isindex> tag appeared is reloaded—with the user's input appended as part of the query string.
Thus, if our program receives no input in the query string, it produces a page containing <isindex>. Whatever the user enters in that text field causes our program to be reloaded, this time with a value in the query string. That value is picked up by our program and passed to MySQL, which returns the results in an HTML table.
That concludes the basic introduction regarding the integration of SQL and CGI programs. As you might imagine, SQL databases are far more powerful than the programs and databases we have seen this month. Over the next few months, we will spend some more time looking at different ways in which we can use MySQL (and relational database servers in general) to make for more interesting, efficient and useful web sites.
Reuven M. Lerner is an Internet and Web consultant living in Haifa, Israel, who has been using the Web since early 1993. In his spare time, he cooks, reads and volunteers with educational projects in his community. You can reach him at email@example.com.
Fast/Flexible Linux OS Recovery
On Demand Now
In this live one-hour webinar, learn how to enhance your existing backup strategies for complete disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible full-system recovery solution for UNIX and Linux systems.
Join Linux Journal's Shawn Powers and David Huffman, President/CEO, Storix, Inc.
Free to Linux Journal readers.Register Now!
- Server Hardening
- The Death of RoboVM
- EnterpriseDB's EDB Postgres Advanced Server and EDB Postgres Enterprise Manager
- BitTorrent Inc.'s Sync
- The Humble Hacker?
- The US Government and Open-Source Software
- Open-Source Project Secretly Funded by CIA
- ACI Worldwide's UP Retail Payments
- New Container Image Standard Promises More Portable Apps
- AdaCore's SPARK Pro
In modern computer systems, privacy and security are mandatory. However, connections from the outside over public networks automatically imply risks. One easily available solution to avoid eavesdroppers’ attempts is SSH. But, its wide adoption during the past 21 years has made it a target for attackers, so hardening your system properly is a must.
Additionally, in highly regulated markets, you must comply with specific operational requirements, proving that you conform to standards and even that you have included new mandatory authentication methods, such as two-factor authentication. In this ebook, I discuss SSH and how to configure and manage it to guarantee that your network is safe, your data is secure and that you comply with relevant regulations.Get the Guide