Class 22 — Wednesday, November 11

A bank that outlives its program: a database, its tables and rows, and SQL sent to it from Java.

Until now every account lived in a Java object, and A10’s server forgot them all when it stopped. Today the accounts go into a database: a file beside the project that a program reads and writes through JDBC.

  1. The words — keyboards down
  2. A first program
  3. Run it again — predict first
  4. Changing a row
  5. A name with a quote in it — predict first
  6. A name given as a value

Today’s work goes in a new project. On https://github.com/cu-cs-labs/cmsc-230-jdbc, Use this template, and in the form: Owner cu-cs-labs, Repository name cmsc-230-jdbc- followed by your GitHub username, and Private. Then clone it in IntelliJ, as with cmsc-230-threads; IntelliJ fetches the database with the rest. In the org rather than under your own account, so that we can see how far each of you got.

1. The words

Keyboards down.

word what it is in today’s program
database data kept outside the program, so it outlives it the file bank.mv.db
table rows that all have the same columns accounts
row one thing the table holds one account
column one fact about every row name, balance
primary key a column no two rows share name
SQL the language a program sends the database CREATE TABLE, INSERT, SELECT
JDBC Java’s classes for talking to a database Connection, Statement, ResultSet
H2 the database we use, which runs inside your program jdbc:h2:./bank

Where the database sits in a program like A10’s server, and what each layer owns:

 a person at a keyboard
          |  types: DEPOSIT 12.50
          v
 +---------------------+
 |  BankClient         |  sends the line over the socket, prints the answer
 +---------------------+
          |  the network (localhost:5230)
          v
 +---------------------------------------------------+
 |  BankServer                                        |
 |    accept() -> a thread per client -> Session      |  the PROTOCOL lives here:
 |    Session.handle("DEPOSIT 12.50")                 |  words, OK / ERROR lines
 +---------------------------------------------------+
          |  bank.deposit("alice", 1250)      <- a Java call, no text
          v
 +---------------------------------------------------+
 |  Bank  (MemoryBank in A9, DatabaseBank in A11)     |  the RULES live here:
 |    no overdraft, one id per transfer, no self-send |  same interface either way
 +---------------------------------------------------+
          |  JDBC: UPDATE customer SET balance = balance + ? WHERE name = ?
          v
 +---------------------------------------------------+
 |  H2 (the database engine, inside the program)      |  the DATA lives here:
 |    tables, rows, row locks, transactions           |  and it outlives the program
 +---------------------------------------------------+
          |
          v
   bank.mv.db   (a file beside pom.xml)

The server never sees SQL, and the database never sees the protocol.

Four SQL statements do most of the work:

CREATE TABLE accounts (name VARCHAR(40) PRIMARY KEY, balance BIGINT)
INSERT INTO accounts VALUES ('alice', 10000)
SELECT name, balance FROM accounts WHERE balance > 5000
UPDATE accounts SET balance = balance + 500 WHERE name = 'alice'

VARCHAR(40) is text of up to 40 characters, and BIGINT a long: the balance in cents, as A9’s bank kept it. In SQL, text goes in single quotes.

2. A first program

Pairs. 2.1, then 2.2 if you are done.

It is in src of your copy of cmsc-230-jdbc:

import java.sql.*;

// makes a table of accounts in a database file beside the project, puts two
// accounts in it, and prints every row
public class FirstTable {
    public static void main(String[] args) throws SQLException {
        try (Connection db = DriverManager.getConnection("jdbc:h2:./bank")) {
            Statement sql = db.createStatement();
            sql.executeUpdate("CREATE TABLE IF NOT EXISTS accounts "
                    + "(name VARCHAR(40) PRIMARY KEY, balance BIGINT)");
            sql.executeUpdate("INSERT INTO accounts VALUES ('alice', 10000)");
            sql.executeUpdate("INSERT INTO accounts VALUES ('bob', 2500)");
            ResultSet rows = sql.executeQuery("SELECT name, balance FROM accounts");
            while (rows.next()) {
                System.out.println(rows.getString("name") + " "
                        + rows.getLong("balance"));
            }
        }
    }
}
  • DriverManager.getConnection opens the database named by the URL: jdbc:h2:./bank is the file bank.mv.db in the project, made the first time. The try closes the connection, and its statements with it.
  • executeUpdate sends SQL that changes something, and returns how many rows it changed.
  • executeQuery sends a SELECT and returns a ResultSet: the rows it found, read one at a time. next() moves to the next row and says whether there was one; getString and getLong read a column of that row.

2.1 Run it

Make your copy of cmsc-230-jdbc as the top of the page says, and clone it. Run FirstTable once, from the arrow beside main. Only once: section 3 is about what a second run does.

Done when it prints alice and bob, and bank.mv.db has appeared beside pom.xml.

2.2 Who is rich (if you are done)

A new program, ShowAccounts: only the accounts holding more than $50.00, the richest first. ORDER BY balance DESC at the end of a SELECT sorts it, largest first.

Done when it prints alice alone, and running it again prints the same.

When the section ends: commit and push.

3. Run it again

Keyboards down. Write in the Journal first, then run it.

Run FirstTable a second time. What happens, and why?

It stops at the first INSERT, with JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation, on 'alice'.

The first run’s rows are still in bank.mv.db. The program ended and the data did not: that is what a database is for. The CREATE TABLE IF NOT EXISTS passes, since the table is there and IF NOT EXISTS says to leave it; without those words the second run would stop one line earlier, with Table "ACCOUNTS" already exists. Then name is the primary key, so the database refuses a second row named alice. A10’s server forgot every account when it stopped; a server that keeps them here finds them all when it starts again.

4. Changing a row

Pairs. 4.1, then 4.2 and 4.3 if you are done.

4.1 A deposit

A new program, Deposit: add $5.00 to alice, and $5.00 to carol, who has no account, each with UPDATE accounts SET balance = balance + 500 WHERE name = '...'. Print what each executeUpdate returns, then alice’s new balance with a SELECT.

Done when it prints 1 for alice and 0 for carol: the count of rows changed is how a program knows there was no such account.

4.2 Safe to run again (if you are done)

Make FirstTable safe to run any number of times. The SQLException from a second alice has getSQLState() equal to "23505", the database’s code for a key already taken: catch that one around each INSERT, print that the row is already there, and let any other SQLException through.

4.3 A record of deposits (if you are done)

A second table, deposits, with an id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, a name and the cents: the database numbers its rows itself, 1, 2, 3, …. Make Deposit also insert a row there for each deposit that changed a row, and print alice’s deposits, oldest first, with ORDER BY id.

When the section ends: commit and push.

5. A name with a quote in it

Keyboards down. Write in the Journal first, then run it.

A program that looks one account up, by a name somebody typed, built into the SQL with +:

import java.sql.*;

// looks one account up by a name typed by somebody else, built into the SQL with +
public class Lookup {
    public static void main(String[] args) throws SQLException {
        String name = "nobody' OR '1'='1";
        try (Connection db = DriverManager.getConnection("jdbc:h2:./bank")) {
            Statement sql = db.createStatement();
            String query = "SELECT name, balance FROM accounts WHERE name = '"
                    + name + "'";
            System.out.println(query);
            ResultSet rows = sql.executeQuery(query);
            while (rows.next()) {
                System.out.println(rows.getString("name") + " "
                        + rows.getLong("balance"));
            }
        }
    }
}

No account is named nobody' OR '1'='1. What does Lookup print?

Every account. The query it printed was

SELECT name, balance FROM accounts WHERE name = 'nobody' OR '1'='1'

The quote in the name ended the SQL’s text, and the rest of the name became SQL: OR '1'='1', which is true of every row. Whoever types the name can change what the query says. In A10’s server, that is any client.

This is an SQL injection: input meant as a value is read as SQL. It is one of the oldest ways a server gets broken into, and section 6 is the standard fix.

6. A name given as a value

Pairs. 6.1, then 6.2 if you are done.

A PreparedStatement keeps the SQL and the values apart: the SQL has a ? where a value goes, and the value is given afterwards, so it can only ever be a value:

PreparedStatement query = db.prepareStatement(
        "SELECT name, balance FROM accounts WHERE name = ?");
query.setString(1, name);
ResultSet rows = query.executeQuery();

setString(1, ...) fills the first ?, setLong(2, ...) would fill a second.

6.1 SafeLookup

Lookup with a PreparedStatement, the same name in it.

Done when it prints no account, and alice in place of the name prints alice.

6.2 A deposit given as values (if you are done)

Change Deposit to a method static int deposit(Connection db, String name, long cents), with a PreparedStatement and two ?s, that returns the rows changed. Call it for alice, for o'neil, and for nobody' OR '1'='1.

Done when alice’s is 1, and the other two are 0 and change nothing.

When the section ends: commit and push.