package de.tudarmstadt.informatik.hostage.logging;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.util.ArrayList;
import de.tudarmstadt.informatik.hostage.logging.Record.TYPE;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
/**
* This class creates SQL tables and handles all access to the database.
* It contains several methods with predefined queries to extract different kinds of information from the database.
* The database contains two tables: {@link #TABLE_RECORDS} and {@link #TABLE_BSSIDS}:
* {@link #TABLE_RECORDS} contains all logging information of a single message record except the SSID.
* {@link #TABLE_BSSIDS} contains the BSSID of all recorded Networks and the corresponding SSID.
* @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_RECORDS = "records";
private static final String TABLE_BSSIDS = "bssids";
// 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_PROTOCOL = "protocol";
private static final String KEY_TYPE = "type";
private static final String KEY_TIME = "timestamp";
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_PACKET = "packet";
// Database sql create statements
private static final String CREATE_RECORD_TABLE = "CREATE TABLE " + TABLE_RECORDS + "(" + KEY_ID
+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_ATTACK_ID + " INTEGER," + KEY_PROTOCOL + " TEXT,"
+ KEY_TYPE + " TEXT," + KEY_TIME + " INTEGER," + KEY_LOCAL_IP
+ " BLOB," + KEY_LOCAL_HOSTNAME + " TEXT," + KEY_LOCAL_PORT + " INTEGER," + KEY_REMOTE_IP
+ " BLOB," + KEY_REMOTE_HOSTNAME + " TEXT," + KEY_REMOTE_PORT + " INTEGER,"
+ KEY_BSSID + " TEXT," + KEY_PACKET + " TEXT,"
+ "FOREIGN KEY("+ KEY_BSSID +") REFERENCES " + TABLE_BSSIDS + "("+KEY_BSSID+")" + ")";
private static final String CREATE_BSSID_TABLE = "CREATE TABLE " + TABLE_BSSIDS + "(" + KEY_BSSID
+ " TEXT PRIMARY KEY," + KEY_SSID + " TEXT" + ")";
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BSSID_TABLE);
db.execSQL(CREATE_RECORD_TABLE);
}
// 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_BSSIDS);
// 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());
ContentValues recordValues = new ContentValues();
recordValues.put(KEY_ATTACK_ID, record.getAttack_id()); // Log Attack ID
recordValues.put(KEY_PROTOCOL, record.getProtocol().toString());
recordValues.put(KEY_TYPE, record.getType().name()); // Log Type
recordValues.put(KEY_TIME, record.getTimestamp()); // Log Timestamp
recordValues.put(KEY_LOCAL_IP, record.getLocalIP().getAddress()); // Log Local IP
recordValues.put(KEY_LOCAL_HOSTNAME, record.getLocalIP().getHostName());
recordValues.put(KEY_LOCAL_PORT, record.getLocalPort()); // Log Local Port
recordValues.put(KEY_REMOTE_IP, record.getRemoteIP().getAddress()); // Log Remote IP
recordValues.put(KEY_REMOTE_HOSTNAME, record.getRemoteIP().getHostName());
recordValues.put(KEY_REMOTE_PORT, record.getRemotePort()); // Log Remote Port
recordValues.put(KEY_BSSID, record.getBSSID());
recordValues.put(KEY_PACKET, record.getPacket()); // Log Packet
// Inserting Rows
db.insertWithOnConflict(TABLE_BSSIDS, null, bssidValues, 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.setProtocol(cursor.getString(2));
record.setType(cursor.getString(3).equals("SEND") ? TYPE.SEND : TYPE.RECEIVE);
record.setTimestamp(cursor.getLong(4));
record.setLocalIP(InetAddress.getByAddress(cursor.getString(6), cursor.getBlob(5)));
record.setLocalPort(Integer.parseInt(cursor.getString(7)));
record.setRemoteIP(InetAddress.getByAddress(cursor.getString(9), cursor.getBlob(8)));
record.setRemotePort(Integer.parseInt(cursor.getString(10)));
record.setBSSID(cursor.getString(11));
record.setPacket(cursor.getString(12));
record.setSSID(cursor.getString(13));
} 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_BSSIDS + " 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 getAllRecords() {
ArrayList recordList = new ArrayList();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS;
SQLiteDatabase db = this.getWritableDatabase();
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();
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_BSSIDS + " 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 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 getRecordOfEachAttack() {
ArrayList recordList = new ArrayList();
String selectQuery = "SELECT * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS + " 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 getRecordOfEachAttack(long attack_id) {
ArrayList recordList = new ArrayList();
String selectQuery = "SELECT * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_BSSIDS + " 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_RECORDS + " GROUP BY " + KEY_ATTACK_ID;
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_RECORDS + " WHERE " + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " GROUP BY " + KEY_ATTACK_ID;
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_RECORDS;
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_RECORDS;
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_RECORDS + " 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.close();
}
/**
* 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;
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.close();
}
}