4

I always include a column ID with an auto increment feature in my databases. Is there any reason why I wouldn't want to use that as the users ID for a social networking site. The ID would be known to public and used in URL's and what not. Figured that would be easier than adding another function to create a separate unique ID for members. Just wanted to see if anyone else found any problems with this before I use it in my code.

kdjernigan
  • 309
  • 2
  • 5
  • 14

4 Answers4

2

I also think, that this is the canonical approach to the basic problem.

It also makes linking to other sites with other user IDs trivial: Just have a table with user-id (as described by you), other site id (from a foreign sites table), user-id to use there.

The only disadvantage I ever observed was, when some script kiddy used n, n+1, n+2, ... to test for user IDs with vulnerable passwords. While this is made harder by random IDs, I personally think, that this should be dealt with elsewhere.

Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • I'm confused to what your saying. What do you mean linking to other sites with other user ID's? And I understand about the n, n+1, n+2 example, but what do you mean it should be dealt with elsewhere? In my script? And what would you recommend if this is the case? – kdjernigan Nov 27 '12 at 00:03
  • 1
    Concerning the other sites: You are talking about a social networking site - this makes it likely, that in the future you want to link your users to their identities on **other** social networks. I think this should be designed-in quite early, hence my approach. – Eugen Rieck Nov 27 '12 at 00:05
  • 1
    Concerning the n, n+1, ... case: It is a disadvantage of AUTO_INCREMENT user IDs, that from one known user ID you can trivially find out other potentially valid user IDs - much easier than with random IDs. But I am of the opinion, that this should not be held against the numbering scheme. Monitoring for brute-force attempts should be completely separate from that IMHO – Eugen Rieck Nov 27 '12 at 00:09
  • Well our login system will be based on email address and we plan on adding a system that will temp block an email address after so many failed attempts, and temp block an IP after so many failed attempts. Is that along the lines of what you are referring to? – kdjernigan Nov 27 '12 at 00:13
  • 1
    Exactly. This is what I ment with "must be dealt with elsewhere". Be carefull with blocking IPs though - in a world full of NAT and even one or the other proxysaurus left, this might hurt - at least here (in AT) all mobile traffice goes via NAT. – Eugen Rieck Nov 27 '12 at 00:17
2

The id itself would leak information that would allow a third party to approximate the date he or she registered in your site. So if Alice is friends with Bob and knows that she registered last year and he followed 3 days later and that her id is 100 and his is 150, she will know that Carol, who is not her friend, registered in your site back then and not 'just recently' as Carol claimed, trying to find an excuse as to why she is not 'friends' with Alice in your social media site!

Is this a problem? You will decide for yourself, but personally I would prefer to be a bit professional/paranoid (these two often go together, whatever that means for our profession!) and avoid including auto-increment ids in the URL where there is even the slightest security / privacy concern. Or at least, advise you to be :-)

If you do decide to take the path of Virtue, you may need to consider that other ids also leak information (e.g. Alice will know Carol earlier than what she claimed if she finds out her profile id is a smaller number than Bob's). So, while it may seem that you could add, say a GUID column, and use it as a secondary id, one that would be safe to include in URLs, you might be better off just switching from autoincrement ids to using GUIDs. (More on GUIDs here: http://en.wikipedia.org/wiki/Globally_unique_identifier)

Hope that helps :-)

Miltos Kokkonidis
  • 3,888
  • 20
  • 14
  • I really enjoyed your story. It reminded me of math problems back in the day. But fair enough on your reasoning. Thanks for your help – kdjernigan Nov 27 '12 at 00:21
  • 1
    This is definitly a technically valid point. While I do not consider it a show-stopper (on a social networking site the "member since" date will most likely show up anyway), it should still be mentioned. – Eugen Rieck Nov 27 '12 at 00:22
  • @kdjernigan: Thanks! I am glad you enjoyed the story. I had fun coming up with it too :-) – Miltos Kokkonidis Nov 27 '12 at 00:40
1

I think that's a perfectly valid approach. If you wanted to support unique IDs for users (say, for instance, if they wanted unique user_ids), you could always add it as a separate column in your Users table later.

jflores
  • 483
  • 6
  • 18
1

No, there is no reason why you would not want to use it as user_id.

If you setup some other column to be id you would be stuck in problems like:

  • how to generate user id
  • how to check is there possible duplicate id in table

Also, lookup in table for particular user will be much faster if you are executing SELECT filtering by Primary key (which is also AUTO_INCREMENT).

Nemanja Boric
  • 21,627
  • 6
  • 67
  • 91