User Tools

Site Tools


howtos:import_hashes_to_sqlite3

Table of Contents

Intro

When dealing with the password hashes locally is faster to read them through a database. I have constructed a version where I use sqlite3 as the database. You can probably get better performance using a real database server.

I will use this sqlite3 database for other scripts found in this section.

Get the Hashes

To get hold of the hashes you can use one of the downloader versions out there, I have used this one: https://github.com/HaveIBeenPwned/PwnedPasswordsDownloader

Someone has conveniently made a Snap so on Ubunut you install it like this:

sudo snap install haveibeenpwned-downloader

Then you simply run:

haveibeenpwned-downloader 

to have it download a very big file with all the breached hashes in it.

Import Script

Here is a Python script which takes this big text file and import it into a sqlite3 database:

#!/usr/bin/env python3
import sqlite3
import sys

# Check if the text file's path is provided as an argument.
if len(sys.argv) < 2:
    print("Usage: init_db.py <path_to_textfile>")
    sys.exit(1)

# Constants for the SQLite database and the input text file.
DATABASE = 'leaked_hashes.db'
TEXTFILE = sys.argv[1]

def insert_hashes_from_file(filename, cursor):
    """
    This function inserts hashes from a given text file into the SQLite database.
    
    Parameters:
    - filename (str): The path to the text file containing the hashes.
    - cursor (sqlite3.Cursor): The SQLite cursor object for database operations.
    """
    print(f"Starting to insert hashes from the file: {filename}...")

    with open(filename, 'r') as f:
        counter = 0
        for line in f:
            hash_part, count_part = line.strip().split(':')
            cursor.execute('INSERT INTO hashes (hash, count) VALUES (?, ?)', (hash_part, int(count_part)))
            counter += 1
            # Print progress for every 1,000,000 records inserted.
            if counter % 1000000 == 0:
                formatted_counter = "{:,}".format(counter)
                print(f"Inserted {formatted_counter} records.")
                
    print(f"Finished inserting {counter} records in total.")
    
# Connect to the SQLite database.
conn = sqlite3.connect(DATABASE)
cursor = conn.cursor()

# Drop the hashes table if it already exists.
cursor.execute('''
DROP TABLE IF EXISTS hashes;
''')

# Create the hashes table in the database.
cursor.execute('''
CREATE TABLE hashes (
    hash TEXT PRIMARY KEY,
    count INTEGER
)
''')

# Insert the leaked hashes from the text file into the database.
insert_hashes_from_file(TEXTFILE, cursor)

# Commit the changes and close the database connection.
conn.commit()
conn.close()
howtos/import_hashes_to_sqlite3.txt · Last modified: 22/08/2023 18:50 by domingo