Thread by @alexpotato

1. So inspired by @AccidentalCISO, @0xfraq, @TinkerSec here is a story about some work I did back in the early 2000's to help fight spam.

It was late 2002. For those of you that don't remember, 9/11 had just happened and the dot com bubble had burst.

@AccidentalCISO @0xfraq @TinkerSec 2. I was fresh out of grad school(MBA) and looking to use my Comp Sci, Econ and MBA to good use.

I ended up at a small(<20 ppl) cybersecurity firm that had spun out of another firm that originally did due diligence background checks for CEOs etc.

@AccidentalCISO @0xfraq @TinkerSec 3. This firm was primarily hired by the law firms of big Fortune 500 companies to do online investigations into fraud and criminal behaviour around their products. e.g. a big satellite TV provider was a big client who wanted to know how people were hacking their smartcards.

@AccidentalCISO @0xfraq @TinkerSec 4. One of the client law firms asked "Hey, Hotmail is one of our clients and they are having a big problem with spam in Korea. Do you guys handle this kind of thing??".

Given that the firm I worked for would say "Yes!" to anything that led to revenue, they said "Of course!"

@AccidentalCISO @0xfraq @TinkerSec 5. At this point you can probably guess that this fell to me.

Key points:
- I didn't (& don't) speak Korean
- I had never dealt with email/SMTP etc
- Had never built a database

Clearly, I was the right guy for the job!

@AccidentalCISO @0xfraq @TinkerSec 6. Thankfully, a buddy of mine from grad school was Korean so we hired him as a temp translator and I got to work trying to figure out how to put 10,000 emails into a Microsoft Access database.

@AccidentalCISO @0xfraq @TinkerSec 7. Surprisingly, it worked! We were able to get some good intel on the spammers and a partner law firm in Korea sent some cease and desist letters and the spam stopped. Smiles and congratulations all around on both sides (us and the client).

@AccidentalCISO @0xfraq @TinkerSec 8. Hotmail was so happy that they said "That was great! We're going to start sending you 100,000 email/day and we're going to start sending them tomorrow. That cool with you guys??"

- The BizDev guy: "Of course!"
- Me: "Wait, what??"

@AccidentalCISO @0xfraq @TinkerSec 9. I said "I'm no DBA but I'm pretty sure we can't handle this in Microsoft Access on a regular desktop!". Thankfully, people agreed with me and a Dell 2600 we had just bought was repurposed for the new project. Time to get my learn on about Perl, MySql and SMTP/Spam.

@AccidentalCISO @0xfraq @TinkerSec 10. On top of learning all of the above on the fly (as in literally having the MySql docs page open as I coded), the Dell 2600s had a nasty bug where they would just restart for no reason. A great feature when you're trying to build a database + email parser.

@AccidentalCISO @0xfraq @TinkerSec 11. For those of you who have never dealt with spam and/or SMTP, the old motto of "be conservative in what you send and liberal in what you accept" was BIG! Spam emails have all kinds of crazy SMTP headers: multiple subject, multiple from fields, totally bogus relay chains etc.

@AccidentalCISO @0xfraq @TinkerSec 12. BUT, it ALL matters because anything could be a signature or point back to a spammer. I started out parsing the headers using my VERY basic Perl regex skills. I quickly realized the bananas that was SMTP headers would need lots of many to many table relationships.

@AccidentalCISO @0xfraq @TinkerSec 13. All in we eventually got to 20 million emails which meant that we 300 million rows of JUST header information. On a single machine. In 2003.

We haven't even gotten to talking about the BODY of the emails which is its own interesting story:

@AccidentalCISO @0xfraq @TinkerSec 14. Because spam email bodies are even crazier than the headers, for a while, the body sat in a BLOB field. As we got better at targeting, clients started asking us to search the email bodies for URLs. Since this was "from email where body like '%url%'", each search took 3 DAYS!

@AccidentalCISO @0xfraq @TinkerSec 15. This was also taking away from parsing time so I said "Hey, if you give me 5 days, I can code a parser and then index the urls so each search takes minutes instead of days." I thought myself pretty smart for proposing this.

@AccidentalCISO @0xfraq @TinkerSec 16. The client comes back and says "That would be uber-awesome but we have a court case where we are presenting the evidence in 4 days so we don't have 5 days to give you." #sadface

@AccidentalCISO @0xfraq @TinkerSec 17. Lo and behold, we ended up having an outage in the database that:
- took 5 days to fix
- SOMEHOW, as part of the fix, we suddenly had parsed email bodies and url searches now took minutes.

Weird huh?

@AccidentalCISO @0xfraq @TinkerSec 18. I should add that no matter how I structured the DB, I couldn't do joins with more than 2 tables b/c they would either take hours or I would get out of memory errors. I had to break every query into 2 table joins that output to a temp table and using that for the next join.

@AccidentalCISO @0xfraq @TinkerSec 19. I learned SO much about: regexes, database indexing, SQL optimization, SMTP, spam, how the legal system and technology intersect and more.

Open to any questions people may have and/or can expand on any of the above. /end

If you liked this thread, here is a meta thread of my other threads:

https://twitter.com/alexpotato/status/1212223167944478720?s=21

https://t.co/YPFQYrRwAN