Conceptual Explanation

This section presents general concepts to help you in the process of refining your application for implementation.

Context

Your database Web application exists in the context of your entire organization. One formal definition of a computer information system is that it consists of hardware, software, people, and procedures built to provide a function for a specific group of users. It is easy to forget the “people and procedures” while you are struggling with programming. You need to identify the ways in which users come to the first script of your application. What are their expectations? Are they prepared to fill out the form or forms that your scripts present?

If everything goes well, you might need to determine what follow-up action is required. This is sometimes called “the back office” operation. A problem with early e-commerce businesses was inadequate back-office capability. You can implement some of the back office application using middleware scripts. However, if your business contains the shipping of tangible goods, you will need ways to initiate acts such as collecting goods from warehouses and shipping them to your customers. A slang way of describing this is going from moving bits to moving atoms.

When you build a Web application, you probably need to include ways for your user (client, customer) to contact you. This might be a link to send e-mail or an e-mail address, a telephone number, or an address. You need to make sure someone knowledgeable is in place to receive these messages or calls.

Offensive and Defensive Interface Design

Many of the sample applications presented in this text involved someone filling out a form, or clicking on hyperlinks that had the effect of filling out a form. You need to design these interfaces for your clientele to minimize the chances of erroneous information coming into the system. You need what can be called offensive and defensive measures. An offensive measure would involve a design that does not give the user a chance to make a mistake. Examples of this are the use of radio buttons or drop-down lists as opposed to text boxes. Text boxes allow the user to enter anything. Buttons and lists minimize the choices. Radio buttons prevent more than one choice from being selected. The use of default values might be appropriate, but require careful planning. You also need to consider the possibility that the default value will be overlooked and remain unchanged even when your user intended to change it.

Defensive measures include checking the input and displaying the values to be confirmed by the user. You can use regular expressions to check values input by users if the input must conform to a known pattern. You can do online searches to find regular expressions for many common situations.

If your database product allows you to specify relationships and other conditions on fields, then the DBMS will signal an error if a query, such as an INSERT, has any problems. You need to do what is termed “trap” or “catch” the error to prevent your user from seeing it. Your PHP or ASP code then would take the appropriate action.

An additional technique is to present the user with the values for confirmation. This can try the user’s patience. However, it is an accepted practice by many online organizations, and if the transaction is important enough—for example, if it involves a significant amount of money—users will accept the burden of making one more click.

Scaling Up

When building an application, you need to analyze the situation in order to do a sizing of the requirements. The tables shown in applications in the text were quite small. You need to estimate how big the tables will become once the system is in operation. If the application already exists, you will have historical data to support your estimates. Your server administration probably will request sizing information. The estimates will help determine how long certain operations take that require searching whole tables.

Most DBMS products have facilities for improving the performance of queries. You can, for example, specify that certain fields are to be stored as indexes (note: this spelling often is used in technical publications). This requires extra space, but speeds up queries involving these fields. Another facility is called “stored procedures.” This speeds up the performance of an SQL statement at what generally is a modest cost in space.

You might choose to change the design of certain pages as the size of tables increases. The origami store displayed all the products in one page. You might choose to display a fixed number at a time. This is termed paging. You can do this directly using the LIMIT clause in the SQL statements, or you can use facilities present in the database product. Another alternative is to incorporate additional fields to specify categories.

Backup and Recovery

An unfortunate fact is that computers and networks do go down, perhaps because of programming errors, but also because of hardware failures, electrical problems, or other catastrophes. It is a good practice to establish a procedure of scheduled backing up of all the data. You can also incorporate keeping a record of all modifications to the database in what is called a log file. This is another area in which the database products promise and, indeed, do provide facilities.

Transactions

The concept of transaction refers to the set or sequence of steps required for a single operation. For example, the quiz show application modified the players table and the history table after determining whether the player’s answer to a question was correct or incorrect. For a banking operation, a transaction could involve checking that the customer’s account contained enough money to cover a withdrawal, making the withdrawal by signaling to the ATM machine to dispense the money, writing out a record to a history file, and updating the account to reflect the new total. You want the entire transaction or none of it to occur.

When you are building an application for widespread use, you need to consider the possibility that more than one person might be using your system at any one time. The DBMS driver that executes the SQL statement passed from the middleware script will make sure that an update to a single table is completed before another update is started. However, what if the transactions involved multiple tables? You do not want two people with access to the same account to make changes at the same time, because that might prevent accurate operations. Generally, you do not want to lock out other access during a time period when your customers or clients are staring at the computer screen, because this could be an unlimited period of time. Instead, a technique is to check the data a second time just before the update to make sure that it still meets the condition for making the change.

Transactions also are important in designing systems that are robust with respect to system failures. If the system failed—say, due to an electrical outage—in the middle of a transaction, you want a method for what is termed rolling back the system to the state before the transaction began.

The solution to these problems making use of the concept of transaction depends on the features of the DBMS. Some have features that allow you to define transactions explicitly. Others provide simpler mechanisms such as locking tables to prevent other access. Incorrect use of locking can cause problems, such as what is called “deadly embrace” when each of two or more processes is waiting for the other(s) to complete. This can cause an entire system to stop operation.

Security

The security of the information of your application—that is, decreasing the possibility of someone other than your customer or client viewing information and even changing it—is a complex issue. Tools and techniques such as encryption, passwords, and log files exist to decrease the chances of inappropriate viewing or changing information, although no one would claim that any system is absolutely safe. In most cases, you and your clients need to make trade-offs of security versus convenience and other costs.

Opportunities for security incursions exist at different places or stages of the application. The techniques to improve security vary. Let us follow the path of information from your users from when they enter it into forms at their computer to the server and then to the database.

The term authentication refers to methods for ensuring that someone such as your users, customers, and clients can be confirmed to be who they claim to be. This is normally done with some type of identification and password system. In most cases, the customers or clients are given the opportunity to select their own IDs and passwords. If the ID has already been used, the system tells the person to try again. Frequently, the system sets a minimal size for IDs and/or passwords. This is the approach demonstrated in the Examples section. However, you should keep in mind that user-created passwords often are easy to guess. If your application demands a heightened level of security, consider using tools such as the md5 function used in the Examples section to generate randomly constructed combinations of letters and numbers for passwords. In addition, consider establishing a schedule for changing passwords periodically.

You can provide what we can call “over-the-shoulder” security by using the input type of password for certain fields of forms and by specifying the method of a form to be POST and not GET. Someone standing behind your client and “looking over his or her shoulder” would not see any information entered into form fields that were designated password as they were being typed. If the method was GET, no one would see information passed in query strings. The latter means that if you did use GET during the debugging of your application, you will have to make changes in the code. If you passed along information in a query string, possibly as part of a hyperlink, consider whether the information would be considered private. People are accustomed to typing in a password without the feedback of seeing the characters. For the other information, most people need to see what they type, even if they consider the information sensitive. You need to depend on your clients to maintain their own privacy.

For both PHP and ASP, we have demonstrated use of shortcuts for form input. Since someone could find out the purpose of a script and trick it by using a link with a query string, you might consider only using the long forms.

Before virtually leaving the client computer, you need to consider that information entered into the forms of your application might be left on that computer after the person has left. This could happen because of cookies you implemented, cached pages, or the actions of the operating system that asks the users if they want to save information entered into a form. You really cannot protect your clients from themselves, but you might consider issuing a warning to erase cached pages and provide an option to not create a cookie if the person is on a public computer. The mini-application in the Examples section contains a script that ends the session, thus destroying the authentication setting that allowed access to the application pages.

The next stage for data is to travel over the communications links to the server. The topic is beyond the scope of this text, but you can arrange for a secure connection and a secure server. This will mean that form data and the request for the page itself will be encrypted and then decrypted. The information, whether it is passed via the query string or the HTTP headers, will be hidden from prying eyes.

You might choose to reconfirm the authenticity of the user on each script of your application. This would be appropriate if different scripts required different levels of rights. In addition, checking on each page would prevent malicious action from someone navigating directly to a particular script through special knowledge of the application.

The next stage of security concerns the database itself. The issue is very delicate, because the main category of trespasses are members of your own organization. What you must rely on here are facilities of the database products and your own code and procedures. One technique to consider for passwords is to store them in an encrypted form. Programs (md5 and crypt) exist for so-called one-way encryption. The “one-way” refers to the fact that there does not exist an easy way to get the original plain text back from the encrypted text. You might now ask how you make use of this. The answer is that you store the original password in the encrypted format and compare only encrypted versions. This will be shown in the Examples section.