SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 45 of 45
  1. #26
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  2. #27
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Selkirk View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #28
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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.

  4. #29
    PEACE WILL WIN abalfazl's Avatar
    Join Date
    Feb 2005
    Location
    Beyond the seas there is a town
    Posts
    711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    I shall build a boat,I shall cast it in the water,
    I shall sail away from this strange earth,
    Where no one awaken the heroes in the wood of love

  5. #30
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "The columns in the referencing table must be the primary key or other candidate key in the referenced table"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #31
    PEACE WILL WIN abalfazl's Avatar
    Join Date
    Feb 2005
    Location
    Beyond the seas there is a town
    Posts
    711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    # 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?
    I shall build a boat,I shall cast it in the water,
    I shall sail away from this strange earth,
    Where no one awaken the heroes in the wood of love

  7. #32
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #33
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    996
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Selkirk
    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.

  9. #34
    PEACE WILL WIN abalfazl's Avatar
    Join Date
    Feb 2005
    Location
    Beyond the seas there is a town
    Posts
    711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Character encoding issues on text field primary keys can be tricky
    How can Character encoding be tricky?
    I shall build a boat,I shall cast it in the water,
    I shall sail away from this strange earth,
    Where no one awaken the heroes in the wood of love

  10. #35
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by abalfazl View Post
    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.

  11. #36
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  12. #37
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Selkirk View Post
    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?)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #38
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  14. #39
    PEACE WILL WIN abalfazl's Avatar
    Join Date
    Feb 2005
    Location
    Beyond the seas there is a town
    Posts
    711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ?
    I shall build a boat,I shall cast it in the water,
    I shall sail away from this strange earth,
    Where no one awaken the heroes in the wood of love

  15. #40
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,875
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  16. #41
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    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.

  17. #42
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
    $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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  18. #43
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by abalfazl View Post
    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.

  19. #44
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.

  20. #45
    PEACE WILL WIN abalfazl's Avatar
    Join Date
    Feb 2005
    Location
    Beyond the seas there is a town
    Posts
    711
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    I shall build a boat,I shall cast it in the water,
    I shall sail away from this strange earth,
    Where no one awaken the heroes in the wood of love


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •