def fetch_domains(): """ To fetch urls from the database, and return urls """ tld ={} db1 = MySQL(DBCONFIG1) db2 = MySQL(DBCONFIG2) sql = 'SELECT SUBSTRING_INDEX(tld,".",-1) as a,count(*) FROM domain_features GROUP BY a' db1.query(sql) tld_count = db1.fetch_all_rows() for i in tld_count: print i if len(i[0])==0: continue sql = 'SELECT type FROM tld_details WHERE tld="%s"' % ('.'+i[0]) db2.query(sql) urls2 = db2.fetch_all_rows() try: if urls2[0][0] in tld.keys(): tld[urls2[0][0]] += i[1] else: tld[urls2[0][0]] = i[1] except: continue print tld db1.close() db2.close()
def master_control(): """主线程控制""" try: db = MySQL(SOURCE_CONFIG) except: print "连接数据库失败" queue.task_done() return while queue.qsize(): # 重要,以前为1,导致线程不结束 check_domain, visit_times = queue.get() check_domain = str(check_domain.strip()) print '线程数量:', threading.activeCount(), ',任务数量:', queue.qsize() print "域名:", check_domain, datetime.now().strftime("%Y-%m-%d %H:%M:%S") tb_name = domain2tb(check_domain) if not tb_name: # 是否转换成功 print "域名名称异常,无法转换为表名称:", check_domain queue.task_done() continue rc_ttl = manage_rc_ttl(check_domain) rc_ttl = rc2str(rc_ttl) # 转换为字符串 if visit_times == 0: # 第一次探测域名直接插入到数据库中 insert_rc_db(db, tb_name, check_domain, rc_ttl) else: update_data(db, tb_name, check_domain, rc_ttl) queue.task_done() # time.sleep(1) # 去掉偶尔会出现错误 db.close() # 关闭数据库
def fetch_domains(): """ To fetch urls from the database, and return urls """ tld = {} db1 = MySQL(DBCONFIG1) db2 = MySQL(DBCONFIG2) sql = 'SELECT SUBSTRING_INDEX(tld,".",-1) as a,count(*) FROM domain_features GROUP BY a' db1.query(sql) tld_count = db1.fetch_all_rows() for i in tld_count: print i if len(i[0]) == 0: continue sql = 'SELECT type FROM tld_details WHERE tld="%s"' % ('.' + i[0]) db2.query(sql) urls2 = db2.fetch_all_rows() try: if urls2[0][0] in tld.keys(): tld[urls2[0][0]] += i[1] else: tld[urls2[0][0]] = i[1] except: continue print tld db1.close() db2.close()
def update_table(tb_name): """ 更新表 :param tb_name: :return: """ sql = 'SELECT flag,sum(whois_sum) FROM domain_whois_%s GROUP BY flag' % tb_name.lower() flag_undetected = flag_no_svr = flag_no_connect = 0 flag_reg_info = flag_reg_date = flag_part_info = 0 db = MySQL(DESTINATION_CONFIG) db.query(sql) results = db.fetch_all_rows() for item in results: flag = item[0] whois_sum = item[1] if flag == '-6': flag_undetected += whois_sum if flag == '-5': flag_no_svr += whois_sum elif flag == '-1' or flag == '-2' or flag == '-3' or flag == '-4': flag_no_connect += whois_sum elif flag == '120' or flag == '121' or flag == '122': flag_reg_info += whois_sum elif flag == '110' or flag == '102' or flag == '112': flag_reg_date += whois_sum elif flag == '100' or flag == '101' or flag == '111': flag_part_info += whois_sum sql = 'INSERT INTO table_overall_history (table_name,flag_undetected,\ flag_no_svr,flag_no_connect,flag_reg_info,flag_reg_date,flag_part_info) \ VALUES ("%s","%s","%s","%s","%s","%s","%s")' % ('domain_whois_'+tb_name,flag_undetected,flag_no_svr,flag_no_connect,flag_reg_info,flag_reg_date,flag_part_info) db.insert(sql) db.close()
def fetch_mal_domains(): """获取待查询的域名列表""" db = MySQL(SOURCE_CONFIG) sql = 'SELECT domain,visit_times FROM domain_records LIMIT 0,71622' db.query(sql) query_domains = db.fetch_all_rows() # 得到总共的数量 db.close() return query_domains
def obtain_monitoring_host_process(): """获取待监测的进程信息""" db = MySQL(SOURCE_CONFIG) # 获取超过间隔时间的进程 sql = fetch_sql db.query(sql) host_process_result = db.fetch_all_rows() host_process = group_host_process(host_process_result) db.close() return host_process
def fetch_urls(): """ 获取要提取词汇特征的网址 """ db = MySQL() sql = 'SELECT url FROM url_features' db.query(sql) urls = db.fetch_all_rows() db.close() return tuple_to_list(urls)
def get_data_from_source(tb_name): """ :param name:string 数据库名称 :return: results 数据库whois数量分布 """ source_db = MySQL(SOURCE_CONFIG) source_db.query('select tld,flag,count(*) as whois_sum from domain_whois_%s group by tld,flag' % (tb_name)) results = source_db.fetch_all_rows() source_db.close() return results
def fetch_source_data(tb_name): """获取源数据 :param tb_name: string 表名 :return: results 数据 """ db = MySQL(DESTINATION_CONFIG) db.query('SELECT tld,flag,whois_sum FROM domain_whois_%s WHERE flag <> "-6" GROUP BY tld,flag' % tb_name) results = db.fetch_all_rows() db.close() return results
def fetch_resource_data(tb_name): """获得源数据 :param tb_name: string 表名 :return: results: 查询结果 """ db = MySQL(DESTINATION_CONFIG) db.query('SELECT tld, SUM(whois_sum) FROM domain_whois_%s GROUP BY tld' % tb_name) results = db.fetch_all_rows() db.close() return results
def fetch_data(): """ 从数据库中获取数据基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT url_length,path_tokens,path_brand,domain_tokens,malicious,domain_characters,path_characters FROM url_features_copy_copy' db.query(sql) urls = db.fetch_all_rows() db.close() return urls
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT tld FROM domain_features WHERE tld_token_count = "2"' db.query(sql) tlds = db.fetch_all_rows() db.close() return tlds
def update_day(): """更新表whois_sum_by_day,即统计数据库中最新的域名whois总量 """ print str(datetime.now()), '开始更新当天数据库中最新的WHOIS总量(whois_sum_by_day)' db = MySQL(DESTINATION_CONFIG) sql = 'INSERT INTO whois_sum_by_day(sum) SELECT MAX(tld_sum) FROM whois_sum \ WHERE to_days(insert_time) = to_days(now())' db.insert(sql) db.close() print str(datetime.now()), '结束更新当天数据库中最新的WHOIS总量(whois_sum_by_day)'
def fetch_mal_domains(): """ 获取待查询的域名列表 """ db = MySQL(SOURCE_CONFIG) sql = 'SELECT domain, malicious_type FROM domain_index LIMIT 143244,71622' db.query(sql) query_domains = db.fetch_all_rows() # 得到总共的数量 db.close() return query_domains
def fetch_data(): """ 从数据库中获取域名(domain)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain FROM domain_features' db.query(sql) domains = db.fetch_all_rows() db.close() return domains
def fetch_data(): """ 从数据库中获取域名(domain)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain FROM domain_features limit 1000' db.query(sql) domains = db.fetch_all_rows() db.close() return domains
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain_characters FROM domain_features' db.query(sql) tlds = db.fetch_all_rows() db.close() return tlds
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain_characters,domain_digit FROM domain_features' db.query(sql) tlds = db.fetch_all_rows() db.close() return tlds
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT domain_length FROM domain_features' db.query(sql) domains_length = db.fetch_all_rows() db.close() return domains_length
def update_db(): """更新数据库""" tld_num = sum_all_domains() db = MySQL(DESTINATION_CONFIG) db.truncate('TRUNCATE TABLE domain_summary') update_sql = 'INSERT INTO domain_update(tld_name, domain_num) VALUES("%s", "%s")' summary_sql = 'INSERT INTO domain_summary(tld_name, domain_num) VALUES("%s", "%s")' for tld,num in tld_num: db.insert_no_commit(update_sql % (tld,num)) db.insert_no_commit(summary_sql % (tld,num)) db.commit() db.close()
def test(results): db = MySQL() for url_feature in results: print url_feature sql = 'UPDATE url_features SET url_length = "%s", domain="%s",domain_tokens="%s",domain_characters="%s",path="%s", ' \ 'path_tokens="%s",path_characters="%s",path_brand="%s" WHERE url="%s"' % \ (url_feature[1],url_feature[2],url_feature[3],url_feature[4],url_feature[5],url_feature[6], url_feature[7],url_feature[8],url_feature[0]) db.no_update(sql) db.commit() db.close()
def update_db(): """ 更新数据库 """ svr_num = sum_all_list() db = MySQL(DESTINATION_CONFIG) db.truncate('TRUNCATE TABLE top_sec_svr') sql = 'INSERT INTO top_sec_svr(top_svr, sec_svr, whois_sum) VALUES("%s", "%s", "%s")' for top,sec,num in svr_num: db.insert_no_commit(sql % (top,sec,num)) db.commit() db.close()
def insert_db(): url_set = pd.read_csv('csv/kt-content.csv') db = MySQL() for i in range(len(url_set)): print url_set.ix[i].values sql = 'Insert ignore into benign_urls(url,type)VALUES ("%s","%s")' % ( url_set.ix[i].values[1], url_set.ix[i].values[2]) db.insert_no_commit(sql) db.commit() db.close()
def fetch_data(): """ 从数据库中获取域名长度(domain_length)基础数据 :return: 返回基础数据 """ db = MySQL() sql = 'SELECT tld FROM domain_features' sql = 'SELECT SUBSTRING_INDEX(tld,".",-1) as a FROM domain_features' db.query(sql) domains_length = db.fetch_all_rows() db.close() return domains_length
def update_db(): """更新数据库""" tld_whois = sum_all_whois() total = 0 db = MySQL(DESTINATION_CONFIG) sql = 'INSERT INTO tld_whois_sum_history(tld,whois_sum) VALUES("%s", "%s")' # 插入数据 for tld,whois in tld_whois: db.insert_no_commit(sql % (tld,whois)) total += whois db.commit() sql = 'INSERT INTO whois_sum(tld_sum) VALUES("%s")' db.insert(sql % total) db.close()
def update_url_features(url_feature): """ 更新数据库中词汇特征 :param url_feature: :return: """ db = MySQL() sql = 'UPDATE url_features SET url_length = "%s", domain="%s",domain_tokens="%s",domain_characters="%s",path="%s", ' \ 'path_tokens="%s",path_characters="%s",path_brand="%s" WHERE url="%s"' % \ (url_feature[1],url_feature[2],url_feature[3],url_feature[4],url_feature[5],url_feature[6], url_feature[7],url_feature[8],url_feature[0]) db.update(sql) db.close()
def update_destination(tb_name, results): """ 更新数据库 :param results: :return: """ destination_db = MySQL(DESTINATION_CONFIG) destination_db.truncate('TRUNCATE TABLE domain_whois_%s' % tb_name) for item in results: destination_db.insert_no_commit('INSERT INTO domain_whois_%s (tld,flag,whois_sum) VALUES ("%s","%s","%s")' % ( tb_name, item[0], item[1], item[2])) destination_db.commit() destination_db.close()
def update_top_sec_num(): """ 更新表msvr_ssvr,统计顶级服务器和二级服务器数量 """ print str(datetime.now()), '开始更新数据库中顶级和二级服务器数量(msvr_ssvr)' db = MySQL(DESTINATION_CONFIG) sql = 'UPDATE msvr_ssvr SET msvr_ssvr.ssvr=(SELECT COUNT(DISTINCT sec_svr) FROM top_sec_svr)' db.update(sql) sql = 'UPDATE msvr_ssvr SET msvr=(SELECT COUNT(DISTINCT addr) FROM whois_addr)' db.update(sql) db.close() print str(datetime.now()), '结束更新数据库中顶级和二级服务器数量(msvr_ssvr)'
def insert_db(): file_name = open('small_benign.txt') urls = file_name.readlines() db = MySQL() count = 0 for url in urls: sql = 'Insert into url_features(url,malicious)VALUES ("%s","0")'%(url.strip()) db.insert_no_commit(sql) count += 1 db.commit() db.close() print count
def update_db(): """更新数据库""" tld_whois = sum_all_whois() total = 0 db = MySQL(DESTINATION_CONFIG) sql = 'INSERT INTO tld_whois_sum_history(tld,whois_sum) VALUES("%s", "%s")' # 插入数据 for tld, whois in tld_whois: db.insert_no_commit(sql % (tld, whois)) total += whois db.commit() sql = 'INSERT INTO whois_sum(tld_sum) VALUES("%s")' db.insert(sql % total) db.close()
def fetch_resource_data(): """ 获得待查询whois信息的域名,包括域名名称、更新时间、到期时间和详细信息,顶级域名 注意: domain_whois表中的域名是由domain_index中根据触发器更新的 """ db = MySQL(SOURCE_CONFIG) sql = 'SELECT domain,update_date,expiration_date,LENGTH (details),tld,flag FROM domain_whois WHERE flag = 1 LIMIT 0,80000' db.query(sql) query_domains = db.fetch_all_rows() # 得到总共的数量 db.close() return query_domains
def get_source_data(tb_name): """ 得到基础数据 :param tb_name:string 表名 :return: """ db = MySQL(SOURCE_CONFIG) sql = 'SELECT top_whois_server as top_svr, sec_whois_server AS sec_svr, count(*) \ FROM domain_whois_%s WHERE sec_whois_server <> "" GROUP BY top_svr, sec_svr' % tb_name db.query(sql) results = db.fetch_all_rows() db.close() return results
def process_id_to_user(process_id): """通过process_id获取用户的邮箱地址和主机地址""" sql = "SELECT email,host_ip,pid,code_route,process_name,warning_times FROM `process_info`,user_account,host_info WHERE process_id = '%s' AND user_account.user_id = process_info.user_id AND host_info.host_id = process_info.host_id" db = MySQL(SOURCE_CONFIG) db.query(sql % process_id) email_host = db.fetch_all_rows() email = email_host[0][0] host = base64.decodestring(email_host[0][1]) pid = email_host[0][2] code_route = email_host[0][3] process_name = email_host[0][4] warning_times = email_host[0][5] db.close() return email, host,pid,code_route,process_name,warning_times
def insert_domains(domain_set): """ 更新数据库中词汇特征 :param url_feature: :return: """ db = MySQL() for domain in domain_set: sql = 'Insert into domain_features(domain,tld,tld_token_count,domain_length,domain_characters,domain_digit)VALUES ("%s","%s","%s","%s","%s","%s")' \ %(domain['domain'],domain['tld'],domain['tld_token_count'],domain['domain_length'],domain['character'],domain['digit']) db.insert_no_commit(sql) db.commit() db.close()
def insert_db(): file_name = open('small_benign.txt') urls = file_name.readlines() db = MySQL() count = 0 for url in urls: sql = 'Insert into url_features(url,malicious)VALUES ("%s","0")' % ( url.strip()) db.insert_no_commit(sql) count += 1 db.commit() db.close() print count
def update_domain_info(whois_dict, whois_geo, rule): """ 依据更新规则,更新信息到数据库表中 """ domain = whois_dict['domain'] db = MySQL(SOURCE_CONFIG) if 1 in rule: print domain + ' ,' + str(rule) + str(datetime.now()) # 注意更新顺序,先更新WHOWAS表 if rule[1]: # 是否更新WHOWAS表 update_whowas(db, domain) if rule[0]: # 是否更新WHOIS表 update_domain_whois_locate(db, whois_dict, whois_geo) db.close()
def insert_host_error(error,process_ids): """更新主机错误""" db = MySQL(SOURCE_CONFIG) error_info = '主机异常:'+ str(error) status = '异常' sql = 'insert into process_status (process_id,cpu,mem,vsz,rss,status,error_info,log_size) values("%s","%s","%s","%s","%s","%s","%s","%s")' for _,process_id,_,_ in process_ids: db.insert(sql % (process_id, 0, 0, 0, 0, status, error_info,'-3')) # -3表示日志的主机错误 email, host, pid, code_route, code_name,warning_times = process_id_to_user(process_id) if warning_times > 0: content = (pid, host, status, error_info, code_route, code_name) if send_process_exception([email], content): print "邮件预警成功:" + email reduce_warning_times(process_id) else: print "邮件预警失败:" + email db.close()
def delete_empty_rc(domain): """ 删除空白域名DNS记录,但弱最后一条为空白,则不删除 方法:1)获取最新记录的last_updated时间 2)删除除最新last_updated所有域名的时间 """ db = MySQL(SOURCE_CONFIG) tb_name = domain2tb(domain) # 获取最近一条记录的last_updated时间 max_time_sql = 'select max(last_updated) from ' + tb_name + ' WHERE domain= "%s" ' db.query(max_time_sql % domain) max_time = db.fetch_all_rows()[0][0] # 得到最大值 # 删除某次探测为空的数据,但不删除最后一条记录 delete_sql = 'DELETE FROM ' + tb_name + ' WHERE (last_updated = insert_time) AND ips = "" AND domain="%s" AND last_updated != "%s"' db.update(delete_sql % (domain, str(max_time))) db.close()
def update_db(): """更新数据库""" db = MySQL(DESTINATION_CONFIG) truncate_sql = 'TRUNCATE TABLE tld_whois_flag' db.truncate(truncate_sql) sql = 'INSERT INTO tld_whois_flag(tld, flag,flag_detail, whois_sum) VALUES("%s", "%s", "%s", "%s")' tld_flag_num = sum_all_flags() for tld, flag_detail, num in tld_flag_num: try: if int(flag_detail) < 0: flag = '0' elif int(flag_detail) >= 120: flag = '1' elif flag_detail == '102' or flag_detail == '110' or flag_detail == '112': flag = '2' else: flag = '3' db.insert_no_commit(sql % (tld,flag,flag_detail,num)) except: continue db.commit() db.close()
def fetch_domains(): """ To fetch urls from the database, and return urls """ db1 = MySQL1() sql = 'SELECT url FROM benign_urls' db1.query(sql) urls1 = db1.fetch_all_rows() db2 = MySQL() sql = 'SELECT domain FROM domain_white_list' db2.query(sql) urls2 = db2.fetch_all_rows() db1.close() db2.close() urls1 = tuple_to_list(urls1) urls2 = tuple_to_list(urls2) urls = urls1+urls2 print len(urls) urls = list(set(urls)) print len(urls) return urls