Conclusion
Bibliography
References
Glossary
Introduction
This report is about how to link a database to a web page, so that people who visit the web pages can extract information from the database or add data to it. I will discuss the various problems, including security and benefits to the webmaster, what solutions are available, and the cost. I will also give some examples of web sites using databases, and I will show how to develop a simple DBI (database interaction) web site.
I will assume that the reader has knowledge of HTML. When giving some example HTML I will not include the usual <HTML> and <BODY> tags.
All Internet links shown in this report are available from: www.cse.dmu.ac.uk/~hc97ds1/ass/Year3Sem5/CPRJ2052/index.html
You are reading the web version of this report. These pages were automatically created by MS Word, so the HTML will, and is very sloppy.
What?
HTML (HyperText Mark-up language) allows the web author to create web pages where the content is static. To change the contents of the page the author has to edit the page. A web page where the author needs to do this is called a static page, its contents never change. A dynamic page is different. The contents of the page can be changed without the author of the page having to edit it. How the change is made and what the effect of the change is can vary, but for the purposes of this report we are only interested in using data from a database to cause changes in the data.
Integrating a database to a web page is where a web page is designed so that data from a database is displayed and looks like part of the web page.
The completed web page can either be published on a web site for the public to visit, or be part of a company Intranet providing up to date information to employees.
Why
There are many advantages and some potential disadvantages to integrating a database into your web page.
Advantages
| Query | The most obvious use of the ability to search the database would be a user initiated search, for example searching a product database. However it can also be used to provide up to date data. For example a web site that displays share prices can reliably keep the prices current. |
| Updateable | If the database is changed the web page immediately changes as well. Take for example a fan web site which contains lots of pictures from the TV show Star Trek. Such a site is usually manually created by typing the HTML tags. However if the user had access to a web server that allowed database connectivity the user would be able to add more pictures easily, without needing to use edit the HTML code. |
| Image | If the company is a reasonably small it may impress many users that they have a web site, and that their site has the ability to be searched. |
Disadvantages
| Redevelopment time and cost | Converting an existing web site to take advantage of database integration takes time, and time is money. |
| Additional maintenance | In addition to the potential for your web page to be hacked, there is now the possibility of hacking the database.
|
| Security | Allowing the public to access your database is opening your system to potential attack.
|
| Server load | Each database query requires additional processor time, the more popular the web site the higher the load. This additional load can be removed by using a separate computer to run the database. |
| Additional cost | As well as financing the web server, you now need some database software, and software to allow the integration of the database. Users will also need to be re-trained to use the new system. |
How?
There are many different techniques for connecting a database and a web page. They all follow a similar technique however.
Typically the following steps are followed when requesting data:
- A connection to the database is established. This tells the database to expect to receive some commands. This will if necessary open the database.
- An SQL (Structured Query Language) query is created. For example "SELECT * From Table" would be used to return all records from the table. However a more complex example such as a search engine would initially require some input from the user. The input is then used to build the WHERE clause. This step can be performed before the connection to the database is established.
- The SQL is issued to the database.
- The database returns the data. Once the data is returned what is done with it is dependent upon what the web designer required.
All the steps are run on the web server. If the user is sending, or posting, data from the web page so that it can be added to the database then these are the steps:
- Establish a connection with the database.
- Create the SQL command.
- Issue the SQL.
- Wait for a result from the database.
To send data the user needs to be able to enter data somewhere I shall discuss this later in the chapter titled Entering data.
Most connectivity solutions are more than simply a system to allow connection to a database. They are full scripting languages. I will discuss all parts of the solution that are connected to database connections.
Regardless of what DBI (DataBase Interaction) solution is most appropriate to your needs you need to ensure that you have access to a web server that supports your needs. If you are running your own server this is not a problem as you can configure the server to meet your own needs. However if you are using a server that you do not administer you are restricted to what the server supports.
Active Server Pages
Active Server Pages (or ASP) is Microsoft’s solution for querying a database. It is capable of much more than that however, it is a full scripting language. ASP pages can only be viewed properly if the web site is running Microsoft’s Internet Information server (IIS), or Microsoft’s personal web server.
It is possible to use many different scripting languages including, Java, Visual Basic, and JavaScript. Installing additional third party software allows you to use other languages such as Perl and REXX.
To use ASP, HTML tags like "< %" and " % >" are used. Any text between these marks is interpreted by the web server as a command. The following example displays the current date and time on the server the script was run:
<HTML><BODY>
<% =Now() %>
</BODY></HTML>
Perhaps the main disadvantage of ASPs is that the code is not removed from the page, possibly allowing users to copy the script and examine it looking for potential security weaknesses.
For ASP pages to work with a given web server additional software needs to be installed, currently this software is only available for MS Windows based systems.
CGI
CGI (Common Gateway interface) is in its self not a system that allows database connectivity. CGI is a standard that allows the creation of dynamic pages. When a web age refers to a CGI script the web server loads, and runs the script. The output of the script is what is returned to the user.
A CGI script can be in many different formats, however the two most common are PERL and C. If the script was written with Perl then the script is interpreted when run, if the script is written in C then an executable program is run (on the server).
For example to create a web page that displays the "Hello World" message a C program like shown below would be used. (BRAIN, M 1998)
#include <stdio.h>
int main()
{
printf("Content-type: text/html\n\n");
printf("<html>\n"); printf("<body>\n");
printf("<h1>Hello there!</h1>\n");
printf("</body>\n");
printf("</html>\n");
return 0;
}
The result of this script can be viewed at http://www.howstuffworks.com/cgi-bin/simplest.cgi. The equivalent Perl script is (BRAIN, M 1998)
#! /usr/bin/perl
print "Content-type: text/html\n\n";
print "<html><body><h1>Hello World!</h1></body></html>\n";
The result of this script is identical to the previous example, and can be viewed at http://www.howstuffworks.com/cgi-bin/simplest.pl. These two examples do not serve much of a purpose as they can be replicated with a static web page.
To achieve a connection to a database then Perl should be used because it provides a database interface as a standard component of the language whereas C does not.
|
(DOMINUS, M 1999) |
PERL uses a Database Interface Module (DBI) to handle the connection with a database driver. This driver knows how to talk to a single specific database format.
All the programmer need know is how to use the DBI, not the specific database driver. The diagram below shows how a program can use either an Oracle database, or a text database, the end result is the same but the implementation of the database is different.
An advantage of CGI is that users cannot view the CGI script (unless the web server is badly configured). This means that users cannot examine the script for security flaws or modify the script.
CGI is an open standard and is implemented with all serious web servers. However a PERL CGI script does need PERL to be installed and configured with the web server. C based CGI programs however need to be re-compiled for the specific operating system – for example a CGI program compiled for use with Linux will not work with Windows until is has been re-compiled.
Java
Java is a relatively new programming language and like PERL offers database connectivity as a standard feature. Like PERL Java uses drivers to access different database formats.
The Java class used for database access is java.sql and its many subclasses. However due to security restrictions an applet can only make connections to the web server that the web page originated from and even then cannot use this connection for DBI purposes. To allow this type of client/server interaction the applet needs to be signed and the user needs to allow the applet to run. This effectivly means that Java is not suitable for DBI because the user can disable the database connection.
[NOTE: 8/5/2000: This isn't actually the whole story; Java servlets can be used to achieve the desired DBI interactions. Therefore Java is well suited to DBI. More information on this can be found in a later assignment here]
PHP
"PHP (officially ‘PHP: Hypertext Preprocessor’) is a server-side HTML-embedded scripting language." (SÆTHER BAKKEN, S 1999). What this means is that the web server processes each web page before sending it to the user. If the page being served contains PHP a script (or scripts) then the scripts are executed and the results placed into the page that is sent to the user.
The PHP language is fairly simple and has many similarities to C and PERL. A simple example to display the message "Hello World" within a web page is:
<?php echo "Hello World"?>
When the user views the source for the page the custom PHP tags have been removed and replaced with "Hello World". I will cover PHP in more detail later.
Because the PHP script is removed from the web page the user cannot examine the code and might not even be aware that the page has been created dynamically.
PHP is primarily a Unix based system that has been ported to Windows. So whilst it is possible to use the Windows version its speed and reliability is not the same as the Unix version.
A Web Server
Most web servers have a built-in scripting language. The capability of the scripting language varies greatly between individual web servers. I will look at two common web servers Apache and Sambar. Both are freely available for many different operating systems.
Apache
Although it is a very popular web server Apache does not by default include any DBI capabilities; it can be expanded to do so however using the various methods mentioned above.
Apache is available for many different operating systems but, according to the documentation, only runs at optimal speed when running in a Unix environment.
Sambar
The Sambar web server uses instructions that are embedded within the HTML code, the instructions are replaced with the result of the instruction and are not sent to the person viewing the document. Sambar can handle several different kinds of embedded instructions, it interprets SQL statements, interprets scripts, and can use conditional logic and text replacement and server variables to enhance user server interactions.
Like PHP the embedded instructions are removed before the page is sent to the user.
Entering data.
Fortunately whichever solution is used to interact with the database the method for entering the data to the web page is the same. This is because HTML is used to create a form that the user uses to enter the data.
Forms are contained within two HTML tags <form> (start form) and </form> (end form). Alone these tags will not achieve much (an empty page), they simply tell the users web browser where a form begins and ends. This allows more than one form per web page. The form tag has one required argument ‘action’ this tells the user agent where to send the data contained in the form when the user clicks on the submit button.
HTML does not impose any special requirements with the design of a form, but does offer two special button controls, submit, which sends the data in the form, and clear, which resets the form values to their defaults.
When the user clicks on the submit button the user agent sends the forms data to the URL specified in the action attribute. The format of the data depends upon the method attribute, which can either be post or get. If the method is get then the data is appended to the end of the URL specified in the action attribute, for example using Yahoo (www.yahoo.co.uk) I filled the search form with the string "database AND internet". The URL once the form had been submitted was:
‘http://uk.search.yahoo.com/search/ukie?p=database+AND+internet&y=y’
From this we can see that the form consists of two elements called ‘p’ and ‘y’. The forms action attribute contained ‘http://uk.search.yahoo.com/search/ukie’. The contents of the form were appended to this. The get method is limited for ASCII codes.
If the post method is used the data sent is hidden from the user. To send the data to the URL specified in the action attribute the user agent performs a HHTP Post operation. Although the average user cannot see the this operation it is possible to view data sent with this method. Because of this any personal data (i.e. credit card numbers) should be encrypted.
"If the service associated with the processing of a form causes side effects (for example, if the form modifies a database or subscription to a service), the "post" method should be used." (RAGGET, D 1998). The reason for using the post method is to hide the method in which the data is added to the database. Although it is possible for a determined user to view the data being sent it does at least prevent the casual user having a look. I will discuss this further in the user privacy section.
Security
Whatever system is used to provide the connectivity security is a serious concern. If users are allowed to post data to the database then you are effectively allowing users to write data to your server. Even if the database is only accessible on a local Intranet securing the database is still important.
Incorrect or invalid data
It is always important to check the data that the user has entered. This is particularly important when in a situation where the public has the ability to use you database. If a database did not have any validation of the users input you could find users adding irrelevant or inappropriate data.
Because all data is entered using a standard method the possible methods for validating the inputted data is greatly reduced to two options. You can either validate the data when the user sends in to the database, validate it when they click on the send button (but before the data is sent), or do both.
The diagram to the right shows a typical data validation setup where data is being validated at the user and server end. Using both validation opportunities might initially seem rather pointless. However using both opportunities to validate data gives the following advantages:
- The users is immediately informed that the data is invalid without needing to wait whilst a page loads. This also reduces the load on the server.
- Increased security. If you were only performing the data validation on the users computer the validation can be bypassed.
By default HTML does not offer any data validation capabilities. It does though provide a number of JavaScript events. Each form element has a number of JavaScript events. For example the INPUT element has onfocus(), onblur(), onselect() and onchange(), events. So when the user changes the control the onchange event is generated, which causes some code to be run. This code can then check that the data is suitable. However immediately validating the data as the user enters it may be distracting to the user who may not notice that a message has been displayed by the computer. For this purpose the input tags events should not be use for this purpose.
The form tag has an event called onsubmit which is generated when the user clicks on the submit button. This can then be used to process all the forms controls and if there are any fields containing invalid data the user is informed.
If the users of the database are only able to read data from it, and not add data, then there only needs to be a relatively small amount of validation of the users input. This is because the user will not be able to add or change any data. Checking the data from the users should be to ensure that they don’t submit any queries that might break the system. This could potentially be achieved by sending too much data.
Server Access
The ability to add data to a database requires that users can write data to the database file. To be able to do this the file must be writable. This is not suitable for a secure system, and so steps must be taken to limit the ability for the system to be broken into.
The ability to gain access to the server can be broken down into two categories. Accidental access, where a user unintentionally gains access to the server due to an error in the system. Deliberate access, where a user intentionally gains access. Loomis, M (1987) lists many deliberate security threats:
- Storage devices maybe stolen
- A user may gain access to a company Intranet therefore also gaining access to the database.
- A user may gain unauthorised access to the computer room, or bypass the security mechanisms of the OS or DBMS.
- A user may eavesdrop on the data. (See the diagram below).
Action can be taken to prevent the above threats, however the following items are much harder to secure against:
- Storage devices can be copied without the DBMS running. If database security is the sole responsibility of the DBMS then copying maybe unauthorised.
- A user may masquerade as a more privileged user, logging in by using someone else’s identification and password.
- An authorised user may sell or give data to unauthorised users.
Threat 1, can be protected against by securing the location that the computer is in and by ensuring that no one can remotely access the computers file system. Threats 2 and 3 however are perpetrated by authorised users of the system and it is therefore harder to protect data from these users. I will cover this in the next section.
User privacy
Many web sites require users log in. The purpose for this is usually to restrict access to particular users, or to retrieve existing data about the user (for example an online shop). This data must be stored securely. "The Data Protection Act 1984 requires personal data to be kept securely and confidentially; this implies that some mechanism must exist to authorise and control access to such data" (CAELLI, W 1994). The Data Protection Act 1984 also states that you must be allowed to view any data held about yourself.
There are three stages where a users personal data is liable to being viewed by a third party:
- When the user enters them. A lot people access the Internet in a busy environment with lots of people around. Any one of these people could be watching what the user is doing. At this stage the security of their personal data is up to the user. Additionally user agents often cache files on the users local computer. When designing a web page that displays or prompts a user for their details it is important to a) not use cookies to save user information to their local disk, and b) encrypt the data. Doing this ensures that the user agent will not cache the file.
- During transmission to the database. It is theoretically possible to intercept or tap into a users web connection. It is not possible to stop someone from doing this but the data they intercept can be encrypted so that only the web server can decrypt it.
- Whilst stored in the database. There are two ways a potential intruder can access the data while it is stored in the database. They can impersonate the user or gain physical access to the database, either sitting at the database computer or telneting to it. To actually sit in front of the computer the intruder would need to either work at the company holding the data for break into the building where the computer is.
If they are an employee of the company then assuming they have existing access to personal data then there is nothing that can be done to prevent the privacy violation from happening. Steps should be taken however to minimise the risk of this happening by a) establishing a clear disciplinary procedure and making the employees aware of it and b) reducing the number of users that have access to restricted data to as few people as is viable.
Employees who do not have access to the personal data and people who gain access to the compute system (either physical access or via Telnet) fall under the same heading. Hacking is where a person tries through either trail and error or exploiting security holes to gain access. In this case encryption should be used to reduce the ability for these people to view the data should they gain access to the database.
Encryption
"Encryption can be defined as the process of taking information that exists in some readable form and converting it into a form so that it cannot be understood by others." (HARE, C and KARANJIT, S 1996)
There are two parts to the encryption process, the algorithm and the key. Everybody knows the algorithm, but the key should be kept secret or shared only with trusted people or organisations. Generally the recipient and the sender only know the key.
In relation to this report there are two areas where encryption should be used, when personal data is being submitted by a user and encryption of the database itself.
The strength of the encryption can be measured in bit, the higher the number the stronger the encryption. Typical encryption strength is either 40 or 56bit, stronger encryption is not easily available outside of the United States, who enforce strong export controls on any software offering encryption greater than 56bit (i.e. it cannot be exported). This effects the two most popular web browsers, Netscape Navigator, and Internet Explorer. However the US law does not effect the web browser Oprea, as it is not published from the US.
To encrypt data being submitted by users the system would need to offer at least two possible encryption strengths, either 56 or 128bit. This would offer American users the strongest available encryption while also offering the rest of the world the strongest than is commonly available to them.
Encrypting the database is not an appropriate security measure because the data would need to be routinely decrypted when a user tries (legitimately) to access it.
CGI
Because CGI allows programs to be run on the server security is a major concern. The best method to ensure security is to ensure that the programs are well written, and expect and deal with all unusual input, not an easy task.
An example backdoor takes advantage of the pipe feature of the Unix shell. For example the command "whatever | rm *". The ‘rm *" is evaluated first, and the output is sent to the program ‘whatever’. Unfortunately this results in all the files in the current directory being deleted.
Examples
Perhaps one of the most obvious areas of on-line activity that uses databases is e-commerce. I have tried to select a broad range of examples of online databases. It is not my intention to recommend any of the web sites in any way.
