2 of 13

Problem 3.b from Midterm

Write the SQL statements to create the tables representing the E/R diagram above. The keys eid, appId are integers, name, appName, platform, language are strings. Include all key and foreign key statements.

3 of 13

Problem 3.b from Midterm

create table Employee (eid int primary key, name text);

create table dba (eid int primary key references Employee, platform text);

create table SE (eid int primary key references Employee, language text);

create table Application (appId int primary key, appName text, manage int references dba );

create table develop (seId int references SE, appId int references Application);

4 of 13

Problem 4.b from Midterm

S(A,B,D)

5 of 13

Java Prepared Statements for hw5

6 of 13

SQL Injection and HW5

7 of 13

HW5 Notes

We will be using Azure again as our database, you’ll need your server name, database name, username and password
Remember you need to make the file dbconn.properties but it won’t be uploaded to your repository.
Remember you need to create tables for data that you need to store globally for all users not for data that is local to an user and might get deleted after the user quits the session.

8 of 13

More HW5 Notes

If you’re on Windows, you will need to change the scripts to use a semicolon “;” instead of “:” in the class path passed to the `java` and `javac` commands

We will update these for homework 6

Add test cases of your own, the ones that we provided are not exhaustive and do not test for the full set of capabilities we expect

9 of 13

SQL Injection

Vulnerable web applications can expose capabilities to the user that are undesired

Dropping tables, inserting values, deleting values, gaining access when it should be rejected, etc.

Using PreparedStatement objects will help prevent SQL Injection

A PreparedStatement pre-compiles the SQL query such that inputs that would result in SQL Injection when parsed can no longer do so

SQL Injection Demo

10 of 13

PreparedStatements

How they work:

Your SQL Connection Object parses the String passed to the `prepareStatement` function and pre-compiles it to a parameterized SQL query
The “?” in the query are interpreted as parameters that can be inserted via calls on the PreparedStatement Object

String rawQuery = “SELECT * FROM Flights WHERE origin_city = ? AND day_of_month = ?”;

PreparedStatement ps = conn.prepareStatement(rawQuery); // Pre-compiles the query into a PreparedStatement Object

ps.clearParameters(); // Clears parameters from previous use

ps.setString(1, originCity); // Sets the first parameter (the first “?”) to the value of the variable “originCity”

ps.setInt(2, dayOfMonth); // Sets the second parameter (the second “?”) to the value of the variable “dayOfMonth”

ResultSet rs = ps.executeQuery(); // Executes the query and stores the ResultSet in the variable “rs”

11 of 13

ResultSet

A ResultSet represents a table of data returned from executing a query
It maintains an internal cursor that points to the current row of data, initially that cursor is positioned before the first row so the first call to `next` moves the cursor to the first row

Makes it ideal for a while loop:

// ... continued from previous slide

ResultSet rs = ps.executeQuery();

while (rs.hasNext() && rs.next()) { // check if there is another row and move to the next row

String destCity = rs.getString(“dest_city”); // Gets the value of the attribute “dest_city” for the current row

...

} // When `next` finally returns false, we know we’ve seen every row

rs.close(); // Remember to close the ResultSet

12 of 13

Final HW5 Notes

The `Flight` class is there for you to use if you like, feel free to modify it, add methods, etc.

You can also add additional classes as you see fit, preferably add them as internal classes so all your code is in Query.java
It might be helpful to create a class that represents an Itinerary, if it implements the `Comparable` interface then you can use `Collections.sort` to sort it (this would be helpful for sorting one-hop and direct flights with one another)