Exploit_In.py

#!/usr/bin/python
import sqlite3
from sqlite3 import Error

read_file =     "/mnt/d781aef8-d798-43ed-85f4-7024e4491547/Datasets/Exploit.in/Data/{}.txt"
write_file =    "/mnt/d781aef8-d798-43ed-85f4-7024e4491547/Datasets/Exploit.in/passwords.txt"
error_file =    "/mnt/d781aef8-d798-43ed-85f4-7024e4491547/Datasets/Exploit.in/corrupt_entrys.txt"
database =      "/mnt/d781aef8-d798-43ed-85f4-7024e4491547/Datasets/Exploit.in/database.db"
batch_size = 10000 # Numeber of entrys to process, null if all
password_count_trigger = 50     # Write all passwords with over n occurences to the file
password_count_max = 1000         # Maximum number of Passworts in output file
clear_db = False

def main():
    conn = create_connection(database)
    with conn:
        output_passwords(conn)
        if clear_db:
            create_db(conn)
        print('Processing Data...')
        process(conn)
        print('Writing Data to File...')
        output_passwords(conn)

def insertPassword(conn,password):
    cur = conn.cursor()
    cur.execute("SELECT * FROM passwords WHERE password == '{}'".format(password))
    entry = cur.fetchall()
    if entry == []:
        cur.execute("""
            INSERT INTO passwords(password,count)
            VALUES('{}',1)""".format(password))
    else:
        cur.execute("""
            UPDATE passwords
            SET count = {}
            WHERE password == '{}'""".format(entry[0][1]+1,password))

def output_passwords(conn):
    cur = conn.cursor()

    cur.execute("SELECT * FROM passwords WHERE count > {} ORDER BY count DESC LIMIT {}".format(password_count_trigger,password_count_max))
    with open(write_file,"w") as fo:
        fo.write('count;password\n')

        for password in cur.fetchall():
            fo.write('{};{}\n'.format(password[1],password[0]))

def process(conn):
    cur = conn.cursor()

    cur.execute("SELECT value FROM settings WHERE name == 'current_file_index'")
    current_file_index = cur.fetchall()[0][0]

    cur.execute("SELECT value FROM settings WHERE name == 'current_batch'")
    current_batch = cur.fetchall()[0][0]

    cur.execute("SELECT value FROM settings WHERE name == 'batch_size'")
    batch_size = cur.fetchall()[0][0]

    while True:
        print('Current File {}'.format(current_file_index))
        with open(read_file.format(current_file_index),"r") as fi:

            if current_batch != 0:  # Skips lines that where already processed
                for _ in range(current_batch * batch_size):
                    try:
                        next(fi)
                    except:
                        print('error')

            conn.commit()
            cur.execute('BEGIN TRANSACTION')
            for index,entry in enumerate(fi):
                try:
                    insertPassword(conn,entry.split(':')[1][:-1])
                except:
                    with open(error_file,'a+') as fe:
                        fe.write(entry+'\n')
                if index != 0 and index % batch_size * (current_batch+1) == 0:
                    conn.commit()
                    cur.execute('BEGIN TRANSACTION')
                    print('Batch {} processed'.format(current_batch))
                    current_batch += 1
                    cur.execute("""
                        UPDATE settings
                        SET value = {}
                        WHERE name == 'current_batch'""".format(current_batch+1))
            conn.commit()

        print('Batch {} processed'.format(current_batch+1))
        print('Finished processing File {}'.format(current_file_index))
        current_file_index += 1
        current_batch = 0
        cur.execute("""
            UPDATE settings
            SET value = {}
            WHERE name == 'current_batch'""".format(current_batch))

        cur.execute("""
            UPDATE settings
            SET value = {}
            WHERE name == 'current_file_index'""".format(current_file_index))
        conn.commit()

def create_connection(db_file):

    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return None

def create_db(conn):
    cur = conn.cursor()
    try:
        cur.execute("DROP TABLE passwords")
    except:
        pass

    try:
        cur.execute("DROP TABLE settings")
    except:
        pass

    cur.execute("""
        CREATE TABLE passwords(
            password varchar(50) NOT NULL,
            count INTEGER NOT NULL,
            PRIMARY KEY(password)
        );""")

    cur.execute("""
        CREATE TABLE settings(
            name varchar(50) NOT NULL,
            value INTEGER NOT NULL,
            PRIMARY KEY(name)
        );""")

    cur.execute("""
        INSERT INTO settings(name,value)
        VALUES('batch_size',{})""".format(batch_size))

    cur.execute("""
        INSERT INTO settings(name,value)
        VALUES('current_file_index',1)""")

    cur.execute("""
        INSERT INTO settings(name,value)
        VALUES('current_batch',0)""")

if __name__ == "__main__":
    main()