public static ArrayList<StatisticalReports> retrieveStatistics(String username, String password) {
ArrayList<StatisticalReports> allStatistics = new ArrayList<StatisticalReports>();
DBCreation sql = DBCreation.getInstance();
Connection conn;
ResultSet res = null;
PreparedStatement st;
conn = sql.connect();
ArrayList<String> nodes = new ArrayList<String>();
int i;
if (!checkMobileExistance(username, password)) {
System.out.println("Invalid un or pw of android client");
return null;
}
try {
st = conn.prepareStatement("select clientID from clients");
res = st.executeQuery();
while (res.next()) {
nodes.add(res.getString("clientID"));
}
st.clearParameters();
for (i = 0; i < nodes.size(); i++) {
StatisticalReports nodeStatistics = new StatisticalReports();
st = conn.prepareStatement("SELECT * FROM statistics WHERE nodeID = ? ");
st.setString(1, nodes.get(i));
res = st.executeQuery();
ArrayList<StatisticsEntry> statistic = new ArrayList<StatisticsEntry>();
while (res.next()) {
StatisticsEntry stat = new StatisticsEntry();
stat.setNodeID(res.getString("nodeID"));
stat.setInterfaceName(res.getString("interfaceName"));
// stat.setMaliciousPatternID(res.getInt("maliciousPatternID"));
stat.setMaliciousPattern(getMaliciousByID(res.getInt("maliciousPatternID")));
stat.setInterfaceIP(res.getString("interfaceIP"));
stat.setFrequency(res.getInt("frequency"));
statistic.add(stat);
}
nodeStatistics.setStatisticalReportEntries(statistic);
allStatistics.add(nodeStatistics);
st.clearParameters();
}
} catch (SQLException e) {
e.printStackTrace();
// System.out.println("Can't get malicious patterns from db");
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return allStatistics;
}
/// for debugging ///
public static void printRetrieveStatistics() {
List<StatisticalReports> allReports = retrieveStatistics("admin", "1234");
if (allReports == null) return;
for (int i = 0; i < allReports.size(); i++) {
System.out.println("+++++++++++ CLIENT ++++++++++++");
StatisticalReports report = allReports.get(i);
List<StatisticsEntry> entriesList = report.getStatisticalReportEntries();
for (int j = 0; j < entriesList.size(); j++) {
StatisticsEntry entry = entriesList.get(j);
System.out.println("\n------------------------------");
System.out.println("nodeID: " + entry.getNodeID());
System.out.println("interfaceName: " + entry.getInterfaceName());
System.out.println("ip: " + entry.getInterfaceIP());
System.out.println("pattern: " + entry.getMaliciousPattern());
System.out.println("freq: " + entry.getFrequency());
}
}
}
////////////////////////////////////////////////////////////////////////
/// Insert to db an S-MPSM entry. If entry exists, updates frequency ///
@SuppressWarnings("resource")
public static void updateStatistics(StatisticsEntry entry, String uid) {
String nodeID = uid;
String interfaceName = entry.getInterfaceName();
String interfaceIP = entry.getInterfaceIp();
String maliciousPattern = entry.getMaliciousPattern();
int frequency = entry.getFrequency();
DBCreation sql = DBCreation.getInstance(); // new DBCreation();
Connection conn;
ResultSet res = null;
PreparedStatement st;
conn = sql.connect();
try {
// st = conn.prepareStatement("SELECT * FROM clients c, statistics s, maliciousPatterns m
// WHERE nodeID=? AND interfaceIP=? AND s.maliciousPatternID=m.patternID AND
// (m.maliciousStringPattern=? OR m.maliciousIP=?)");
st =
conn.prepareStatement(
"SELECT nodeID,interfaceName,interfaceIP,maliciousPatternID FROM statistics WHERE nodeID=? AND interfaceName=? AND interfaceIP=? AND maliciousPatternID=(select patternID from maliciousPatterns WHERE maliciousStringPattern=? OR maliciousIP=?)");
st.setString(1, nodeID);
st.setString(2, interfaceName);
st.setString(3, interfaceIP);
st.setString(4, maliciousPattern);
st.setString(5, maliciousPattern);
res = st.executeQuery();
///////////////////////////////////////////////////
/// If the trinity already exist in an entry ///
/// (nodeID - interfaceIP - maliciousPattern) ///
/// Then we just update the frequency ///
if (res.next()) {
st.clearParameters();
// freq = res.getInt("frequency");
// st = conn.prepareStatement("UPDATE statistics SET frequency=frequency+? WHERE
// nodeID=? AND interfaceIP=? AND maliciousPattern=?");
st =
conn.prepareStatement(
"UPDATE statistics SET frequency=frequency+? WHERE nodeID=? AND interfaceIP=? AND maliciousPatternID=(select patternID from maliciousPatterns WHERE maliciousStringPattern=? OR maliciousIP=?)");
st.setInt(1, frequency);
st.setString(2, nodeID);
st.setString(3, interfaceIP);
st.setString(4, maliciousPattern);
st.setString(5, maliciousPattern);
st.executeUpdate();
conn.close();
} else {
st.clearParameters();
st =
conn.prepareStatement(
"INSERT INTO statistics (nodeID, interfaceName, interfaceIP, maliciousPatternID, frequency) VALUES ( ?, ?, ?, (select patternID from maliciousPatterns where maliciousStringPattern=? OR maliciousIP=?), ?)");
// st = conn.prepareStatement("INSERT INTO statistics (nodeID, interfaceName,
// interfaceIP, maliciousPatternID, frequency) VALUES ( (select clientID from clients where
// clientID=?), ?, ?, ?, ?)");
st.setString(1, nodeID);
st.setString(2, interfaceName);
st.setString(3, interfaceIP);
st.setString(4, maliciousPattern);
st.setString(5, maliciousPattern);
st.setInt(6, frequency);
st.executeUpdate();
st.clearParameters();
}
} catch (SQLException e) {
System.out.println("Can't insert into statistics table");
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
