Internet Systems Supplement, November 1997
By Brian Jepson
Settling back into your deluxe high-intensity task chair, you figure it's okay to take a break now. After all, you've earned it. You've just completed the last review of your project specifications with that hotshot Web programmer who works from home so he can listen to Hısker Dı and the Pixies as loud as he wants. In three months, you will have rolled out the system on which he's working -- an extensive, high-volume, Sybase-backed intranet for thousands of users. You left a lot of the good decisions up to him: the operating system, the choice of Web servers, and the tool with which dynamic content will be generated. You went with Perl because it's a powerful, object-oriented language with a rich history of connectivity to databases. Besides that, you know that it's widely deployed; according to O'Reilly and Associates, "[Perl] now activates 99 percent of dynamic Web pages." (O'Reilly Perl Conference literature, O'Reilly and Associates.)
Since you chose Perl, it should be a no-brainer for this consultant to design and implement the system, right? "Sure," you mutter, sipping your double lattı. As you lean back as far as the task chair will let you, you pick up the most recent copy of DBMS and begin perusing an article about Web development using Sybase and Perl. Your calm gradually turns to panic as you see the variety of options available, including a summary of their benefits and shortcomings. There, at the absolute bottom of the survey, is the exact configuration that your consultant chose.
Let's face it. There are lots of ways you can use Perl to build Sybase applications on the Web. You can deploy a Perl-based configuration that allows you to develop applications using HTML fill-out forms and dynamic HTML pages. The surprisingly powerful applications that can be built on the Web are the ultimate thin client -- if you have a Web browser, you can use the application. Even a lowly palmtop running Windows CE can be a front end to such applications.
Perl packs a surprising amount of power into what is perceived by many to be a simple scripting language. It is an object-oriented, user-extensible development tool that is available on many platforms. Further, the Perl user community provides a huge archive of well-documented Perl modules (object-oriented "black boxes" that can be used by any Perl programmer) that will prevent developers from having to reinvent the wheel. Chances are, if there's some programming task that you need to perform, you can find a Perl module that does it for you. This archive is kept on the Comprehensive Perl Archive Network (CPAN) at www.perl.com/CPAN. Although Perl is stable and user-supported, there may be times when you feel comfortable with corporate support behind a product. The Perl Clinic (www.perl.co.uk) offers support contracts for Perl, and the contracts can be customized to meet your needs.
Before looking at what goes into developing a Web-enabled Sybase application, it might be sensible to justify such an endeavor. In a traditional client/server system, the developer will generally develop the client side with a package that provides a sophisticated user interface and database connectivity options, such as Microsoft Corp.'s Visual FoxPro, Microsoft Access, or Powersoft's (a subsidiary of Sybase Inc.) PowerBuilder. The client-side application can consume a large amount of resources and is usually platform-dependent. Even with a cross-platform toolkit, a certain amount of tweaking is necessary.
In a Web-based application, the fancy user interface is sacrificed for a Web browser. Instead of having the processing logic of the client application run on the user's machine, it actually runs as a process (a CGI script or other application) on a Web server. A Web browser such as Netscape Communications Corp.'s Navigator or Microsoft's Internet Explorer simply displays the output of this application and generates fill-out forms with which the user can interact with the database.
The benefits of this approach are numerous. For one, applications become instantly portable. Netscape Navigator, one of the most popular browsers on the market, is available for many platforms including Macintosh, Windows, and several Unix variants. Chances are, if your computer can be plugged into a TCP/IP network, there is a browser available for it. Another benefit is cost. Browsers are generally quite inexpensive, and some, such as Lynx and Internet Explorer, are free. If you are using a browser such as Netscape Navigator, which features integrated email and Usenet news, your users can integrate these features, your application, and Web browsing in one application.
The amount of network traffic is sharply reduced with Web-based development. All the application-level logic is bound to the script that runs on the Web server, and only the finished document is sent to the browser. Finally, and perhaps most important, your organization is spared the high cost of rolling out the application. If your users already have a Web browser on the desktop, then they have everything they need to use an application that is built for the Web. There are no outrageous memory requirements to satisfy, and Web browsers such as Netscape Navigator and Internet Explorer are such popular products that few workstation configurations would have problems running either. Rolling out a Web-based application is as simple as giving users the URL of your system's entry point.
Web-based applications are inherently multitier. At the user's level, a Web browser renders Web pages using HTML. HTML documents are sent to the Web browser by a Web server. The Web browser and Web server communicate using hypertext transfer protocol (HTTP). The great thing about HTML is that the Web server can send the Web browser either the contents of a document on the server, or it can run a program that generates the HTML. These programs have traditionally been implemented using CGI, or the Common Gateway Interface, and are often written in a scripting language such as Perl. CGI and similar APIs are specifications that dictate the manner in which the Web server invokes applications that are designed to deliver "on the fly" Web content. There are a variety of options for developing dynamic Web content. Many of these options are similar to CGI but take advantage of native Web server extensions to work faster and consume fewer resources than CGI.
Web-based programs can be built to take advantage of database connectivity, and this allows you to generate HTML documents composed on the fly using information from a database. The HTML specification allows you to create forms on your Web page. When users load the document in a browser, they can fill in a form and press a submit button to indicate that they are finished. The contents of the form they filled out are sent to a program that runs on the Web server when they press the submit button. This program will manipulate the data and send any relevant output to the user. Figure 1 shows a form that asks the user for some query terms, and < A HREF="#figure2">Figure 2 shows the result of that query. The query was generated by a script that takes the parameters from the first form, queries the database, and formats the results in HTML.
In contrast to the richness of functionality available from a Java-based user interface, the HTML forms interface is quite limited. There are few graphical controls available. These include text input fields, large text editing areas, pop-up menus, scrolling lists, checkboxes, and radio buttons. A rather serious limitation is that these forms are stateless. Each time you submit a form, a program is run on the server, and it terminates when it has finished formatting the results. This is one of the biggest conceptual hurdles that a new Web developer must face. In a typical desktop application, the program starts when the user logs in, and it keeps track of the user's navigation through the system, finally terminating when the user logs out. In a Web-based system, this is not the case. Each time a script is run, the system needs to re-establish a connection with the database and determine the state of the previous form. There are many simple programming techniques that can be used to maintain state between forms, but it represents a conceptual shift from traditional client/server development. Despite the limitations of Web-based application development, it is still the best choice for cross-platform networked application development.
CGI may be on its way out, while Web-based application development that circumvents CGI grows in popularity. CGI itself is a specification that details the way the Web server communicates with scripts. These scripts accept a series of parameters that are either encoded in a URL or keyed in by a user to an HTML form. As CGI scripts are often slow and can consume large amounts of memory, many tools have emerged that circumvent CGI, often using a server API to interact with a scripting language that has been embedded within the Web server. One of the most time-consuming parts of running a Perl CGI script can be loading and executing the Perl interpreter. If Perl can be embedded in the Web server or linked in using a server API, this startup overhead can be diminished drastically. Another bit of overhead is incurred when the CGI program makes a connection to the database. An embedded scripting language can take advantage of the fact that it is not shut down between invocations and therefore can keep database connections open.
From the programmer's viewpoint, using these tools looks and feels a lot like writing CGI scripts. Under CGI alternatives, data that is sent in from the user is encoded in the same fashion as CGI, and the same environment variables are available. The only difference between these implementations and CGI is in the way the Web server communicates with the programs you run. From the developer's perspective, there is little to distinguish a Perl script that is running as a CGI script from one that is running in a Perl interpreter embedded in the Web server.
If you're going to use Sybase as a database server for a Perl-driven Web application, you're in luck. There are several ways that you can accomplish this using Windows NT or Unix, or even a combination of the two. The tricky part is that there's no official "right way" to do it. Each option comes with good consequences. It's more important that you match the capabilities of each solution with your needs and expertise. You should also do a sanity check with regard to your team's and your own expertise: Is this solution a tool that you can use without encountering a significant learning curve?
On both Windows NT and Unix, you can use Perl as a Web application-scripting language, and it can communicate with Sybase using Sybase's client libraries (Unix) or ODBC (Windows NT). Sybase's web.sql is available for both Windows NT and Unix, but it requires that you use a Netscape server for best performance. A version of Perl is available for Windows NT that uses the Microsoft Internet Information Server API (ISAPI) to offer performance improvements over CGI. An improvement on this model is available on Unix: mod_perl, a kit that is available for Perl and the freely redistributable Apache Web server. Not only does this solution use a server API to reduce the overhead involved with CGI scripting, but it also doesn't dispose of the Perl interpreter between invocations. Furthermore, it allows you to cache database connections.
This is the stock solution that works with nearly every modern Web server. In a Perl/ CGI environment, the Web server reacts to a request for dynamic content by executing a Perl script. Each time this happens, the Perl interpreter starts up, loads and compiles the script, and then executes it. There are many options for embedding Perl, so it is unlikely that you will have to use this configuration. Nevertheless, it is the worst-case scenario, so I explore it here. In a pure CGI configuration on Unix, Perl can be combined with a Sybase extension known as Sybperl. In order to take advantage of the latest features of Sybperl, you should be running a Perl version 5.001 or greater, preferably 5.004. On Windows NT, Perl can be used with the Sybase ODBC driver.
Perl itself is freely redistributable and is available from ftp://ftp.funet.fi/pub/languages/perl/CPAN/src/latest.tar.gz. The Sybperl extension is available from ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module/Sybase. In order to compile Perl, you will need a fairly modern ANSI C compiler. Unless you obtained a binary distribution of Perl, you should have almost everything you need to compile it. Sybperl needs to link against either the DB-Lib or CT-Lib Sybase client library. These client libraries are available from Sybase. (See the accompanying sidebar, "Sybase and Linux?" for more information on obtaining CT-Lib for Linux.)
Although the core Perl that I just listed can be compiled on Windows NT, there are two precompiled versions of Perl that will be of interest to Windows NT developers. ActiveWare Internet Corp. has ported Perl 5.001 and Perl 5.003 to Windows NT and Windows 95. Perl is available on the company's Web site, www.activeware.com. The Win32::ODBC extension is available from www.roth.net/~rothd.
Gurusamy Sarathy has made a binary (precompiled) distribution of Perl available at ftp://ftp.funet.fi/pub/languages/perl/CPA/ports/nt/Gurusamy_Sarathy/. This distribution is in a constant state of improvement, so you should look for the latest file named "perl5.004XX-bindistYY-bc.tar.gz" where XX and YY will vary depending upon the version of the build. This is an excellent package, and it includes many popular extensions, including the DBI-ODBC library, an interface to ODBC. (This is an alternative to Win32::ODBC.) Perl is easy to install and easy to use once it is up and running.
Bottom line: Using Perl with nothing but a CGI interface to the Web server is slow and consumes a lot of system resources. Go with embedded Perl, which is free when used with a server such as Apache, or use a product like web.sql if you feel it meets your needs and if you do not need all the flexibility of Perl.
Sybase's web.sql offering is a powerful combination of Perl and ingenuity. (To hear more about my personal experiences with the product, see my sidebar, "The Strange and Terrible Saga of web.sql,".) It runs well when attached to a Netscape server using NSAPI, but it runs rather slow when used as a CGI script. These are the two configuration options available for web.sql. Under the hood, web.sql is actually Perl with the aforementioned Sybperl extension. However, web.sql offers a number of extensions, including the ability to embed Transact-SQL or Perl code in HTML documents, caching of database connections, and seamless integration with Netscape servers. It is a fast, easy-to-install solution that offers novice developers powerful features without tying too tightly the hands of more experienced developers. The ability to embed Perl and SQL calls right into your Web documents is a handy feature that doesn't require programming expertise. However, advanced programmers can get almost as much out of web.sql as they could get out of a Perl system. The biggest limitation is that web.sql's embedded Perl interpreter cannot be extended with certain modules from CPAN in the way that a stand-alone Perl interpreter could. This is mostly limited to modules with a binary extension because, as the web.sql FAQ points out, "It is not possible to link additional libraries with ours." Certain modules that can be installed by hand will work fine with web.sql.
If you need a high-performance, easy-to-use solution for bringing your databases to the Web, web.sql is a great choice. You can develop anything from simple catalog screens to full-blown transactional applications. However, don't bother with web.sql unless you have a Web server that is supported by web.sql with a server API. Currently, this is limited to certain Netscape servers. As a result, you will have to pay for both the server and the web.sql product. Fortunately, Netscape servers and web.sql are available for trial download off of the companies' Web sites (www.netscape.com and www.sybase.com).
Bottom line: If you are using a supported Netscape server (see the web.sql page at www.sybase.com/products/internet/websql/ for requirements), this is an excellent choice. It is fast and easy to use, despite the fact that it can be difficult to extend. If you need a lot of power over your development environment or don't want to be tied to a Netscape server, you may wish to go with Perl and the Apache Web server.
Microsoft's alternative is ISAPI. Like Netscape's NSAPI, ISAPI allows executables to hook directly into the server, eliminating the typical CGI overhead. The Fnord! Server (www.wpi.edu/~zik/fnord/) will be supporting ISAPI in the near future. Developers can write their own C programs that link with ISAPI, or they can choose to write scripts using ActiveWare's port of Perl to Win32, which includes an ISAPI version of Perl. For the most part, Perl with ISAPI seems to work exactly like Perl with CGI. At press time, I learned that the latest build of Perl for ISAPI supports subprocesses.Its lack of support had been a major problem in the past.
Bottom line: The ISAPI version of Perl should be used on Windows NT if you need a speed boost over your Perl CGI scripts. You get it for free with the ActiveWare port of Perl, but it is not presently supplied with the core Perl distribution or Gurusamy Sarathy's binary distribution of core Perl.
The Apache Web server, which is based on the original NCSA HTTPD code, has become the single most popular Web server on the Internet, exceeding the popularity of its competitors by wide margins. The July 1997 Netcraft Web Server Survey (www.netcraft.com) finds Apache used on 42.62 percent of the 1,203,096 Web servers surveyed. Microsoft's servers trails in second place at 16.90 percent, with Netscape servers third at 11.76 percent. Apache 1.3, which entered public testing in the summer of 1997, supports Windows NT and Windows 95. Considering that Apache was the leading Web server when it was supported only on Unix platforms, imagine how much more popular it will be when it catches on with Win32 users!
A large part of Apache's appeal is that it is free and robust. It is also popular because of the many hooks that developers can exploit. Face it -- if you are developing a complex, data-driven Web site, you'll want as much control over your Web server as possible. Apache offers this control and more. The Apache module interface allows you to link extensions into the Web server itself. The mod_perl extension will link a Perl interpreter in with each HTTP process (known as httpd, the executable name of the http daemon) that is running. Traditionally, a Web server keeps as many httpd processes running as it thinks are necessary. This is nice, because it does not always need to "kick off" a new process for each visit. However, if you are using a scripting language such as Perl, each of these processes needs to start a new copy of Perl. This is not the case with Apache, because Perl is embedded in the httpd process and is already running. The extra touch is that after each request is served, the httpd process does not terminate. Instead, it waits for another hit. The Perl interpreter does not terminate either, so you can write scripts that maintain open database connections between hits. This can offer massive performance improvements over traditional CGI scripts. Not only is the overhead of starting Perl eliminated, but the database connection is reused each time, eliminating another potential bottleneck. Apache is available from www.apache.org, and mod_perl can be found at ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module/Apache. The Perl/Apache Integration Project site is at perl.apache.org.
Bottom line: If you don't mind buying a vendor-supported product and you want a high-quality solution that offers you speed and complete control over your application, Apache and mod_perl are the way to go. At the present time, mod_perl is only available for Unix platforms. As for the Windows NT and 95 port of Apache matures, keep your eye out for more development in this area.
The testing of each of these technologies was divided across two Web server platforms, although the data server was running on a Windows NT machine. The Unix client software tests were run on a Sun Microsystems Inc. SPARCstation IPX with 64MB of RAM and a Weitek 80 MHz POWER uP CPU. The Windows NT client software tests were run on a 133 MHz Pentium laptop with 32MB of RAM. It's important to note that the SPARCstation upon which these tests were run is somewhat slow by today's standards, so it is not a good idea to use these benchmarks for any comparison of Unix and NT.
The test used to benchmark performance was the generation of a simple Web page. This Web page consisted of a borderless HTML table that displayed the output of the "sp_tables" stored procedure. Each instance of the test suite executed this request five times in a row. Five copies of the test were started simultaneously, and the time it took to execute all five requests was recorded. All times are in seconds. The results are listed in Table 1.
The Web page for web.sql for Windows NT claims that it requires any of the following Netscape servers: Communications Server 1.1, Commerce Server 1.12, FastTrack Server 2.0, or Enterprise Server 2.0. At press time, only FastTrack Server 2.01 and Enterprise Server 2.01 were available from Netscape's download site. Although this lack of support for these servers is likely to be remedied in the near future, it made it impossible to provide data about the performance of web.sql on Windows NT. The web.sql 1.1.1 Maintenance Release for SPARC Solaris does support the 2.01 versions of the Netscape servers, so testing on that platform was not a problem.
Apache is a fast, freeware Web server that has captured the hearts of nearly half of the webmasters in the known universe. Even without its developers' hooks, it's a very powerful choice for Web development. Combined with mod_perl and Sybperl, Apache offers all of the benefits of web.sql without the cost, and it offers all the benefits of Perl with CGI without the performance sacrifices. In other words, it is the best of both worlds.
Some of the design decisions regarding Sybase's web.sql limited the ability to extend the embedded Perl interpreter, but these limitations do not apply to Apache and mod_perl. As a result, a developer can get much more power out of Apache and mod_perl than web.sql. The performance of mod_perl is close enough to that of web.sql that its other advantages outweigh the extra second or two.
For Windows NT Perl development, Perl with ISAPI is clearly the way to go. Both Perl and Microsoft's IIS are available for free. IIS ships with Windows NT and Perl is available from ActiveWare's Web site. It is hard to say at present how well ISAPI Perl will compare with mod_perl for Win32, because no such beast exists at the present time. If a version of mod_perl becomes available for Win32, then it will be a strong contender against ISAPI Perl.
Although there exist a number of ways in which Perl can be used to develop Web applications that use Sybase as a back end, choosing the best way to accomplish this task should not be a cause of stress. Each solution has a certain set of strengths and weaknesses. By no means should the opinions I offered for Windows NT and Unix be considered an absolute ruling. You will need to assess the strengths of your development team, consider the requirements of your project, and compare this to the offerings of each solution discussed within this article. Armed with this information, I hope that you will be able to choose the best way to solve your Web-to-Sybase connectivity needs without painting yourself into a corner or going mad in the process.
Figure 2. Table 1. Perl-to-Sybase Connectivity Solutions Performance Results
The results of the query in Figure 1.
| Platform | Scripting Tool | Best Time | Worst Time | Average |
|---|---|---|---|---|
| Solaris 2.5 (Unix) | Perl/CGI) | 119 | 124 | 123.6 |
| Solaris 2.5 (Unix) | mod_perl/Apache | 41 | 45 | 43 |
| Solaris 2.5 (Unix) | web.sql | 37 | 42 | 39.8 |
| Windows NT 4.0 | Perl/CGI | 157 | 162 | 160.2 |
| Windows NT 4.0 | Perl/ISAPI | 55 | 63 | 58.6 |
I've already told you that I like web.sql. Not only is it a very fast way of bringing Sybase data to the Web, but it's powerful enough for enterprise-level applications. Unfortunately, my early experimentation with the product uncovered a bit of ugliness that bears retelling.
It was early spring in 1996. I was working as a Perl/SQL consultant on a large intranet development project. Looking to balance speed and power, I evaluated some solutions for performance and flexibility: C/DB-Lib, Sybperl, and web.sql. Of course, the C programs blew everything else away, because they were at least 10 times faster than the Sybperl examples. However, the web.sql programs took only twice as long as the C programs (five times faster than Sybperl). This proved to be rather intriguing, because web.sql was almost as powerful as the latest version of Perl. Although web.sql is based on Perl, the way in which it is implemented limits the functionality. It does not offer an easy means of installing modules from the Comprehensive Perl Archive Network (CPAN). Some modules can be installed "by hand," but there does not seem to be an easy way to install binary modules.
Although I thought it was a wonderful product, one thing started to bother me. I thought about the way that web.sql projected CGI parameters into the programmer's namespace. CGI parameters are values that are either typed into a form or are part of a URL. They are key/value pairs, along the lines of:
search_term=Elvis+Aaron%27s+Gravy
The + is converted into a space and the hexadecimal sequence (%27) is converted into an apostrophe before it is made available to the programmer. This yields the value "Elvis Aaron's Gravy." The problem is that it's turned into a variable right within your web.sql application, and you can refer to it as the variable $search_term. At first, this doesn't seem too bad, but one thing that people forget is that users can change the CGI parameters at will. They could either type something bogus into a data-entry field, or they can type the whole URL, key/value pairs and all, right into their browser. I got to thinking, what if the end user does something like:
ENV{REMOTE_ADDR}=192.168.1.1
When a CGI script (or, in the case of web.sql, an NSAPI script) is started up, it's given an environment variable called remote_addr that tells it the IP address of the user's machine. In sophisticated Web applications, this is often used as part of a user verification scheme. Well, Perl's environment variables are contained within an associative array called %ENV. The elements of that array can be manipulated as $env{element_name}. If the user types in remote_addr as shown in the previous example, web.sql will dutifully execute the statement:
$ENV{REMOTE_ADDR}=192.168.1.1
This is problematic, to say the least.
It only gets worse from there. Upon further investigation, it turned out that the means by which web.sql creates these variables is a textbook example of how not to process user input. The bug in this code lets remote users of your web.sql application execute any bit of Perl code they choose to type in. This is a horrible security hole. Naturally, I brought the issue to Sybase's attention, citing an example of how it could be exploited. They assured me that it would be fixed, and I let the matter rest until I started working on this article, more than a year later. Yes, it turns out that they fixed the bug.... Well, sort of. The test case I was using was no longer successfully exploiting the security hole. It turns out that they had patched the application to prevent attacks that were similar or identical to the example of the breach that I had cited over a year ago, but they did not address the issue that the web.sql application would still execute untrusted user input.
To Sybase's credit, when I contacted them again, they began looking into the problem immediately. I was given the impression that the current team assigned to web.sql is a little more proactive than the people I encountered over a year ago. web.sql is an excellent, high-performance product -- let's hope that Sybase's people can beat it into shape.
There is a little twist to this story. About two weeks after I started hollering at Sybase to fix this bug, a dim light went on in the recesses of my mind. Less than a year before, I had developed a template script for Win32 Perl that did essentially the same thing as Microsoft's IDC files. As it turns out, I had made the exact same mistake (it's fixed now -- see users.ids.net/~bjepson/template.html) as the developers of web.sql! Ah, well, we all make mistakes, don't we? After all, we're only human.
With all this talk of free software, it would be a shame if I didn't say something about Linux. Linux is a freely redistributable Unix-like kernel that is usually bundled with lots and lots of software written by the Free Software Foundation and other contributors. It provides a robust and fast environment on a wide variety of hardware, including SPARC, Intel, and Alpha. Best of all, it's free. If you are running Sybase SQL Server on another platform but are using Linux (or are considering using Linux) as your Web server, you are in luck. Sybase has made an unsupported release of CT-Lib available for Linux users running on the Intel platform. However, it's provided "as is," so please do not approach Sybase asking for product support. Using this package, developers can build applications (including Sybperl) in Linux that can connect to Sybase on other platforms. A very detailed description of using this offering is available at www.ssc.com/lg/issue18/sybase.html.