Leak Review: Reviewing Collection #1-5 and AntiPublic MYR & Zabagur #1-2

Preface

In 17th of January 2019 Troy Hunt wrote a blogpost about a data leak that was shared on late December 2018/early January 2019, named Collection #1. Upon the blog-post from Troy Hunt, a forum post in RaidForums surfaced, containing the the following statement:

It’s come to my attention that someone has posted a fake or false database calling it Collection #1. He said it’s a different base but includes the Collection #1. That is untrue. Collection #1 comes in a 1TB cloud that troy hasn’t seen. Here I present to you The real Collection #1 + More: (These were all included in original dump, troy only got 1 link rather than all)

The forum post contained:

• Collection #2
• Collection #3
• Collection #4
• Collection #5
• AntiPublic MYR & Zabagur #1
• AntiPublic MYR & Zabagur #2

I checked to see if anyone has performed a new analysis on the full data, but to my surprise, no one attempted to analyze the new leak. As a result, I took it upon myself to perform a somewhat basic analysis of the data, analyzing the counts of passwords, domains, etc., and making comparisons.

Parsing the Data

To analyze the data, I first had to parse it so that I could save the contents of the leaks into a database and run queries. At first, I wrote a basic parser that would write:

• Collection number
• Subcollection name
• Username
• Email
• Password

into a SQLite database. Since my script was “basic”, it didn’t recursively browse the directories. As I tested the script in various subcollections from Collection 1, I started to face issues in Python 3 (specifically, regarding Pandas and other similar libraries) not liking the data that were present within the subcollections. Due to these errors, I came across p3pperp0tts’s leaks_parser while looking at possible ways to solve my issues. I forked the repository and tested the code on several the same subcollections from before.

After seeing the original script working fairly well, I started editing the script to remove useless functions and unclear variables. For example, the script originally checked if the passwd variable was a MD5, SHA1, SHA-256 or bcrypt hash. If there was a password instead of a hash, it’d hash the password in the aforementioned hash formats and write them to the database alongside:

• Collection name
• Subcollection name
• Username
• Email
• Password

The hashing process was “useless” for my research and it just ate valuable and limited disk space.

After I finished modifying the script, I started out parsing with just a collection at a time. The reason behind this is that the whole leaks were ~900 GB in space (when zipped). When unzipped this went up to ~1.5 GB. I estimated that the parsed would take roughly 4-6 TB of space (assuming if all the data was parsable). Thus, I assumed I’d a total of 8 TB space at best, which I didn’t have. As a result, I decided to parse each collection individually, then move the databases to the various old HDDs I had lying around.

By the end of my parsing process, I was a bit surprised to see that my size expectations fell short, mainly due to the nature of some of the leaked data. The parser generally skipped .sql, .xlsx, .enc etc. file formats because unlike the .txt files, these files contained varying formattings.

An overview of my parse can be found below:

Due to how I parsed the data, it’s impossible to count the actual number of unique emails and unique passwords in general. Instead, it’s only available per-database basis, i.e. number of unique emails and unique passwords for 7 databases.

All of the data above were gathered using the following SQL queries:

• Number of Rows in Table: SELECT max(RowID) FROM credentials;
• Number of Emails: SELECT count(email) FROM credentials WHERE email != '';
• Number of Unique Emails: SELECT count(DISTINCT(email)) FROM credentials WHERE email != '';
• Number of Passwords: SELECT count(password) FROM credentials WHERE password != '';
• Number of Unique Passwords: SELECT count(DISTINCT(password)) FROM credentials WHERE password != '';

Analyzing the Data

For the first part of the analysis, we’ll be taking a look at the collections individually, then as a whole.

In order to gather the data below, I ran the following SQL queries in addition to the queries listed above:

• Top 50 Emails Domains: SELECT domain, count(domain) FROM credentials WHERE domain != '' GROUP BY domain ORDER BY count(domain) DESC LIMIT 50;
• Top 50 Passwords: SELECT password, count(password) FROM credentials WHERE password != '' GROUP BY password ORDER BY count(password) DESC LIMIT 50;

It should be noted that the domain column was added afterwards to the table after I realized that I was not able to regex/get the domains from emails then group them in SQLite. However, this change was commited to the Collections-Parser script as it was only done for data analysis purposes.

Collection #1

In Collection 1, we can see that there are:

• 2,216,800,541 (two billion, two hundred sixteen million, eight hundred thousand, five hundred forty-one) rows in the credentials table.
• 2,130,119,663 (two billion, one hundred thirty million, one hundred nineteen thousand, six hundred sixty-three) email addresses, 740,059,190 (seven hundred forty million, fifty-nine thousand, one hundred ninety) of which (34.74261107743223%) is unique.
• 2,213,968,255 (two billion, two hundred thirteen million, nine hundred sixty-eight thousand, two hundred fifty-five) email addresses, 389,342,787 (three hundred eighty-nine million, three hundred forty-two thousand, seven hundred eighty-seven) of which (17.58574388411906%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

Collection #2

In Collection 2, we can see that there are:

• 6,541,870,525 (six billion, five hundred forty-one million, eight hundred seventy thousand, five hundred twenty-five) rows in the credentials table.
• 6,351,762,246 (six billion, three hundred fifty-one million, seven hundred sixty-two thousand, two hundred forty-six) email addresses, 622,753,539 (six hundred twenty-two million, seven hundred fifty-three thousand, five hundred thirty-nine) of which (9.804421432684714%) is unique.
• 6,533,529,584 (six billion, five hundred thirty-three million, five hundred twenty-nine thousand, five hundred eighty-four) email addresses, 389,342,787 (one billion, five hundred ten million, nine hundred thirty thousand, five hundred thirty-two) of which (23.125793073626344%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

Collection #3

In Collection 3, we can see that there are:

• 320,154,512 (three hundred twenty million, one hundred fifty-four thousand, five hundred twelve) rows in the credentials table.
• 258,308,897 (two hundred fifty-eight million, three hundred eight thousand, eight hundred ninety-seven) email addresses, 196,555,402 (one hundred ninety-six million, five hundred fifty-five thousand, four hundred two) of which (76.09315988833323%) is unique.
• 319,673,012 (three hundred nineteen million, six hundred seventy-three thousand, twelve) email addresses, 110,834,431 (one hundred ten million, eight hundred thirty-four thousand, four hundred thirty-one) of which (34.671188007575694%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

Collection #4

In Collection 4, we can see that there are:

• 4,855,096,127 (four billion, eight hundred fifty-five million, ninety-six thousand, one hundred twenty-seven) rows in the credentials table.
• 4,812,592,131 (four billion, eight hundred twelve million, five hundred ninety-two thousand, one hundred thirty-one) email addresses, 1,238,571,009 (one billion, two hundred thirty-eight million, five hundred seventy-one thousand, nine) of which (25.736047753181186%) is unique.
• 4,847,643,851 (four billion, eight hundred forty-seven million, six hundred forty-three thousand, eight hundred fifty-one) email addresses, 485,832,078 (four hundred eighty-five million, eight hundred thirty-two thousand, seventy-eight) of which (10.022024986422625%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

Collection #5

In Collection 5, we can see that there are:

• 1,177,050,940 (one billion, one hundred seventy-seven million, fifty thousand, nine hundred forty) rows in the credentials table.
• 1,147,163,307 (one billion, one hundred forty-seven million, one hundred sixty-three thousand, three hundred seven) email addresses, 464,129,446 (four hundred sixty-four million, one hundred twenty-nine thousand, four hundred forty-six) of which (40.45888176233308%) is unique.
• 1,173,460,341 (one billion, one hundred seventy-three million, four hundred sixty thousand, three hundred forty-one) email addresses, 237,831,630 (two hundred thirty-seven million, eight hundred thirty-one thousand, six hundred thirty) of which (20.26754732906649%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

AntiPublic #1 & MYR

In AntiPublic #1 & MYR, we can see that there are:

• 1,034,710,004 (one billion, thirty-four million, seven hundred ten thousand, four) rows in the credentials table.
• 1,030,512,696 (one billion, thirty million, five hundred twelve thousand, six hundred ninety-six) email addresses, 720,017,713 (seven hundred twenty million, seventeen thousand, seven hundred thirteen) of which (69.86985369465066%) is unique.
• 1,032,805,550 (one billion, thirty-two million, eight hundred five thousand, five hundred fifty) email addresses, 276,966,790 (two hundred seventy-six million, nine hundred sixty-six thousand, seven hundred ninety) of which (26.816934707602996%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

AntiPublic #2 & MYR

In AntiPublic #2 & MYR, we can see that there are:

• 629,339,966 (six hundred twenty-nine million, three hundred thirty-nine thousand, nine hundred sixty-six) rows in the credentials table.
• 627,586,251 (six hundred twenty-seven million, five hundred eighty-six thousand, two hundred fifty-one) email addresses, 345,897,019 (three hundred forty-five million, eight hundred ninety-seven thousand, nineteen) of which (55.1154551982051%) is unique.
• 629,315,426 (six hundred twenty-nine million, three hundred fifteen thousand, four hundred twenty-six) email addresses, 188,895,062 (one hundred eighty-eight million, eight hundred ninety-five thousand, sixty-two) of which (30.015959278265015%) is unique.

Taking a look top 10 domains by the number of occurrences in the table, we get the following table:

Similarly, if we were to take a look at the top 10 passwords by the number of occurrences in the table, we get the following:

Taking a look at the overall results:

1. In each collection, the top 10 domains (sorted by the number of occurrences) make up 50% or more of the collection in question.
2. In terms of email domains, Yahoo users seem to be pwned more than their counterparts.
3. In each collection, the top 10 passwords (sorted by the number of occurrences) make up less than 10% of the collection in question. I anticipated a higher percentage and I’m unsure why this happened. One assumption was that there is a large amount of hashed passwords in the databases, but as I mentioned above, I gathered the top 50 passwords, and in those passwords I only got 3 MD5 hashes, which I cracked and updated the databases for. Thus the top 50 results were updated for hashes for “healthier” results.

Taking a Look At the Bigger (and Whole) Picture

For further analysis, we can merge all these email and password data from the tables above (alongside the other 40 entries in top 50). Once we do that, we see the following:

In the table above, we can see that gmail.com is the most pwned domain with a ~20% share in percentage, closely followed by yahoo.com with a ~18% share in percentage. When summed up, all of the domains in top 10 make up for 73.0362362098% of the email addresses in 16,358,045,191 email addresses found in all 7 databases.

Examining NordPass’ Top 200 Most Common Passwords of 2021 research, we see the following passwords in top 10 for 2021:

Taking a look at both of the tables above, we can easily make the following conclusions (per password):

• 123456 is still the most common password,
• 123456789 is still the 2nd most common password,
• qwerty has lost a position, currently sitting at 4th place,
• password has also lost a position, currently sitting at 5th place,
• 12345 has gained 2 positions, currently sitting at 3rd place,
• 12345678 has retained its position as the 6th common password,
• qwerty123 has lost its overall position in top 10, currently sitting at 11th place in NordPass’ research,
• 1q2w3e has also lost its overall position in top 10, currently sitting at 13th place in NordPass’ research,
• 111111 has gained 2 positions, currently sitting at 7th place,
• 1234567890 has gained a position, currently sitting at 9th place,
• 1234567 was 12th place in my research, hence it lacking on the table, but it has gained 2 positions, currently sitting at 10th place.

A “safe” assumption would be that since January 2019 (the date all these Collections were posted on RF), there has been a small difference in most commonly used passwords.

Conclusion

With the bigger picture examined, it’s possible to say that despite the leak being nearly 3 years old, it still holds some relevancy, perhaps to be used in password cracking attempts. However, some password lists are being shared around the web that will certainly contain the majority of the passwords in these collections. Even if they do not contain them, they can likely be obtained by using a decent rule.

Author’s Advice

As a closing statement, I wanted to write a small section talking about some good practices for your credentials. These are some of the general tips I give to everyone that ask me questions about passwords and password managers:

1. Use a password manager. There are great password managers that are free or offer free plans, such as KeePass XC or Bitwarden. Start using whatever you’re comfortable with, free or paid. Just be sure to use one that’s well known by others.
2. Use unique passwords for every account you have. This goes hand-in-hand with (1). Since you’ll be using a password manager, you don’t have to worry about “But how will I remember all these passwords?”.
3. Use lengthy passwords. It’s a decent idea to use 16 characters as a base minimum. Be sure to add numbers and special characters to the mix.
4. Don’t use PI (personal identifiers) in your passwords. In other words don’t include anniversary dates, birthdays, pet names, etc. in the passwords.
5. Use 2FA (two-factor authentication)/MFA (multifactor authentication) whenever it’s available. It doesn’t matter if it’s SMS-based or timer (Google Authenticator, Authy, etc.) based, using either one is better than using none.

Special Thanks

I’d like to thank brittnik of BishopFox for her invaluable feedback for this post.