Should i obscure database primary keys (id's) in application front end

Question

I'm working on an application which allows a moderator to edit information of user. So, at the moment, i have URL's like

http://xxx.xxx/user/1/edit
http://xxx.xxx/user/2/edit

I'm a bit worried here, as i'm directly exposing the users table primary key (id) from database. I simply take the id from the URL's (eg: 1 and 2 from above URL's), query the database with the ID and get user information (of course, i sanitize the input i.e ID from URL).

Please note that:

I'm validating every request to check if moderator has access to edit that user

This is what i'm doing. Is this safe? If not, how should i be doing it?

I can think of one alternative i.e. have a separate column for users table with 25 character key and use the keys in URL's and query database with those keys

But,

• What difference does it make? (Since key is exposed now)
• Querying by primary key yields result faster than other columns

Visible

Of course it's not safe. You could always just base64encode/decode on submit – Exuberant

Answer 1

This is safe (and seems to be the best way to do it) as long as the validation of the admin rights is correct and you have prevention for SQL injection. Both of which you mention so I'd say you're good.

Godforsaken

Answer 2

The basic question is if exposing primary key is safe or not. I would say that in most situations it is safe and I believe that Stackoverflow is doing it in the same way:

http://stackoverflow.com/users/1/
http://stackoverflow.com/users/2/
http://stackoverflow.com/users/3/

If you check the member for you can see that the time is decreasing, so the number is probably PK as well.

Anyway, obscuring PK can be useful in situation where you want a common user to avoid going through all entries just by typing 1, 2, 3 etc. to URL, in that case obscuring PK for something like 535672571d2b4 is useful.

Margerymarget

Answer 3

If you are really unsure, you could also use XOR with a nice(big) fixed value. This way you would not expose your ids. When applying the same "secret number" again with the xor'ed field, you get the original value.

$YOUR_ID xor $THE_SECRET_NUMBER = $OUTPUTTED_VALUE

$PUTPUTTED_VALUE xor $THE_SECRET_NUMBER = $YOUR_ID

Slugabed

Answer 4

Fast answer no

Long answer

You have a primary key to identify some one with, which is unique. If you add an unique key to prevent people from knowing it, you get that they know an other key. Which still needs to be unique and have an index (for fast search), sound a lot like a primary key.

If it is a matter of nice url's well then you could use an username or something like that.

But it would be security to obscurity. So beter prevent SQL injection and validate that people have access to the right actions

Tepee

Answer 5

If you have plain autoincrement ids you will expose your data to the world. It is not sequre (e.g. for bruteforcing all available data in your tables). But you can generate ids of your DB entities not sequentially, but in pseudo random manner. E.g. in PostgreSQL:

CREATE TABLE t1 (
    id bigint NOT NULL DEFAULT (((nextval('id_seq'::regclass) * 678223072849::bigint) 
    % (1000000000)::bigint) + 460999999999::bigint),
    ...
    <other fileds here>
)

Beseem

I don't understand the "bruteforcing" thing. If for example those user profiles are accessible on the website then obscuring PK for something like 535672571d2b4 would make no sense, because those data can be collected easily by writing simple HTML crawler. In case that those user profiles are not accessible on the website then none except authorized users is able to display them. – Margerymarget

Generally not all available through API things have public access hyperlinks. Thus crawler will not help. But if you generate your ids (for example users) sequentially it is possible to iterate through all your users without using any crawler. You just need to do simple GET request with monotonically increasing ID to obtain list of all existing users. Even if you have not page with users list on your site. – Beseem