Primary key = email address

[Selkirk]

Taking this in a direction that might be more helpful to feders, let me ask, what is the best length for the email field?

I don't think 50 is big enough. As I understand, all valid emails fit in 256 bytes. Anyone have a definitive answer on this one? Is it in characters or bytes?

Let me also ask what character encoding should he use? Are there people using unicode email addresses?

I realize this may be over designing a bit and a bit academic. Most people probably use a least common denominator email, rather than exotic but valid emails. However, I've run into bug reports at various times about people with valid email addresses that we're rejected due to one or another restriction that wasn't technically necessary.

What's the best definition of an email field in mysql? Lets get it on the record.

[felgall]

Taking this in a direction that might be more helpful to feders, let me ask, what is the best length for the email field?

The maximum length for an email address is 320 bytes. The local part before the @ can be up to 64 characters long and a domain part can be up to 255 characters long. Few would actually be that long though - most should be well under 255 characters.

[Ren]

The maximum length for an email address is 320 bytes. The local part before the @ can be up to 64 characters long and a domain part can be up to 255 characters long. Few would actually be that long though - most should be well under 255 characters.

The maximum usable email address length is 256.

SMTP (RFC 2821) has a restriction on the length of the email address with the MAIL & RCPT commands, and therefore anything over that isn't useful.

Believe this was corrected in an errata for RFC 3696.

[abalfazl]

http://en.wikipedia.org/wiki/Foreign_key

In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table

Well, to have a field referencing another table, you have to ... well, be referencing it first, and you do that by having a primary key in the first table already. (While you don't necessarily have to reference a record in another table via primary key, this is usually the case.)

But according to Wikipedia , It is necessary to use primary key as a reference.

Please make me correct if it is wrong.

[r937]

"The columns in the referencing table must be the primary key or other candidate key in the referenced table"

[abalfazl]

# Emails are hard to validate
# You have to validate for security purposes
# Character encoding issues on text field primary keys can be tricky

Why?May you give an example?

[r937]

email validation should be done by opt-in

the easiest way is to send an email to the email address, and if you get a reply, then it was a valid email address

[TomB]

I realize this may be over designing a bit and a bit academic. Most people probably use a least common denominator email, rather than exotic but valid emails. However, I've run into bug reports at various times about people with valid email addresses that we're rejected due to one or another restriction that wasn't technically necessary.

my email [...]@r.je often gets reported as invalid due to the domain length and over zealous validation scripts, even though it's perfectly valid.

[abalfazl]

Character encoding issues on text field primary keys can be tricky

How can Character encoding be tricky?

[Selkirk]

How can Character encoding be tricky?

In mysql if you create an index on a utf8 field, it has to reserve 3 times the number of characters in the field for processing that string.

In the context of email being a primary key, if you were to declare the email in utf8 and 256 characters, it would take up 768 bytes of the index buffers used during joining versus 4 bytes for a numberic id.

The default index buffer limit is 1024, so that makes it hard to form compound indexes on that field without raising the limit, especially if you are in the habit of using primary keys with meaning.

Also, using keys with meaning makes it more likely that eventually you will have to go unicode with them anyway.

Internationalizing email is a thing of it own. But, if you were to use username as a primary key and wanted international support, you might run into this issue. I have.

[Selkirk]

email validation should be done by opt-in

the easiest way is to send an email to the email address, and if you get a reply, then it was a valid email address

So where do you store the email and all the user's data while you're waiting for the opt in response? If its in your members table, then you have the potential of be storing XSS badness in your primary key prior to completion of the validation.

Correct validation of emails via regexps is notoriously tricky. Most systems I've worked on are too restrictive.

[r937]

if you were to declare the email in utf8 and 256 characters, it would take up 768 bytes of the index buffers used during joining versus 4 bytes for a numberic id.

that's a decent argument for not joining on email ...

... when joins are required

what about when there's only one table?

would you still want an index on email? (hint: yes, to pull out the row for that email)

would you still want a numeric primary key?? (hint: why?)

[Ren]

that's a decent argument for not joining on email ...

... when joins are required

what about when there's only one table?

would you still want an index on email? (hint: yes, to pull out the row for that email)

would you still want a numeric primary key?? (hint: why?)

I do remember reading an (theoretical?) article about a set of exploits that relied on something like email address* as a primary key.

They were based on the fact that email addresses can, and are transferred to someone else.

So whilst the email address is unique, it not necessarily the same person you are communicating with.

So the new owner of an email address should not be able to access private data of a previous owner.

There other requirements for the exploit to work, IIRC, things like data associated with email addresses not being deleted, either because it not has been done, or done incompletely (like assuming cascading deletes, when there isn't none.)

* Could also be user name.

[abalfazl]

So where do you store the email and all the user's data while you're waiting for the opt in response? If its in your members table, then you have the potential of be storing XSS badness in your primary key prior to completion of the validation.

Correct validation of emails via regexps is notoriously tricky. Most systems I've worked on are too restrictive.

Dear Selkirk

As I understand , You mean that save email as P.K is dangerous ,because of XSS threat ...

If you don't validate email correctly .Then it can be potential of risk, Even If you don't save it as P.K

Please explain how it makes more risk of security if email be as P.K,Why is it more dangerous than save as normal column ?

[felgall]

None of the security issues with email addresses have anything to do with its suitability or not to be used as the primary key.

If an email address were reallocated to someone else (as happened when I took over writing about JavaScript for About Inc when I was allocated the email address that used to belong to the person who wrote about that subject previously) it doesn't give any access to any accounts that were set up by the prior owner of the email address as the new owner still doesn't know what password the old owner had set up on the account or even that the account exists (unless they try to sign up for an account on the same site). Anyway if you were handing over an email address to someone else then you'd either close accounts, remove personal info, or change the email address for any existing accounts in advance of the handover.

[Ren]

None of the security issues with email addresses have anything to do with its suitability or not to be used as the primary key.

If an email address were reallocated to someone else (as happened when I took over writing about JavaScript for About Inc when I was allocated the email address that used to belong to the person who wrote about that subject previously) it doesn't give any access to any accounts that were set up by the prior owner of the email address as the new owner still doesn't know what password the old owner had set up on the account or even that the account exists (unless they try to sign up for an account on the same site). Anyway if you were handing over an email address to someone else then you'd either close accounts, remove personal info, or change the email address for any existing accounts in advance of the handover.

There are other situations where an attacker uses a CSRF exploit to delete an account, and then the attacker creates a new account with the victims email address. If the application is sloppy and left any residual data belonging to the original user, then the attacker would gain access to it.

This is not theoretical, it has happened.

So adding a AUTOINCREMENT, SERIAL, or GUID or some other column that isn't directly under control of an attacker, and using that as a primary key, adds a little more security to an application.

[Jake Arkinstall]

Ok then, how about this.

On many websites, you can have your account tied to multiple email addresses.

How would you go about doing that if your user table uses the email address as a PK? You're instantly limiting yourself. Sure, you could use the email address as the primary key and have a table of email addresses linked to that, but then the removal of that original email address will be a fairly fiddly process, and the maintaining distinct email addresses would be hard to accomplish if one account has it as its main account and another uses it as a child account.

I like the use of IDs as numbers for various reasons. For one, casting input IDs is simple and pretty much foolproof:

$id = (int)$_GET['id']; 

whereas some email addresses are infact loopholes to the general rules, so may not pass typical validation. Then there's the URI itself (used to identify the rows), which is ideally alphanumeric, which an email address certainly isn't.

Then the changing of an address can cause issues, though cascading etc should fix that.

Then there's not wanting to expose email addresses. An ID is, by definition, an identifier. What use is an identifier if you can't use it to identify a record?

Sure, it's possible. It's plausible, and some systems DO use it.

However, it takes more work, you need to take more into account when planning the system and it isn't foolproof.

In the end, I know I'D have an easier time using the a numerical ID.

[Selkirk]

Please explain how it makes more risk of security if email be as P.K,Why is it more dangerous than save as normal column ?

You still have to take precautions. email as a PK is no more of a risk than as a normal field. More annoying, maybe.

[Selkirk]

what about when there's only one table?

would you still want an index on email? (hint: yes, to pull out the row for that email)

would you still want a numeric primary key?? (hint: why?)

So that when you add table #2, you get the benefit of the slim foreign keys & fast joins without having to rewrite existing code.

[abalfazl]

Thanks for asnwer my questions Selkirk

In mysql if you create an index on a utf8 field, it has to reserve 3 times the number of characters in the field for processing that string.

Do you mean that if I save a string in utf-8 field in Mysql,The lenght of that string become *3?

Long primary keys also take up more space in your indexes. That can cause you to have to bump up buffer sizes to accommodate compound indexes.

May you explain more?

Sorry if my questions is basic!