#!/usr/bin/python2 # This script is designed to parse a file created with ngrep, to compile lists of usernames and passwords. # It is complimented by the script auto_user_add.html, which takes the output of this script, and # adds the users to the unix passwords file without a shell. # It relies on the sql.html script. def ReCreatedb(): "" #------------- Re-create the database ------------- print "Re-creating database"; print CREATEQUERY = "DROP DATABASE %s;" % SQL_LOGIN["db"] CREATEQUERY1 = "CREATE DATABASE %s;" % SQL_LOGIN["db"] CREATEQUERY2 = """CREATE TABLE `username` ( `id` int(16) NOT NULL auto_increment, `this_date` date default NULL, `this_time` time default NULL, `ip` varchar(50) default NULL, `username` varchar(60) default NULL, PRIMARY KEY (`id`), KEY `this_date` (`this_date`), KEY `this_time` (`this_time`), FULLTEXT KEY `ip` (`ip`) ) TYPE=MyISAM""" CREATEQUERY3 = """CREATE TABLE `password` ( `id` int(16) NOT NULL auto_increment, `this_date` date default NULL, `this_time` time default NULL, `ip` varchar(50) default NULL, `password` varchar(60) default NULL, PRIMARY KEY (`id`), KEY `this_date` (`this_date`), KEY `this_time` (`this_time`), FULLTEXT KEY `ip` (`ip`) ) TYPE=MyISAM""" CREATEQUERY4 = """CREATE TABLE `final` ( `id` int(16) NOT NULL auto_increment, `username` varchar(60) default NULL, `password` varchar(60) default NULL, `occur` int(16) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM""" CREATEQUERY5 = """CREATE TABLE `username1` ( `id` int(16) NOT NULL auto_increment, `this_date` date default NULL, `this_time` time default NULL, `ip` varchar(50) default NULL, `username` varchar(60) default NULL, PRIMARY KEY (`id`), KEY `this_date1` (`this_date`), KEY `this_time1` (`this_time`), FULLTEXT KEY `ip` (`ip`) ) TYPE=MyISAM""" CREATEQUERY6 = """CREATE TABLE `password1` ( `id` int(16) NOT NULL auto_increment, `this_date` date default NULL, `this_time` time default NULL, `ip` varchar(50) default NULL, `password` varchar(60) default NULL, PRIMARY KEY (`id`), KEY `this_date1` (`this_date`), KEY `this_time1` (`this_time`), FULLTEXT KEY `ip` (`ip`) ) TYPE=MyISAM""" CREATEQUERY7 = """CREATE TABLE `final1` ( `id` int(16) NOT NULL auto_increment, `username` varchar(60) default NULL, `password` varchar(60) default NULL, `occur` int(16) default NULL, PRIMARY KEY (`id`) ) TYPE=MyISAM""" db.send( CREATEQUERY ) db.send( CREATEQUERY1 ) db.send( CREATEQUERY2 ) db.send( CREATEQUERY3 ) db.send( CREATEQUERY4 ) db.send( CREATEQUERY5 ) db.send( CREATEQUERY6 ) db.send( CREATEQUERY7 ) print "Database has been re-created"; print def ProcessFile(filepath, userTableName, passTableName): "" print "Now opening file, reading all contents into a variable"; print print filepath print userTableName print passTableName c = 0 wordFile = open(filepath, 'r') allLines = wordFile.readlines() wordFile.close() data = list(allLines) datanum = len(data) print "There are %d records to be processed." % (len(allLines)/3) print "Now extracting all recordsets, and putting into apropriate tables in MySQL." ; print for i in range(datanum): complete = data[i].split() try: complete1 = data[i+1].split() except IndexError: break try: if complete[0] == "T" : if complete1[0] == "USER": comp_temp = str(complete1[1]) com2 = str(complete[2]) QUERY1 = "INSERT INTO %s VALUES (null, '%s', '%s', '%s', '%s')" % (userTableName, complete[1], com2[:-7], complete[3], comp_temp[:-2]) db.send( QUERY1 ) elif complete1[0] == "PASS": comp_temp = str(complete1[1]) com2 = str(complete[2]) QUERY1 = "INSERT INTO %s VALUES (null, '%s', '%s', '%s', '%s')" % (passTableName, complete[1], com2[:-7], complete[3], comp_temp[:-2]) db.send( QUERY1 ) except IndexError: c = (c + 1) incrcount = c % phase1counter if incrcount == 0: print "%s records processed" % c def ExtractSets(userTableName, passTableName, finalTableName): "" print "Now selecting distinct usernames from the MySQL database"; print print userTableName print passTableName print finalTableName QUERY2 = "SELECT DISTINCT(u.username) FROM %s u;" % userTableName result2 = db.send( QUERY2 ) names = len(result2) print "There are %s usernames found" % names; print print "Begin matching usernames to passwords"; print total = len(result2) count = 0 for item1 in result2: QUERY3 = "SELECT u.ip, u.this_date, u.this_time FROM %s u WHERE u.username = '%s';" % (userTableName, item1[0]) result3 = db.send( QUERY3 ) count = (count + 1) count1 = 0 incrcount = count % phase2counter if incrcount == 0: print "%s usernames processed" % count for item in result3: QUERY4 = "SELECT p.password FROM %s p WHERE p.ip = '%s' AND p.this_date = '%s' AND p.this_time = '%s';" % (passTableName, item[0], item[1], item[2]) result3 = db.send( QUERY4 ) count2 = 0 count1 = (count1 + 1) for item2 in result3: QUERY5 = "INSERT INTO %s VALUES (null, '%s', '%s', '%s');" % (finalTableName, item1[0], item2[0], count1) db.send( QUERY5 ) print "Finished matching data sets"; print def MakeUnique(finalpath, finalTableName): "" print "Now getting distinct Username/password set list"; print RESQUERY = "SELECT DISTINCT f.username, f.password FROM %s f ORDER BY f.username ASC;" % finalTableName resresult = db.send( RESQUERY ) print "Saving the distinct username/password sets into a file"; print finFile = open(finalpath, 'w') for guess in resresult : print >> finFile, guess[0] + " " + guess[1] finFile.close() print "Finished, your file is '%s'" % outpath if __name__ == "__main__": #------------- Import all modules needed ------------- import sql #------------- Set 'global' variables ------------- # inpath are the input files from ngrep inpath = "/{masked}/typescript14" inpath1 = "/{masked}/recovery_phase2_1" # outpath are the files that will store the username|password sets. outpath = "/{masked}/final_list14" outpath1 = "/{masked}/final_list_phase2_1" phase1counter = 25000 phase2counter = 50 data = [] complete = "" count = 0 SQL_LOGIN = { 'host': "{hostname}", 'user': "{sqluser}", 'pass': "{sqlpassword}", 'db': "{databasename}", } #------------- Connect to the database ------------- db = sql.connection( SQL_LOGIN ) print; print "Database connection made"; print #------------- Begin Doing Stuff ------------- ReCreatedb() ## Re-create the database's table structure #------------- Process the second file ------------- ProcessFile(inpath1, 'username1', 'password1') ExtractSets('username1', 'password1', 'final1') MakeUnique(outpath1, 'final1') #------------- Process the first file ------------- ProcessFile(inpath, 'username', 'password') ExtractSets('username', 'password', 'final') MakeUnique(outpath, 'final') print; print "Thankyou please come again"