hostage/src/de/tudarmstadt/informatik/hostage/logging/DatabaseHandler.java

package de.tudarmstadt.informatik.hostage.logging;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.ArrayList;

import de.tudarmstadt.informatik.hostage.R;
import de.tudarmstadt.informatik.hostage.logging.Record.TYPE;
import de.tudarmstadt.informatik.hostage.protocol.Protocol;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
/**
 * This class creates SQL tables and handles all access to the database.<br>
 * It contains several methods with predefined queries to extract different kinds of information from the database.<br>
 * The database contains two tables: {@link #TABLE_RECORDS} and {@link #TABLE_BSSIDS}:<br>
 * {@link #TABLE_RECORDS} contains all logging information of a single message record except the SSID.<br>
 * {@link #TABLE_BSSIDS} contains the BSSID of all recorded Networks and the corresponding SSID.<br>
 * @author Lars Pandikow
 */
public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "recordManager";

	// Contacts table names
	private static final String TABLE_ATTACK_INFO = "attack_info";
	private static final String TABLE_RECORDS = "records";
	private static final String TABLE_BSSIDS = "bssids";
	private static final String TABLE_PORTS = "ports";

	// Contacts Table Columns names
	private static final String KEY_ID = "_id";
	private static final String KEY_ATTACK_ID = "_attack_id";
	private static final String KEY_TYPE = "type";
	private static final String KEY_TIME = "timestamp";
	private static final String KEY_PACKET = "packet";
	private static final String KEY_PROTOCOL = "protocol";
	private static final String KEY_EXTERNAL_IP ="externalIP";
	private static final String KEY_LOCAL_IP = "localIP";
	private static final String KEY_LOCAL_HOSTNAME = "localHostName";
	private static final String KEY_LOCAL_PORT = "localPort";
	private static final String KEY_REMOTE_IP = "remoteIP";
	private static final String KEY_REMOTE_HOSTNAME = "remoteHostName";
	private static final String KEY_REMOTE_PORT = "remotePort";
	private static final String KEY_BSSID = "_bssid";
	private static final String KEY_SSID = "ssid";
	private static final String KEY_LATITUDE = "latitude";
	private static final String KEY_LONGITUDE = "longitude";
	private static final String KEY_ACCURACY = "accuracy";

	
	// Database sql create statements
	private static final String CREATE_RECORD_TABLE = "CREATE TABLE " + TABLE_RECORDS + "(" 
			+ KEY_ID + " INTEGER NOT NULL," + KEY_ATTACK_ID + " INTEGER NOT NULL," 
			+ KEY_TYPE + " TEXT," + KEY_TIME + " INTEGER," +  KEY_PACKET + " TEXT," 
			+ "FOREIGN KEY("+ KEY_ATTACK_ID +") REFERENCES " + TABLE_ATTACK_INFO + "("+KEY_ATTACK_ID+")" 
			+ "PRIMARY KEY("+ KEY_ID + ", " + KEY_ATTACK_ID + ")"
			+ ")";
	
	private static final String CREATE_ATTACK_INFO_TABLE = "CREATE TABLE " + TABLE_ATTACK_INFO + "(" 
			+ KEY_ATTACK_ID + " INTEGER PRIMARY KEY," + KEY_PROTOCOL + " TEXT,"
			+ KEY_EXTERNAL_IP + " TEXT," + KEY_LOCAL_IP + " BLOB," + KEY_LOCAL_HOSTNAME + " TEXT," 
			+ KEY_REMOTE_IP + " BLOB," + KEY_REMOTE_HOSTNAME + " TEXT," + KEY_REMOTE_PORT + " INTEGER," + KEY_BSSID + " TEXT," 
			+ "FOREIGN KEY("+ KEY_BSSID +") REFERENCES " + TABLE_BSSIDS + "("+KEY_BSSID+")" 
			+ "FOREIGN KEY("+ KEY_PROTOCOL +") REFERENCES " + TABLE_PORTS + "("+KEY_PROTOCOL+")" 
			+ ")";
	
	private static final String CREATE_BSSID_TABLE = "CREATE TABLE " + TABLE_BSSIDS + "(" 
			+ KEY_BSSID + " TEXT PRIMARY KEY," + KEY_SSID + " TEXT," + KEY_LATITUDE + " INTEGER,"
			+ KEY_LONGITUDE + " INTEGER," + KEY_ACCURACY + " INTEGER"
			+ ")";
	
	private static final String CREATE_PORT_TABLE = "CREATE TABLE " + TABLE_PORTS + "(" 
			+ KEY_PROTOCOL + " TEXT PRIMARY KEY," + KEY_LOCAL_PORT + " INTEGER"
			+ ")";

	private Context context;
	
	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_PORT_TABLE);
		db.execSQL(CREATE_BSSID_TABLE);
		db.execSQL(CREATE_ATTACK_INFO_TABLE);
		db.execSQL(CREATE_RECORD_TABLE);
				
		String[] protocols = context.getResources().getStringArray(R.array.protocols);
		String packageName = Protocol.class.getPackage().getName();

		//Initialize Port Table
		for (String protocol : protocols) {
			try {				
				int port = ((Protocol<?>) Class.forName(String.format("%s.%s", packageName, protocol)).newInstance()).getPort();
		        db.execSQL("INSERT INTO " + TABLE_PORTS + " VALUES ( '" + protocol + "'," + port + ")");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORDS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ATTACK_INFO);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BSSIDS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_PORTS);
		
		// Create tables again
		onCreate(db);
	}

	/**
	 * Adds a given {@link Record}  to the database.
	 * @param record The added {@link Record} .
	 */
	public void addRecord(Record record) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues bssidValues = new ContentValues();
		bssidValues.put(KEY_BSSID, record.getBSSID());
		bssidValues.put(KEY_SSID, record.getSSID());
		bssidValues.put(KEY_LATITUDE, record.getLatitude());
		bssidValues.put(KEY_LONGITUDE, record.getLongitude());
		bssidValues.put(KEY_ACCURACY, record.getAccuracy());
		
		ContentValues attackValues = new ContentValues();
		attackValues.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		attackValues.put(KEY_PROTOCOL, record.getProtocol().toString());
		attackValues.put(KEY_EXTERNAL_IP, record.getExternalIP());
		attackValues.put(KEY_LOCAL_IP, record.getLocalIP().getAddress()); // Log Local IP
		attackValues.put(KEY_LOCAL_HOSTNAME, record.getLocalIP().getHostName());
		attackValues.put(KEY_REMOTE_IP, record.getRemoteIP().getAddress()); // Log Remote IP
		attackValues.put(KEY_REMOTE_HOSTNAME, record.getRemoteIP().getHostName());
		attackValues.put(KEY_REMOTE_PORT, record.getRemotePort()); // Log Remote Port
		attackValues.put(KEY_BSSID, record.getBSSID());
		
		ContentValues recordValues = new ContentValues();
		recordValues.put(KEY_ID, record.getId()); // Log Message Number
		recordValues.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
		recordValues.put(KEY_TYPE, record.getType().name()); // Log Type
		recordValues.put(KEY_TIME, record.getTimestamp()); // Log Timestamp
		recordValues.put(KEY_PACKET, record.getPacket()); // Log Packet
		

		// Inserting Rows
		db.insertWithOnConflict(TABLE_BSSIDS, null, bssidValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.insertWithOnConflict(TABLE_ATTACK_INFO, null, attackValues, SQLiteDatabase.CONFLICT_REPLACE);
		db.insert(TABLE_RECORDS, null, recordValues);
		db.close(); // Closing database connection
	}
	
	/**
	 * Creates a {@link Record} from a Cursor. If the cursor does not show to a valid data structure a runtime exception is thrown.
	 * @param cursor
	 * @return Returns the created {@link Record} .
	 */
	private Record createRecord(Cursor cursor){
		Record record = new Record();
		try {
		record.setId(Integer.parseInt(cursor.getString(0)));
		record.setAttack_id(cursor.getLong(1));
		record.setType(cursor.getString(2).equals("SEND") ? TYPE.SEND : TYPE.RECEIVE);
		record.setTimestamp(cursor.getLong(3));
		record.setPacket(cursor.getString(4));
		record.setProtocol(cursor.getString(5));
		record.setExternalIP(cursor.getString(6));
		record.setLocalIP(InetAddress.getByAddress(cursor.getString(8), cursor.getBlob(7)));
		record.setRemoteIP(InetAddress.getByAddress(cursor.getString(10), cursor.getBlob(9)));
		record.setRemotePort(Integer.parseInt(cursor.getString(11)));
		record.setBSSID(cursor.getString(12));
		record.setSSID(cursor.getString(13));
		record.setLatitude(Double.parseDouble(cursor.getString(14)));
		record.setLongitude(Double.parseDouble(cursor.getString(15)));
		record.setAccuracy(Float.parseFloat(cursor.getString(16)));
		record.setLocalPort(Integer.parseInt(cursor.getString(17)));
		} catch (UnknownHostException e) {
			e.printStackTrace();
		}
		return record;
	}

	/**
	 * Gets a single {@link Record} with the given ID from the database.
	 * @param id The ID of the {@link Record};
	 * @return The {@link Record}.
	 */
	public Record getRecord(int id) {
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS + " WHERE " + KEY_ID + " = " + id;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);
		Record record = null;
		if (cursor.moveToFirst()){
			record = createRecord(cursor);
		} 
		
        cursor.close();
		db.close();
		// return contact
		return record;
	}

	/**
	 * Gets all {@link Record Records} saved in the database. 
	 * @return A ArrayList of all the {@link Record Records} in the Database.
	 */
	public ArrayList<Record> getAllRecords() {
		ArrayList<Record> recordList = new ArrayList<Record>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		Log.i("Database", "Start loop");
		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Log.i("Database", "Add Record");
				Record record = createRecord(cursor);				
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}
        cursor.close();
		db.close();
		// return record list
		return recordList;
	}
    
	/**
	 * Gets a single {@link Record} with the given attack id from the database.
	 * @param attack_id The attack id of the {@link Record};
	 * @return The {@link Record}.
	 */
    public Record getRecordOfAttackId(long attack_id) {
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS + " WHERE " + KEY_ATTACK_ID + " = " + attack_id + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        Record record = null;
        
		if (cursor.moveToFirst()) {
				record = createRecord(cursor);
		}
        cursor.close();
 
        // return record list
        db.close();
        return record;
    }
    
	/**
	 * Gets all received {@link Record Records} for every attack identified by its attack id and ordered by date.
	 * @return A ArrayList with all received {@link Record Records} for each attack id in the Database.
	 */
    public ArrayList<Record> getAllReceivedRecordsOfEachAttack() {
    	ArrayList<Record> recordList = new ArrayList<Record>();
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS + " WHERE " + KEY_TYPE + "='RECEIVE'" + " ORDER BY " + KEY_TIME;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
		
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}       
        cursor.close();
 
        // return record list
        db.close();
        return recordList;
    }
    
	/**
	 * Gets a representative {@link Record} for every attack identified by its attack id.
	 * @return A ArrayList with one {@link Record Records} for each attack id in the Database.
	 */
    public ArrayList<Record> getRecordOfEachAttack() {
    	ArrayList<Record> recordList = new ArrayList<Record>();
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
		
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext());
		}       
        cursor.close();
 
        // return record list
        db.close();
        return recordList;
    }
    
	/**
	 * Gets a representative {@link Record} for every attack with a higher attack id than the specified.
	 * @param attack_id The attack id to match the query against.
	 * @return A ArrayList with one {@link Record Records} for each attack id higher than the given.
	 */
    public ArrayList<Record> getRecordOfEachAttack(long attack_id) {
    	ArrayList<Record> recordList = new ArrayList<Record>();
        String selectQuery = "SELECT  * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS + " WHERE " + KEY_ATTACK_ID + " > " + attack_id + " GROUP BY " + KEY_ATTACK_ID;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
		
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Record record = createRecord(cursor);
				// Adding record to list
				recordList.add(record);
			} while (cursor.moveToNext()); 
		}       
        cursor.close();
 
        // return count
        db.close();
        return recordList;
    }
	
	/**
	 * Determines the number of {@link Record Records} in the database.
	 * @return The number of {@link Record Records} in the database.
	 */
    public int getRecordCount() {
        String countQuery = "SELECT  * FROM " + TABLE_RECORDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
       int result = cursor.getCount();
       cursor.close();
 
        // return count
        db.close();
        return result;
    }
    
	/**
	 * Determines the number of different attack_ids in the database.
	 * @return The number of different attack_ids in the database.
	 */
    public int getAttackCount() {
        String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
 
        // return count
        db.close();
        return result;
    }
    
	/**
	 * Determines the number of different attack_ids for a specific protocol in the database.
	 * @param protocol The String representation of the {@link de.tudarmstadt.informatik.hostage.protocol.Protocol Protocol}
	 * @return The number of different attack_ids in the database.
	 */
    public int getAttackPerProtokolCount(String protocol) {
        String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO + " WHERE " + KEY_PROTOCOL + " = " + "'" + protocol + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
 
        // return count
        db.close();
        return result;
    }
    
   /**
    * Determines the smallest attack id stored in the database.
    * @return The smallest attack id stored in the database.
    */
    public long getSmallestAttackId(){
    	String selectQuery = "SELECT MIN(" + KEY_ATTACK_ID +") FROM " + TABLE_ATTACK_INFO;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);        
        int result;
        
        if (cursor.moveToFirst()) {
        	result = cursor.getInt(0);
        } else{
        	result = -1;
        }        
    	cursor.close();
    	db.close();
    	return result;
    }
    
    /**
     * Determines the highest attack id stored in the database.
     * @return The highest attack id stored in the database.
     */
    public long getHighestAttackId(){
    	String selectQuery = "SELECT MAX(" + KEY_ATTACK_ID +") FROM " + TABLE_ATTACK_INFO;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        int result;
        
        if (cursor.moveToFirst()) {
        	result = cursor.getInt(0);
        } else{
        	result = -1;
        }        
    	cursor.close();
    	db.close();
    	return result;
    }
   
    /**
     * Determines if an attack has been recorded on a specific protocol in a network with a given BSSID.
     * @param protocol The {@link de.tudarmstadt.informatik.hostage.protocol.Protocol Protocol} to inspect.
     * @param BSSID The BSSID of the network.
     * @return True if an attack on the given protocol has been recorded in a network with the given BSSID, else false.
     */
    public boolean bssidSeen(String protocol, String BSSID){
        String countQuery = "SELECT  * FROM " + TABLE_ATTACK_INFO + " NATURAL JOIN " + TABLE_BSSIDS+ " WHERE " + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " AND " + KEY_BSSID + " = " + "'" + BSSID + "'";
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        int result = cursor.getCount();
        cursor.close();
        db.close();
        return result > 0;        
    }
    
    /**
     * Returns a String array with all BSSIDs stored in the database.
     * @return String[] of all recorded BSSIDs.
     */
    public String[] getAllBSSIDS(){
        String selectQuery = "SELECT  * FROM " + TABLE_BSSIDS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        String[] bssidList = new String[cursor.getCount()];
        int counter = 0;
        // looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				bssidList[counter] = cursor.getString(0);
				counter++;
			} while (cursor.moveToNext());
		}       
        cursor.close();
        db.close();
        return bssidList;
    }
    
    /**
     * Gets the last recorded SSID to a given BSSID.
     * @param bssid The BSSID to match against.
     * @return A String of the last SSID or null if the BSSID is not in the database.
     */
    public String getSSID(String bssid){
    	String selectQuery = "SELECT "+ KEY_SSID +" FROM " + TABLE_BSSIDS + " WHERE " + KEY_BSSID + " = " + "'" + bssid + "'";
    	SQLiteDatabase db = this.getReadableDatabase();
    	Cursor cursor = db.rawQuery(selectQuery, null);
    	String ssid = null;
    	if(cursor.moveToFirst()){
    		ssid = cursor.getString(0);
    	}
        cursor.close();
        db.close();
        return ssid;
    }
    
    /**
     * Deletes all records from {@link #TABLE_RECORDS} with a specific BSSID.
     * @param bssid The BSSID to match against.
     */
    public void deleteByBSSID(String bssid){
    	SQLiteDatabase db = this.getReadableDatabase();
    	db.delete(TABLE_RECORDS, KEY_BSSID + " = ?", new String[]{bssid});
    	db.delete(TABLE_ATTACK_INFO, KEY_BSSID + " = ?", new String[]{bssid});
    	db.close();
    }
    
  //TODO Delete statement überarbeiten
    /**
     * Deletes all records from {@link #TABLE_RECORDS} with a time stamp smaller then the given
     * @param date A Date represented in milliseconds.
     */
    public void deleteByDate(long date){
    	SQLiteDatabase db = this.getReadableDatabase();
    	String deleteQuery = "DELETE  FROM " + TABLE_RECORDS + " WHERE " + KEY_TIME + " < " + date;
    	//TODO Delete statement überarbeiten
//    	String deleteQuery2 = "DELETE "
    	db.execSQL(deleteQuery);
    	db.close();
    }
    
    /**
     *  Deletes all records from {@link #TABLE_RECORDS}.
     */
    public void clearData(){
    	SQLiteDatabase db = this.getReadableDatabase();
        db.delete(TABLE_RECORDS, null, null);
        db.delete(TABLE_ATTACK_INFO, null, null);
        db.close();
    }
}