• Accessing Relational Data

    Concept of persistence

    Data exchange RDBMS ⇌ Java application

    Dealing with transactions

  • RDBMS schema and SQL - DDL:

    PRIMARY KEY, UNIQUE, FOREIGN KEY, NOT NULL, datatypes.

  • SQL, SQL - DML:

    Predicate based queries, joins.

  • Transactions, ACID principle:

    Isolation level 1 - 4.

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Persistence allows an object to outlive the process that created it. The state of the object may be stored to disk and an object with the same state re-created at some point in the future.

public class User {
  String commonName; // Common name e.g. 'Joe Bix'
  String uid;        // Unique login name e.g. 'bix'
  ...// getters, setters and other stuff

}
//------------------------------------
// Thread lifespan (transient instance)
User u = new User("Joe Bix", "bix");
CREATE TABLE User(
  CHAR(80) commonName
 ,CHAR(10) uid PRIMARY KEY
);
-- Persistent record (see Durability in ACID)
INSERT INTO User VALUES('Joe Bix', 'bix');
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  • Processes in disjoint address spaces:

    1. JRE™ runtime.

    2. RDBMS server.

  • Multiple runtimes possible (PHP)

  • save and load denote communications across OS boundaries.

  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • ➟ Write access, principles

        JDBC architecture

        Important interfaces in java.sql

        Write data using java.sql.Statement

layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  • Protocol connecting database client and server.

  • Vendor dependent implementations.

layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  1. Database server type i.e. Oracle, DB2, Informix, Postgresql, Mysql etc. due to vendor specific JDBC™ protocol implementations.

  2. Server DNS name or IP number.

  3. Server service's port number.

  4. The database name within the given server.

  5. Optional: A database user's account name and password.

layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image

https://www.ietf.org/rfc/rfc2396.txt:

absoluteURI   = scheme ":" ( hier_part | opaque_part )

hier_part     = ( net_path | abs_path ) [ "?" query ]

net_path      = "//" authority [ abs_path ]

abs_path      = "/"  path_segments
...
  • http://www.hdm-stuttgart.de/aaa

  • http://someserver.com:8080/someResource

    Non-standard port 8080

  • ftp://mirror.mi.hdm-stuttgart.de/Firmen

Database JDBC URI
PostgreSQL jdbc:postgresql://<HOST>:<PORT>/[database]
MySQL jdbc:mysql://[host][:port]/[database][?p1=v1]...
Oracle jdbc:oracle:thin:[user/password]@[host][:port]:SID
DB2 jdbc:db2://<HOST>:<PORT>/[database]
Derby jdbc:derby://[host][:port]/[database]
MS. SQL S. jdbc:sqlserver://host[:port];user=xxx;password=xyz
Sybase jdbc:sybase:Tds:<HOST>:<PORT>/[database]
Postgresql:

5432

IBM DB2:

50000

Oracle:

1521

  • No official IETF standard port assignments

  • Vendor specific defaults

  • Explicit port specification required

>grep postgresql /etc/services 
postgresql      5432/tcp       postgres      # PostgreSQL Database
postgresql      5432/udp       postgres
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  • postgresql-42.1.4.jar

  • mysql-connector-java-x.y.z.jar

  • ojdbc6.jar

<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901-1.jdbc4</version>
<groupId>com.oracle</groupId>  <!-- requires access credentials -->
<artifactId>ojdbc7</artifactId>
<version>12.1.0</version>
conn = DriverManager.getConnection(
 "jdbc:postgresqll://localhost/hdm", "hdmuser", "XYZ");
java.sql.SQLException: No suitable driver found for
 jdbc:postgresqll://localhost/hdm

  at java.sql.DriverManager.getConnection(DriverManager.java:689)
  at java.sql.DriverManager.getConnection(DriverManager.java:247)
  at de.hdm_stuttgart.mi.sda1.DatabaseTest.initDatabase(DatabaseTest.java:34)
 ...
java.sql.Connection
java.sql.Statement

Two distinct operation classes:

executeUpdate()

INSERT, UPDATE, DELETE: Integer return code

executeQuery()

SELECT: Returning java.sql.ResultSet, see the section called “Read Access”.

layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image

From JDBC and Multithreading:

Because all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use.

Consequence:

try (final Connection conn =
       C3P0DataSource.getInstance().getConnection()) {

  final PreparedStatement pstmt = conn.create...;
    ...
  pstmt.executeUpdate();
  // Auto close connection, back to pool.
} catch (SQLException e) {
  e.printStackTrace();
}
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • ➟ Write access, coding!

        Providing a driver based on maven.

        Specifying unit tests.

...
<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.1-901-1.jdbc4</version>
  <scope>runtime</scope>
</dependency> ...
Why <scope>runtime</scope>?
CREATE TABLE Person (
   name CHAR(20)
  ,email CHAR(20) UNIQUE
)

Java application executing:

INSERT INTO Person VALUES('Jim', 'jim@foo.org')
  • No database read required (No java.sql.ResultSet).

  • Success / failure related database return parameter.

// Step 1: Open connection to database server
final Connection conn = DriverManager.getConnection (
  "jdbc:postgresql://localhost/hdm", // Connection parameter URL
  "hdmuser",                         // Username
  "XYZ");                            // Password

// Step 2: Create a Statement instance
final Statement stmt = conn.createStatement();

// Step 3: Execute the desired INSERT
final int updateCount = stmt.executeUpdate(
  "INSERT INTO Person VALUES('Jim', 'jim@foo.org')");

// Step 4: Give feedback to the end user
System.out.println("Successfully inserted " + updateCount + " dataset(s)");

Execution yields:

Successfully inserted 1 dataset(s)

Note

The database server returns the number of inserted datasets.

  1. IDE level.

  2. Project level (Maven).

Exception on inserting objects
Missing exception handling:
public static void main(String[] args)
  throws SQLException { ...
Hard coded connection parameters:
... = DriverManager.getConnection (
  "jdbc:postgresql://localhost/hdm", //JDBC URL
  "hdmuser",                         // Username
  "XYZ")                             // Password
  • Connection parameter changes require recompilation!

  • Parameters should be configurable.

Possible solution: Java properties.

layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
Properties key / value file resources/jdbc.properties
jdbcurl=jdbc:postgresql://localhost/hdm
username=hdmuser
password=XYZ
ResourceBundle reading properties
// resources/jdbc.properties
ResourceBundle jdbcProperties = ResourceBundle.getBundle("jdbc");
Using ResourceBundle
... Connection conn = DriverManager.getConnection(
      jdbcProperties.getString("jdbcurl"),
      jdbcProperties.getString("username"),
      jdbcProperties.getString("password"));
 settings,
        preconditions
  1. Set up: Test preparation.

    • Open database connection

    • Create a required schema.

    • Optional: Insert initial data.

  2. Test: Execute JDBC CRUD / SELECT operations.

  3. Tear down:

    • Drop schema

    • Close database connection.

public class InsertTest {
  static private Connection conn;
  static private Statement stmt;

  @BeforeClass  static public void initDatabase() throws SQLException {
    conn = DriverManager.getConnection(
      SimpleInsert.jdbcProperties.getString("jdbcurl"),
      SimpleInsert.jdbcProperties.getString("username"),...);
    ScriptUtils.executeSqlScript(conn, new ClassPathResource("schema.sql"));
    stmt = conn.createStatement();}

  @Test 
  public void test_010_insertJill() throws SQLException {
    Assert.assertEquals(1, SimpleInsert.insertPerson(
         stmt, "Jill", "jill@programmer.org"));
 }
@AfterClass  static public void releaseDatabase()
   throws SQLException {conn.close();}

Getting ScriptUtils.executeSqlScript(...) to work:

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>5.1.5.RELEASE</version>
  <scope>test</scope>
</dependency>
Project layout
final Connection conn = DriverManager.getConnection(...);
... // CRUD operations
conn.close(); // Important! Wanna use a connection pool instead?

Using try-with-resources statement.

try (final Connection conn = DriverManager.getConnection(...) {
  ... // CRUD operations
} catch (SQLException e) {...}
  1. Interactive inserts, connection properties, error handling and unit tests
  2. Avoiding intermediate SQL file export
  3. Interfaces and classes in JDBC
  4. Closing JDBC connections
  5. Driver dispatch mechanism
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • JDBC and security
        • ➟ Network sniffing
layered SVG image
layered SVG image
layered SVG image
  • Database server and JDBC client on same machine.

  • Connecting to the loopback (lo) interface only.

    (Sufficient since client connects to localhost)

  • Capture packets of type TCP having port number 3306.

Setting up Wireshark
[...
5.5.24-0ubuntu0.12.04.1.%...X*e?I1ZQ...................e,F[yoA5$T[N.mysql_native_password.
 A...........!.......................hdmuser ❶......U.>S.%..~h...!.xhdm............j..../*

 ... INSERT INTO Person VALUES('Jim', 'jim@foo.org') ❷6...
  .&.#23000Duplicate entry 'jim@foo.org' for key 'email' 

username initiating database connection.

INSERT(...) statement.

Resulting error message sent back to the client.

Password?

What about the missing password?

Making MySQL Secure Against Attackers:

When you connect to a MySQL server, you should use a password.

The password is not transmitted in clear text over the connection.

  • Data exchange client to server nearly fully disclosed.

  • Mysql mitigates the attack type's severity

  • Possible solutions:

  • Irrelevant e.g. within DMZ.

  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • JDBC and security
        • ➟ SQL injection
Assembling SQL
layered SVG image
layered SVG image
Preventing traffic tickets
Trouble at
          school
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image

Many people say they know what SQL injection is, but all they have heard about or experienced are trivial examples.

SQL injection is one of the most devastating vulnerabilities to impact a business, as it can lead to exposure of all of the sensitive information stored in an application's database, including handy information such as usernames, passwords, names, addresses, phone numbers, and credit card details.

Current achievements continue to be questionable.

Attack from the dark side
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • JDBC and security
        • ➟ Sanitizing user input
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  1. Using regular expressions in Java
  2. Input validation by regular expressions
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • JDBC and security
        • java.sql.PreparedStatement
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  1. What happens when executing structural identical SQL statements (differing only by attribute values) repeatedly?

    E.g. inserting thousands of records of identical structure.

  2. Is this architecture adequate with respect to security concerns?

INSERT INTO Person VALUES ('Jim', 'jim@q.org')
INSERT INTO Person VALUES ('Eve', 'eve@y.org')
INSERT INTO Person VALUES ('Pete', 'p@rr.com')
...

Wasting time parsing SQL over and over again!

INSERT INTO Person VALUES
  ('Jim', 'jim@q.org'),
  ('Eve', 'eve@y.org'),
  ('Pete', 'p@rr.com') ... ;

Dealing with large record counts even this option may become questionable.

The database server's interpreter may interpret an attacker's malicious code among with intended SQL.

  • Parsing happens only once.

  • Reuse per record.

  • Avoids parsing contained «payload» / user input.

layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
  1. PreparedStatement instance creation: Parsing SQL statement possibly containing place holders.

  2. Set values of all placeholder values: No SQL parsing happens.

  3. Execute the statement.

Steps 2. and 3. may be repeated without requiring re-parsing SQL statements thus saving database server resources.

final Connection conn = DriverManager.getConnection (...

final PreparedStatement pStmt = conn.prepareStatement(
  "INSERT INTO Person VALUES(?, ?)");❶

pStmt.setString(1, "Jim");❷
pStmt.setString(2, "jim@foo.org");final int updateCount = pStmt.executeUpdate();❹

System.out.println("Successfully inserted " + updateCount + " dataset(s)");
Jim', 'jim@c.com');DROP TABLE Person;INSERT INTO Person VALUES('Joe

Attacker's injection text simply becomes part of the database server's content.

Problem solved!

PreparedSatatement statement =
  connection.prepareStatement("SELECT ?  from ?" );
statement.setString(1, "birthday") ;
statement.setString(2, "Persons") ;
ResultSet rs = statement.executeQuery() ;

In a nutshell: Only attribute value literals may be parameterized.

Prepared Statements to keep the barbarians at the gate
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • ➟ Read Access
  • CREATE / UPDATE / DELETE

    client modifies database server data:

    int result = statement.executeUpdate("UPDATE Person ...");
  • SELECT

    client receives copies of database server data:

    ResultSet result = statement.executeQuery("SELECT ... FROM Person ...");
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
layered SVG image
CREATE TABLE Friends (
   id INTEGER NOT NULL PRIMARY KEY
  ,nickname char(10)
  ,birthdate DATE
);
INSERT INTO Friends VALUES
   (1, 'Jim', '1991-10-10')
  ,(2, 'Eve', '2003-05-24')
  ,(3, 'Mick','2001-12-30')
  ;
final Connection conn = DriverManager.getConnection (...);
final Statement stmt = conn.createStatement();
// Step 3: Creating the client side JDBC container holding our data records
final ResultSet data = stmt.executeQuery("SELECT * FROM Friends"); 

// Step 4: Dataset iteration
while (data.next()) { 
   System.out.println(data.getInt("id") 
       + ", " + data.getString("nickname") 
       + ", " + data.getString("birthdate")); 
}
New: resultSet = statement.executeQuery(...)

Caution: Data not yet accessible!

Cursor positioned: resultSet.next() returning true

Data accessible until resultSet.next()returns false.

Closed: resultSet.next() returning false

Caution: Data not longer accessible!

JDBC Type Java type
CHAR, VARCHAR, LONGVARCHAR String
NUMERIC, DECIMAL java.math.BigDecimal
BIT boolean
TINYINT byte
... ...

Shamelessly copied from JDBC Types Mapped to Java Types.

Java Type JDBC type
String CHAR, VARCHAR, LONGVARCHAR
java.math.BigDecimal NUMERIC
boolean BIT
... ...

Shamelessly copied from Java Types Mapped to JDBC Types.

int getInt​(int columnIndex)
double getDouble​(int columnIndex)
Date getDate​(int columnIndex)
...
Object getObject​(int columnIndex)

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

final int id =
  resultSet.getInt("id");
final String nickName =
  resultSet.getString("nickname");
final Date birthDate =
  resultSet.getDate​("birthdate");
CREATE TABLE Friends (
   id INTEGER NOT NULL PRIMARY KEY
  ,nickname char(10)
  ,birthdate DATE
);
final int id =
  resultSet.getInt(1);
final String nickName =
  resultSet.getString(2);
final Date birthDate =
  resultSet.getDate(3);
CREATE TABLE Friends (
   id INTEGER NOT NULL PRIMARY KEY
  ,nickname char(10)
  ,birthdate DATE
);
Getter methods and type conversion
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • ➟ Read Access
        • ➟ Getter methods and type conversion
final int count = resultSet.getInt("numProducts");

Problem: Two possibilities in case of count == 0:

  1. DB attribute numProducts is 0 (zero).

  2. DB attribute numProducts is null.

final int count = resultSet.getInt("numProducts");

if (resultSet.wasNull()) {
...
} else {
...
}

See wasNull().

  1. Handling NULL values.
  2. Reversing XML to Rdbms
  3. Generating HTML from both XML and relational input data
  • Accessing Relational Data
    • ➟ Introduction to JDBC
      • ➟ Handling surrogate keys
Users and groups
  1. JDBC and transactions
  2. Isolation level 1 vs. 2
  3. Aborted transactions