How to avoid incremental ID security gap in mysql

Question

What is the best practice both security and performance focused to avoid letting users see incrementally IDed data in database or other dataset.

Main concern is to avoid urls such as

www.myweb.com/user/123

This of course applies to posts, users, files or messages.

It that really is a problem then Stack Overflow has to change the URLs of the users ;) — juergen d, Jul 09 '15 at 19:12
A properly built system wouldn't care that the user's hacking urls. they'd enforce security at the server on every request to confirm that the user is allowed to see the url. e.g. user 124 tries to hit `.../123`, they should get "not authorized" instead of seeing 123's stuff. — Marc B, Jul 09 '15 at 19:12
It's not a security problem as long as the resource behind the URL is properly secured (e.g. by a login) — Pekka, Jul 09 '15 at 19:13
As mentioned already not really a security concern, but it might be business concern. I suppose a competitor could reverse engineer how fast your business is growing by tracking your ids. — dpmcmlxxvi, Jul 09 '15 at 20:10
@dpmcmlxvi true, business or social aspect is more troubling than security when it comes to this — John Doe, Jul 09 '15 at 20:13
possible duplicate of [Using Primary Key / ID Field as an identifier in a URL](http://stackoverflow.com/questions/566996/using-primary-key-id-field-as-an-identifier-in-a-url) — Neil McGuigan, Jul 10 '15 at 18:44
@neilmcguigan Thank you, some good points have been made in that thread as well — John Doe, Jul 11 '15 at 15:19

score 2 · Accepted Answer · answered Jul 09 '15 at 19:15

2

Implement permissions so that only authorised people can see/change/delete data.

If you still want to hide the incremental ID from users or API consumers, you could add a hash column to your database and index it, then expose that instead of the incremental ID.

answered Jul 09 '15 at 19:15

jweyrich

31,198
5
66
97

We have had a discussion with @kittykittybangbang about this solution previously in another thread. I was proposing this solution and it turned out to be an overkill in majority of cases. I still would go with your solution out of habit though. – John Doe Jul 09 '15 at 20:11

score 0 · Answer 2 · edited May 23 '17 at 11:43

Hiding Incremental IDs

Depending on your programming language, the unique ID you assign your users may not need to be displayed in the URL. For example, with PHP, you can use the $_SESSION[] array to store values on your server for each user. Those variables will never be seen by the user, but the server will be able to identify each user appropriately (via PHP cookies) and serve them the correct page dynamically.

For example, when a user signs in to your site, after authenticating, your script might do something like:

$sql = 'SELECT id FROM user_table WHERE name = :username';

// Prepare & execute SQL query, putting result in $sqlResult

$_SESSION['user_id'] = $sqlResult;

Now, whenever the user wants to visit their own page, your server will know which information to fill your home page template with -- and the URL will appear the same to every user.

If a user wants to visit another user's page, you could do something similar: upon choosing a specific user page to visit, your script could set a $_SESSION['visit_user'] variable. Thus, you would be able to fill a visit page template with the appropriate information, and your user will be none the wiser.

This same tactic can be applied to posts, files, etc. that are assigned incremental IDs.

But Is This Necessary?

As you yourself mentioned in your previous post, there are plenty of examples of sites that use incremental IDs -- and with no qualms about displaying them. Because while this does give a malicious user the ability to view other users' IDs, etc., this doesn't necessarily pose a threat to your site's security. If you follow basic security principles (require strong passwords, watch your MySQL users' and files' permissions, sanitize user input, etc.), it doesn't matter if malicious users can guess at auto_incremented IDs. Those IDs aren't valuable information unless your site can be exploited in another manner.

I do agree, this could be more of a social issue rather than security issue. Say you have an eShop and ID incrementally your invoices. Its not a security issue that users know how many purchases there have been so far, but you may want to hide this statistic. In fact I know a major local eShop that work like this. — John Doe, Jul 09 '15 at 20:09
That's a point I hadn't considered: that incremental IDs might let on about your company's stats. Something like that may be best kept within `$_SESSION[]` variables. Even still, though, those incremental IDs can't be relied upon to display stats accurately: how many IDs were used as tests in development, or were deleted/undone/modified and act as false positives? — kittykittybangbang, Jul 09 '15 at 20:19
Now that I give it more thought, I dont see a pattern, some use incremental IDs (FB, SO) some do not (Youtube, Vine, Twitter, 9gag). Trying to see the reason why. — John Doe, Jul 09 '15 at 20:32
Twitter, for example, requires a unique handle, thus ensuring that your user page URL will be unique if that handle is used. SO requires unique user names -- but a single user can have multiple posts, so another means of generating a unique URL is necessary. — kittykittybangbang, Jul 09 '15 at 20:45

How to avoid incremental ID security gap in mysql

2 Answers2

Hiding Incremental IDs

But Is This Necessary?