User Tools

Site Tools


howtos:import_hashes_to_sqlite3

This is an old revision of the document!


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.

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.1692730131.txt.gz · Last modified: 22/08/2023 20:48 by domingo