ignis-sec
commented
4 years ago Thanks! Glad you like it!
Why im not sharing the code
I've been reluctant to give away too much information about the process and the analysis code itself, because if i can confirm it doesn't violate GDPR (and KVKK in my country), i'm planning to deploy a search engine allowing users to search by email, or crunch password lists with their prefered parameters.
If it violates GDPR, i'll just release the same service after hashing emails, or just dropping the email addresses table all together (This will still allow networking/grouping based on the email id and statistical analysis, but email address itself will be lost)
That being said;
How i processed the text dumps - Python
I've used a python script to crawl a directory and process all the text dumps in that directory into an SQL table. It was running on a single thread in the background for around a week or more.
I was processing text dumps by mainly pattern matching the lines to find out the dump format(most of them are formatted username:password, but not all).
If it failed to recognize any pattern, it would mark that line completed either way, not write anything to the database, but instead write this line to a potfile. I'd later normalize the format of all the lines in this potfile and copy it into the crawled directory to add it back into the mix.
I've also set up a small environment allowing me to pause/continue the processing and save the state - saving which files are proccessed to which line, and committing changes to the sql connection.
Managing redundancy in tables - SQL
At first, it was a simple table to store username:password, and the source dump this credential was found in.
I've decided to do this as an intermediate step first to limit the number of write cycles to my SSD disk. (doing only insert after insert lets me commit database changes in chunks)
After this step was completed, i've grouped up repetetive credentials, and added an occurrence row to the table instead.
Next step was splitting this table to the Boyce-Codd Normal Form. This removed all the redundancy from the database, allowing for much faster queries, and dropping the database size by a lot.
DBMS
For dbms choice, i've used SQLite3 because its much more faster to work with on local, because sqlite is pretty much an fopen() while mysql,psql is unix socket operations and are much slower than direct file operations.
Analysis
Since all the usernames and passwords and credentials are in their own pretty tables, rest of the analysis boils down to simple SQL queries.
Some of the simplest examples
For example, to generate the top 10.000.000 passwords all i had to do was:
.output 10m.txt
SELECT value FROM password ORDER BY occurrence DESC LIMIT 10000000;Many of the statistical data boils down to simple uses of COUNT(*) and SUM(occurrence);
Example: Average length of password which were only found once
SELECT AVG(LENGTH(value)) FROM password WHERE occurrence=1;Absolute hell part of this project
Worst part of this project was one of the redundancy-reducing intermediate steps because i constantly copy paste lines from table definitions when constructing new ones, and i forgot an extra UNIQUE keyword somewhere.
I was inserting the result of another select query to this table(like INSERT INTO new(...) SELECT ...)
That select query took 21 hours. And it threw an error at the end because insert was violating a constraint. I had to repeat.
I hope this answers the parts you were curious about. If not, please feel free to ask!
enseitankado
pooki3bear
kivik92
First of all, thank you for your work. What method or tool you used to process 1B data will be useful if you share the original methods you used. We are also very interested in this part of the study.