Using MySQL to stop freeloaders (in only 8 minutes) 

FYI, the plus sign in email addresses isn’t part of any standard. Strictly speaking, an email with and without a plus are different emails. It’s only handled as such by certain (albeit popular) providers. I wouldn’t treat them as the same email just to be safe. Just block emails with plus signs if you really need to but keep in mind plus signs are valid characters in email addresses.

I am always blown away by how you explain things so simply and casually. Do you even need to rehearse these?!

I'm team good guys, but I like to use the + email addresses to identify the service that I'm using. That way, if I start receiving spam I know exactly who leaked my email.

If you are already adding an index, it makes sense to also make it unique, you are not loosing anything. Besides, even if you have an app which checks for these plus signs, it does not meat someone won't try adding those emails directly to database (as part of some maintenance, for example). Yes, this should not happen, but if you can account for that with virtually no effort, I'd say go for it.

This is a wonderful video! I really appreciate the flow of how you explain things

Deleting all entries is fine for a demo, but it would also be an interesting migration for a real use case where you notice people doing this and you want to identify the culprits and also avoid new entries in the database. But that would be for a longer video 😊

Thanks a lot for this one, Aaron. That was nifty and a good extension from your generated columns + indexes video.

You are all sorts of awesome! Great content and delivery style. Thank you kindly for your advice, guidance, and candor! 🍻

Another great tip, thank you! I don't personally think this is too much business logic at all. Data validation should be redundant any, meaning on the client, server, and DB. If ensuring non-duplicate emails is a priority, this is an amazing way to do it on the DB level.

This channel and especially Aaron's videos are god send for every single self taught backend developer. Keep up the great work!

Could you make a video on the difference between using a CTE and subqueries (when we should use one or the other)? That would be totally awesome!

Using + sign in email address is really good. Been doing that since gmail introduced the feature. However, since it is a standard way of doing alias, it is easy to just remove the alias and still send spam. The fix is to use your own domain and use a non-standard delimiter on it(like minus) as others will considered it as a different user and not end up in your inbox if you remove -youtube part. My domain provider offers unlimited email forwarding addresses, with no extra cost. So they take care of that part for me. If the email address does not exactly match the email address, then the email will never reach my inbox. I never get spam with this approach as I will change it when I see the first spam email - I thereby know who got hacked.

Your content is perfect. Short, entertaining and informative.

love yours videos, you can make a video on everything it's always so interesting (also love your mysql courses on planetscale)

Love this video. Awesome and straight to the point.

I never thought of creating logic for validating email in database like this before, It accually help a lot especially having second validation for data consistency.

For Gmail you can also create variations of the same address using dot aaron, a.aron, aa.ron, etc

I use virtual columns very often to extract specific values from JSON strings. That virtual column then is indexed, so I can use that column in a where clause and also benefit from the index to speed up the query.

Aaron that was totally awesome and I think you're totally awesome! That was a really great video, thank you :)

Did know about generated columns. Thanks!

Thanks for this.🙌

When you first started i was like "oh check constraint, i use that for enforcing trimmed columns" Here I am pleasantly surprised, very clever trick there.

I agree with you, this is totally awesome!

this is totally awesome xexex

This is indeed totally awesome. You are correct.

Users with their own domain and a "catch all" setting (all unknown handles are redirected to a common address) enabled would like to introduce themselves. Not me, I'm team good guys, but I won't deny the thought haven't crossed my mind (:

Just here to say thank you Aaron, good stuff.

Love this, was hoping this was the way you were going to go. (Was worried until 2:07 that banning the "+" was going to happen) Good stuff! Part of me wishes there was a cleaner* way to do the double substring index, but all I can think of is an expensive regex call. Oh well 😅 ('cleaner' meaning more concise in my mind)

A good use of this technique might be for when you have phone numbers from various countries. Not all countries format them the same (heck, the US can't agree if it's (xxx) xxx-xxxx or xxx-xxx-xxxx). So you might want to store the phone number as the user formatted it, for display, but compute them in E.164 format for deduplication and interfacing with APIs.

I'm doing this since last 3 years and it's working as it should.

Very interesting approach. Thank you for this video. I do have one small question though, which app do you use in this video (and the others...)? I really like its interface!

Your videos are so polishes, how long does something like this take to produce?

I have also decided this is totally awesome too 💯😂

I love this guy! 😅 Especially this part... ➡✌See ya!✌

I like the approach and have been using similar pattern quite a few times. In specific case you forgot to handle aa.ron, a.aron, ...

Great video! I had no idea about the "+" trick. Quick question, will you consider doing a video on MongoDB and whether their marketing is exaggerating its capabilities compared to a battle tested solution like MySQL?

Aaron, What should we do when our tables grow organically into +30 columns over the course of business years. What should we do? Is it ever a good idea to separate concerns onto multiple tables even when the relation is 1-to-1? High number of columns is certainly scary to me, and I consider it a smell, but... Is it? really?

awesome :)

Plus email addressing is supported by a lot of servers not just gmail. It's a recommended part of the current email spec but not a requirement.

can you talk about Database Partition? hehe would love the discussion regarding this function

Don't forget to use the baseline address with dots as well: a.a.r.o.n => aaron

I was expecting him to use regex for this. Pretty cool! Also, didn't know the whole plus gmail thing, thanks for that.

Its totally awesome. But what does "generated always" do? I mean in course you've created generated column just like "domain varchar(255) AS (substring_index(email, '@', -1))" Thank you

We have added unique index on email_normal, then why it is not allowing duplicate in email column?

Living and learning, hum? Did know about that + thing, that would have helped me in a lot of moments 🤔

This is totally awesome

What about putting dots between the same email address?

Is there an addon/extension that does it automatically for gmail addresses?

that is a nice solution but why not just stop users doing that on form validation? seems to me easier and faster to just stop and feedback user in the client

These types of videos are awesome! This is a really useful and practical use case where generated columns and a unique index solves a very specific business issue. Keep up the good work over there at Plantscale!

Is it possible to create a generated stored column that counts the has-many relationship of an entity?

Nice trick!

Friendly reminder that it's still kinda hostile towards users to prevent account creation with multiple plus based emails. Let's say someone provides a service I want to use multiple times (e.g. some hosting stuff or so) for different businesses I own. Naturally I want them to be isolated from each other, so I create multiple accounts. For logistical reasons I want them all to point to the same email inbox. This is a totally legitimate use case. Preventing this actually makes stuff more complicated for me. But as always: It's about what you do with the data. The better way to handle multiple accounts effectively pointing to the same email would be to e.g. allow your free tier for only one of the address.

Maybe some examples using ORMs will be so good like Prisma

I use it to attribute what it's for, so I can find the source of leaks; if you prevent me from using + aliases, I just wouldn't use your services. Do I sometimes mis-use it? Maybe, but there are other controls for this, like not offering a free tier (honestly why anyone punishes paying customers to support freeloaders IDK)

The database can hold business logic because that's the closest layer to your data, it's even better because the more guards you move on the db the less application layer dependant you become and it also prevents to mess your data if someone insert a value outside your application layer which is great to keep data consistency! Also bonus point for showing this infinite email breach, gonna fix it as well!

Sheesh. So good... and now I have some altering to do in my db. I did not know by the way that you could just add a `+` symbol to create multiple emails. Am I the only one?

Good video! What about temp mails. Is there a solution ?

Could you make some videos on how to live without foreign key constraints in planetscale?

Thanks! My website is just a little more secure now

I like this guy, I make a "+" account to subscribe again.

is generated column same concept with views and materialized views?

That's a very neat solution! Couldn't you just create a functional unique index without creating a virtual column?

A little bit of business logic on the database layer will not hurt, it can actually protect your data integrity if you're importing data directly to your DB without any prior validation or when multiple applications shares the same database and you want to make sure you do not allow unexpected data to come in and out from app you don't have control on I worked in the past (15 years ago) on a project (MSSQL) where most of its business logic is buried inside column constrains, user roles, stored procedures and functions. Anyway, I find it easier for maintaining and debugging purposes to have the business logic in one layer than splitting it between the front-end, back-end and the DB. Suggestion: What about talking about stored routines in MySql🤔 Thanks Aaron!

I cant believe after the decades of using gmail I never knew this.

liked this idea. I dont care if customer wants to use email with + character.

Hm... Interesting trick you got here! 😄 I mean, for preventing freeloaders, I swear I'm a good guy! 🤭

this channel is making mysql more and more attractive tbh

Cool thought experiments, how does it actually connect to your Gmail?

You can still use the dot to create multiple adresses ...

Preventing disposable emails is also a must otherwise million bots can register on your platform in 5 minutes.

0:16 certified team bad guys

There is another issue: when you have Gapps,.you can create aliases and do the same thing.

Catch all email gang, good luck blocking that😁

What if you have 2 or more + signs?

This is why i use masked address rather than alias.

Ahh don't expose the modifiers lol, such a useful feature.

lol, definitely totally awesome!

maybe two "+" signs in the email?

if you have your own domain you can always just route it w another account on it

This is news to me 😮 Is this a gmail thing or does it works with any email provider? Btw, I’m team good guy, I just want to know 😁

You make me wanna use MySQL

"Using MySQL to stop your QA team (in only 8 minutes)"

meanwhile I was here thinking of creating burner mail accounts for each of my side projects. O'well ¯\_(ツ)_/¯

just checked - no freeloaders in my DB 😀

This has "Terrible Idea" stamped on its forehead in red :) The database is definitely not the place to do this. And why would you do CPU work like that unnecessarily on the most expensive CPU in your house. With all the validations that needed to happen before it got to your database layer (length, etc.), may as well have filtered out the plus section earlier too. If I saw this in our database at work, I would find who did it and send them a polite WTF.

Me and my catch-all email server with 20 different domains pointed at it doesn't care. Are you really stopping anyone with this? Like If somebody gets blocked with an "email is already used" message, what do you think they're gonna do? pay you? nah, they'll just put in a different email address. I refuse to believe that you only have one email address, and making new ones for free is easier than ever. So you gained nothing and lost the ability to associate the multiple accounts together in case of abuse.

Good guy: What happens if I use multiple “+” characters 😉

Using this "+" observation to filter unique users is such an ugly idea. If someone offers a free service per email, they should actually do that. If they want an actually real person they should tie it to government ID + face and become a bank. Otherwise, handle it in the security layer where you track ips/cookies/devices and find abnormal activity and request to those users additional verification. And remember murphy's law: If it can, it will.

You are not team good guys, we hate you for stopping us from free trials :P

Pleass dont do this. Many people, myself included, use this for tracking which companies sell my data for spam