Bachelorarbeit (veraltet): SQL in Joana

Information flow is topic of detecting flows between two different program entities in a program (in our case the flow from secret input to public output). Currently, Joana only deals with Java programs but does not understand any interactions with a database.

Take for example the following program:

/* 
Store as Scratch.java, run 
wget https://oss.sonatype.org/content/repositories/snapshots/org/xerial/sqlite-jdbc/3.15.2-SNAPSHOT/sqlite-jdbc-3.15.2-20161108.194537-3.jar -O sqlite.jar
javac Scratch.java
java -cp ".:sqlite.jar" Scratch
*/
import java.sql.*;

public class Scratch {
    public static int secretId = 1;
    public static String secretName = "Hans";

    public static void main(String[] args) {
        try {
            // create a database connection
            Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");
            Statement statement = connection.createStatement();
            statement.executeUpdate("create table person (id integer, name string)");
            PreparedStatement insert = connection.prepareStatement("insert into person values(?, ?)");
            PreparedStatement select = connection.prepareStatement("select * from person");
            // do some work to obtain the secret
            // ...
            insert.setInt(1, secretId);
            insert.setString(2, secretName);
            insert.executeUpdate();
            // ...
            ResultSet rs = select.executeQuery();
            while(rs.next()) { System.out.println("name = " + rs.getString("name")); }
        }
        catch(SQLException e) {}
    }
}

There is clearly a flow between the secretName property and the public output through the database. Joana itself can find (with the right annotations) a flow between secretName and the insert.setString call, but is indifferent to the database calls.

The grande aim

Add support for static flow through databases to Joana using preprocessing and automatic detection of database layout.

Use this to write a tool that discovers columns used in a component and create custom views.

Restrictions

more to come if necessary

• Only prepared statements

• Database structure has to be known

  • either by discovery from code or via given database connection

• Only JDBC

• Limited SQL dialect (no procedures, …)

Ideas

• Preprocess Java code and thereby replace SQL-Statements with generated

  code

  • aim is to transform the SQL code into flow equivalent code

    • flow before ? flow after

• Use soot and ANTLRv4

Preprocessing

The example from before can be translated into something like the following

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

class PersonRow {
    public int id;
    public String name;

    public PersonRow(int id, String name) {
        this.id = id;
        this.name = name;
    }
}

class PersonTable {
    public List<PersonRow> rows = new ArrayList<>();
}

class Connection {
    public PersonTable personTable = new PersonTable();

    public void insertStatement(int id, String person) {
        personTable.rows.add(new PersonRow(id, person));
    }

    public Iterator<PersonRow> selectStatement() {
        return personTable.rows.iterator();
    }
}

public class Scratch2 {
    public static int secretId = 1;
    public static String secretName = "Hans";

    public static void main(String[] args) {
        // create a database connection
        Connection connection = new Connection();
        // do some work to obtain the secret
        // ...
        connection.insertStatement(secretId, secretName);
        // ...
        Iterator<PersonRow> rs = connection.selectStatement();
        while(rs.hasNext()) { System.out.println("name = " + rs.next().name); }
    }
}