Bad idea to have two unique IDs in database table?

Question

Hello all,

I wish to allow users to be able to view records from my database by following a URL.

I am guessing its not a good idea to have this sort of URL where the identifier of the record to be viewed is the record auto increment ID!

http://www.example.com/$db_record_id

The above is giving info away unnecessarily. Is this really true? Wouldn't creating my own ID for each row pose the same problem?

Is there a better way to solve my problem.

Environment: LAMP (PHP)

Thanks all

Answer 1

You give too little information for a really useful answer.

• How is the URL generated?
• How does the software find the records to display?

That said, I'd agree it's problematic to expose IDs like that. The commmon solution is to generate temporary IDs every time you query the database for a set of records, and store tempID->real ID internally. Then put these temporary IDs into the URL, and translate on the server.

Answer 2

You are giving out two pieces of info:

• easily predictable url's of records
• easy to find out how many records are in the set in total

Are these things a problem for your application, then use something else

You could use GUID's for the Db key, but that could have potential performance impacts in large datasets.

Answer 3

Putting the id of a record in the URL is generally not a problem. For example, look at the URL of this browser window and you will see the id of this question.

You can scramble or encrypt the value if you for some reason don't want the id to be clearly visible. This is of course not a safe method if you need to protect some records from being viewed, then you have to combine it with some kind of authentication, like the session id of a logged in user.

Edit:
For proper security one has to focus on the real problem. If certain pages should have restricted access, the problem is not that anyone can figure out the URL to the page, the problem is that anyone can view the page. An URL can always be obtained some way or the other (e.g. package sniffing), so security has to be implemented in some other way.

Answer 4

Maybe you should expose another unique identifier, such as a title instead of a primary key? Exposing your PK doesn't neccessarily pose a security risk, but you might get higher up on search results if you can expose another attribute.

Answer 5

One possible problem with that approach is that it is very easy for someone to retrieve all the pages/records by sequentially incrementing the ID in the URL. This might not be what you want, however you should not rely on using obscure URLs for security anyway.

Some other options would be:

• Generate a random hash on each row (although this will be a nasty meaningless url)
• Generate a 'slug' for each row (e.g. 'bad-idea-to-have-two-unique-ids-in'). You need to obviously make sure that this is unique, e.g. by adding something else to it.

Answer 6

If I were you, I'd use an Integer Primary Key (auto ident) on your table and also add a GUID with a unique constraint & index on it, defaulted to UUID()

Then rig your page up to take a GUID on the URL and use that to search your table.

Answer 7

It's not a good idea as it's already been said ( you're practically exposing every row in the table ) and I don't think it's necessary.

I think users will get to that page after a certain scenario ( selecting an element from a list or whatever ), so you'll have a page submit and a controller ( if you're trying to use an MVC architecture ) that will handle any processing needed and it will dispatch to a view page which could be just a www.yoursite.com/displayElement.htm

In this way the ID is just passed as a session variable and is not displayed anywhere.

But in case I got it wrong and you're displaying some kind of catalog, so you want users to be able to bookmark pages, than you just need to add some obfuscation mechanism that will create a complex string based on the ID, kind of encoding the ID before sending it to the URL and decoding it in code in order to obtain the actual ID.

Answer 8

Primary key in user request is not vulnerable itself. Just cast it into integer value and that's all

$id = (int)$id;.

The other question is about restricting some records to access. This trouble you can solve by simple edit of your query, adding there some rules for access. For example if you have users of type admin/editor/reader/guest you could save access rights in SET field and add to your query the condition

SELECT ... 
FROM mytable 
WHERE 
    id = (int)$id 
    AND 
    (FIND_IN_SET('admin', access) OR FIND_IN_SET('editor', access))

...
if (!$fetch = mysql_fetch_acssoc($res))
    throw new Exception('Record not found or you have no permission to access it');

So, don't worry abour primary keys - they are pretty safe if you don't forget to cast them into integer to avoid SQL-injections.

Answer 9

If a user can watch that page anyways, I see no problem with using the id. If they want to have fun changing the url and go to random pages, I would have no problem with that.

If the pages are not to be watched by anyone, I would actually still have no problem with it. Cause if that was the case, you should have some authorization check anyways which would prevent them from seeing the pages they are not allowed to see.

Answer 10

What you're describing is the vulnerability known as Direct Object Reference. These are not inherently bad, but Insecure Direct Object References are and are #4 in the OWASP Top Ten.

You can secure direct object references by checking the object belongs to the current user, if it doesn't then don't display it.

In addition even an access denied message might give away information. If your parameter was, for example, an order number, an attacker could discover how many orders your company actually has even if you don't display them by simply increasing the number until they get a 404.

Personally I just use GUIDs as a form of indirect object reference, but obviously not as a primary key in the database.

Answer 11

The threat level depends on scope and audience:

1. Is this an internal crud app? If so, you probably don't need to worry about it.
2. Is this publicly available information (like twitter posts)? Again, you probably don't need to worry about it. In fact, predictable URL's are probably ideal for linking.
3. Can your authenticated users modify any record or see something they're not supposed to? You can mitigate this risk by implementing an ownership system.
4. Can it cause a 1 click action or XSS vulnerability? If you can, you're doing it wrong.

In general I'm in favor of predictable URL's. That being said, it's important to think about how they can be used and who can see them.

Answer 12

If you want to make sure that people are not just guessing URL's, then here is a simple approach. This is in no way secure (ie. don't use this method for access checking), but it will make sure that you can't just input a random url and get sendt to that page.

Complement the ID with another piece of information in the url. I suggest a salted hash of the id.

When generating a url:

$id = 5;
$hash = md5($id . "MY_SALT_VALUE");
$url = "/$id/$hash";

And when displaying the record:

$params = explode(',', $_SERVER['REQUEST_URL']);
$id = $params[0];
$hash = $params[1];
if($hash != md5($id ."MY_SALT_VALUE")) {
    die('Uh oh! You guessed the URL, didn\'t you! Bad user!');
}
$data = $MyModel->load($id);
if(!$data) {
    die('No such record');
}
DisplayModel($data);

Of course, this is a mockup (saying too much in error messages, just die()ing instead of a proper 404 page, and so on and so forth).