Should primary keys of MySQL tables be exposed?

Question

I have a number of MySQL tables describing models such as "User", "Business" etc. Should the primary keys of these tables ever be exposed to the client-side? I am asking from a security perspective, primarily, but are there also other considerations that I haven't thought of?

Answer 1

Exposing your primary keys (especially if they are predictable) is a vulnerability called Insecure Direct Object Reference.

By having a URL (or any other client provided param) like this:

http://www.domain.com/myaccount?userid=12

You give your end users the opportunity to mess with those variables and pass any data that they like. The counter measure to mitigate this vulnerability is to create indirect object references instead. This may sound like a big change, but it does not necessarily have to be. You don't have to go and rekey all your tables or anything, you can do it just by being clever with your data through the use of an indirect reference map.

Consider this: You have a user who is making a purchase on your site. And when it is time to pay they are presented with a drop down of the credit card numbers of theirs that you have "on file". If you look at the code for the drop down you see that the credit card numbers are associated with the keys 8055, 9044, and 10099.

The user might look at this and think that they look a lot like auto-incrementing primary keys (the user would probably be right). So he starts trying other keys to see if he can pay with someone else's card.

Now technically, you should have code on the server-side that ensures that the selected card is part of the user's account and that they can use it. This is a contrived example. For now we will assume that this is not the case or that this is another type of form that perhaps does not have that kind of server side control.

So how do we prevent the end user from choosing a key that should not be available to them?

Instead of showing them a direct reference to the record in the DB, give them an indirect reference.

Instead of putting the DB keys into the dropdown, we will create an array on the server and stuff it in the user's session.

Array cards = new Array(3);
cards[0] = 8055;
cards[1] = 9044;
cards[2] = 10099;

In the drop down we now provide the reference to the index of the array where the card is stored. So instead of seeing the actual keys, the end user will see the values 0, 1 and 2, if they view the source.

When the form is submitted one of those values will be passed along. Then we get the array out of the user's session and use the index to get the value. The actual key has never left the server.

And the user can pass in different values all-day-long if he wants, but he will never, ever, get a result other than his own cards, regardless of the server-side access control thats in place.

Keep in mind though that when using the passed-in index to get the value out that if the user does mess with it that you could get some exceptions (ArrayOutOfBounds, InvalidIndex, whatever). So wrap that stuff in a try/catch so you can suppress those errors and log the failures to look for cracking attempts.

Hope this helps.

To read more about Insecure Direct Object References, check out the OWASP Top 10. It is risk number A4. https://www.owasp.org/index.php/Top_10_2010-A4-Insecure_Direct_Object_References