Overview

  • Accessing Relational Data

    Concept of persistence

    Data exchange RDBMS ⇌ Java application

    Dealing with transactions

    Lecture notes

Prerequisite knowledge

  • 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.

Lecture notes

  • Accessing Relational Data

Create comment

Overview

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

      Lecture notes

Persistence [Bauer2015]

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Java transient instances

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");

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

RDBMS persistent records

CREATE TABLE User(
  CHAR(80) commonName
 ,CHAR(10) uid PRIMARY KEY
);
-- Persistent record (see Durability in ACID)
INSERT INTO User VALUES('Joe Bix', 'bix');

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Persisting transient User instances

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Observations

  • Processes in disjoint address spaces:

    1. JRE™ runtime.

    2. RDBMS server.

  • Multiple runtimes possible (PHP)

  • save and load denote communications across OS boundaries.

Lecture notes

  • Accessing Relational Data
    • ➟ Persistence in Object Oriented languages

Create comment

Overview

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

        JDBC architecture

        Important interfaces in java.sql

        Write data using java.sql.Statement

        Lecture notes

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

Networking between clients and database server

layered SVG image

Lecture notes

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

Create comment

JDBC features

  • Protocol connecting database client and server.

  • Vendor dependent implementations.

Lecture notes

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

Create comment

JDBC™ in a three-tier application

layered SVG image

Lecture notes

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

Create comment

JDBC™ in a three-tier application

layered SVG image

Lecture notes

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

Create comment

JDBC™ in a three-tier application

layered SVG image

Lecture notes

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

Create comment

JDBC™ in a three-tier application

layered SVG image

Lecture notes

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

Create comment

JDBC™ in a three-tier application

layered SVG image

Lecture notes

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

Create comment

JDBC™ connecting application server and database.

layered SVG image

Lecture notes

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

Create comment

JDBC™ connecting application server and database.

layered SVG image

Lecture notes

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

Create comment

JDBC™ connecting application server and database.

layered SVG image

Lecture notes

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

Create comment

JDBC™ connecting application server and database.

layered SVG image

Lecture notes

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

Create comment

JDBC™ connecting application server and database.

layered SVG image

Lecture notes

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

Create comment

JDBC™ connecting application server and database.

layered SVG image

Lecture notes

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

Create comment

JDBC connection parameter

  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.

Lecture notes

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

Create comment

Components of a JDBC™ URL

layered SVG image

Lecture notes

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

Create comment

Components of a JDBC™ URL

layered SVG image

Lecture notes

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

Create comment

Components of a JDBC™ URL

layered SVG image

Lecture notes

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

Create comment

Components of a JDBC™ URL

layered SVG image

Lecture notes

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

Create comment

Components of a JDBC™ URL

layered SVG image

Lecture notes

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

Create comment

Components of a JDBC™ URL

layered SVG image

Lecture notes

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

Create comment

IETF Uniform Resource Identifier

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
...

Lecture notes

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

Create comment

URL examples

  • http://www.hdm-stuttgart.de/aaa

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

    Non-standard port 8080

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

Lecture notes

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

Create comment

Sub protocol examples

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]

Lecture notes

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

Create comment

No standard port assignments ...

Postgresql:

5432

IBM DB2:

50000

Oracle:

1521

  • No official IETF standard port assignments

  • Vendor specific defaults

  • Explicit port specification required

Lecture notes

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

Create comment

... but Postgresql made it into Linux

>grep postgresql /etc/services 
postgresql      5432/tcp       postgres      # PostgreSQL Database
postgresql      5432/udp       postgres

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

JDBC architecture

layered SVG image

Lecture notes

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

Create comment

DriverManager: Bootstrapping connections

Lecture notes

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

Create comment

Example: Mysql connection implementation

Lecture notes

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

Create comment

Driver libraries

  • postgresql-42.1.4.jar

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

  • ojdbc6.jar

Lecture notes

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

Create comment

Driver libraries, Maven

<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>

Lecture notes

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

Create comment

Driver unavailable

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)
 ...

Lecture notes

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

Create comment

Connection interface

java.sql.Connection

Lecture notes

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

Create comment

Statement interface

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”.

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

JDBC™ instances and relationships.

layered SVG image

Lecture notes

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

Create comment

Important Connection methods

Lecture notes

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

Create comment

Important Statement methods

Lecture notes

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

Create comment

JDBC and threading.

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:

Lecture notes

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

Create comment

JDBC connection pooling

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();
}

Lecture notes

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

Create comment

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

        Providing a driver based on maven.

        Specifying unit tests.

        Lecture notes

pom.xml driver runtime scope

...
<dependency>
  <groupId>postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>9.1-901-1.jdbc4</version>
  <scope>runtime</scope>
</dependency> ...

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Followup exercise

No. 22: Why <scope>runtime</scope>?

Person table

CREATE TABLE Person (
   name CHAR(20)
  ,email CHAR(20) UNIQUE
)

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Objective: insert person record

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

JDBC™ backed data insert

// 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)");

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Result

Execution yields:

Successfully inserted 1 dataset(s)

Note

The database server returns the number of inserted datasets.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Two JDBC configurations

  1. IDE level.

  2. Project level (Maven).

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Followup exercise

No. 23: Exception on inserting objects

Figure 838, “JDBC™ backed data insert” deficiencies

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

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Why properties?

  • Connection parameter changes require recompilation!

  • Parameters should be configurable.

Possible solution: Java properties.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

message.properties string externalization

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

message.properties string externalization

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

message.properties string externalization

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

message.properties string externalization

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

message.properties string externalization

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Properties code sketch

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"));

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Intellij IDEA settings, preconditions

Intellij IDEA settings, preconditions

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Database related unit test phases

  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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Implementing unit tests

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();}

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Spring is your friend

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

<dependency>
  <groupId>org.springframework</groupId>
  <artifactId>spring-jdbc</artifactId>
  <version>5.1.5.RELEASE</version>
  <scope>test</scope>
</dependency>

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Project layout

Project layout

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Closing connections

final Connection conn = DriverManager.getConnection(...);
... // CRUD operations
conn.close(); // Important! Wanna use a connection pool instead?

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Employ AutoCloseable

Using try-with-resources statement.

try (final Connection conn = DriverManager.getConnection(...) {
  ... // CRUD operations
} catch (SQLException e) {...}

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Write access, coding!

Create comment

Followup exercises

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

          Lecture notes

Sniffing a JDBC™ connection by an intruder.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Sniffing a JDBC™ connection by an intruder.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Sniffing a JDBC™ connection by an intruder.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Setting up Wireshark

  • 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

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Capturing results

[...
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?

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Mysql security

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Mysql security

  • Data exchange client to server nearly fully disclosed.

  • Mysql mitigates the attack type's severity

  • Possible solutions:

  • Irrelevant e.g. within DMZ.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Network sniffing

Create comment

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

          Lecture notes

Assembling SQL

Assembling SQL

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection principle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection principle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

Preventing traffic tickets

Preventing traffic tickets

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

Trouble at school

Trouble at school

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection impact

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection impact

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection impact

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection impact

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection impact

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

SQL injection relevance, [Clarke2009]

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

Lessons learned?

Current achievements continue to be questionable.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ SQL injection

Create comment

Followup exercise

No. 29: Attack from the dark side

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Sanitizing user input

          Lecture notes

Validating user input prior to dynamically composing SQL statements.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Sanitizing user input

Create comment

Validating user input prior to dynamically composing SQL statements.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Sanitizing user input

Create comment

Validating user input prior to dynamically composing SQL statements.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Sanitizing user input

Create comment

Validating user input prior to dynamically composing SQL statements.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ Sanitizing user input

Create comment

Followup exercises

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

          Lecture notes

SQL statements in Java applications get parsed at the database server

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

SQL statements in Java applications get parsed at the database server

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

SQL statements in Java applications get parsed at the database server

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

SQL statements in Java applications get parsed at the database server

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

SQL statements in Java applications get parsed at the database server

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Two questions

  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?

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Addressing performance

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!

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Addressing performance mitigation

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Addressing security

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

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Solution: Use java.sql.PreparedStatement

  • Parsing happens only once.

  • Reuse per record.

  • Avoids parsing contained «payload» / user input.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement principle.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Three phases using parameterized queries

  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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

PreparedStatement example

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)");

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Injection attempt example

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!

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

No dynamic table support

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ and security
        • ➟ java.sql.PreparedStatement

Create comment

Followup exercise

No. 32: Prepared Statements to keep the barbarians at the gate

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

        Lecture notes

JDBC read and write

  • 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 ...");

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Server / client object's life cycle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Server / client object's life cycle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Server / client object's life cycle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Server / client object's life cycle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Server / client object's life cycle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Server / client object's life cycle

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

JDBC record container

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Reading data from a database server.

layered SVG image

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Names and birth dates of friends

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')
  ;

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Accessing friend's database records

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")); 
}

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Important ResultSet states

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!

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

JDBC to Java type conversions

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Java to JDBC type conversions

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

Shamelessly copied from Java Types Mapped to JDBC Types.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Fixed type accessors

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

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Polymorphic accessors

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Access by column name

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
);

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Access by column index

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
);

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access

Create comment

Followup exercise

No. 33: Getter methods and type conversion

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access
        • ➟ Getter methods and type conversion

          Lecture notes

Problem: null value ambiguity

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.

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access
        • ➟ Getter methods and type conversion

Create comment

Resolving null value ambiguity

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

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

See wasNull().

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Read Access
        • ➟ Getter methods and type conversion

Create comment

Followup exercises

Overview

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Handling surrogate keys

        Lecture notes

Users and groups

Users and groups

Lecture notes

  • Accessing Relational Data
    • ➟ Introduction to
      • ➟ Handling surrogate keys

Create comment

Followup exercises