3
0

StatsDatabase.py 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398
  1. import os.path
  2. import random as rnd
  3. import re
  4. import sqlite3
  5. import sys
  6. # TODO: double check this import
  7. # does it complain because libpcapreader is not a .py?
  8. import ID2TLib.libpcapreader as pr
  9. def dict_gen(curs: sqlite3.Cursor):
  10. """
  11. Generates a dictionary of a sqlite3.Cursor object by fetching the query's results.
  12. Taken from Python Essential Reference by David Beazley.
  13. """
  14. field_names = [d[0] for d in curs.description]
  15. while True:
  16. rows = curs.fetchmany()
  17. if not rows:
  18. return
  19. for row in rows:
  20. yield dict(zip(field_names, row))
  21. class StatsDatabase:
  22. def __init__(self, db_path: str):
  23. """
  24. Creates a new StatsDatabase.
  25. :param db_path: The path to the database file
  26. """
  27. self.existing_db = os.path.exists(db_path)
  28. self.database = sqlite3.connect(db_path)
  29. self.cursor = self.database.cursor()
  30. # If DB not existing, create a new DB scheme
  31. if self.existing_db:
  32. if self.get_db_outdated():
  33. print('Statistics database outdated. Recreating database at: ', db_path)
  34. else:
  35. print('Located statistics database at: ', db_path)
  36. else:
  37. print('Statistics database not found. Creating new database at: ', db_path)
  38. def get_file_info(self):
  39. """
  40. Retrieves general file statistics from the database. This includes:
  41. - packetCount : Number of packets in the PCAP file
  42. - captureDuration : Duration of the packet capture in seconds
  43. - timestampFirstPacket : Timestamp of the first captured packet
  44. - timestampLastPacket : Timestamp of the last captured packet
  45. - avgPacketRate : Average packet rate
  46. - avgPacketSize : Average packet size
  47. - avgPacketsSentPerHost : Average number of packets sent per host
  48. - avgBandwidthIn : Average incoming bandwidth
  49. - avgBandwidthOut : Average outgoing bandwidth
  50. :return: a dictionary of keys (see above) and their respective values
  51. """
  52. return [r for r in dict_gen(
  53. self.cursor.execute('SELECT * FROM file_statistics'))][0]
  54. def get_db_exists(self):
  55. """
  56. :return: True if the database was already existent, otherwise False
  57. """
  58. return self.existing_db
  59. def get_db_outdated(self):
  60. """
  61. Retrieves the database version from the database and compares it to the version
  62. it should have to check whether the database is outdated and needs to be recreated.
  63. :return: True if the versions match, otherwise False
  64. """
  65. self.cursor.execute('PRAGMA user_version;')
  66. return self.cursor.fetchall()[0][0] != pr.pcap_processor.get_db_version()
  67. @staticmethod
  68. def _get_selector_keywords():
  69. """
  70. :return: a list of selector keywords
  71. """
  72. return ['most_used', 'least_used', 'avg', 'all']
  73. @staticmethod
  74. def _get_parametrized_selector_keywords():
  75. """
  76. :return: a list of parameterizable selector keywords
  77. """
  78. return ['ipaddress', 'macaddress']
  79. @staticmethod
  80. def _get_extractor_keywords():
  81. """
  82. :return: a list of extractor keywords
  83. """
  84. return ['random', 'first', 'last']
  85. def get_all_named_query_keywords(self):
  86. """
  87. :return: a list of all named query keywords, used to identify named queries
  88. """
  89. return (
  90. self._get_selector_keywords() + self._get_parametrized_selector_keywords() + self._get_extractor_keywords())
  91. @staticmethod
  92. def get_all_sql_query_keywords():
  93. """
  94. :return: a list of all supported SQL keywords, used to identify SQL queries
  95. """
  96. return ["select", "insert"]
  97. def process_user_defined_query(self, query_string: str, query_parameters: tuple = None):
  98. """
  99. Takes as input a SQL query query_string and optional a tuple of parameters which are marked by '?' in the query
  100. and later substituted.
  101. :param query_string: The query to execute
  102. :param query_parameters: The tuple of parameters to inject into the query
  103. :return: the results of the query
  104. """
  105. if query_parameters is not None:
  106. self.cursor.execute(query_string, query_parameters)
  107. else:
  108. self.cursor.execute(query_string)
  109. self.database.commit()
  110. return self.cursor.fetchall()
  111. def get_field_types(self, *table_names):
  112. """
  113. Creates a dictionary whose keys are the fields of the given table(s) and whose values are the appropriate field
  114. types, like TEXT for strings and REAL for float numbers.
  115. :param table_names: The name of table(s)
  116. :return: a dictionary of {field_name : field_type} for fields of all tables
  117. """
  118. dic = {}
  119. for table in table_names:
  120. self.cursor.execute("PRAGMA table_info('%s')" % table)
  121. results = self.cursor.fetchall()
  122. for field in results:
  123. dic[field[1].lower()] = field[2]
  124. return dic
  125. def named_query_parameterized(self, keyword: str, param_op_val: list):
  126. """
  127. Executes a parameterizable named query.
  128. :param keyword: The query to be executed, like ipaddress or macadress
  129. :param param_op_val: A list consisting of triples with (parameter, operator, value)
  130. :return: the results of the executed query
  131. """
  132. named_queries = {
  133. "ipaddress": "SELECT DISTINCT ip_statistics.ipAddress from ip_statistics INNER JOIN ip_mac, ip_ttl, "
  134. "ip_ports, ip_protocols ON ip_statistics.ipAddress=ip_mac.ipAddress AND "
  135. "ip_statistics.ipAddress=ip_ttl.ipAddress AND ip_statistics.ipAddress=ip_ports.ipAddress "
  136. "AND ip_statistics.ipAddress=ip_protocols.ipAddress WHERE ",
  137. "macaddress": "SELECT DISTINCT macAddress from ip_mac WHERE "}
  138. query = named_queries.get(keyword)
  139. field_types = self.get_field_types('ip_mac', 'ip_ttl', 'ip_ports', 'ip_protocols', 'ip_statistics', 'ip_mac')
  140. conditions = []
  141. for key, op, value in param_op_val:
  142. # this makes sure that TEXT fields are queried by strings,
  143. # e.g. ipAddress=192.168.178.1 --is-converted-to--> ipAddress='192.168.178.1'
  144. if field_types.get(key) == 'TEXT':
  145. if not str(value).startswith("'") and not str(value).startswith('"'):
  146. value = "'" + value + "'"
  147. # this replacement is required to remove ambiguity in SQL query
  148. if key == 'ipAddress':
  149. key = 'ip_mac.ipAddress'
  150. conditions.append(key + op + str(value))
  151. where_clause = " AND ".join(conditions)
  152. query += where_clause
  153. self.cursor.execute(query)
  154. return self.cursor.fetchall()
  155. def _process_named_query(self, query_param_list):
  156. """
  157. Executes a named query.
  158. :param query_param_list: A query list consisting of (keyword, params), e.g. [(most_used, ipAddress), (random,)]
  159. :return: the result of the query
  160. """
  161. # Definition of SQL queries associated to named queries
  162. named_queries = {
  163. "most_used.ipaddress": "SELECT ipAddress FROM ip_statistics WHERE (pktsSent+pktsReceived) == "
  164. "(SELECT MAX(pktsSent+pktsReceived) from ip_statistics) ORDER BY ipAddress ASC",
  165. "most_used.macaddress": "SELECT macAddress FROM (SELECT macAddress, COUNT(*) as occ from ip_mac GROUP BY "
  166. "macAddress) WHERE occ=(SELECT COUNT(*) as occ from ip_mac GROUP BY macAddress "
  167. "ORDER BY occ DESC LIMIT 1) ORDER BY macAddress ASC",
  168. "most_used.portnumber": "SELECT portNumber FROM ip_ports GROUP BY portNumber HAVING COUNT(portNumber)="
  169. "(SELECT MAX(cntPort) from (SELECT portNumber, COUNT(portNumber) as cntPort FROM "
  170. "ip_ports GROUP BY portNumber)) ORDER BY portNumber ASC",
  171. "most_used.protocolname": "SELECT protocolName FROM ip_protocols GROUP BY protocolName HAVING "
  172. "COUNT(protocolCount)=(SELECT COUNT(protocolCount) as cnt FROM ip_protocols "
  173. "GROUP BY protocolName ORDER BY cnt DESC LIMIT 1) ORDER BY protocolName ASC",
  174. "most_used.ttlvalue": "SELECT ttlValue FROM (SELECT ttlValue, SUM(ttlCount) as occ FROM ip_ttl GROUP BY "
  175. "ttlValue) WHERE occ=(SELECT SUM(ttlCount) as occ FROM ip_ttl GROUP BY ttlValue "
  176. "ORDER BY occ DESC LIMIT 1) ORDER BY ttlValue ASC",
  177. "most_used.mssvalue": "SELECT mssValue FROM (SELECT mssValue, SUM(mssCount) as occ FROM tcp_mss GROUP BY "
  178. "mssValue) WHERE occ=(SELECT SUM(mssCount) as occ FROM tcp_mss GROUP BY mssValue "
  179. "ORDER BY occ DESC LIMIT 1) ORDER BY mssValue ASC",
  180. "most_used.winsize": "SELECT winSize FROM (SELECT winSize, SUM(winCount) as occ FROM tcp_win GROUP BY "
  181. "winSize) WHERE occ=(SELECT SUM(winCount) as occ FROM tcp_win GROUP BY winSize ORDER "
  182. "BY occ DESC LIMIT 1) ORDER BY winSize ASC",
  183. "most_used.ipclass": "SELECT ipClass FROM (SELECT ipClass, COUNT(*) as occ from ip_statistics GROUP BY "
  184. "ipClass ORDER BY occ DESC) WHERE occ=(SELECT COUNT(*) as occ from ip_statistics "
  185. "GROUP BY ipClass ORDER BY occ DESC LIMIT 1) ORDER BY ipClass ASC",
  186. "least_used.ipaddress": "SELECT ipAddress FROM ip_statistics WHERE (pktsSent+pktsReceived) == (SELECT "
  187. "MIN(pktsSent+pktsReceived) from ip_statistics) ORDER BY ipAddress ASC",
  188. "least_used.macaddress": "SELECT macAddress FROM (SELECT macAddress, COUNT(*) as occ from ip_mac GROUP "
  189. "BY macAddress) WHERE occ=(SELECT COUNT(*) as occ from ip_mac GROUP BY macAddress "
  190. "ORDER BY occ ASC LIMIT 1) ORDER BY macAddress ASC",
  191. "least_used.portnumber": "SELECT portNumber FROM ip_ports GROUP BY portNumber HAVING COUNT(portNumber)="
  192. "(SELECT MIN(cntPort) from (SELECT portNumber, COUNT(portNumber) as cntPort FROM "
  193. "ip_ports GROUP BY portNumber)) ORDER BY portNumber ASC",
  194. "least_used.protocolname": "SELECT protocolName FROM ip_protocols GROUP BY protocolName HAVING "
  195. "COUNT(protocolCount)=(SELECT COUNT(protocolCount) as cnt FROM ip_protocols "
  196. "GROUP BY protocolName ORDER BY cnt ASC LIMIT 1) ORDER BY protocolName ASC",
  197. "least_used.ttlvalue": "SELECT ttlValue FROM (SELECT ttlValue, SUM(ttlCount) as occ FROM ip_ttl GROUP BY "
  198. "ttlValue) WHERE occ=(SELECT SUM(ttlCount) as occ FROM ip_ttl GROUP BY ttlValue "
  199. "ORDER BY occ ASC LIMIT 1) ORDER BY ttlValue ASC",
  200. "least_used.mssvalue": "SELECT mssValue FROM (SELECT mssValue, SUM(mssCount) as occ FROM tcp_mss GROUP BY "
  201. "mssValue) WHERE occ=(SELECT SUM(mssCount) as occ FROM tcp_mss GROUP BY mssValue "
  202. "ORDER BY occ ASC LIMIT 1) ORDER BY mssValue ASC",
  203. "least_used.winsize": "SELECT winSize FROM (SELECT winSize, SUM(winCount) as occ FROM tcp_win GROUP BY "
  204. "winSize) WHERE occ=(SELECT SUM(winCount) as occ FROM tcp_win GROUP BY winSize "
  205. "ORDER BY occ ASC LIMIT 1) ORDER BY winSize ASC",
  206. "avg.pktsreceived": "SELECT avg(pktsReceived) from ip_statistics",
  207. "avg.pktssent": "SELECT avg(pktsSent) from ip_statistics",
  208. "avg.kbytesreceived": "SELECT avg(kbytesReceived) from ip_statistics",
  209. "avg.kbytessent": "SELECT avg(kbytesSent) from ip_statistics",
  210. "avg.ttlvalue": "SELECT avg(ttlValue) from ip_ttl",
  211. "avg.mss": "SELECT avg(mssValue) from tcp_mss",
  212. "all.ipaddress": "SELECT ipAddress from ip_statistics ORDER BY ipAddress ASC",
  213. "all.ttlvalue": "SELECT DISTINCT ttlValue from ip_ttl ORDER BY ttlValue ASC",
  214. "all.mss": "SELECT DISTINCT mssValue from tcp_mss ORDER BY mssValue ASC",
  215. "all.macaddress": "SELECT DISTINCT macAddress from ip_mac ORDER BY macAddress ASC",
  216. "all.portnumber": "SELECT DISTINCT portNumber from ip_ports ORDER BY portNumber ASC",
  217. "all.protocolname": "SELECT DISTINCT protocolName from ip_protocols ORDER BY protocolName ASC"}
  218. # Retrieve values by selectors, if given, reduce results by extractor
  219. last_result = 0
  220. for q in query_param_list:
  221. # if selector, like avg, ttl, is given
  222. if any(e in q[0] for e in self._get_selector_keywords()):
  223. (keyword, param) = q
  224. query = named_queries.get(keyword + "." + param)
  225. self.cursor.execute(str(query))
  226. last_result = self.cursor.fetchall()
  227. # if selector is parametrized, i.e. ipAddress(mac=AA:BB:CC:DD:EE) or macAddress(ipAddress=192.168.178.1)
  228. elif any(e in q[0] for e in self._get_parametrized_selector_keywords()) and any(
  229. o in q[1] for o in ["<", "=", ">", "<=", ">="]):
  230. (keyword, param) = q
  231. # convert string into list of triples
  232. # example string 'paramName1<operator1>paramValue1,paramName2<operator2>paramValue2,...'
  233. param_op_val = [(key, op, value) for (key, op, value) in
  234. [re.split("(<=|>=|>|<|=)", x) for x in param.split(",")]]
  235. last_result = self.named_query_parameterized(keyword, param_op_val)
  236. # if extractor, like random, first, last, is given
  237. elif any(e in q[0] for e in self._get_extractor_keywords()) and (
  238. isinstance(last_result, list) or isinstance(last_result, tuple)):
  239. extractor = q[0]
  240. if extractor == 'random':
  241. index = rnd.randint(a=0, b=len(last_result) - 1)
  242. last_result = last_result[index]
  243. elif extractor == 'first':
  244. last_result = last_result[0]
  245. elif extractor == 'last':
  246. last_result = last_result[-1]
  247. return last_result
  248. def process_db_query(self, query_string_in: str, print_results=False, sql_query_parameters: tuple = None):
  249. """
  250. Processes a database query. This can either be a standard SQL query or a named query (predefined query).
  251. :param query_string_in: The string containing the query
  252. :param print_results: Indicated whether the results should be printed to terminal (True) or not (False)
  253. :param sql_query_parameters: Parameters for the SQL query (optional)
  254. :return: the results of the query
  255. """
  256. named_query_keywords = self.get_all_named_query_keywords()
  257. # Clean query_string
  258. query_string = query_string_in.lower().lstrip()
  259. # query_string is a user-defined SQL query
  260. result = None
  261. if sql_query_parameters is not None or query_string.startswith("select") or query_string.startswith("insert"):
  262. result = self.process_user_defined_query(query_string, sql_query_parameters)
  263. # query string is a named query -> parse it and pass it to statisticsDB
  264. elif any(k in query_string for k in named_query_keywords) and all(k in query_string for k in ['(', ')']):
  265. # Clean query_string
  266. query_string = query_string.replace(" ", "")
  267. # Validity check: Brackets
  268. brackets_open, brackets_closed = query_string.count("("), query_string.count(")")
  269. if not (brackets_open == brackets_closed):
  270. sys.stderr.write("Bracketing of given query '" + query_string + "' is incorrect.")
  271. # Parse query string into [ (query_keyword1, query_params1), ... ]
  272. delimiter_start, delimiter_end = "(", ")"
  273. kplist = []
  274. current_word = ""
  275. for char in query_string: # process characters one-by-one
  276. # if char is no delimiter, add char to current_word
  277. if char != delimiter_end and char != delimiter_start:
  278. current_word += char
  279. # if a start delimiter was found and the current_word so far is a keyword, add it to kplist
  280. elif char == delimiter_start:
  281. if current_word in named_query_keywords:
  282. kplist.append((current_word,))
  283. current_word = ""
  284. else:
  285. print("ERROR: Unrecognized keyword '" + current_word + "' found. Ignoring query.")
  286. return
  287. # else if characeter is end delimiter and there were no two directly following ending delimiters,
  288. # the current_word must be the parameters of an earlier given keyword
  289. elif char == delimiter_end and len(current_word) > 0:
  290. kplist[-1] += (current_word,)
  291. current_word = ""
  292. result = self._process_named_query(kplist[::-1])
  293. else:
  294. sys.stderr.write(
  295. "Query invalid. Only named queries and SQL SELECT/INSERT allowed. Please check the query's syntax!\n")
  296. return
  297. # If result is tuple/list with single element, extract value from list
  298. requires_extraction = (isinstance(result, list) or isinstance(result, tuple)) and len(result) == 1 and \
  299. (not isinstance(result[0], tuple) or len(result[0]) == 1)
  300. while requires_extraction:
  301. if isinstance(result, list) or isinstance(result, tuple):
  302. result = result[0]
  303. else:
  304. requires_extraction = False
  305. # If tuple of tuples or list of tuples, each consisting of single element is returned,
  306. # then convert it into list of values, because the returned colum is clearly specified by the given query
  307. if (isinstance(result, tuple) or isinstance(result, list)) and all(len(val) == 1 for val in result):
  308. result = [c for c in result for c in c]
  309. # Print results if option print_results is True
  310. if print_results:
  311. if isinstance(result, list) and len(result) == 1:
  312. result = result[0]
  313. print("Query returned 1 record:\n")
  314. for i in range(0, len(result)):
  315. print(str(self.cursor.description[i][0]) + ": " + str(result[i]))
  316. else:
  317. self._print_query_results(query_string_in, result)
  318. return result
  319. def _print_query_results(self, query_string_in: str, result):
  320. """
  321. Prints the results of a query.
  322. Based on http://stackoverflow.com/a/20383011/3017719.
  323. :param query_string_in: The query the results belong to
  324. :param result: The results of the query
  325. """
  326. # Print number of results according to type of result
  327. if isinstance(result, list):
  328. print("Query returned " + str(len(result)) + " records:\n")
  329. else:
  330. print("Query returned 1 record:\n")
  331. # Print query results
  332. if query_string_in.lstrip().upper().startswith(
  333. "SELECT") and result is not None and self.cursor.description is not None:
  334. widths = []
  335. columns = []
  336. tavnit = '|'
  337. separator = '+'
  338. for cd in self.cursor.description:
  339. widths.append(len(cd) + 10)
  340. columns.append(cd[0])
  341. for w in widths:
  342. tavnit += " %-" + "%ss |" % (w,)
  343. separator += '-' * w + '--+'
  344. print(separator)
  345. print(tavnit % tuple(columns))
  346. print(separator)
  347. if isinstance(result, list):
  348. for row in result:
  349. print(tavnit % row)
  350. else:
  351. print(tavnit % result)
  352. print(separator)
  353. else:
  354. print(result)