123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462 |
- 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.<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_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_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_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_EXTERNAL_IP + " TEXT," + 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_EXTERNAL_IP, record.getExternalIP());
- 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.setExternalIP(cursor.getString(5));
- record.setLocalIP(InetAddress.getByAddress(cursor.getString(7), cursor.getBlob(6)));
- record.setLocalPort(Integer.parseInt(cursor.getString(8)));
- record.setRemoteIP(InetAddress.getByAddress(cursor.getString(10), cursor.getBlob(9)));
- record.setRemotePort(Integer.parseInt(cursor.getString(11)));
- record.setBSSID(cursor.getString(12));
- record.setPacket(cursor.getString(13));
- record.setSSID(cursor.getString(14));
- } 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<Record> getAllRecords() {
- ArrayList<Record> recordList = new ArrayList<Record>();
- // 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 all received {@link Record Records} for every attack identified by its attack id and ordered by date.
- * @return A ArrayList with one {@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_BSSIDS + " 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_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<Record> getRecordOfEachAttack(long attack_id) {
- ArrayList<Record> recordList = new ArrayList<Record>();
- 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();
- }
- }
|