Are there any problems with the column id from the database as user id

Question

I always include a column id with auto-increment in my databases. Is there a reason why I would not want to use this as a user identifier for a social networking site. The identifier will be known to the public and used in the url and what not. It is clear that it would be easier than adding another function to create a separate unique identifier for members. I just wanted to know if anyone had a problem with this before using it in their code.

Answer 1

The identifier itself will be a leak of information that will allow a third party to approach the date recorded on your site. So, if Alice is friends with Bob and knows that she registered last year, and he followed 3 days later, and her identification is 100, and his 150, she will know that Carol, who is not her friend, is registered on your the site then wasn’t “quite recently,” as Carol claimed, trying to find an excuse as to why she’s not “friends” with Alice on your site on social networks!

This is problem? You decide, but personally, I would rather be a little professional / paranoid (the two often go together, whatever that means to our profession!) And avoid including auto-increment identifiers in the URL, where there is even the slightest security / privacy. Or at least advise you to be :-)

If you decide to follow the path of Virtue, you may need to consider that other identifiers are also leaks of information (for example, Alice will know Carol before what she claimed if she finds out that her profile identifier is less than that of Bob), Thus, although it might seem that you can add, say, a GUID column, and use it as a secondary identifier that is safe for inclusion in URLs, you might be better off just switching from auto-increment identifiers to used e GUID. (More on GUIDs here: http://en.wikipedia.org/wiki/Globally_unique_identifier )

Hope that helps :-)

Answer 2

I also think that this is a canonical approach to the main problem.

It also makes the link to other sites with other user IDs trivial: just use a table with a user ID (as described by you), another site ID (from the foreign sites table), the user ID that will be used there.

The only drawback I have ever observed was that when some script kiddy used n, n + 1, n + 2, ... to check user IDs with vulnerable passwords. Although it gets harder with random identifiers, I personally think this should be considered elsewhere.

Answer 3

I think this is the absolutely correct approach. If you want to maintain unique identifiers for users (for example, if they want unique user_ids), you can always add them as a separate column to the Users table later.

Answer 4

No, there are no reasons why you do not want to use it as user_id.

If you set up some other column as id, you would be stuck with issues like:

• how to generate user id
• How to check if duplicate id exists in table

Also, looking up a table for a specific user will be much faster if you perform SELECT filtering using a primary key (which is also AUTO_INCREMENT).