Jason Fel Jason Fel - 4 months ago 21
SQL Question

Is there anything wrong with doing user operations with an email instead of id?

I am in the process of developing a mobile application. Is there anything wrong if I choose to do most of my user lookups through email? For example when the user registers I would just save their email in phone cache and automatically have them logged in next open. Also when the user logs in I would search for that user's information based on the email they entered into text field (assuming the password is correct). Is there any problems I might run into doing this, perhaps emails failing to match in the database? Also I am assuming I would also place an index on my email field in the database?

Also are there any security risks saving user email in phone cache? Is it possible for someone to spoof that (so they could use someone else's user info)?

Answer

If your communication channel is secure such as TLS(SSL), then the user entering

101
johnny101@gmail.com
Excalibur10X

plus a password during authentication are all equally fine from that aspect. The communication is secure, and unbeknownst to other parties and server hops. Note, I said if.

It assumes you have performed secure registration, your pages are secure. You maintain some session state Application Server thereafter. You follow best practices to avoid Session Hijacking. Meaning, after authentication, you don't fall back to non-secure pages and pass user information around in the clear or allow hijacking.

As for the database performance, saving natural keys or non-computer generated ids is fine. Performance will be slightly slower. Hopefully soon I can document that aspect on a tens of million db with wider keys versus, say, 4 byte ints. Anyone telling you it will be just as fast and don't worry about it will be using small databases or won't back it up with metrics. The proof is in complex joins, not trivial one row lookups.

Don't leak information. Don't allow for SQL Injection. Keep those front and foremost.