Stop using Integer ID’s in your Database

Summary

The website content advises against using integer IDs in databases due to security and data management risks, recommending the use of UUIDs instead.

Abstract

The article emphasizes the risks associated with using integer IDs as primary keys in databases, a common practice for over 30 years. It highlights the difficulties encountered when merging databases with overlapping IDs and the potential security breaches when exposing data through APIs. The author illustrates how easy it is for a hacker to access another user's data by incrementing an integer ID in an API call, potentially exposing sensitive information. The solution proposed is to use UUIDs, which are more secure and eliminate issues with ID collisions during database merges. The author also addresses the outdated concern of performance degradation when using strings like UUIDs, stating that modern databases are optimized for such operations. The article concludes by stressing the importance of proper authorization and authentication measures to protect user data and prevent breaches.

Opinions

The author strongly advises against using integer IDs due to the potential for ID collisions when merging databases and the security risks associated with sequential IDs.
Merging databases with overlapping integer IDs is seen as a problematic task that can lead to complex renumbering and data integrity issues.
Exposing integer IDs through APIs is considered a significant security flaw, as it allows unauthorized access to other users' data by simply altering the ID in the API call URL.
The author advocates for the use of UUIDs as a more secure and practical alternative to integer IDs, as they mitigate the risks of collisions and unauthorized data access.
The concern over performance penalties when using UUIDs (strings) as keys is dismissed, with the author noting that modern databases handle hashed keys efficiently.
The article underscores the necessity for robust authorization mechanisms to ensure that only authorized users can access their respective data, even if the API is compromised.
The author encourages readers to take action to secure their databases and APIs to prevent data breaches, citing examples of major companies that have suffered from similar vulnerabilities.

Stop using Integer ID’s in your Database

Bad Integer ID Schema

I’ve seen this over and over for the last 30 years, people let the database set the ID or Primary Key of a table from the database, at first glance this sounds simple and everyone knows you should let the database do the heavy lifting, with a numeric “Sequence” number you need to let the database do the work since there may be multiple applications or threads creating new records in the table. DON”T DO IT!

First, if and when you need to merge two databases that now have the same Primary Key ID values for the same table, your screwed. You have to come up with a scheme to change the ID’s, maybe adding 10,000 to each ID, what if you have more than 10,000 rows? The you also have to update all children records, maybe not that easy if you have constraints defined in the database.

Second, if you expose your data as an API then sooner or later that will happen, you have just opened up your system to what hackers love to see. Let me explain:

I can login and get access to my information, maybe my purchases as follows:

Simple right? I get the purchases for user ID 123, but what if you make another call:

Now you are getting the purchases for user 100, that’s not who you are, you have just allowed an external hacker to get access to your database. Lets say this is something personal like a Chia Pet, now the hacker knows user ID 100 has purchased a Chia Pet. Another call to an API for user ID 100 you can see that the user is Elon Musk. Do we really want the world to know that Elon Musk has a fetish for Chia Pet’s? You have just exposed your secure data and you will loose the trust of your customers, but it could be worse, the API could also expose Birth Days, Addresses, Social Security Numbers, maybe even Credit Card Number (If your not following PCI rules).

Now lets take a look at using a UUID for the call.

Much more secure since adding a number here or there probably would not access any information.

So, the moral of the story is DON’T USE INTEGERS for Primary KEY ID’s EVER!

Note that using a UUID for the Key which means you are now using a String is a huge move forward, you can now merge two databases without any issues.

In the old days (10 years ago) Database were slow and people were told not to use Strings for keys because of performance, now every database uses hashed keys to some extent so there is no longer a performance concern.

You still have the over all security issue of Authorization, it would be assumed that you system has Authentication solved so you can know who has logged into the system. You also need to make sure only the Authorized users can access their resources, this means that when someone tries to access a user or purchases and those do not belong to them they receive and error.

This needs to be taken seriously, there has been so many hacks using this simple technique, some of the biggest US Retailers exposed customer data in this way. Even some of the largest security password keeper had the same issues and were caught with their pants down. Don’t let the next victim be your company. Fix it today!

Good luck, feel free to connect and follow me.

Follow me please

I’ve lived in California all my life. I grew up in Silicon Valley and have seen so many changes.

Please feel free to follow me on Medium and YouTube, I have classes on Udemy and just started a new Series called “Fast and Simple Development” where you can learn some new technologies and skills that are needed to help upskill yourself in today’s job market or simply continue to make the move up the ladder by adding to your technical chops.