Should MySQL table primary keys be displayed?

Question

I have several MySQL tables describing such models as "User", "Business", etc. Should the primary keys of these tables be client side affected? I ask from a security point of view, first of all, but are there any other considerations that I have not thought about?

Answer 1

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

Having a URL (or any other parameter provided by the client), for example:

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

You give your end users the opportunity to contact these variables and transfer any data they like. A control measure to mitigate this vulnerability is to instead refer to indirect object references. This may seem like a big change, but it is not necessary. You do not need to go and redo all your tables or anything else, you can do this just by knowing your data using an indirect reference card.

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

The user may look at this and think that they are very similar to automatic incremental primary keys (the user will probably be right). So he begins to try other keys to see if he can pay with another card.

Now, technically, you should have a server-side code that ensures that the selected card is part of the user account and that they can use it. This is a contrived example. For now, we assume that this is not the case or that it is a different type of form that may not have such 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 link to a record in the database, give them an indirect link.

Instead of inserting database keys into the drop-down list, we will create an array on the server and write it to the user session.

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

In the drop-down list, we will now provide a link to the index of the array in which the map is stored. Therefore, instead of seeing the actual keys, the end user will see the values ​​0, 1, and 2 if they look at the source.

When the form is submitted, one of these values ​​will be submitted together. Then we get the array from the user session and use the index to get the value. The actual key never left the server.

And the user can transmit different values ​​throughout the day if he wants, but he will never get a result different from his own cards, no matter what access control on the server side is in place.

Keep in mind that when using the passed index to get the value, if the user is not working with it, you may get some exceptions (ArrayOutOfBounds, InvalidIndex, whatever). So, wrap this stuff in try / catch so that you can suppress these errors and report crashes to look for hack attempts.

Hope this helps.

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

Answer 2

In general, it’s normal to send any data to the browser. But do not forget:

Any data transmitted to the client and transferred to the server may be compromised in any way . Do not trust the data returned by the client!

Unfortunately, if you publish the keys - in some way - the user changes it, and you cannot correctly verify the key, if it is sent back from the client to the server, harmful things can happen.

Therefore, you must write very security code for the client-server-post / receive keys. In fact, you should not trust any data sent from the client to the server.

My question may also be of interest.

As stated in my question, my last application never publishes data for the client. More generally, even those things that are usually used as parameters in GET / POST parameters are never written to the client .

All keys or attributes associated with the entity that control the flow of the application are strictly server-side.

What I suggested as an alternative to D) in my question :

• Any request received by the server is valid by default , since all active links are generated at the time of rendering the page , and not at the time of the request.
• The user cannot redirect links using e-mail or publish them, since they exist only when his session is active.
• The user can request only those links inside the web page , since only these links are registered on the server side.
• All state-related data is stored on the server side and therefore cannot be changed by the client.

Answer 3

Enter the UUID column along with primary auto-increment keys in the table. Open the UUID in clients and use the auto-increment keys for connections and backend processes.

Answer 4

You can use hashing if you are looking for a different level of security, if your users do not mind the confusing URL. Something like below in PHP.

 $id = 1234; $url = 'http://domain.com?id='. $id .'&v='. sha1('SALT1'. $id. 'SALT2'); 

Server-side data can be double checked, as shown below.

 if ( $_GET['v'] == sha1('SALT1'. $_GET['id']. 'SALT2') ){ //run further checks here } 

Answer 5

It is generally considered a bad idea to disclose auto-increment keys. Primary domain keys are wonderful.

Answer 6

It would not be so much harm to expose it as a whole. People will be able to guess which entries were created later by having a higher identifier.

But you will need all the necessary security. If users change their identifier, are you already checking to see if they are allowed to view this data? If you have another method for finding data, it will probably be difficult to guess the correct values. But you must make sure that they are different, and the data you use may also be valid.