Objectives:

  • To gain experience using transactions.
  • To learn how to use SQL from within Java via JDBC.

Assignment tools: SQL Server (via Azure) and this starter code

Due date: Thursday, Aug 10 11:00pm.
Turn in your solution using the assignment drop box linked from the main course web page. Warning: This is a long assignment. Do not wait until the last minute to start!

What to turn in:
Submit two files. First, setup.sql should contain the SQL statements you used to create your schema. Second, FlightsDB.java should contain your updated implementation of the client, which uses the schema you created.

After showing your friends the high quality, publicly available data about flights that we worked with in HW2, one of your friends decided to start writing an app that allows people to book flights. In this homework, you will complete that app.

Your friend has already created a command-line client that allows users to type in commands that will search for flights and book them. Unfortunately, having not taken CSE 344, your friend doesn't know how to make the application read and write to the actual database. They will need your help for that.

In this assignment, you will have two broad tasks. First, you need to design the rest of the database that will store information about customers and the flights they have booked. Second, you will need to write the Java code to query and update the database from the command-line client.

Note: Be sure to read this document to the end before starting your work. There is information throughout that could affect your design in the early parts of the assignment.

Getting Started

The command-line application is written in Java. When you download the starter code and unpack it, you will see a number of files:

  • FlightsApp.java is the entry point for the command-line application. This class is complete, and you should not need to modify it.
  • FlightsDB.java is a class that handles querying and updating the database. This is class is only partially complete, and your job (after designing your schema) will be to complete it's implementation.
  • User.java and Flight.java are helper classes for the application. You will need to use these (but not modify them), so look through the code to see how they work. (Both are immutable classes with only a constructor and no methods.)
  • sqljdbc4.jar is Microsoft's driver for providing JDBC access to SQL Server. You will need this on your classpath when running the application.
  • dbconn.properties contains information on how to connect to your instance of SQL Server in Azure. You will need to edit this file.

Start by opening up dbconn.properties in a text editor. You will need to fill in the name of your database server running in Azure in two places, along with the username and password for connecting to that server. (If you run into problems connecting, double check that the database name, shown after database= in flightservice.url matches the name of your database. The file provided uses the name we suggested in the instructions for HW3, but if you used a different database name, then you would need to change that part.)

Once that is done, you can compile and run the java program. Open the terminal and change into the directory where you unpacked the Java code. Then execute the following commands. (Windows users: change the ":" (colon) to a ";" (semicolon) on the line starting java -cp ....)

mkdir bin
javac -d bin *.java
java -cp bin:sqljdbc4.jar FlightsApp

(If you get a "command not found" error about javac, then you need to install the JDK (if you haven't already) and make sure that the JDK directory containing the javac and java tools is on your path.)

If you would prefer, you can instead use Eclipse to compile and run the application. To do this, create a new workspace and project in Eclipse. Copy the .java files you unpacked above into the project src/ folder. Copy the .jar and .properties files into the project root folder. Select "Build Path > Configure Build Path > Add Jars" via the menu, and then select the sqljdbc4.jar you copied in just before. Once that is all done, you should be able to use the "Run" button to run the app.

When you run the application, it will prompt you for a command. If you type help, it will show you a list of the commands its supports:

Supported commands:
 * login <handle> <password>
 * search <origin-city> <dest-city> <day-of-month> (Day of month is 1-31 of July 2015)
 * book <itinerary-num>
 * reservations
 * cancel <itinerary-num>
 * quit

Only two of these commands work at the moment: search and quit. The other commands will not do much of anything yet because they require functionality in FlightsDB that you will write later on.

Nonetheless, here is a brief description of what each command is supposed to do:

  • login takes a user's handle (a short username) and password and checks that they exist in the database.
  • search shows a list of all the one- and two-hop itineraries for flying from the given origin to the given destination on the given day of the month. (The search is always limited to the month of July 2015, so the user only needs to specify which day in that month.)
  • book allows the logged in user to reserve seats on the flights of an itinerary just printed by search. They do so by passing in the number listed next to that itinerary in the search output.
  • reservations shows all of the logged in user's current reservations.
  • cancel allows the logged in user to cancel a reservation made earlier. As with book, they do so by passing in the number listed next to that itinerary in the reservations output.

At this point, you should be able to use search, provided that your SQL Server instance still has the Flights and Carriers tables from HW3. (If not, you will need to recreate them by following the instructions from HW3.) The other commands will mostly do nothing at this point, but each command will work once you have filled in the missing code in FlightsDB that they rely on.

Problems

Problem 1: Schema Design [5 points]

Your first task is to determine the additional tables that you want to add to the database in order to support the functionality described above (and below... keep reading). You will need to create a Customer table. It should store, for each known user, their unique user ID, their full name, their handle (the short name they use to log in — it is also unique), and their password. (Note: it is not good practice to store unecrypted passwords in a database, but that will be fine for this assignment.) You will likely want to create other tables as well, but we will leave the details of that up to you.

You should also create one or more tables to hold the reservations made by customers. The design of these tables are up to you. You can add other tables to your database as well.

Put your CREATE TABLE statements into a file called setup.sql that you will turn in. You may also want to include some initial data in these tables, via INSERT INTO ... VALUES ... statements, to make testing easier. At the very least, you must include two rows in the Customer table with "user1/pass1" and "user2/pass2" as username password pairs so that it is possible to log in.

Write a separate script file with DROP TABLE statements; it's useful to run it whenever you find a bug in your schema or data (don't turn in this file).

Problem 2: Stop SQL Injection [5 points]

The starter code you are given has a serious problem: it is vulnerable to SQL injection attacks.

To see this, start by performing the following search:

search "Seattle WA" "Boston MA" 10

You should see 99 itineraries, including both some direct flights and some two-hop itineraries. Now, try entering the following:

search "Seattle WA" "Boston MA' AND F1.carrier_id = 'AA" 10

This query is looking for flights whose destination city is the long, weird string "Boston MA' AND F1.carrier_id = 'AA"". Of course, there is no such destination city. But when you run the query, you will see results. In fact, what you will see is this query now restricts the first leg of two-hop itineraries to use American Airlines.

Look through the Java code in FlightsDB.java to figure out why this is happening. In short, when that destination city is pasted into the SQL query, SQL Server does not parse it as a single string. Instead, the apostrphy in the text ends that string and the remainder of the text adds additional conditions to the SQL query itself.

This example was fairly harmless, but it is possible to use the same approach to cause more significant damage. In particular, it would be easy to drop tables in the database!

You will fix this problem by changing the uses of the Statement class in FlightsDB.java to uses of PreparedStatement instead. The latter lets you write queries with placeholders (written as ?) where parameters are to be inserted. However, unlike with simple text substitution, JDBC will make sure that no SQL injection is allowed.

See the slides from quiz section 8 for an example of how to use PreparedStatement or, alternatively, read the official documentation.

Once you have removed all uses of Statement, delete the Java import of that class. That will help prevent you from using it in any of the later parts of the assignment. (You should be using PreparedStatement from here on.) Anything you turn in that is succeptible to SQL injection (due to using Statement rather than PreparedStatement) will receive very little credit.

Problem 3: Support Login [5 points]

Implement the logIn function in FlightsDB.java by having it query your Customer table to see if there is a user with the given handle and password. If so, return a new User object with the full information about that user. Otherwise, return null to indicate that login failed.

You should now be able to use the login command in the command-line application.

Problem 4: Display Reservations [5 points]

Implement the getReservations functions in FlightsDB.java by having it query your tables to find a complete list of all the flights on which the given user is booked. For each flight, create a new Flight object containing all of the required information (see Flight.java to see what information it requires).

You can test your implementation more easily if you already have some reservations in the database. Use some INSERT INTO ... statements to add reservations (if you have not done so already) and include these statements in setup.sql.

Once that is done, you should be able to use the reservations command to see a list of the reservations for the logged in user.

Problem 5: Removing Reservations [5 points]

Implement the removeReservations function in FlightsDB.java so that it removes the given user's reservations on all of the given flights.

To be safe, you will want to implement all of these removals in a transaction. That will eliminate the possibility of ending up removing the user from one hop in their itinerary but not the other.

We have provided helper functions to make this easier. To use a transaction, you can simply call beginTransaction() at the start of the function and commitTransaction() or rollbackTransaction() at the end. Be sure you understand how these functions work - this is an example of working with JDBC connections.

Problem 6: Adding Reservations [5 points]

Your last task is to implement the addReservations function in FlightsDB.java so that it adds reservations for the given user on each of the given flights provided that doing so would not violate either of the following constraints:

  1. One user cannot reserve multiple itineraries in the same day. (I.e., if they already have a reservation on that day, then they cannot make another one.)
  2. No more than three users can make reservations on the same flight.

If you find that either of these constraints would fail, then you will return an error code (see FlightsDB.java for details) indicating the failure. If neither fails, then you can go ahead and add the reservations.

As in problem 5, be sure to implement all of these SQL operations (checking the constraints and then, if it is okay, adding the reservations) in one transaction.

To test that your transactions are working properly, start by inserting an arbitrary length pause between booking a flight and committing the transaction. The easiest way to do that is to write "Press any key to continue..." to System.out and then call System.in.read(), which will pause until a key is pressed. Then, you can run two clients at the same time and control when each one reads and writes. Try letting the first client start to book a reservation on a flight and, while it is paused after checking the constraints and booking (but before it has commited), have the second client try to book another flight on the same day for the same user. If everything is working correctly, SQL Server should prevent the second user from booking a flight.

Once you have tested adding reservations (and removing them again), you are done. Turn in your final version of FlightsDB.java, in which all of the operations are now functional, along with the setup.sql file that you wrote earlier.

Final Comments

  • The starter code is designed to give you a gentle introduction to embedding SQL into Java. Start by running the starter code, examine it and make sure you understand the part that works. You will need to create new tables and slowly add code to the application.
  • The completed project has multiple simple SQL queries embedded in the Java code. Some queries are parameterized: a parameter is a constant that is known only at runtime, and therefore appears as a '?' in the SQL code in Java: you already have examples in the started code.
  • We won't use recovery in this homework. Instead you will rely on the script file that you need to prepare for Task 1. This file contains all the CREATE TABLE and INSERT statements that are needed to start your project.

Troubleshooting

If you run into problems getting the starter code to work, first make sure that you have Java 1.8 (the most recent version of Java) installed.

SSLv3 SSLContext not available

If you get a SQLServerException saying that "the driver could not establish a secure connection to SQL Server", look further down in the stack trace for a "Caused by". If that says "SSLv3 SSLContext not available", then you may need to update your JDBC driver.

You can download a more recent driver from this page. In the tar.gz archive available from that page, you should find a jar called sqljdbc42.jar. Replace the sqljdbc4.jar that came with the starter code with this newer jar file.

No response from server

If you get a SQLServerException saying that "SQL Server did not return a response", there could be a problem with your network connectivity to the server. (In particular, you may be behind a firewall that is blocking the ports used to connect to SQL Server.) Some students have reported this problem when trying to connect from home. Try running your program on campus instead.

Isolation level violations

If your database is behaving in a manner that appears to be violating the isolation property of ACID transactions, the JDBC driver may not be properly setting the isolation level. Try setting it directly by calling setTransactionIsolation on the Connection object (called conn in FlightsDB.java). Pass in the constant Connection.TRANSACTION_SERIALIZABLE to indicate that you want serializable transactions.