SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 45
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Primary key = email address

    I have created a MySQL file whose primary key is an email address (since it is unique). However, when I try to select a matching record it does not like the @ sign in it. I am not sure how to tackle this. Do I modify the code to use (at) in place of the @ sign? Do I make a 2 part primary key, where username is part one and URL is part 2 and I never have to enter the @ sign at all? Or, do I rewrite my code (somehow) to get around the @ sign problem?
    Snippets of code:
    ===================== the SQL table
    CREATE TABLE userTable
    ( emailAddress varchar(50) primary key,
    personName varchar(30) not null,
    passWord varchar(30) not null,
    cdate DATETIME);
    ================
    it pulls this emailID from post method from previous form
    $emailid= trim($_POST['emailID']);

    $sql = "SELECT * FROM $dbtableName where emailAddress = $emailid";
    It complains about this SQL statement
    I would appreciate any help on this!
    SandyFeder.us

  2. #2
    SitePoint Evangelist
    Join Date
    Mar 2006
    Location
    Sweden
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your SQL should be:
    $sql = "SELECT * FROM $dbtableName where emailAddress = '". mysql_real_escape_string($emailid) ."'";

    Or even better, use PDO and prepared statements.

  3. #3
    SitePοint Troll disgracian's Avatar
    Join Date
    Aug 2006
    Location
    Samsara
    Posts
    451
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is the specific error message?

    This example is atrociously vulnerable to SQL injection also, but we'll get to that in good time.

    Cheers,
    D.

  4. #4
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by wysiwyg View Post
    Your SQL should be:
    $sql = "SELECT * FROM $dbtableName where emailAddress = '". mysql_real_escape_string($emailid) ."'";

    Or even better, use PDO and prepared statements.
    Thank you so much! That's exactly what I needed.
    Sandy
    SandyFeder.us

  5. #5
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would reconsider using e-mail for primary key. E-mail is mutable data and this can cause problems later on.

  6. #6
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by dbevfat View Post
    I would reconsider using e-mail for primary key. E-mail is mutable data and this can cause problems later on.
    My thoughts exactly, upon seeing this topic.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    what "problems" can be caused by a mutable PK that cannot be solved with an UPDATE statement?

    this eagerness to use auto_increments everywhere causes as many if not more problems

    what is one of the most frequent questions posted on database forums?

    "HALP!!!1! i need to remove all but one of the duplicates in my table!!!"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd agree. email as primary key is problematic on many levels.
    • People change their emails
    • You shouldn't expose their emails, such as in a profile url.
    • Emails are hard to validate
    • You have to validate for security purposes
    • Character encoding issues on text field primary keys can be tricky
    • Large primary keys (>4 bytes) result in slower joins

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    People change their emails

    that's not problematic, that is a reality that must be acknowledged

    luckily, SQL acknowledges that data sometimes changes, and provides the UPDATE statement for this purpose

    and changing emails does not require the use of an autonumber




    You shouldn't expose their emails, such as in a profile url.

    of course you shouldn't

    i agree, don't show emails

    but not showing them does not require the use of an autonumber




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

    use an opt-in process which requires an email reply

    and validating emails does not require the use of an autonumber



    Large primary keys (>4 bytes) result in slower joins

    myth

    besides, even if you use an autonumber, you ~still~ have to have an index on email

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

  10. #10
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not "eagerness to use auto incremental everywhere", it's "using the right tool for the job". You don't even have to use an autoincremental value, you can generate an arbitrary value, as long as it isn't mutable, and is fixed-length. Natural keys can be useful, but not in all cases. In the end it depends on your application, but it's usually not good.

    First, you don't want to update your primary key - it's supposed to be permanent in such a way that the same value always refers to the same entity. Also, when a user modifies his/her e-mail, you have to update all the references to the user in other tables, which means a lot of updates and table locks. Think updating a table that records user events throughout account life-time (purchases, messages, etc).

    To stay with references (and as Selkirk already pointed out), you need to use e-mail when (if) you need to reference a user from elsewhere. Sending a message to a user exposes the e-mail. Viewing a profile also. These issues can be overcome with generating a unique reference value for each user and send messages to that value rather than to an e-mail address. But then why not just use that as your primary key?

    Another drawback is that if your application becomes large, data size may also be a problem - every reference will be storing significantly more data than needed, compared to an INT. Indices are less effective with human values, so their sizes must be increased, resulting in larger (and slower) indices, compared to INT.

    Also, joins on larger fields in fact are slower, it's not a myth. At least on MySQL. VARCHARs are the slowest to join, and that's what you're using for the e-mail field. See this, this, and other posts on their blog.

    Last but not least, you definitely don't want to change the data type of your primary key. With mutable data in general, this can happen - like changing varchar[50] to varchar[60] for a few users with really long e-mails. Not only that means altering the structure of your userTable, it also means altering any table that refers to it.

    There's not much more to be said. If you think all these possible issues won't happen in your application, then I guess you've selected the correct primary key.

    regards

  11. #11
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    988
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    iirc you should be able to set an "ON UPDATE CASCADE" to automatically update all references to the PK providing you've set up your foreign keys correctly, (though i've never tried it myself) but I still wouldn't use mutable data for the primary key.

    using username for primary key may be worthwhile however, depending on the situation. For example, if most queries on the field are coming from exposed data (e.g. GET), it's nicer to show the username than an ID number.

  12. #12
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    An email address is not something that would change frequently enough to cause any problems. It is probably a much better primary key than most alternative values and definitely better than an arbitrary one.
    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="^$">

  13. #13
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    Regarding the updates ... You think all your references are going to just be in your DB? Integrate with external systems much?

    You need an opaque identifier for members for the times where you are passing that ID outside of your control (The profile url problem.) Why not just use that as your primary key? (as dbevfat wisely points out.)

    If you don't validate the email before creating the record, do you really want a bunch of XSS stuff in your primary key?

    Regarding the join performance. That's a fact. I've personally benchmarked it out on real data in a production system.

    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.

    I have direct experience with using email as primary key on a members table. I inherited a system that does this. I would definitely never do it on a new system.

  14. #14
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Using a single field to store two types of information - a record identifier and a record attribute (email address) - surely breaks one of the normal forms? (Don't ask me which one exactly).

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by McGruff View Post
    Using a single field to store two types of information - a record identifier and a record attribute (email address) - surely breaks one of the normal forms? (Don't ask me which one exactly).
    you're thinking of first normal form, in which every column must hold atomic values, but that's not the case here

    using email as the primary key does not violate any normal form

    see how pervasive this idea of a "record identifier" (a.k.a. numeric surrogate key) is -- people think you need one or your table isn't "normalized"

    if anyone is interested, here is a nice 3-part article which refutes the idea that mutability automatically rules out the possibility of being a PK -- Primary Keyvil, Part I
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Selkirk View Post
    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.
    do not be fooled into thinking you can just declare a numeric surrogate key to avoid this issue

    you must ~also~ declare a unique index on the "real" key, or else risk duplicate data

    what is one of the most frequent questions posted on database forums?

    "HALP!!!1! i need to remove all but one of the duplicates in my table!!!"


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

  17. #17
    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
    you must ~also~ declare a unique index on the "real" key, or else risk duplicate data
    But if have email address as the primary key, you must also have indexes on email address columns in other tables.

    Thereby exploding you index size, relative to the number of related tables. And also increasing the cost of updating the email address, even if the RDBMS & table type supports cascading updates.

    If people are having trouble with duplicate rows, that's because they haven't declared unique constraints, not because they haven't declared it as a primary key.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Ren View Post
    But if have email address as the primary key, you must also have indexes on email address columns in other tables.
    if you would kindly do a quick review of this thread, you might notice that there aren't any other tables





    Quote Originally Posted by Ren View Post
    If people are having trouble with duplicate rows, that's because they haven't declared unique constraints, not because they haven't declared it as a primary key.
    that's what i said, a couple of times

    you need to have a unique constraint in addition to the surrogate key, thus rendering arguments about the length of the column being indexed irrelevant

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

  19. #19
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Ren View Post
    But if have email address as the primary key, you must also have indexes on email address columns in other tables.
    Why must you have them? Just because it is a primary key doesn't mean that the foreing keys pointing to it in the other tables need to have indexes.

    That there are fields in other tables that reference it is a good reason for making it the primary key.
    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="^$">

  20. #20
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    Why must you have them? Just because it is a primary key doesn't mean that the foreing keys pointing to it in the other tables need to have indexes.
    Because you want to query other tables by this value, obviously. "messages" table has to be queried for incoming messages with "to" field, your outbox must be queried with "from" etc. Also, you can't make it a true foreign key without a constraint, which requires an index.

    That there are fields in other tables that reference it is a good reason for making it the primary key.
    This logic is circular and doesn't favor e-mails for PK. Same can be said for any primary key, because by definition you refer records by their primary key.
    Last edited by dbevfat; Oct 9, 2009 at 13:06. Reason: added something

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dbevfat View Post
    Because you want to query other tables by this value, obviously.
    which other tables?

    have another look, there is only one table mentioned in this thread
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    which other tables?

    have another look, there is only one table mentioned in this thread
    feders didn't say anything about it being the only table either, so you can't really say. If you'd have to guess, you'd consider the probability: how many projects are ever done with a single table vs. how many are there with many tables?

    Besides, the conversation has long ago departed discussing this particular case, we're talking about e-mail as PK in general. I think this is fairly obvious. And after all, I've clearly said that in the end it depends. There definitely are cases where e-mail makes a good PK, but with lack of more information, I can't say that for this case. And since feders is asking a pretty basic question (and having unsafe code, and using a varchar(30) for password), I felt safe to assume that he isn't really think about pros and cons of using an e-mail for PK -- he probably even isn't (wasn't?) aware that it can be a hard decision that will affect much of the code developed further on.

  23. #23
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by dbevfat View Post
    This logic is circular and doesn't favor e-mails for PK. Same can be said for any primary key, because by definition you refer records by their primary key.
    You are looking at the table accesses backwards. You start from the tables with the foreign key and from there do the lookup of the matching record by its primary key.

    If you are going the other way then the primary key of the first table would be a part of the primary key of the other tables and not a foreign key.
    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="^$">

  24. #24
    SitePoint Guru dbevfat's Avatar
    Join Date
    Dec 2004
    Location
    ljubljana, slovenia
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by felgall View Post
    You are looking at the table accesses backwards. You start from the tables with the foreign key and from there do the lookup of the matching record by its primary key.

    If you are going the other way then the primary key of the first table would be a part of the primary key of the other tables and not a foreign key.
    I think you've misunderstood me, because I'm pretty confident I know what a foreign key is and how it's used . I was referring to your comment in which you said (as I understand) that the fact you're referencing a record from other tables, makes that reference column a candidate for a primary key in the first 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.)

  25. #25
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,789
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by dbevfat View Post
    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.)
    So you agree with me then.
    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="^$">


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
  •