package de.tudarmstadt.informatik.hostage.deprecated;
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;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import de.tudarmstadt.informatik.hostage.logging.Record;
import de.tudarmstadt.informatik.hostage.logging.MessageRecord.TYPE;
import de.tudarmstadt.informatik.hostage.model.Profile;
import de.tudarmstadt.informatik.hostage.ui.model.LogFilter;
/**
* 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 UglyDbHelper extends SQLiteOpenHelper {
// All Static variables
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "hostage.db";
// Contacts table names
private static final String TABLE_ATTACK_INFO = "attack";
private static final String TABLE_RECORDS = "packet";
private static final String TABLE_BSSIDS = "network";
private static final String TABLE_PROFILES = "profiles";
// Contacts Table Columns names
public static final String KEY_ID = "_id";
public static final String KEY_ATTACK_ID = "_attack_id";
public static final String KEY_TYPE = "type";
public static final String KEY_TIME = "packet_timestamp";
public static final String KEY_PACKET = "packet";
public static final String KEY_PROTOCOL = "protocol";
public static final String KEY_EXTERNAL_IP = "externalIP";
public static final String KEY_LOCAL_IP = "localIP";
public static final String KEY_LOCAL_PORT = "localPort";
public static final String KEY_REMOTE_IP = "remoteIP";
public static final String KEY_REMOTE_PORT = "remotePort";
public static final String KEY_BSSID = "_bssid";
public static final String KEY_SSID = "ssid";
public static final String KEY_LATITUDE = "latitude";
public static final String KEY_LONGITUDE = "longitude";
public static final String KEY_ACCURACY = "accuracy";
public static final String KEY_GEO_TIMESTAMP = "geo_timestamp";
public static final String KEY_PROFILE_ID = "_profile_id";
public static final String KEY_PROFILE_NAME = "profile_name";
public static final String KEY_PROFILE_DESCRIPTION = "profile_description";
public static final String KEY_PROFILE_ICON = "profile_icon";
public static final String KEY_PROFILE_EDITABLE = "profile_editable";
public static final String KEY_PROFILE_ACTIVE = "profile_active";
public static final String KEY_PROFILE_ICON_NAME = "profile_icon_name";
// Database sql create statements
private static final String CREATE_PROFILE_TABLE = "CREATE TABLE " + TABLE_PROFILES + "(" + KEY_PROFILE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ KEY_PROFILE_NAME + " TEXT," + KEY_PROFILE_DESCRIPTION + " TEXT," + KEY_PROFILE_ICON + " TEXT," + KEY_PROFILE_ICON_NAME + " TEXT,"
+ KEY_PROFILE_EDITABLE + " INTEGER," + KEY_PROFILE_ACTIVE + " INTEGER" + ")";
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_PORT + " INTEGER," + KEY_REMOTE_IP + " BLOB," + KEY_REMOTE_PORT
+ " INTEGER," + KEY_BSSID + " 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,"
+ KEY_LATITUDE + " INTEGER," + KEY_LONGITUDE + " INTEGER," + KEY_ACCURACY + " INTEGER," + KEY_GEO_TIMESTAMP + " INTEGER" + ")";
public UglyDbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/*
* // 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";
*/
/**
* Gets all received {@link Record Records} for the specified information in
* the LogFilter ordered by date.
*
* @return A ArrayList with all received {@link Record Records} for the
* LogFilter.
*/
public ArrayList getRecordsForFilter(LogFilter filter) {
ArrayList recordList = new ArrayList();
String selectQuery = "SELECT * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")";
// TIMESTAMPS
selectQuery = selectQuery + " WHERE " + TABLE_RECORDS + "." + KEY_TIME;
selectQuery = selectQuery + " < " + filter.getBelowTimestamp();
selectQuery = selectQuery + " AND " + TABLE_RECORDS + "." + KEY_TIME;
selectQuery = selectQuery + " > " + filter.getAboveTimestamp();
if (filter.getBSSIDs() != null && filter.getBSSIDs().size() > 0) {
selectQuery = selectQuery + " AND ";
selectQuery = selectQuery + filter.getBSSIDQueryStatement(TABLE_BSSIDS, KEY_BSSID);
}
if (filter.getESSIDs() != null && filter.getESSIDs().size() > 0) {
selectQuery = selectQuery + " AND ";
selectQuery = selectQuery + filter.getESSIDQueryStatement(TABLE_BSSIDS, KEY_SSID);
}
if (filter.getProtocols() != null && filter.getProtocols().size() > 0) {
selectQuery = selectQuery + " AND ";
selectQuery = selectQuery + filter.getProtocolsQueryStatement(TABLE_ATTACK_INFO, KEY_PROTOCOL);
}
selectQuery = selectQuery + " GROUP BY " + TABLE_RECORDS + "." + KEY_ATTACK_ID;
if (filter.getSorttype() == LogFilter.SortType.packet_timestamp) {
// DESC
selectQuery = selectQuery + " ORDER BY " + filter.getSorttype() + " DESC";
} else {
selectQuery = selectQuery + " ORDER BY " + filter.getSorttype();
}
System.out.println(selectQuery);
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 all non duplicate Records For the key BSSID.
*
* @return A ArrayList with received Records.
*/
public ArrayList getUniqueBSSIDRecords() {
return this.getUniqueDataEntryForKeyType(KEY_BSSID, TABLE_BSSIDS);
}
/**
* Gets all non duplicate Records For the key ESSID.
*
* @return A ArrayList with received Records.
*/
public ArrayList getUniqueESSIDRecords() {
return this.getUniqueDataEntryForKeyType(KEY_SSID, TABLE_BSSIDS);
}
public ArrayList getUniqueESSIDRecordsForProtocol(String protocol) {
return this.getUniqueIDForProtocol(KEY_SSID, protocol);
}
public ArrayList getUniqueBSSIDRecordsForProtocol(String protocol) {
return this.getUniqueIDForProtocol(KEY_BSSID, protocol);
}
private ArrayList getUniqueIDForProtocol(String id, String protocol) {
ArrayList recordList = new ArrayList();
String selectQuery = "SELECT DISTINCT " + id + " FROM " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ") " + " WHERE "
+ TABLE_ATTACK_INFO + "." + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " ORDER BY " + id; // " NATURAL JOIN "
// +
// TABLE_ATTACK_INFO
// +
// " NATURAL JOIN "
// +
// TABLE_BSSIDS
// +
// " NATURAL JOIN "
// +
// TABLE_PORTS
// +
// ORDERED BY TIME
System.out.println(selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
String record = cursor.getString(0);
recordList.add(record);
} while (cursor.moveToNext());
}
cursor.close();
// return record list
db.close();
return recordList;
}
/**
* Gets all non duplicate Data Entry For a specific KeyType ( e.g. BSSIDs).
*
* @return A ArrayList with received Records.
*/
public ArrayList getUniqueDataEntryForKeyType(String keyType, String table) {
ArrayList recordList = new ArrayList();
// String selectQuery = "SELECT * FROM " + TABLE_RECORDS +
// " NATURAL JOIN " + TABLE_ATTACK_INFO + " NATURAL JOIN " +
// TABLE_BSSIDS + " NATURAL JOIN " + TABLE_PORTS;
String selectQuery = "SELECT DISTINCT " + keyType + " FROM " + table + " ORDER BY " + keyType; // " NATURAL JOIN "
// +
// TABLE_ATTACK_INFO
// +
// " NATURAL JOIN "
// +
// TABLE_BSSIDS
// +
// " NATURAL JOIN "
// +
// TABLE_PORTS
// +
// ORDERED BY TIME
System.out.println(selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
String record = cursor.getString(0);
recordList.add(record);
} while (cursor.moveToNext());
}
cursor.close();
// return record list
db.close();
return recordList;
}
/**
* Adds a given {@link Record} to the database.
*
* @param record
* The added {@link Record} .
*/
public void addRecord(Record record) {
SQLiteDatabase db = this.getWritableDatabase();
HashMap bssidValues = new HashMap();
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());
bssidValues.put(KEY_GEO_TIMESTAMP, record.getTimestampLocation());
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()); // Log Local IP
attackValues.put(KEY_LOCAL_PORT, record.getLocalPort());
attackValues.put(KEY_REMOTE_IP, record.getRemoteIP()); // Log Remote IP
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_ATTACK_INFO, null, attackValues, SQLiteDatabase.CONFLICT_REPLACE);
db.insert(TABLE_RECORDS, null, recordValues);
db.close(); // Closing database connection
// Update Network Information
updateNetworkInformation(bssidValues);
}
/**
* Determines if a network with given BSSID has already been recorded as
* malicious.
*
* @param BSSID
* The BSSID of the network.
* @return True if an attack has been recorded in a network with the given
* BSSID, else false.
*/
public boolean bssidSeen(String BSSID) {
String countQuery = "SELECT * FROM " + TABLE_BSSIDS + " WHERE " + KEY_BSSID + " = " + "'" + BSSID + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int result = cursor.getCount();
cursor.close();
db.close();
return result > 0;
}
public int numBssidSeen(String BSSID) {
String countQuery = "SELECT COUNT(*) FROM " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE "
+ TABLE_BSSIDS + "." + KEY_BSSID + " = " + "'" + BSSID + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.moveToFirst();
int result = cursor.getInt(0);
cursor.close();
db.close();
return result;
}
public int numBssidSeen(String protocol, String BSSID) {
String countQuery = "SELECT COUNT(*) FROM " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE "
+ TABLE_ATTACK_INFO + "." + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " AND " + TABLE_BSSIDS + "." + KEY_BSSID + " = " + "'" + BSSID + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.moveToFirst();
int result = cursor.getInt(0);
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 + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID + ")" + " WHERE " + TABLE_ATTACK_INFO
+ "." + KEY_PROTOCOL + " = " + "'" + protocol + "'" + " AND " + TABLE_BSSIDS + "." + KEY_BSSID + " = " + "'" + BSSID + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
int result = cursor.getCount();
cursor.close();
db.close();
return result > 0;
}
/**
* 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.delete(TABLE_PROFILES, null, null);
db.close();
}
/**
* 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} with a specific Attack ID.
*
* @param attackID
* The Attack ID to match against.
*/
public void deleteByAttackID(long attackID) {
SQLiteDatabase db = this.getReadableDatabase();
db.delete(TABLE_RECORDS, KEY_ATTACK_ID + " = ?", new String[] { String.valueOf(attackID) });
db.delete(TABLE_ATTACK_INFO, KEY_ATTACK_ID + " = ?", new String[] { String.valueOf(attackID) });
db.close();
}
/**
* 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 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 getAllReceivedRecordsOfEachAttack() {
ArrayList recordList = new ArrayList();
String selectQuery = "SELECT * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")" + " WHERE " + KEY_TYPE + "='RECEIVE'" + " ORDER BY " + TABLE_RECORDS + "." + 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 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_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")";
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;
}
/**
* 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);
if (!cursor.moveToFirst()) return 0;
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 getAttackPerProtocolCount(String protocol) {
String countQuery = "SELECT * FROM " + TABLE_ATTACK_INFO + " WHERE " + KEY_PROTOCOL + " = " + "'" + protocol + "'";
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
if (!cursor.moveToFirst()) return 0;
int result = cursor.getCount();
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;
}
public ArrayList> getNetworkInformation() {
String selectQuery = "SELECT * FROM " + TABLE_BSSIDS;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
ArrayList> networkInformation = new ArrayList>();
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
HashMap values = new HashMap();
values.put(KEY_BSSID, cursor.getString(0));
values.put(KEY_SSID, cursor.getString(1));
values.put(KEY_LATITUDE, Double.parseDouble(cursor.getString(2)));
values.put(KEY_LONGITUDE, Double.parseDouble(cursor.getString(3)));
values.put(KEY_ACCURACY, Float.parseFloat(cursor.getString(4)));
values.put(KEY_GEO_TIMESTAMP, cursor.getLong(5));
networkInformation.add(values);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
return networkInformation;
}
/**
* 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 + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")" + " WHERE " + TABLE_RECORDS + "." + 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;
}
/**
* 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;
}
/**
* 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 + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")" + " WHERE " + TABLE_RECORDS + "." + KEY_ATTACK_ID + " = " + attack_id + " GROUP BY " + TABLE_RECORDS + "." + 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_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")" + " GROUP BY " + TABLE_RECORDS + "." + 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;
}
/*
* Returns the Conversation of a specific attack id
*
* @param attack_id Tha attack id to match the query against.
*
* @return A arraylist with all {@link Record Records}s for an attack id.
*/
public ArrayList getConversationForAttackID(long attack_id) {
ArrayList recordList = new ArrayList();
String selectQuery = "SELECT * FROM " + TABLE_RECORDS + " NATURAL JOIN " + TABLE_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")" + " WHERE " + TABLE_RECORDS + "." + KEY_ATTACK_ID + " = " + attack_id;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor.moveToFirst()) {
do {
Record record = createRecord(cursor);
recordList.add(record);
} while (cursor.moveToNext());
}
cursor.close();
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_ATTACK_INFO + " JOIN " + TABLE_BSSIDS + " USING " + "(" + KEY_BSSID
+ ")" + " WHERE " + TABLE_RECORDS + "." + KEY_ATTACK_ID + " > " + attack_id + " GROUP BY " + TABLE_RECORDS + "." + 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 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;
}
/**
* 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;
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_BSSID_TABLE);
db.execSQL(CREATE_ATTACK_INFO_TABLE);
db.execSQL(CREATE_RECORD_TABLE);
db.execSQL(CREATE_PROFILE_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_ATTACK_INFO);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_BSSIDS);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_PROFILES);
// Create tables again
onCreate(db);
}
/**
* Retrieves all the profiles from the database
*
* @return list of profiles
*/
public List getAllProfiles() {
List profiles = new LinkedList();
// Select All Query
String selectQuery = "SELECT * FROM " + TABLE_PROFILES;
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Profile profile = new Profile(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getInt(5) == 1);
if (cursor.getInt(6) == 1) {
profile.mActivated = true;
}
profile.mIconName = cursor.getString(4);
// Adding record to list
profiles.add(profile);
} while (cursor.moveToNext());
}
cursor.close();
db.close();
// return record list
return profiles;
}
/**
* Persists the given profile into the database
*
* @param profile
* the profile which should be persisted
*
* @return
*/
public long persistProfile(Profile profile) {
SQLiteDatabase db = this.getReadableDatabase();
ContentValues values = new ContentValues();
if (profile.mId != -1) {
values.put(KEY_PROFILE_ID, profile.mId);
}
values.put(KEY_PROFILE_NAME, profile.mLabel);
values.put(KEY_PROFILE_DESCRIPTION, profile.mText);
values.put(KEY_PROFILE_ICON, profile.mIconPath);
values.put(KEY_PROFILE_ICON_NAME, profile.mIconName);
values.put(KEY_PROFILE_ACTIVE, profile.mActivated);
values.put(KEY_PROFILE_EDITABLE, profile.mEditable);
return db.replace(TABLE_PROFILES, null, values);
}
/**
* private static final String CREATE_PROFILE_TABLE = "CREATE TABLE " +
* TABLE_PROFILES + "(" + KEY_PROFILE_ID +
* " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_PROFILE_NAME + " TEXT," +
* KEY_PROFILE_DESCRIPTION + " TEXT," + KEY_PROFILE_ICON + " TEXT," +
* KEY_PROFILE_ICON_ID + " INTEGER," + KEY_PROFILE_EDITABLE + " INTEGER," +
* KEY_PROFILE_ACTIVE + " INTEGER" + ")";
*/
public Profile getProfile(int id) {
String selectQuery = "SELECT * FROM " + TABLE_PROFILES + " WHERE " + TABLE_PROFILES + "." + KEY_PROFILE_ID + " = " + id;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(selectQuery, null);
Profile profile = null;
if (cursor.moveToFirst()) {
profile = new Profile(cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getInt(5) == 1);
if (cursor.getInt(6) == 1) {
profile.mActivated = true;
}
profile.mIconName = cursor.getString(5);
}
cursor.close();
db.close();
// return contact
return profile;
}
public void deleteProfile(int id) {
SQLiteDatabase db = this.getReadableDatabase();
db.delete(TABLE_PROFILES, KEY_PROFILE_ID + "=?", new String[] { String.valueOf(id) });
}
public void updateNetworkInformation(ArrayList> networkInformation) {
Log.i("DatabaseHandler", "Starte updating");
for (HashMap values : networkInformation) {
updateNetworkInformation(values);
}
}
public void updateNetworkInformation(HashMap networkInformation) {
SQLiteDatabase db = this.getReadableDatabase();
String bssid = (String) networkInformation.get(KEY_BSSID);
String bssidQuery = "SELECT * FROM " + TABLE_BSSIDS + " WHERE " + KEY_BSSID + " = " + "'" + bssid + "'";
Cursor cursor = db.rawQuery(bssidQuery, null);
int result = cursor.getCount();
if (cursor != null && cursor.moveToFirst() && (result <= 0 || cursor.getLong(5) < (Long) networkInformation.get(KEY_GEO_TIMESTAMP)))
;
{
ContentValues bssidValues = new ContentValues();
bssidValues.put(KEY_BSSID, bssid);
bssidValues.put(KEY_SSID, (String) networkInformation.get(KEY_SSID));
bssidValues.put(KEY_LATITUDE, (double) (Double) networkInformation.get(KEY_LATITUDE));
bssidValues.put(KEY_LONGITUDE, (double) (Double) networkInformation.get(KEY_LONGITUDE));
bssidValues.put(KEY_ACCURACY, (float) (Float) networkInformation.get(KEY_ACCURACY));
bssidValues.put(KEY_GEO_TIMESTAMP, (Long) networkInformation.get(KEY_GEO_TIMESTAMP));
db.insertWithOnConflict(TABLE_BSSIDS, null, bssidValues, SQLiteDatabase.CONFLICT_REPLACE);
}
cursor.close();
db.close();
}
/**
* 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();
record.setId(Integer.parseInt(cursor.getString(0)));
record.setAttack_id(cursor.getLong(1));
record.setType(TYPE.valueOf(cursor.getString(2)));
record.setTimestamp(cursor.getLong(3));
record.setPacket(cursor.getString(4));
record.setProtocol(cursor.getString(5));
record.setExternalIP(cursor.getString(6));
record.setLocalIP(cursor.getString(7));
record.setLocalPort(Integer.parseInt(cursor.getString(8)));
record.setRemoteIP(cursor.getString(9));
record.setRemotePort(Integer.parseInt(cursor.getString(10)));
record.setBssid(cursor.getString(11));
record.setSsid(cursor.getString(12));
record.setLatitude(Double.parseDouble(cursor.getString(13)));
record.setLongitude(Double.parseDouble(cursor.getString(14)));
record.setAccuracy(Float.parseFloat(cursor.getString(15)));
record.setTimestampLocation(cursor.getLong(16));
return record;
}
}