Why I Like Using UUIDs on Database Tables

Be it for security or marketing your teams should probably be using them.

When working with relational DBs we usually assign auto-incrementing integer IDs to tables: the first row has ID 1, the second ID 2 and so on. Let me illustrate the problem with this by using a fake CRM application:

It’s the last day of the month and we want to find our client’s email address to send him a bill. We open the CRM, navigate through the UI, and click on his name. We are then redirected to his specific page on the system:

https://example.com/client/1 . Up until here, everything works as expected!

Looking at the URL it’s pretty easy to figure out what's our client's ID: 1. I’m a curious person, so I’ll change the number 1 to 2 and see what happens… The correct behavior would be for that client not be shown (with 403 Not Authorized or, ideally, with a HTTP 404 Not Found).

Unfortunately, our supposed CRM developers forgot to authenticate this specific route, so we’re able to see client 2’s data which is a huge security issue!

Vulnerabilities like this happen often since code-bases are pretty big, and it’s easy to forget 1 simple line of code. E.g. in Rails the correct line of code is currentUser.clients.find(id) while the vulnerable one is Client.find(id). Spot the difference? Pretty small…

Also, as my client’s ID is 1 I guess that this CRM is pretty small since I was the first one to ever create a client in it, so I might want to migrate to a more popular and reliable system... with this, the marketing team will groan at the developers for losing their hard-earned customer!

How does using UUIDs fix this?

Quick note: If you've never seen a UUID, it's a random string like 34e80691-9878-4e78-a1f2-eea8981984ef , so there's no number preceding or succeeding it; in contrast, with integers, we all know that the number 2 is preceded by 1 and succeeded by 3.

When we use UUIDs we change the URL's ID from sequential integers to a random string. So user 1’s page would look like https://example.com/client/34e80691-9878-4e78-a1f2-eea8981984ef instead of https://example.com/client/1.

There isn't a number that comes after (or before) that huge thing! It's a random and non-sequential string; so, even if the CRM developers forget to authenticate the route, it's much harder for someone to exploit that vulnerability: no one will know what to replace the UUID with! Also, no one knows how many users our platform has, so… Happy marketing team!

Does this mean we should change our primary keys from integer IDs to UUIDs?

I've heard some debate about this, but, in my opinion, no!

If we change the primary key to something other than integers we risk losing performance on queries since integers are faster to sort through than strings on SQL queries; instead, I create a separate column called UUID and use it on all user-exposed routes.

To know how to implement UUIDs on Rails, read my article.

I’ve also heard of some other benefits of using integer primary keys, such as accelerating the indexing process of RDBMSs; however, that goes beyond the scope of this article and of my knowledge on the internal workings of RDBMSs.

Quick FAQ

Q: If I’m using UUIDs on the URL which is, at some point, converted to a SQL query, how does maintaining the ID on the Database for indexing help at all?"

A: You make the first SQL query using the UUID and get the client's ID! Then you use the ID to do all other queries…

E.g. A user makes a request GET https://example.com/client/34e80691-9878-4e78-a1f2-eea8981984ef. Our system receives that request and does a SQL query for Client with UUID 34e80691-9878-4e78-a1f2-eea8981984ef and we get that his ID is 1. Now we use the primary-key for all other relational queries! For instance, we could use it to find all orders for that client.

Summing Up

It's good practice to use both IDs and UUIDs. You should use UUIDs for everything that goes out of your system (i.e HTTP requests) but keep using IDs for internal processing (SQL Queries).

Hope this was an insightful post, and if it was too confusing please let me know.

Thanks for reading this far!

Want to Connect?
If you ever have a great startup idea but not a software team, please, don’t hesitate to contact my company AlmostHackers! We’ll develop your software without charging money, getting equity instead.