Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2007
    Location
    London
    Posts
    117

    Auto Increment field as Primary Key?

    Hello all,

    I am currently using an Auto Increment field as my primary key. I want know if its ok to have an auto increment field for a primary key for a table or should i create my own id for secuity purposes?

    Thanks all

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's okay to use an auto_increment primary key, as long as the table also has a unique constraint

    you could, in many situations, use a natural key as the primary key instead

    creating your own surrogate key is best left to a last resort

    by the way, what kind of security purposes were you thinking of?

    take, for example, a url like this -- http://example.com/foo.html?userid=42

    are you worried that someone might see what they get if they put in a different number?

    do not rely on your choice of PK scheme to prevent this type of insecurity

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by r937
    by the way, what kind of security purposes were you thinking of?
    The security issues i am worried about are slightly "childish" actually since i've read about a lot of sites that have had their db hacked or stolen. Recently, monster - the recruitmet site had their db hacked and they emailed me to tell me that. That was very nice of them! So i am worried about somebody stealing or worse corrupting my data.

    Quote Originally Posted by r937
    are you worried that someone might see what they get if they put in a different number?
    I am not worried about this since if this is done on my site then all they will get is just a new set of data. Harmless i think. It just means they will be reading the wrong thing in the wrong section.

    Is it ok, if i publicly gave my db name or table names? Could anyhing be done with this sort of info?

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    what kind of database are you using?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    117
    Quote Originally Posted by loquin
    what kind of database are you using?
    MySQL - and i am using PHP to access it and make changes.

  6. #6
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    Auto incrementing is done internally. It shouldn't affect security.

    I think the biggest worry you need to look at is sql injection.

    I don't give out my db name. Unless you have a reason to then I wouldn't bother. The only reason to have it is to connect to it. Not giving it out isn't going to secure it of course If it accepts incoming connections over the network then that is part of the info they need to get in. The most important part is the password though.

    If you published your table names it would be nice for any malicious person, but if they get that far in.. they can find them out for themselves and do a little: drop tablename

    The other things would be:
    1) protect your backups. backups are an easy way to get the data. (I think some of the stories about data theft are due to people getting backups or files that had information dumped into them like csv)

    2) protect the machine that is running your db. If someone gets root/admin privileges on the machine they are going to get in a lot easier.

    3) of course put some good passwords on your db and don't give them out.

    4) since you are using PHP the connection info shouldn't be flying across the internet for some reason.. so you don't have to worry about someone sniffing out connection info.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    amthomas, any thoughts on what this thread is actually about: is auto_increment okay as the primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    May 2005
    Location
    San Antonio, Texas
    Posts
    134
    Yes, my first sentence in my previous post and seemed a simple answer (although I don't work with mysql). I don't see why using auto incrementing features would be a problem. It solves a lot of problems for me. I use (in postgres) serial as a surrogate key on almost all my tables.

    And I believe the partial nature of his post was a concern about security and not just the auto increment feature. People ask questions sometimes in round about ways so I expounded on the idea. But if all that other info was not helpful compsci, please ignore it.

Posting Permissions

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