def delete_telegram(id): con, cur = create_db.get_cur() sql = """ delete from telegram where id = %s """ % (id) try: cur.execute(sql) con.commit() except sqltool.Error as e: out_error(e) con.rollback() else: return True cur.close() con.close()
def insert_new_telegram(token, chanel, group):
con, cur = create_db.get_cur()
sql = """insert into telegram(`token`, `chanel_name`, `groups`) values ('%s', '%s', '%s') """ % (token, chanel, group)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
else:
return True
cur.close()
con.close()
def is_master(ip, **kwargs):
con, cur = create_db.get_cur()
sql = """ select slave.ip from servers as master left join servers as slave on master.id = slave.master where master.ip = '%s' """ % ip
if kwargs.get('master_slave'):
sql = """ select master.hostname, master.ip, slave.hostname, slave.ip from servers as master left join servers as slave on master.id = slave.master where slave.master > 0 """
try:
cur.execute(sql)
except sqltool.Error as e:
out_error(e)
else:
return cur.fetchall()
cur.close()
con.close()
def insert_new_ssh(name, enable, group, username, password):
con, cur = create_db.get_cur()
sql = """insert into cred(name, enable, groups, username, password) values ('%s', '%s', '%s', '%s', '%s') """ % (name, enable, group, username, password)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
out_error(e)
con.rollback()
else:
return True
cur.close()
con.close()
def select_roles(**kwargs):
con, cur = create_db.get_cur()
sql = """select * from role ORDER BY id"""
if kwargs.get("roles") is not None:
sql = """select * from role where name='%s' """ % kwargs.get("roles")
try:
cur.execute(sql)
except sqltool.Error as e:
print("An error occurred:", e.args[0])
else:
return cur.fetchall()
cur.close()
con.close()
def select_groups(**kwargs):
con, cur = create_db.get_cur()
sql = """select * from groups ORDER BY id"""
if kwargs.get("group") is not None:
sql = """select * from groups where name='%s' """ % kwargs.get("group")
try:
cur.execute(sql)
except sqltool.Error as e:
out_error(e)
else:
return cur.fetchall()
cur.close()
con.close()
def select_groups(**kwargs):
con, cur = create_db.get_cur()
sql = """select * from groups ORDER BY id"""
if kwargs.get("group") is not None:
sql = """select * from groups where name='%s' """ % kwargs.get("group")
try:
cur.execute(sql)
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
else:
return cur.fetchall()
cur.close()
con.close()
def select_metrics(serv, **kwargs):
con, cur = create_db.get_cur()
sql = """ select * from metrics where serv = '%s' order by `date` desc """ % serv
try:
cur.execute(sql)
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
else:
return cur.fetchall()
cur.close()
con.close()
def delete_server(id):
con, cur = create_db.get_cur()
sql = """delete from servers where id = '%s'""" % (id)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
else:
return True
cur.close()
con.close()
def select_waf_servers():
con, cur = create_db.get_cur()
sql = """ select serv.ip from waf left join servers as serv on waf.server_id = serv.id where waf.metrics = '1'"""
try:
cur.execute(sql)
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
else:
return cur.fetchall()
cur.close()
con.close()
def select_ssh_password():
con, cur = create_db.get_cur()
sql = """select password from cred """
try:
cur.execute(sql)
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
else:
for password in cur.fetchone():
return password
cur.close()
con.close()
def add_server(hostname, ip, group, typeip, enable, master):
con, cur = create_db.get_cur()
sql = """INSERT INTO servers (hostname, ip, groups, type_ip, enable, master) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')""" % (hostname, ip, group, typeip, enable, master)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
return False
else:
return True
cur.close()
con.close()
def update_setting(param, val):
con, cur = create_db.get_cur()
sql = """update `settings` set `value` = '%s' where param = '%s' """ % (
val, param)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
cur.close()
con.close()
def update_waf_metrics_enable(name, enable):
con, cur = create_db.get_cur()
sql = """ update waf set metrics = %s where server_id = (select id from servers where hostname = '%s') """ % (
enable, name)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
cur.close()
con.close()
def insert_waf_metrics_enable(serv, enable):
con, cur = create_db.get_cur()
sql = """ insert into waf (server_id, metrics) values((select id from servers where ip = '%s'), '%s') """ % (
serv, enable)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
cur.close()
con.close()
def delete_mentrics():
con, cur = create_db.get_cur()
if mysql_enable == '1':
sql = """ delete from metrics where date < now() - INTERVAL 3 day """
else:
sql = """ delete from metrics where date < datetime('now', '-3 days') """
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
cur.close()
con.close()
def select_user_name_group(id):
con, cur = create_db.get_cur()
sql = """select name from groups where id='%s' """ % id
try:
cur.execute(sql)
except sqltool.Error as e:
print(
'<span class="alert alert-danger" id="error">An error occurred: ' +
e + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
else:
for group in cur.fetchone():
return group
cur.close()
con.close()
def get_token(uuid):
con, cur = create_db.get_cur()
sql = """ select token.token from token left join uuid as uuid on uuid.user_id = token.user_id where uuid.uuid = '%s' """ % uuid
try:
cur.execute(sql)
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
else:
for token in cur.fetchall():
return token[0]
cur.close()
con.close()
def insert_mentrics(serv, curr_con, cur_ssl_con, sess_rate, max_sess_rate):
con, cur = create_db.get_cur()
if mysql_enable == '1':
sql = """ insert into metrics (serv, curr_con, cur_ssl_con, sess_rate, max_sess_rate, date) values('%s', '%s', '%s', '%s', '%s', now()) """ % (serv, curr_con, cur_ssl_con, sess_rate, max_sess_rate)
else:
sql = """ insert into metrics (serv, curr_con, cur_ssl_con, sess_rate, max_sess_rate, date) values('%s', '%s', '%s', '%s', '%s', datetime('now', 'localtime')) """ % (serv, curr_con, cur_ssl_con, sess_rate, max_sess_rate)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
cur.close()
con.close()
def delete_mentrics():
con, cur = create_db.get_cur()
if mysql_enable == '1':
sql = """ delete from metrics where date < now() - INTERVAL 3 day """
else:
sql = """ delete from metrics where date < datetime('now', '-3 days') """
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
out_error(e)
con.rollback()
cur.close()
con.close()
def add_user(user, email, password, role, group):
con, cur = create_db.get_cur()
sql = """INSERT INTO user (username, email, password, role, groups) VALUES ('%s', '%s', '%s', '%s', '%s')""" % (user, email, password, role, group)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
return False
else:
return True
cur.close()
con.close()
def insert_waf_mentrics(serv, conn):
con, cur = create_db.get_cur()
if mysql_enable == '1':
sql = """ insert into waf_metrics (serv, conn, date) values('%s', '%s', now()) """ % (serv, conn)
else:
sql = """ insert into waf_metrics (serv, conn, date) values('%s', '%s', datetime('now', 'localtime')) """ % (serv, conn)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
out_error(e)
con.rollback()
cur.close()
con.close()
def add_user(user, email, password, role, group):
con, cur = create_db.get_cur()
sql = """INSERT INTO user (username, email, password, role, groups) VALUES ('%s', '%s', '%s', '%s', '%s')""" % (user, email, password, role, group)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
out_error(e)
con.rollback()
return False
else:
return True
cur.close()
con.close()
def add_group(name, description):
con, cur = create_db.get_cur()
sql = """INSERT INTO groups (name, description) VALUES ('%s', '%s')""" % (name, description)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
return False
else:
print(cur.lastrowid)
return True
cur.close()
con.close()
def select_telegram(**kwargs):
con, cur = create_db.get_cur()
sql = """select * from telegram """
if kwargs.get('group'):
sql = """select * from telegram where groups = '%s' """ % kwargs.get('group')
if kwargs.get('token'):
sql = """select * from telegram where token = '%s' """ % kwargs.get('token')
try:
cur.execute(sql)
except sqltool.Error as e:
print("An error occurred:", e.args[0])
else:
return cur.fetchall()
cur.close()
con.close()
def add_group(name, description):
con, cur = create_db.get_cur()
sql = """INSERT INTO groups (name, description) VALUES ('%s', '%s')""" % (name, description)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
out_error(e)
con.rollback()
return False
else:
print(cur.lastrowid)
return True
cur.close()
con.close()
def update_telegram(token, chanel, group, id): con, cur = create_db.get_cur() sql = """ update telegram set `token` = '%s', `chanel_name` = '%s', `groups` = '%s' where id = '%s' """ % (token, chanel, group, id) try: cur.execute(sql) con.commit() except sqltool.Error as e: out_error(e) con.rollback() cur.close() con.close()
def add_server(hostname, ip, group, typeip, enable, master, cred, alert, metrics, port, desc):
con, cur = create_db.get_cur()
sql = """ INSERT INTO servers (hostname, ip, groups, type_ip, enable, master, cred, alert, metrics, port, `desc`)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')
""" % (hostname, ip, group, typeip, enable, master, cred, alert, metrics, port, desc)
try:
cur.execute(sql)
con.commit()
return True
except sqltool.Error as e:
out_error(e)
con.rollback()
return False
cur.close()
con.close()
def update_telegram(token, chanel, group, id):
con, cur = create_db.get_cur()
sql = """
update telegram set
`token` = '%s',
`chanel_name` = '%s',
`groups` = '%s'
where id = '%s' """ % (token, chanel, group, id)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<span class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></span>')
con.rollback()
cur.close()
con.close()
def update_last_act_user(uuid):
con, cur = create_db.get_cur()
session_ttl = funct.get_config_var('main', 'session_ttl')
if mysql_enable == '1':
sql = """ update uuid set exp = now()+ INTERVAL %s day where uuid = '%s' """ % (session_ttl, uuid)
else:
sql = """ update uuid set exp = datetime('now', '+%s days') where uuid = '%s' """ % (session_ttl, uuid)
try:
cur.execute(sql)
con.commit()
except sqltool.Error as e:
print('<div class="alert alert-danger" id="error">An error occurred: ' + e.args[0] + ' <a title="Close" id="errorMess"><b>X</b></a></div>')
con.rollback()
cur.close()
con.close()
