6
#exception + zone : select * from exception LEFT JOIN (peer as srcpeer, peer as dstpeer, connections, match_zone) on (connections.src_peer_id = srcpeer.peer_id and connections.dst_peer_id = dstpeer.peer_id and connections.exception_id = exception.exception_id and match_zone.exception_id = exception.exception_id);
10
#select de gros bourrin : select exc.url, exc.count, exc.md5, cap.http_request, srcpeer.peer_ip, dstpeer.peer_host, mz.zone, mz.arg_name, mz.rule_id from exception as exc, capture as cap, peer as srcpeer, peer as dstpeer, match_zone as mz, connections as conn WHERE (mz.exception_id = exc.exception_id and cap.exception_id = exc.exception_id and srcpeer.peer_id = conn.src_peer_id and conn.dst_peer_id = dstpeer.peer_id);
13
# select all exception with associated peers.
14
##select * from exception LEFT JOIN (peer as srcpeer, peer as dstpeer, connections) on (connections.src_peer_id = srcpeer.peer_id and connections.dst_peer_id = dstpeer.peer_id and exception.connection_id = connections.connection_id) LIMIT 10;
16
# select all exceptions with associated zone_match and peers.
17
# select * from exception LEFT JOIN (peer as srcpeer, peer as dstpeer, connections, match_zone) on (connections.src_peer_id = srcpeer.peer_id and connections.dst_peer_id = dstpeer.peer_id and exception.connection_id = connections.connection_id and match_zone.exception_id = exception.exception_id) where srcpeer.peer_ip != '88.191.133.106' and srcpeer.peer_ip != '82.234.123.117' and srcpeer.peer_ip != '82.247.12.197';
19
class signature_parser:
20
def __init__(self, host, user, password, dbname):
21
# print "[+] Connecting to database"
22
self.db = MySQLdb.connect(host, user, password, dbname)
26
self.cursor = self.db.cursor()
27
if self.cursor is None:
30
# Checking wether the base already exists
32
self.cursor.execute("SELECT COUNT(*) FROM exception")
36
print ("[+] drop'ing and creating new tables")
37
self.cursor.execute("DROP TABLES IF EXISTS rules")
38
self.cursor.execute("CREATE TABLE rules (rule_id integer "
39
"auto_increment primary key "
40
", action TEXT, msg TEXT, rx TEXT, "
41
"rx_type INT, url TEXT, "
42
"zone TEXT, arg_name TEXT);")
44
self.cursor.execute("DROP TABLES IF EXISTS connections")
45
self.cursor.execute("CREATE TABLE connections (connection_id INTEGER "
46
"auto_increment primary key, "
47
"src_peer_id INT, dst_peer_id INT, exception_id INT, capture_id INT);")
49
self.cursor.execute("DROP TABLES IF EXISTS peer")
50
self.cursor.execute("CREATE TABLE peer (peer_id INTEGER "
51
"auto_increment primary key, "
52
"peer_ip TEXT, peer_host TEXT, peer_tags TEXT);")
55
self.cursor.execute("DROP TABLES IF EXISTS exception")
56
self.cursor.execute("CREATE TABLE exception (exception_id integer "
57
"auto_increment primary key "
58
",url TEXT, md5 TEXT, count INT default 1);")
60
self.cursor.execute("DROP TABLES IF EXISTS match_zone")
61
self.cursor.execute("CREATE TABLE match_zone (match_id INTEGER "
62
"auto_increment primary key, exception_id INTEGER, "
63
"zone TEXT, arg_name TEXT, rule_id INTEGER);")
65
self.cursor.execute("DROP TABLES IF EXISTS capture")
66
self.cursor.execute("CREATE TABLE capture (capture_id INTEGER "
67
"auto_increment primary key, http_request TEXT, exception_id INTEGER);")
70
# self.cursor.execute("DROP TABLES IF EXISTS router")
71
# self.cursor.execute("CREATE TABLE router(route_id INTEGER "
72
# "auto_increment primary key,"
73
# "exception_id INTEGER, rule_id INTEGER, "
74
# "conn_id INTEGER, capture_id INTEGER);")
76
def extract_sig(self, raw_rule, is_from_http=False, is_from_log=False):
77
start = raw_rule.find(": ")
79
if (is_from_log == True):
80
end = raw_rule[start:].find(", client: ")
82
return (raw_rule[raw_rule.find(": ") + 2:
83
raw_rule.find(", client: ")])
84
elif (is_from_http == True):
85
return (raw_rule[raw_rule.find(": ") + 2:])
89
self.cursor.execute("SELECT last_insert_id()")
90
data = self.cursor.fetchone()
92
def insert(self, fmt, *args):
93
self.cursor.execute(fmt, [args])
94
def add_capture(self, exception_id, raw_request):
96
self.cursor.execute("SELECT COUNT(*) FROM capture where exception_id = %s", (str(exception_id)))
97
x = self.cursor.fetchone()
98
if (x is None or x[0] < 10):
99
# print "less than 10 "+str(x[0])
100
self.cursor.execute("INSERT INTO capture (http_request, exception_id)"
101
"VALUES (%s, %s)", (str(raw_request), str(exception_id)))
102
capture_id = self.last_id()
106
def sig_to_db(self, raw_request, d, force_insert=False):
107
if (force_insert == False):
108
sig_hash = d["server"][0]+"#"+d["uri"][0]+"#"
109
for i in range(0, 50):
110
if "zone"+str(i) in d:
111
sig_hash = sig_hash + d["zone"+str(i)][0] + "#"
114
if "var_name"+str(i) in d:
115
sig_hash = sig_hash + d["var_name"+str(i)][0] + "#"
116
sig_hash = sig_hash + d["id"+str(i)][0] + "#"
117
sig_md5 = hashlib.md5(sig_hash).hexdigest()
118
self.cursor.execute("SELECT exception_id FROM exception where md5 = %s LIMIT 1", (sig_md5))
119
exception_id = self.cursor.fetchone()
120
if (exception_id is not None):
121
self.add_capture(exception_id[0], raw_request)
122
self.cursor.execute("UPDATE exception SET count=count+1 where md5 = %s", (sig_md5))
125
sig_hash = d["server"][0]+"#"+d["uri"][0]+"#"
126
self.cursor.execute("INSERT INTO peer (peer_ip) "
127
"VALUES (%s)", (d["ip"][0]))
128
ip_id = self.last_id()
130
self.cursor.execute("INSERT INTO peer (peer_host) "
131
"VALUES (%s)", (d["server"][0]))
132
host_id = self.last_id()
134
self.cursor.execute("INSERT INTO exception (url) VALUES "
135
"(%s)", (d["uri"][0]))
136
exception_id = self.last_id()
138
capture_id = self.add_capture(exception_id, raw_request)
139
# print "cap id : "+str(capture_id)
140
#connection information
141
self.cursor.execute("INSERT INTO connections (src_peer_id, dst_peer_id, exception_id, capture_id)"
142
"VALUES (%s, %s, %s, %s)", (str(ip_id), str(host_id), str(exception_id), str(capture_id)))
143
connection_id = self.last_id()
145
for i in range(0, 50):
148
if "zone"+str(i) in d:
149
zn = d["zone"+str(i)][0]
150
sig_hash = sig_hash + d["zone"+str(i)][0] + "#"
153
if "var_name"+str(i) in d:
154
vn = d["var_name"+str(i)][0]
155
sig_hash = sig_hash + d["var_name"+str(i)][0] + "#"
156
sig_hash = sig_hash + d["id"+str(i)][0] + "#"
157
self.cursor.execute("INSERT INTO match_zone (exception_id, zone, arg_name, rule_id) "
158
"VALUES (%s, %s, %s, %s)", (str(exception_id), zn, vn, d["id"+str(i)][0]))
159
self.cursor.execute("UPDATE exception SET md5=%s WHERE exception_id=%s", (hashlib.md5(sig_hash).hexdigest(), str(exception_id)))
160
return (connection_id)
161
def raw_parser(self, raw_request, raw_rule, is_from_http=True, is_from_log=False):
162
sig = self.extract_sig(raw_rule, is_from_http, is_from_log)
163
tmpdict = urlparse.parse_qs(sig)
164
connection_id = self.sig_to_db(raw_request, tmpdict, force_insert=False)