def insert_stat(id_user: int, protocol: Protocol, successful: bool):
query: Text = f"INSERT INTO Stats(id_user, id_port, successful) VALUES (?, ?, ?)"
logger.info(query)
conection_sqlite(DB,
query,
query_params=(id_user, protocol.port, str(successful)),
is_dict=False)
def check_database() -> NoReturn:
"""
Comprueba si existe la base de datos, sino existe la crea
:return:
"""
try:
query: Text = "SELECT * FROM Hosts"
conection_sqlite(DB, query)
except OSError:
logger.info(
f'the database {DB} doesn\'t exist, creating it with the default configuration'
)
execute_script_sqlite(DB, DB_STRUCTURE.read_text())
def select_mac_all_hosts(lock: Lock) -> Tuple[Text, ...]:
query: Text = "SELECT Hosts.mac FROM Hosts group by Hosts.mac ORDER BY Hosts.mac;"
response_query: List[Tuple[Text, ...]] = conection_sqlite(DB,
query,
is_dict=False,
mutex=lock)
return tuple(i[0] for i in response_query)
def select_user_stats(id_user: int, limit) -> Stats:
query = "SELECT Stats.id_user, Stats.successful, Ports.* " \
"FROM Stats INNER JOIN Ports ON Stats.id_port = Ports.port " \
"WHERE Stats.id_user=?" \
"LIMIT ?;"
total_fail: int = 0
total_success: int = 0
protocols: List[Protocol] = list()
response_query: List[Dict[str,
Any]] = conection_sqlite(DB,
query,
query_params=(id_user,
limit),
is_dict=True)
for i in response_query:
success: bool = False
if i['successful'] == 'True':
success = True
total_success += 1
else:
total_fail += 1
protocol: Protocol = Protocol(i['name'],
i['port'],
i['protocol'],
i['description'],
successful=success)
protocols.append(protocol)
stats: Stats = Stats(id_user, total_fail, total_success, protocols)
return stats
def select_all_protocol() -> List[Protocol]:
query: Text = "SELECT * FROM Ports"
response_query: List[Dict[str, Any]] = conection_sqlite(DB,
query,
is_dict=True)
response: List[Protocol] = list()
for i in response_query:
protocol: Protocol = Protocol(i['name'], i['port'], i['protocol'],
i['description'])
response.append(protocol)
return response
def select_hosts_online(lock: Lock) -> List[Host]:
"""
Filtramos por los hosts cuya fecha de actualizacion coincida con la fecha de la tabla datetime que registra
el ultimo escaneo realizado.
"""
query: Text = \
"""SELECT h.id, h.ip, h.mac, h.date, h.network, d.description, v.vendor
FROM Hosts as h
LEFT JOIN Descriptions as d ON h.mac = d.mac
INNER JOIN Vendors as v ON h.vendor = v.id
WHERE h.date IN (SELECT Datetime.date FROM Datetime LIMIT 1)
ORDER BY h.id DESC;"""
response_query_str: List[Dict[Text, Any]] = conection_sqlite(DB,
query,
is_dict=True,
mutex=lock)
return get_list_host(response_query_str)
def select_hosts_offline(lock: Lock) -> List[Host]:
"""
Para seleccionar los hosts offline, lo que se hace es descartar los online:
1. Seleccionamos la tabla hosts ordenandola descendentemente por id para que al agrupar obtendamos los ids mayores.
2. Filtramos aquellos hosts que tienen la fecha como la ultima actualizacion, ya que estos estan online.
3. Agrupamo por mac para eliminar hosts duplicados.
"""
query: Text = \
"""SELECT h.id, h.ip, h.mac, h.date, h.network, d.description, v.vendor
FROM (SELECT * FROM Hosts ORDER BY id DESC) as h
LEFT JOIN Descriptions as d ON h.mac = d.mac
INNER JOIN Vendors as v ON h.vendor = v.id
WHERE h.mac NOT IN (
SELECT h2.mac
FROM Hosts as h2
WHERE h2.date IN (SELECT Datetime.date FROM Datetime LIMIT 1)
)
GROUP BY h.mac"""
response_query_str: List[Dict[Text, Any]] = conection_sqlite(DB,
query,
is_dict=True,
mutex=lock)
return get_list_host(response_query_str)
def insert_host(host: Host, lock: Lock) -> NoReturn:
# insert vendor if not exist vendor in table vendors
query: Text = \
f"""INSERT INTO Vendors(vendor)
SELECT * FROM (SELECT '{host.vendor}')
WHERE NOT EXISTS (
SELECT vendor FROM Vendors WHERE vendor = '{host.vendor}'
) LIMIT 1;"""
logger.debug(query)
conection_sqlite(DB, query, mutex=lock)
# get id vendors
query: Text = f"SELECT id FROM Vendors WHERE vendor LIKE '{host.vendor}'"
logger.debug(query)
vendor: List[Dict[Text, Any]] = conection_sqlite(DB,
query,
is_dict=True,
mutex=lock)
if len(vendor) == 1:
id_vendor: int = vendor[0]['id']
else:
id_vendor: int = 1
# insert host
query: Text = f"INSERT INTO Hosts(ip, mac, vendor, date, network) VALUES ('{host.ip}'," \
f"'{host.mac}', {id_vendor},'{host.date}','{host.network}');"
logger.debug(query)
conection_sqlite(DB, query, mutex=lock)
# insert description if not exist mac in table description
query: Text = \
f"""INSERT INTO Descriptions(mac, description)
SELECT * FROM (SELECT '{host.mac}', '+')
WHERE NOT EXISTS (
SELECT mac FROM Descriptions WHERE mac = '{host.mac}'
) LIMIT 1;"""
logger.debug(query)
conection_sqlite(DB, query, mutex=lock)
def update_date(date: Text, lock: Lock) -> NoReturn:
query: Text = f"UPDATE Datetime SET date='{date}' WHERE id LIKE 1;"
logger.debug(query)
conection_sqlite(DB, query, mutex=lock)
def update_descriptions(mac: Text, description: Text, lock: Lock) -> NoReturn:
query: Text = f"UPDATE Descriptions SET description='{description}' WHERE mac LIKE '{mac}';"
logger.debug(query)
conection_sqlite(DB, query, mutex=lock)
def update_host(host: Host, lock: Lock) -> NoReturn:
query: Text = f"UPDATE Hosts SET ip='{host.ip}', mac='{host.mac}', vendor='{host.vendor}', date='{host.date}', " \
f"network='{host.network}' WHERE ip LIKE '{host.ip}';"
logger.debug(query)
conection_sqlite(DB, query, mutex=lock)