CISC474, Reading Notes for Sebesta,
Programming The World Wide Web, 3rd Edition

Reading notes by Phill Conrad, Asst. Professor, CIS Dept. University of Delaware

Chapter 14: Database Access through the Web

• http://www.w3schools.com/sql/default.asp

The chapter is divided into three parts:

So the game plan for now, is to read Part 1, and then skip to Section 14.7 about Database access from Java.

(I will not comment on sections 14.5 and 14.6 until after I've filled in the commentary for Chapter 10 on Perl, and Chapter 12 on PHP.)

14.1 Relational Databases

mysql> create database sebesta
 -> ;
 Query OK, 1 row affected (0.00 sec)
mysql> 

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON sebesta.* TO 'pconrad'@'localhost';
     Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON sebesta.* TO 'pconrad'@'%.cis.udel.edu';
       Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON sebesta.* TO 'pconrad'@'strauss.udel.edu';
       Query OK, 0 rows affected (0.00 sec)

mysql> 

       GRANT SELECT ON sebesta.* TO 'oanat'@'localhost' 
       GRANT SELECT ON sebesta.* TO 'oanat'@'%cis.udel.edu' 
       GRANT SELECT ON sebesta.* TO 'oanat'@'strauss.udel.edu' 

       GRANT SELECT ON sebesta.* TO 'oana'@'localhost' 
       GRANT SELECT ON sebesta.* TO 'oana'@'%cis.udel.edu' 
       GRANT SELECT ON sebesta.* TO 'oana'@'strauss.udel.edu' 

       GRANT SELECT ON sebesta.* TO 'toy'@'localhost' 
       GRANT SELECT ON sebesta.* TO 'toy'@'%cis.udel.edu' 
       GRANT SELECT ON sebesta.* TO 'toy'@'strauss.udel.edu' 

       GRANT SELECT ON sebesta.* TO 'christia'@'localhost' 
       GRANT SELECT ON sebesta.* TO 'christia'@'%cis.udel.edu' 
       GRANT SELECT ON sebesta.* TO 'christia'@'strauss.udel.edu' 

...

mysql> set password for 'pconrad'@'localhost' = password('secret');
 	Query OK, 0 rows affected (0.00 sec)
mysql>

mysql> set password = password('secret');
 	Query OK, 0 rows affected (0.00 sec)
mysql>

To create the Equipment table shown in Figure 14.2 of Sebesta, I used the following command (which comes from the bottom of p. 579, Section 14.4)

mysql> use sebesta
 	     Database changed
 	     mysql> create table Equipment ( 
 -> Equip_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, Equip CHAR(10) );
 Query OK, 0 rows affected (0.01 sec)

mysql> 

To insert the first three rows into the table, we can use the following command (the AUTO_INCREMENT part automatically inserts the numbers 1, 2, 3, etc. as explained at the top of p. 580 in Sebesta.) We insert NULL in place of the value that gets auto-incremented.

mysql> insert into Equipment values (NULL,'Automatic');
        Query OK, 1 row affected (0.00 sec)

mysql> insert into Equipment values (NULL,'4-speed');
 	     Query OK, 1 row affected (0.00 sec)

mysql> insert into Equipment values (NULL,'5-speed');
 	     Query OK, 1 row affected (0.00 sec)

The following command can be used to show what we've inserted into the table so far:

mysql> select * from Equipment;
 	     +----------+-----------+
 	     | Equip_id | Equip     |
 	     +----------+-----------+
 	     | 1        | Automatic |
 	     | 2        | 4-speed   |
 	     | 3        | 5-speed   |
 	     +----------+-----------+
 	     3 rows in set (0.00 sec)

Another way to insert is to just specify that we are only inserting into the field Equip, like this:

mysql> insert into Equipment(Equip) values ('6-speed');
 	     Query OK, 1 row affected (0.00 sec)

This shows the table after we add an additional row:

mysql> select * from Equipment;
 	     +----------+-----------+
 	     | Equip_id | Equip     |   
 	     +----------+-----------+
 	     | 1        | Automatic |
 	     | 2        | 4-speed   | 
 	     | 3        | 5-speed   |  
 	     | 4        | 6-speed   | 
 	     +----------+-----------+
 	     4 rows in set (0.00 sec)
mysql>

The commands to insert the remaining rows are left as an exercise to the reader. After doing those, we get the following table:

mysql> select * from Equipment;
+----------+-----------+
| Equip_id | Equip     |
+----------+-----------+
|        1 | Automatic |
|        2 | 4-speed   |
|        3 | 5-speed   |
|        4 | 6-speed   |
|        5 | CD        |
|        6 | Leather   |
+----------+-----------+
6 rows in set (0.00 sec)
mysql> 

Doing a select command on the Equipment table yourself

If you want to see the same command yourself, you can do the following commands yourself:

1. First, ssh into strauss.udel.edu with your strauss account. Then use the command:

   ssh username@jaguar.cis.udel.edu

   to login to jaguar.cis.udel.edu. If you are on an X terminal, use the command

   ssh -X username@jaguar.cis.udel.edu

   so that you can open X-windows, use your mouse and so forth.

2. Set the environment variable MYSQL_HOME as follows:

   setenv MYSQL_HOME /jaguar/cisc474/mysql

   Note that later on, if you are running on porsche.cis.udel.edu, or a client of porsche such as acura, toyota, etc. you need to set this variable instead as follows:

   setenv MYSQL_HOME /porsche/cisc474/mysql

   You need to use /porsche even if you are on acura or toyota, etc.—don't use /acura/cisc474/mysql. One way to correctly set these environment variables in your .cshrc file is as follows:

   if ( -d /jaguar/cisc474/mysql ) then
      setenv MYSQL_HOME /jaguar/cisc474/mysql
   else if ( -d /porsche/cisc474/mysql ) then
      setenv MYSQL_HOME /porsche/cisc474/mysql
   endif

   Remember to type at the Unix command prompt source .cshrc after you change your .cshrc file so that the settings take effect (or you can log off and log back on again.)

3. Run the command as shown below. What you type is in bold. You'll be prompted for your password, as shown:

   jaguar[84] > mysql  -p
   Enter password: xxxxx
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 42 to server version: 5.0.18-standard-log
   Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
   mysql> 

    

4. You should then be able to run the following commands to select the sebesta database, and then list the contents of the Equipment table.

   mysql> use sebesta;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
   Database changed
   mysql> select * from Equipment;
   +----------+-----------+
   | Equip_id | Equip     |
   +----------+-----------+
   | 1        | Automatic |
   | 2        | 4-speed   |
   | 3        | 5-speed   |
   | 4        | 6-speed   |
   | 5        | CD        |
   | 6        | Leather   |
   +----------+-----------+
   6 rows in set (0.00 sec)
   mysql>

To get out of the mysql client program, use the command exit.

Creating and populating the Corvettes table

Here are commands to create, populate and list the additional tables shown on pages 568 through 569. Note that you can read more about the CREATE TABLE syntax in Section 13.1.5 of the MySQL 5.0 Reference Manual.

To create the Corvettes table:

mysql> create table Corvettes ( 
    ->   Vette_id int(10) unsigned not null auto_increment primary key default null,     
    ->   Body_style char (12),    
    ->   Miles float(4,1) default 0.0,    
    ->   Year int(10) unsigned default 0,    
    ->   State int(10) unsigned default 0 );
Query OK, 0 rows affected (0.00 sec)
mysql> 

To insert the first few rows into the table, we use the commands below. Notice how the auto-increment choose the primary key values for us. Also note that we can "hit the up arrow" after each mysql command and just edit the command on the command line; we don't have to retype the full command each time.

mysql> insert into Corvettes (Body_style,Miles,Year,State) 
    -> values ('coupe',18.0,1997,4);
Query OK, 1 row affected (0.01 sec)

mysql> insert into Corvettes (Body_style,Miles,Year,State)  values ('hatchback',58.0,1996,7);
Query OK, 1 row affected (0.00 sec)

mysql> insert into Corvettes (Body_style,Miles,Year,State)  values ('convertible',13.5,2001,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from Corvettes;
+----------+-------------+-------+------+-------+
| Vette_id | Body_style  | Miles | Year | State |
+----------+-------------+-------+------+-------+
|        1 | coupe       |  18.0 | 1997 |     4 |
|        2 | hatchback   |  58.0 | 1996 |     7 |
|        3 | convertible |  13.5 | 2001 |     1 |
+----------+-------------+-------+------+-------+
3 rows in set (0.00 sec)

mysql> 	   

We can also insert multiple rows at the same time in this way by separating the "rows" by commas:

mysql> insert into Corvettes (Body_style,Miles,Year,State) 
    ->  values ('hatchback',19.0,1995,2), 
    ->  ('hatchback',25.0,1991,5), 
    ->  ('hardtop',15.0,2000,2); 
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from Corvettes;
+----------+-------------+-------+------+-------+
| Vette_id | Body_style  | Miles | Year | State |
+----------+-------------+-------+------+-------+
|        1 | coupe       |  18.0 | 1997 |     4 |
|        2 | hatchback   |  58.0 | 1996 |     7 |
|        3 | convertible |  13.5 | 2001 |     1 |
|        4 | hatchback   |  19.0 | 1995 |     2 |
|        5 | hatchback   |  25.0 | 1991 |     5 |
|        6 | hardtop     |  15.0 | 2000 |     2 |
+----------+-------------+-------+------+-------+
6 rows in set (0.00 sec)

mysql> 

However, even this eventually gets tedious, and we may want, instead, to edit a file in which we insert all the mysql commands and then run those all at once. We can use emacs, vi, or any other ASCII text editor to create the following file and call it insertRowsIntoCorvettes.sql

insert into Corvettes (Body_style,Miles,Year,State) values ('coupe',55.0,1979,10);
insert into Corvettes (Body_style,Miles,Year,State) values ('convertible',17.0,1999,5);
insert into Corvettes (Body_style,Miles,Year,State) values ('hardtop',17.0,2000,5);
insert into Corvettes (Body_style,Miles,Year,State) values ('hatchback',50.0,1995,7);

You can then execute these SQL commands all at once using Unix input redirection at the command line. The example below shows this. Note that you still type in your password interactively; it does not go in the file.

jaguar[107] > ls
insertRowsIntoCorvettes.sql
jaguar[108] > cat insertRowsIntoCorvettes.sql
# P. Conrad, SQL commands to populate Corvettes table from
# Sebesta p. 568 (last four rows)

use sebesta;

insert into Corvettes (Body_style,Miles,Year,State) 
               values ('coupe',55.0,1979,10);

insert into Corvettes (Body_style,Miles,Year,State) 
               values ('convertible',17.0,1999,5);

insert into Corvettes (Body_style,Miles,Year,State) 
               values ('hardtop',17.0,2000,5);

insert into Corvettes (Body_style,Miles,Year,State) 
               values ('hatchback',50.0,1995,7);
jaguar[109] > mysql -p < insertRowsIntoCorvettes.sql
Enter password: xxxxxxx
jaguar[110] > mysql -p 
Enter password: xxxxxxx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49 to server version: 5.0.18-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sebesta;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from Corvettes;
+----------+-------------+-------+------+-------+
| Vette_id | Body_style  | Miles | Year | State |
+----------+-------------+-------+------+-------+
|        1 | coupe       |  18.0 | 1997 |     4 |
|        2 | hatchback   |  58.0 | 1996 |     7 |
|        3 | convertible |  13.5 | 2001 |     1 |
|        4 | hatchback   |  19.0 | 1995 |     2 |
|        5 | hatchback   |  25.0 | 1991 |     5 |
|        6 | hardtop     |  15.0 | 2000 |     2 |
|        7 | coupe       |  55.0 | 1979 |    10 |
|        8 | convertible |  17.0 | 1999 |     5 |
|        9 | hardtop     |  17.0 | 2000 |     5 |
|       10 | hatchback   |  50.0 | 1995 |     7 |
+----------+-------------+-------+------+-------+
10 rows in set (0.00 sec)

mysql> 		 

A key tip for working with SQL

When setting up the schema for the database that you are using for some application, and inititially populating the tables with data, you can either

• do it by hand at the command line, on-the-fly, or
• create files and use redirection in the way just shown.

However, I want to put in a plug for using the second method—creating the files.

The reason is that if you do it this way, you can easily make a script that will allow you to wipe out the database, and recreate it from scratch. This is very useful when, for example,

• you want to move the entire application from one server to another
• you want to make a script that will "automatically install" some web application on a server, including the database and the HTML, PHP, and/or Java code tht goes along with that database.
• you want to refactor the database by altering its structure some how

Creating and populating the other two tables

For the other two tables, I'll just show the SQL commands. These could be written into two files called createAndPopulateStateTable.sql and createAndPopulateCorvettesEquipmentTable.sql. Those SQL commands could then be executed in the same way as was described earlier on this web page.

Listed below are the contents of createAndPopulateStateTable.sql. Note that before we create the table States, we issues a command

drop table if exists States;

This says, in effect, "if the table States already exists, get rid of it; we are going to start from scratch." This is a useful line to put in SQL command files such as these, because that makes them "repeatable".

# P. Conrad, SQL commands to create and populate 
# States table from Sebesta Figure 14.4, p. 568.

# also see top of p. 571 (section 14.2.1) for Schema

use sebesta;

drop table if exists States;

create table States (
   State_id INTEGER PRIMARY KEY NOT NULL,
   State char (20));


insert into States values (1,'Alabama');
insert into States values (2,'Alaska');
insert into States values (3,'Arizona');
insert into States values (4,'Arkansas');
insert into States values (5,'California');
insert into States values (6,'Colorado');
insert into States values (7,'Connecticut');
insert into States values (8,'Delaware');
insert into States values (9,'Florida');
insert into States values (10,'Georgia');

select * from States;

Here's the contents of createAndPopulateCorvettesEquipmentTable.sql

# P. Conrad, SQL commands to create and populate 
# Corvettes_Equipment table from Sebesta Figure 14.5, p. 569.


use sebesta;


drop table if exists Corvettes_Equipment;


create table Corvettes_Equipment (
   Vette_id INTEGER NOT NULL,
   Equip INTEGER NOT NULL);


# As you can see, line breaks can be inserted
# wherever convenient; only the semicolon ends
# the SQL command.   Here we use line breaks
# to  group all the rows that go with the same
# Vette_id.


insert into Corvettes_Equipment values 
 (1,1), (1,5),  (1,6),
 (2,1), (2,5),  (2,6),
 (3,1), (3,6),
 (4,2), (4,6),
 (5,1), (5,6),
 (6,2),
 (7,4),  (7,6),
 (8,4), (8,5),  (8,6),
 (9,4), (9,5), (9,6),
 (10,1), (10,5);
 
select * from Corvettes_Equipment;

Showing how it all fits together

The following shows how all the tables fit together to list all the available Corvettes with their locations and available equipment. This is an illustration of something called a join, where you bring multiple tables together, and also the use of an order by clause to sort the resulting rows.

jaguar[130] > mysql -p
Enter password: xxxxx
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57 to server version: 5.0.18-standard-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sebesta;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select Corvettes.Vette_id, Body_style, Miles, Year, States.State, Equipment.Equip 
    ->   from Corvettes, Equipment, States, Corvettes_Equipment 
    ->   where Corvettes.Vette_id = Corvettes_Equipment.Vette_id 
    ->     and Corvettes_Equipment.Equip = Equipment.Equip_id 
    ->     and Corvettes.State = States.State_id 
    ->   order by Corvettes.Vette_id;
+----------+-------------+-------+------+-------------+-----------+
| Vette_id | Body_style  | Miles | Year | State       | Equip     |
+----------+-------------+-------+------+-------------+-----------+
|        1 | coupe       |  18.0 | 1997 | Arkansas    | Leather   |
|        1 | coupe       |  18.0 | 1997 | Arkansas    | Automatic |
|        1 | coupe       |  18.0 | 1997 | Arkansas    | CD        |
|        2 | hatchback   |  58.0 | 1996 | Connecticut | CD        |
|        2 | hatchback   |  58.0 | 1996 | Connecticut | Leather   |
|        2 | hatchback   |  58.0 | 1996 | Connecticut | Automatic |
|        3 | convertible |  13.5 | 2001 | Alabama     | Automatic |
|        3 | convertible |  13.5 | 2001 | Alabama     | Leather   |
|        4 | hatchback   |  19.0 | 1995 | Alaska      | Leather   |
|        4 | hatchback   |  19.0 | 1995 | Alaska      | 4-speed   |
|        5 | hatchback   |  25.0 | 1991 | California  | Automatic |
|        5 | hatchback   |  25.0 | 1991 | California  | Leather   |
|        6 | hardtop     |  15.0 | 2000 | Alaska      | 4-speed   |
|        7 | coupe       |  55.0 | 1979 | Georgia     | Leather   |
|        7 | coupe       |  55.0 | 1979 | Georgia     | 6-speed   |
|        8 | convertible |  17.0 | 1999 | California  | Leather   |
|        8 | convertible |  17.0 | 1999 | California  | CD        |
|        8 | convertible |  17.0 | 1999 | California  | 6-speed   |
|        9 | hardtop     |  17.0 | 2000 | California  | Leather   |
|        9 | hardtop     |  17.0 | 2000 | California  | CD        |
|        9 | hardtop     |  17.0 | 2000 | California  | 6-speed   |
|       10 | hatchback   |  50.0 | 1995 | Connecticut | Automatic |
|       10 | hatchback   |  50.0 | 1995 | Connecticut | CD        |
+----------+-------------+-------+------+-------------+-----------+
23 rows in set (0.01 sec)

mysql> 

Hopefully this helps you to see how all of this fits together. The following sections will walk you through some of the details that may be missing in the examples above.

14.2 An Introduction to the Structured Query Language

Some things you should get out of the intro to this section

• The two different ways that people pronounce the letters SQL
• The rules for upper vs. lower case in SQL (which things are case-sensitive, and which are not)
• The rules for whitespace
• How character strings are delimited.

14.2.1 The CREATE TABLE SQL Command

As the book points out, each database vendor (Oracle, MySQL, etc.) has its own slight variations on syntax. So be sure to look over the documentation for CREATE TABLE for MySQL 5.0 as well.

14.2.2 The INSERT SQL Command

We've covered the INSERT syntax pretty extensively in the examples in the reading notes for Section 14.1, so there's not much more to say here.

14.2.3 The SELECT SQL Command

.. in any of those cases, you may want to brush up a bit. The web site w3schools.com has an SQL tutorial that will walk you through the basics of the SELECT command, as well as the other commands listed in Section 14.2 of Sebesta.

14.2.4 The UPDATE SQL Command

14.2.5 The DELETE SQL Command

14.2.6 The DROP SQL Command

14.2.7 Joins

In Section 14.1 of these reading notes I showed you how to access a read-only copy of the sebesta database which contains the same tables shown in this section of the book. Use that to try out the joins shown in this section, and try some joins of your own until you are comfortable with the idea.

14.3 Architectures for Database Access

14.3.1 Client/Server Architecture

14.3.2 Database Access with Embedded SQL

14.3.3 The Microsoft Access Architecture

14.3.4 The Perl DBI/DBD Architecture

14.3.5 PHP and Database Access

14.3.6 The Java JDBC Architecture

14.4 The MySQL Database System

As of this writing, the URL the book cites, www.mysql.org, actually redirects to dev.mysql.com. Although MySQL is freely available, it is owned and maintained by a private company. According to the web site www.mysql.com:

The company was founded in Sweden by two Swedes and a Finn: David Axmark, Allan Larsson and Michael "Monty" Widenius who have worked together since the 80's. MySQL AB is the sole owner of the MySQL server source code, the MySQL trademark and the mysql.com domain worldwide. The company is privately held and without debt, and it is financed by venture capital since July 2001.

This section does "not discuss how to install or manage MySQL". In fact, we will not do so either in CISC474; we will use an existing installation that is managed by the instructor and the TA. We do this so that you can focus on the main theme of the course, which is web application development.

The main command used at the Unix command line to interact with MySQL is the command mysql, which is described in this section.

The mysql command is shown in the textbook as

mysql [-h host] [-u username] [database_name] [-p]

Compare that with the examples shown earlier in this section, in which we only used:

mysql -p

The reason we can omit the host, username and database name is based on the assumption that we've set the MYSQL_HOME environment variable. Among other things, setting that environment variable directs the mysql command to look in the file ${MYSQL_HOME}/my.cnf for default values to use for the host and port to which to connect. For example, the file /jaguar/cisc474/mysql/my.cnf might contain:

[client]
port=8099
socket=/tmp/cisc474.mysql.sock
host=jaguar.cis.udel.edu

My including this, we can omit the -h host. We can omit the -u username because I just happened to assign usernames that correspond to the usernames on the ECL machines. However, note that the usernames and passwords for MySQL are not connected in any way with your Unix username and password. I just used the same usernames as your ECL account for convenience. If you ever connect to the MySQL database from strauss, you'll have to use the -u username flag.

Be sure to use a DIFFERENT PASSWORD for MySQL!

Also note that your MySQL password is competely separate from your ECL Unix username and password and is kept in a separate database. You should use a DIFFERENT PASSWORD for MySQL from your Unix accounts. We'll end up having to hardcode that password in files on the file system, and you might even need to share that password with other members of your team.

Trying out the commands in this section on the system

The only change you should need to make is this: instead of

use cars;

as shown in the book, type

use sebesta;  

Note that sebesta is a database matching the cars database in Chapter 14 of sebesta—you should have read-only access to this database.

If you want to try creating your own tables, you can first do:

use jsmith;

where jsmith is replaced by your own username. Each of you has her/his own database, within which you have full privileges to create and drop tables.

A few things to know about MySQL from Section 14.4

• Check out the description of the AUTO_INCREMENT constraint on p. 580.
• Note the datatypes CHAR(length), INT, and FLOAT(total,fractional).

14.5 Database Access with Perl and MySQL

A web search on keywords such as "SQL Injection" will turn up some information to help illustrate this point. Consider, for example this article by Chris Shiflett about SQL Injection in PHP applications.

Let's just say that while Sebesta's example is useful as a very simple illustration of how to connect to a database and return some results, we should probably NOT use it as an illustration of "web-app best practices."

@@@ More complete commentary deferred until after comments on Chapter 10 are finished. @@@

14.5.1 The DBI Module

@@@ More complete commentary deferred until after comments on Chapter 10 are finished. @@@

14.5.2 An Example

See the comments at the beginning of reading notes on Section 14.5 about the security problem present in this example.

@@@ Commentary deferred until after comments on Chapter 10 are finished. @@@

14.6 Database Access with PHP and MySQL

Please read over the introduction to the reading notes for Section 14.5 before using the code in Section 14.6 of Sebesta as example code for accessing a database with PHP.

@@@ Further commentary deferred until after comments on Chapter 12 are finished. @@@

14.6.1 Potential Problems with Special Characters

@@@ Commentary deferred until after comments on Chapter 12 are finished. @@@

14.6.2 Connecting to MySQL and Selecting a Database

@@@ Commentary deferred until after comments on Chapter 12 are finished. @@@

14.6.3 Requesting MySQL Operations

@@@ Commentary deferred until after comments on Chapter 12 are finished. @@@

14.6.4 A PHP/MySQL Example

@@@ Commentary deferred until after comments on Chapter 12 are finished. @@@

14.7 Database Access with JDBC and MySQL

JDBC is a standard way to access databases from within Java programs. As we'll learn when we study Chapter 7 of "Better, Faster, Lighter Java", it is not the only way to access databases from within Java—there are many reasons why we might prefer to use a persistance framework such as Hibernate—but it is a reasonable place to start in terms of learning about building web apps.

Note that the same advice—i.e. to use something like Hibernate instead of "naked JDBC"—can be found in the MySQL documentation on JDBC, where it says:

"Although JDBC is useful by itself, we would hope that if you are not familiar with JDBC that after reading the first few sections of this manual, that you would avoid using "naked" JDBC for all but the most trivial problems and consider using one of the popular persistence frameworks such as Hibernate, Spring's JDBC templates or Ibatis SQL Maps to do the majority of repetitive work and heavier lifting that is sometimes required with JDBC."

In CISC474, we'll start with JDBC, and then move in the direction of Hibernate once we understand the principles involved.

Getting MySQL to work with JDBC

Some links on getting MySQL to work with JDBC can be found in the file topics/sql/usefulLinks.html

In particular, the component that is needed to get JDBC to work with MySQL is something called Connector/J. The JAR file for Connector/J has been downloaded to the following directories:

• On strauss: /www/htdocs/CIS/software/dist/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar
• On porsche: /porsche/cisc474/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar
• On jaguar: /jaguar/cisc474/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar

This JAR file needs to be in your classpath, so you might do something like the following in your .cshrc

if ( -r /porsche/cisc474/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar ) then
   setenv MYSQL_CONNECTOR_J /porsche/cisc474/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar

   setenv CLASSPATH ${MYSQL_CONNECTOR_J}:${CLASSPATH}
else  if ( -r /jaguar/cisc474/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar ) then
   setenv MYSQL_CONNECTOR_J /jaguar/cisc474/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar

   setenv CLASSPATH ${MYSQL_CONNECTOR_J}:${CLASSPATH}
endif

Be careful though: this needs to be put at the right place in your .cshrc, i.e. after the CLASSPATH has already been set. If you put it before a statement that sets the CLASSPATH to some specific value (i.e. doesn't just add things into the existing definition of CLASSPATH), then it will not have the desired effect.

On strauss, you'd do something similar in your .localenv; figuring that out is left as an exercise for the student.

Some links to resources about how to get Tomcat to work with MySQL can be found in the file topics/tomcat/usefulLinks.htm

14.7.1 Approaches to Using JDBC Outside the Web

Compare the use of the term tier in this section with that in Section 14.3.1.

14.7.2 JDBC and MySQL

This section, by contrast, is a bit more important for our purposes. You'll want to read through it carefully—but, I'd suggest actually reading through Section 14.7.3 and its accompanying reading notes first, because it provides a complete example—one you can actually try out on porsche.cis.udel.edu.

After you work through that section, then come back and read Section 14.7.2, which explains many of the lines of code from the example in more detail.

One important change you'll want to make is this one. Sebesta shows the URL syntax

jdbc:mysql://localhost/cars?user=root

which encodes the user as part of the URL, and then passing the whole thing into the DriverManager.getConnection() method as shown here.

myCon = DriverManager.getConnection(
	                   "jdbc:mysql://localhost/cars?user=root

Instead of doing that, we'll leave the username out of the URL, but specify the hostname and port, as shown here. (This URL also reflects the fact that the name of the database on Jaguar is "sebesta" rather than "cars").

jdbc:mysql://jaguar.cis.udel.edu:8099/sebesta

and pass the username and password in as separate parameters into the DriverManager.getConnection() method, as follows:

 final String URL = "jdbc:mysql://jaguar.cis.udel.edu:8099/sebesta"; 
 final String username = "pconrad"; // change to YOUR username
 final String password = "abcd1234"; // change to YOUR username

 myCon = DriverManager.getConnection(URL, username, password);
     

14.7.3 A Complete JDBC/MySQL Example

1. set your CLASSPATH to include the Connector/J JAR file, as shown in the reading notes for Section 14.7.

2. download the slightly modified version of the code from the book found in readingNotes/Sebesta/ch14/Query.java

   • Helpful Unix Hint: You can copy this file directly from strauss to your current directory on porsche by logging on to strauss and typing the following at the shell prompt. Substitute your strauss username in place of username.

     scp username@strauss.udel.edu:/www/htdocs/CIS/474/pconrad/06S/readingNotes/Sebesta/ch14/Query.java .

3. Edit the file Query.java and change the section of code shown below so that it reflects

   • your MySQL username (which is the same as your ECL username) and
   • your MySQL password (which should be DIFFERENT from your ECL and strauss password—especially since you have to hard code it into a Java file! We'll talk about ways of avoiding that later, but you still need to keep the MySQL password DIFFERENT.)

      final String URL = "jdbc:mysql://jaguar.cis.udel.edu:8099/sebesta"; 
      final String username = "pconrad"; // change to YOUR MySQL username 
      final String password = "abcd1234"; // change to YOUR MySQL password (NOT your Unix password!)

4. Compile the Query.java file and run it at the command line in the normal way. You should get output similar to that shown on p. 604 of Sebesta.

For understanding what is happening in this code, the explanation on p. 601 is helpful to read over, as are the details in Section 14.7.2 and its accompanying reading notes.

14.7.4 Metadata

You can also try these methods by downloading the code from readingNotes/Sebesta/ch14/QueryMetaData.java—this code can be compiled and run in the same manner as the code described in the reading notes for Section 14.7.3.

14.7.5 JDBC and Servlets

Please read over the introduction to the reading notes for Section 14.5 before using the code in Section 14.7.5 of Sebesta as example code for accessing a database from a Java web application.

In particular, the code in this section suggests using a web page such as the carsdata.html file on p. 590 of Sebesta as the input to a servlet that queries a database.

But as many sources will point out, it is considered a very risky practice to design a webapp in a way that allows the user to execute arbitrary SQL commands in the backend. In fact, usually, specific precautions need to be taken to prevent users from being able to inject arbitrary SQL commands into a web app through various hacks.

The reason this matters is that in a real-life web app, typically different users have access to different parts of the database—user "pconrad" has access to orders that he created, and "janniston" has access to orders that she created. Mr. Conrad should not be able to access Ms. Anniston's order data. However, it is typical that all the order data is stored in the same database.

It may seem "nitpicky" to make a big deal out of this when Sebesta is clearly only trying to show a simple example. However, it is important to re-enforce best practices, and discourage poor practices, especially related to security. Many large applications are "grown" from simple examples, and if you start out on the wrong track, it can be difficulty to get back on the right track later on.

So, rather than use the carsdata.html form as a starting point, we'll use a more restrictive form called carForm.html. This form allows the users to specify values for various fields. We'll then use the values on that form construct the query in the back end.

As an challenge exercise, I invite you to read Chris Shiflett's article about SQL Injection in PHP applications, and then see if you can find a way to do an SQL injection attack on my form. If you can successful do so (e.g. to execute a query other than the ones the form allows), and you write up a web page describing how you did it, I'll award 20 points toward the course final grade. To submit this, write this up on a web page, link to it from your personal cisc474 web page, and then send an email to myself and the TA with subject line "CISC474 Sebesta 14.7.4 SQL Injection Challenge".

The carForm.html file

The carForm.html file can be found on strauss in the file /www/htdocs/CIS/474/pconrad/06S/readingNotes/Sebesta/ch14/carForm.html.

Here, we provide access to query based on either the body style (for which we provide a limited menu), or the mileage (for which we provide five relational operators) or both. The mileage field can accept arbitrary text, and for that reason, might leave the servlet open to an SQL injection attack—and I invite you to try (though I'm running it under a user with restricted privleges, so hopefully even if you are able to launch an attack, hopefully you can't do any serious damage.)

I'm using the GET method rather than the POST method so that we can see the parameters in the URL.

The modified JDBCServlet.java (JDBCServlet_v2.java)

First, note that the original JDBCServlet.java source code has a typo on p. 608—it is in the header for the doPost() method. Can you find it? An entire line is missing—what should go in the blank spot?

I've put together an entire development directory (following the example of the Chapter 3 Servlet in HFSJ), including a deploy.sh script (converting this to an Ant build.xml file is left as an exercise for you.)

Our modified version has several changes from the original.

• We specify the package as edu.udel.cis.pconrad.web

• It uses the doGet() method instead of doPost(). In addition to exposing the parameters as part of the URL, this also allows us to validate the resulting HTML returned by the Servlet using the validator at validator.w3.org.(That validator supports the HTTP GET method, but not the POST method.)

• We use valid XHTML 1.1, including the <!DOCTYPE ... declaration at the top.

• We set the character set by doing:

  response.setContentType("text/html;charset=utf-8");

  instead of:

  response.setContentType("text/html");

  
  

• We create our own SQL query by processing the values of the parameters.

If you try deploying this and have no luck, remember to check the logs files under ~/tomcat/logs—especially the tail end of the file catalina.out—for error messages. For example, if your MySQL username and password are incorrectly specified, this is where you are likely to see the error show up.

Some final thoughts about this example JDBC Servlet

This JDBC Servlet is fine for a simple example. However, if you are going to use it as a basis for building a larger web app, you'll need to keep a few things in mind, and look more deeply into certain issues:

• Consider separation of concerns, e.g. using Model-View-Controller or other design pattern to separate out various parts of the problem. This example lumps everything into one big servlet, which is not a good way to build a production application.

• Hard coding the URL, username and password is not the best way to do things. In later chapters of HFSJ, you'll learn how you can specify these things inside the Deployment Descriptor (that web.xml file) so that you can change them without having to recompile your servlet classes.

• When you want a web app to have multiple users and actually stay up for a long period of time, some issues such as Connection Pooling have to be addressed. Otherwise, as some CISC474 students learned last year, what you don't know can bite you in the rear when your otherwise perfect webapp that has been running perfectly for hours suddenly slows to a crawl or starts crashing for no apparaent reason (usually just as you are demoing it to your instructor or TA.)

14.8 Summary

14.9 Review Questions

14.10 Exercises