21
23
"auto_increment primary key "
22
24
", action TEXT, msg TEXT, rx TEXT, "
23
25
"rx_type INT, url TEXT, "
24
"zone TEXT, arg_name TEXT);")
26
"zone TEXT, arg_name TEXT, INDEX id (rule_id));")
26
28
self.cursor.execute("DROP TABLES IF EXISTS connections")
27
29
self.cursor.execute("CREATE TABLE connections (connection_id INTEGER "
28
30
"auto_increment primary key, "
29
31
"src_peer_id INT, dst_peer_id INT, exception_id "
30
32
"INT, capture_id INT, date TIMESTAMP default "
31
"CURRENT_TIMESTAMP);")
33
"CURRENT_TIMESTAMP, match_id INT, INDEX id (connection_id, exception_id));")
33
35
self.cursor.execute("DROP TABLES IF EXISTS peer")
34
36
self.cursor.execute("CREATE TABLE peer (peer_id INTEGER "
35
37
"auto_increment primary key, "
36
"peer_ip TEXT, peer_host TEXT, peer_tags TEXT);")
38
"peer_ip TEXT, peer_host TEXT, peer_tags TEXT, INDEX id (peer_id));")
38
40
self.cursor.execute("DROP TABLES IF EXISTS exception")
39
41
self.cursor.execute("CREATE TABLE exception (exception_id integer "
40
42
"auto_increment primary key "
41
",url TEXT, md5 TEXT, count INT default 1);")
43
",url TEXT, md5 TEXT, count INT default 1, INDEX id (exception_id));")
43
45
self.cursor.execute("DROP TABLES IF EXISTS match_zone")
44
46
self.cursor.execute("CREATE TABLE match_zone (match_id INTEGER "
45
47
"auto_increment primary key, exception_id INTEGER, "
46
"zone TEXT, arg_name TEXT, rule_id INTEGER);")
48
"zone TEXT, arg_name TEXT, rule_id INTEGER, INDEX id (match_id, exception_id, rule_id));")
48
50
self.cursor.execute("DROP TABLES IF EXISTS capture")
49
51
self.cursor.execute("CREATE TABLE capture (capture_id INTEGER "
101
103
capture_id = self.last_id()
102
104
return capture_id
104
def sig_to_db(self, raw_request, sig, add_capture=False):
106
def sig_to_db(self, raw_request, sig, add_capture=False, date = None):
106
108
Insert signature into database. returns
107
109
associated connection_id.
116
118
"VALUES (%s)", (d.get("server", "")))
117
119
host_id = self.last_id()
118
120
self.cursor.execute('SELECT 1 FROM exception where md5=%s', (sig_hash))
119
if self.cursor.fetchall():
121
if self.cursor.fetchall():
120
122
self.cursor.execute("UPDATE exception SET url=%s,md5=%s,count = count + 1 "
121
123
"where md5=%s", (d.get("uri", ""), sig_hash, sig_hash))
122
124
self.cursor.execute("select exception_id from exception where url=%s and md5=%s", (d.get('uri', ''), sig_hash))
128
130
if self.cursor.fetchall():
129
131
add_capture = True
130
132
capture_id = self.add_capture(exception_id, raw_request, add_capture)
134
connection_id = self.last_id()
135
self.add_matchzones(exception_id, d)
136
match_id = self.last_id()
131
137
self.cursor.execute("INSERT INTO connections (src_peer_id, "
132
"dst_peer_id, exception_id, capture_id)"
133
"VALUES (%s, %s, %s, %s)", (str(ip_id),
138
"dst_peer_id, exception_id, capture_id, date, match_id)"
139
"VALUES (%s, %s, %s, %s, %s, %s)", (str(ip_id),
135
141
str(exception_id),
137
connection_id = self.last_id()
138
self.add_matchzones(exception_id, d)
142
str(capture_id), datetime.now() if date is None else date, str(match_id)))
139
143
# self.cursor.execute("UPDATE exception SET md5=%s where "
140
144
# "exception_id=%s", (sig_hash, str(exception_id)))
141
145
return (connection_id)
260
264
if __name__ == '__main__':
261
db = MySQLConnector.MySQLConnector().connect()
262
cursor = db.cursor(MySQLdb.cursors.DictCursor)
263
bla = signature_extractor(cursor)
264
# bla.extract_exceptions()
265
# bla.extract_whitelists()