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.
Great point! You could restrict this logic to @gmail.com addresses only, which would work around that. But then you'd miss out on all the GSuite addresses. Much to consider (including that this is a bad idea)
There does exist a proposed standard for it called Sieve Email Filtering: Subaddress Extension, it's RFC 5233. Subaddressing is also great for finding out if a service is sharing your email with a third party.
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.
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 😊
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.
Oh neat, adding your own delimiter is a cool trick! I agree, there are legit reasons to add a + sign, which is why I don't like stripping it out altogether (01:58)
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 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.
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, 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?
Eh... hard to say. It *can* be a good idea to separate really large tables IF you have a lot of columns AND you really only use a subset of "hot" columns. At 30 columns, I don't know that it'd be worth the pain honestly. Creating an extra "meta" or "addendum" table that is still logically tied to a single model comes with a fair amount of maintenance. Unless you see that it's a problem, I'd probably leave it alone for a while.
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.
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 (:
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!
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)
Whew, glad we're on the same page regarding 02:07! Yeah kind of a bummer about the nested substring, but honestly... more readable than a regex, like you said!
@@PlanetScalerandom question, this may be negligible, but do you think something like `IF( INSTR(email, "+") = 0, email, ... )` Would help at all? Like avoid the computation if it isn't subaddressed? Honestly the only case I could imagine is a bulk import of user emails as the only thing capable of stress testing this, and I wonder if the branch condition negates the benefits of avoiding the string manipulation. 🤔
@@PlanetScale oh I think I figured out a simpler way! CONCAT_WS( '@', SUBSTRING_INDEX(email, '+', 1), SUBSTRING_INDEX(email, '@', -1) ) Edit: only works with the prior IF-INSTR combo, or else it borks up the original non-plus email
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.
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!
"The database can hold business logic because that's the closest layer to your data" It's also not really version-able, far, FAR harder to scale than any other layer and incredibly tricky to evolve/refactor, that argument seems logical at first glance, but it quickly turns into a nightmare in the real world.
Version controlling and scaling is not hard with planetscale but if you don't use planetscale or something similar then your point is 100% valid.@@themrubios
This would be a massive no no at our place. Putting business logic on the database has caused us so many headaches over the years that it becomes an instant Close on the pull request..
@@themrubios The logic is part of the schema, which can be refactored using migrations and is therefore already versioned. I have never experienced scalability issues with this in tables that have hundreds of millions of rows and a considerable amount of reads and writes every second. My position is that any data validation logic goes into the database to ensure data consistency, that's why we use relational databases after all.
@@PlanetScale well, I guess the concept isn't far off. So if people are aware of the +, then the dot is close by 😎 After all it will be a simple delimiter split / concat type of query.
Dots can’t be handled by just deleting, unfortunately, as though gsuite does ignore them, most other email systems require the dots and treat them as different email 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
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?
Um I suppose it's somewhat similar in theory. In practice they are completely distinct things. A generated column belongs to a single table and is the result of an expression rather than a full on query. But in the sense that they are both calculated, they are similar!
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?
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!
Oh nice, this is actually a great point! Thanks for all your good comments Ahmad. I'll add stored routines to my list, but I honestly haven't used them much. I'll see if anyone inside PlanetScale has good ideas on them!
@@PlanetScale Thanks 😄 I used stored procedures in the past for MSSQL CRUD operations where I create a SP for each table and call it with all parameters necessary for the specified operation. Inside each SP I use IF statement to determine which code block to execute depending on a parameter represent the operation I want. This might not be a good example, it gets was messy sometimes but I was very excited about SQL back then😎
Indeed you could! I've got a full deep-dive into function indexes coming soon. Functional indexes actually use generated columns under the hood (surprise!) so it's not super different, but you're totally right
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
It is totally awesome! Regarding the generated always, that marks the column as a "generated" column, which MySQL then manages the value of. That means that any time the `email` column is updated, the `email_normal` column gets updated automatically without us having to do anything. You can see more on those here: ruclips.net/video/5aezDBaNfcc/видео.html
If you look carefully the error at the end is mentioning the email normal column, which is where the unique key is! The email normal column is based on the email column so that's how they relate
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)
Agreed! As I mentioned in the video, I'm not sure it's a great idea. Also as mentioned I wouldn't prevent people from using an alias, as that seems user hostile. The video was showing how to prevent duplicate signups, not prevent aliases!
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.
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!
@@PlanetScale - enjoyed the video btw! Re, my comment: I think there’s many ways to set up automation test scripts for any user journey that uses email (create account, guest transaction etc), but I also know plenty of teams use the +alias email quirk (which also works in most current Microsoft / Outlook scenarios) to guarantee that the emails can all be read from the same user / test / api account.
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.
@@PlanetScale ah, ok. If you present it as a "solution" to a real problem then some people will still get confused and might fall into a false sense of security. I guess you kinda have to nowadays get the CPR higher but still.
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.
Isn't it funny how different opinions can be! The comment immediately preceding yours was that it's a great idea to do this in the database 😂 Good idea or not, it's not CPU intensive at all, these string functions will be negligible.
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.
As I noted in the video, I don't think it's a good idea to block people from using this (which is why I didn't!) and it's worth considering if it's right for your product at all. More a way to teach a concept
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.
Great point! You could restrict this logic to @gmail.com addresses only, which would work around that. But then you'd miss out on all the GSuite addresses. Much to consider (including that this is a bad idea)
There does exist a proposed standard for it called Sieve Email Filtering: Subaddress Extension, it's RFC 5233. Subaddressing is also great for finding out if a service is sharing your email with a third party.
Hey. Thank you for Lucia
just to be safe from what?
It might not be part of the base standard but is definitely defined as an extension. See RFC5233
I am always blown away by how you explain things so simply and casually. Do you even need to rehearse these?!
I have SO many outtakes. Dozens and dozens 😭
WTB blooper reel in a short!@@PlanetScale
@@roberthaberle138 Here are some bloopers from our MySQL course 😂 twitter.com/aarondfrancis/status/1623385494309543937
@@PlanetScale amazing editing in that case! Flows so nicely it feels like a single take
For Gmail you can also create variations of the same address using dot
aaron, a.aron, aa.ron, etc
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.
01:58 is just for you then! I agree, there are legit reasons do it
I do the same
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.
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 😊
Haha that would make for a longer video! But would be really useful for actual prod environments. I'll keep that in mind for next time :D
Thanks a lot for this one, Aaron. That was nifty and a good extension from your generated columns + indexes video.
Kinda fun right? You're welcome!
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!
Ah yeah, that's a good idea!
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.
Oh neat, adding your own delimiter is a cool trick! I agree, there are legit reasons to add a + sign, which is why I don't like stripping it out altogether (01:58)
@@PlanetScale Yes, there are different use-cases for this. You can use both. I use my for signup forms etc, and not real humans.
This channel and especially Aaron's videos are god send for every single self taught backend developer. Keep up the great work!
Thank you!
This is a wonderful video! I really appreciate the flow of how you explain things
Thank you so much! Lots and lots of working through the content until I find the right flow. Glad you're enjoying it!
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.
😮💨 whew, glad I was able to surprise you!
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.
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.
Yesssss that's my next video 😎
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?
Eh... hard to say. It *can* be a good idea to separate really large tables IF you have a lot of columns AND you really only use a subset of "hot" columns. At 30 columns, I don't know that it'd be worth the pain honestly. Creating an extra "meta" or "addendum" table that is still logically tied to a single model comes with a fair amount of maintenance. Unless you see that it's a problem, I'd probably leave it alone for a while.
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.
You are all sorts of awesome! Great content and delivery style. Thank you kindly for your advice, guidance, and candor! 🍻
Thank you for the kind words! Means a lot to me
This is indeed totally awesome. You are correct.
Your content is perfect. Short, entertaining and informative.
Thank you so much! I really like teaching in what I think is a fun way, so I'm pumped to hear this
Did know about generated columns. Thanks!
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 (:
Team good guys, of course. But... well... that's interesting isn't it
yeah i thought of this too
Love this video. Awesome and straight to the point.
🫡 that's always my goal
Aaron that was totally awesome and I think you're totally awesome! That was a really great video, thank you :)
Hey nice, thank you so much!
Your videos are so polishes, how long does something like this take to produce?
I'm doing this since last 3 years and it's working as it should.
Woah cool! Nice to hear
Just here to say thank you Aaron, good stuff.
You're welcome! Thanks for the kind words
love yours videos, you can make a video on everything it's always so interesting (also love your mysql courses on planetscale)
you're kind to say so, thank you!
I agree with you, this is totally awesome!
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!
Good news, I have a whole video on it! ruclips.net/video/7V_CJBPZPes/видео.html
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)
Whew, glad we're on the same page regarding 02:07!
Yeah kind of a bummer about the nested substring, but honestly... more readable than a regex, like you said!
@@PlanetScalerandom question, this may be negligible, but do you think something like
`IF( INSTR(email, "+") = 0, email, ... )`
Would help at all? Like avoid the computation if it isn't subaddressed?
Honestly the only case I could imagine is a bulk import of user emails as the only thing capable of stress testing this, and I wonder if the branch condition negates the benefits of avoiding the string manipulation. 🤔
@@PlanetScale oh I think I figured out a simpler way!
CONCAT_WS( '@',
SUBSTRING_INDEX(email, '+', 1),
SUBSTRING_INDEX(email, '@', -1)
)
Edit: only works with the prior IF-INSTR combo, or else it borks up the original non-plus email
I have also decided this is totally awesome too 💯😂
💪💪
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.
Oh that's a fantastic idea!
Hm... Interesting trick you got here! 😄
I mean, for preventing freeloaders, I swear I'm a good guy! 🤭
oh no, what have I done
I love this guy! 😅
Especially this part... ➡✌See ya!✌
✌️😎✌️
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!
Very good points, thank you!
"The database can hold business logic because that's the closest layer to your data"
It's also not really version-able, far, FAR harder to scale than any other layer and incredibly tricky to evolve/refactor, that argument seems logical at first glance, but it quickly turns into a nightmare in the real world.
Version controlling and scaling is not hard with planetscale but if you don't use planetscale or something similar then your point is 100% valid.@@themrubios
This would be a massive no no at our place. Putting business logic on the database has caused us so many headaches over the years that it becomes an instant Close on the pull request..
@@themrubios The logic is part of the schema, which can be refactored using migrations and is therefore already versioned. I have never experienced scalability issues with this in tables that have hundreds of millions of rows and a considerable amount of reads and writes every second. My position is that any data validation logic goes into the database to ensure data consistency, that's why we use relational databases after all.
Don't forget to use the baseline address with dots as well:
a.a.r.o.n => aaron
Yeah great point. I thought about going into that but didn't thought it'd be too in the weeds
@@PlanetScale well, I guess the concept isn't far off. So if people are aware of the +, then the dot is close by 😎
After all it will be a simple delimiter split / concat type of query.
this is totally awesome xexex
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.
Oh nice! I didn't know how widely supported it was.
can you talk about Database Partition? hehe would love the discussion regarding this function
I was expecting him to use regex for this. Pretty cool! Also, didn't know the whole plus gmail thing, thanks for that.
Him is me! While I do love regex, this feels cleaner. Glad you enjoyed it!
@@PlanetScaleoh yes, cleaner and probably faster. Cheers!
Thanks for this.🙌
I like this guy, I make a "+" account to subscribe again.
wait hang on
Living and learning, hum? Did know about that + thing, that would have helped me in a lot of moments 🤔
Could you make some videos on how to live without foreign key constraints in planetscale?
Yes, for sure! Also... Foreign key constraints are coming soon 🙊
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, ...
Dots can’t be handled by just deleting, unfortunately, as though gsuite does ignore them, most other email systems require the dots and treat them as different email addresses.
this channel is making mysql more and more attractive tbh
The secret plan is working
maybe two "+" signs in the email?
Haha, maybe!
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
They may have valid reasons to do that (mentioned in 02:00) so I don't like to prevent it altogether, I just don't want duplicates!
This is totally awesome
see, you get it!
Is there an addon/extension that does it automatically for gmail addresses?
I actually have no idea! Sorry
Good video! What about temp mails. Is there a solution ?
You'd have to use an API for that, unfortunately
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?
0:16 certified team bad guys
Nice trick!
devcunha+thanks!
What about putting dots between the same email address?
is generated column same concept with views and materialized views?
Um I suppose it's somewhat similar in theory. In practice they are completely distinct things. A generated column belongs to a single table and is the result of an expression rather than a full on query. But in the sense that they are both calculated, they are similar!
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?
Haha that sounds very spicy... I do have some videos coming on MySQL scalability, but I'll think about a direct comparison video 🙊
Thanks! @@PlanetScale
Catch all email gang, good luck blocking that😁
/me shakes fist at REAZNx
liked this idea. I dont care if customer wants to use email with + character.
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!
Oh nice, this is actually a great point! Thanks for all your good comments Ahmad.
I'll add stored routines to my list, but I honestly haven't used them much. I'll see if anyone inside PlanetScale has good ideas on them!
@@PlanetScale Thanks 😄
I used stored procedures in the past for MSSQL CRUD operations where I create a SP for each table and call it with all parameters necessary for the specified operation.
Inside each SP I use IF statement to determine which code block to execute depending on a parameter represent the operation I want.
This might not be a good example, it gets was messy sometimes but I was very excited about SQL back then😎
That's a very neat solution! Couldn't you just create a functional unique index without creating a virtual column?
Indeed you could! I've got a full deep-dive into function indexes coming soon. Functional indexes actually use generated columns under the hood (surprise!) so it's not super different, but you're totally right
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
It is totally awesome!
Regarding the generated always, that marks the column as a "generated" column, which MySQL then manages the value of. That means that any time the `email` column is updated, the `email_normal` column gets updated automatically without us having to do anything.
You can see more on those here: ruclips.net/video/5aezDBaNfcc/видео.html
We have added unique index on email_normal, then why it is not allowing duplicate in email column?
If you look carefully the error at the end is mentioning the email normal column, which is where the unique key is! The email normal column is based on the email column so that's how they relate
Cool thought experiments, how does it actually connect to your Gmail?
It doesn't connect to your Gmail, it merely inspects the style of your 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)
Agreed! As I mentioned in the video, I'm not sure it's a great idea. Also as mentioned I wouldn't prevent people from using an alias, as that seems user hostile. The video was showing how to prevent duplicate signups, not prevent aliases!
Thanks! My website is just a little more secure now
Preventing disposable emails is also a must otherwise million bots can register on your platform in 5 minutes.
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.
Is it possible to create a generated stored column that counts the has-many relationship of an entity?
Unfortunately not, it can't reference other tables 🥲
Ahh don't expose the modifiers lol, such a useful feature.
I cant believe after the decades of using gmail I never knew this.
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!
Thanks! I'm trying to do more videos where I show cool techniques but with a business use case.
awesome :)
lol, definitely totally awesome!
🫡
This is why i use masked address rather than alias.
"Using MySQL to stop your QA team (in only 8 minutes)"
Sounds like this would fall under "bad idea" for your product!
@@PlanetScale - enjoyed the video btw!
Re, my comment: I think there’s many ways to set up automation test scripts for any user journey that uses email (create account, guest transaction etc), but I also know plenty of teams use the +alias email quirk (which also works in most current Microsoft / Outlook scenarios) to guarantee that the emails can all be read from the same user / test / api account.
There is another issue: when you have Gapps,.you can create aliases and do the same thing.
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.
Just trying to teach some database concepts in a fun way, as a treat
@@PlanetScale ah, ok. If you present it as a "solution" to a real problem then some people will still get confused and might fall into a false sense of security. I guess you kinda have to nowadays get the CPR higher but still.
What if you have 2 or more + signs?
You cannot register it. Anything after first + sign is alias (also next n + signs) and is thrown away.
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 😁
I'm not sure actually! It works with all gsuite addresses, but I'm not sure about any others
You make me wanna use MySQL
[me, excitedly] the plan... it's working
@@PlanetScale I've also always disliked DBs, so I'd say it is working, yes. 😂
You can still use the dot to create multiple adresses ...
Don't tell the bad guys
if you have your own domain you can always just route it w another account on it
Hmm as a good guy, I can think of many ways to use this 😏
I've used the + trick to delete an illegal account that was using my phone number somehow lol
meanwhile I was here thinking of creating burner mail accounts for each of my side projects. O'well ¯\_(ツ)_/¯
Good guy: What happens if I use multiple “+” characters 😉
Sounds like something a bad guy would say!
I think this technique still works on that tho, btw
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.
Isn't it funny how different opinions can be! The comment immediately preceding yours was that it's a great idea to do this in the database 😂 Good idea or not, it's not CPU intensive at all, these string functions will be negligible.
You are not team good guys, we hate you for stopping us from free trials :P
I bet exactly zero people have this implemented, so you're probably still ok 🤐
@@PlanetScale 😂
just checked - no freeloaders in my DB 😀
😮💨 whew, good news!
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.
Like I said at the end, it may or may not be a good idea, but it's at least interesting 🤗
Pleass dont do this. Many people, myself included, use this for tracking which companies sell my data for spam
As I noted in the video, I don't think it's a good idea to block people from using this (which is why I didn't!) and it's worth considering if it's right for your product at all. More a way to teach a concept