Prevent Business Intelligence Leaks by Using UUIDs Instead of Database IDs on URLs and in APIs

Peter Locke
Jun 26, 2017 · 6 min read

Let’s examine an innocuous looking URL that you may see when you are looking at your order history on some e-commerce site:

https://www.yourfavsite.com/account/orders?orderid=5963

Speaking strictly from an application security perspective, there is no problem here. As long you’ve done your job on the back end checking that the order’s user matches the logged in user, there is no security risk. You can’t look at other users’ orders, and they can’t look at yours.

The problem is that this one URL gives a surprising amount of insight into the underlying data, and therefore the business. Let’s see how.

Leak #1: Overall Business Data Size

Leak #2: Overall Business Data Velocity

https://www.yourfavsite.com/account/orders?orderid=5963

https://www.yourfavsite.com/account/orders?orderid=5983

This shows me that the business is doing roughly 20 orders per hour. Again, very sensitive competitive information no business wants so easily determinable.

Why This is So Common

Relational databases and relational data still power the vast majority of the transactional web and will continue to do so for the foreseeable future. Auto-incrementing numeric sequences as primary keys are a very standard, often default pattern for a relational database table. It’s easy to see why: the database does it for you and lookups are lightening fast. The first row gets an id of ‘1’, the second an id of ‘2’, and so on.

Every online business generally starts with a single relational database at the core of its architecture. Even as they grow and potentially introduce distributed components and NoSQL datastores and caches to meet load demands, it is likely that this core e-commerce data (orders, items, users) will remain relational. The data is relational and the data architecture itself using numeric sequences and relations is not wrong.

Image for post
Image for post
A common e-commerce data architecture. ‘id’ is a generated, auto-inrementing sequence

So what to do? Luckily there is a good solution that doesn’t require data re-architecture or moving away from auto-incrementing sequences.

The Solution: UUIDs to the Rescue

Before change, a excerpt of data from our example data architecture could be as follows. There will naturally be many other columns in these tables (they have been omitted for diagram readability).

Image for post
Image for post
A snippet of relational data. ‘id’ always increments per row

The column we want to add (to any table that will be addressed via the client side) is a UUID.

A UUID is a universally unique alphanumeric identifier that says nothing about the underlying data. While any long unguessable alphanumeric string will work, UUIDs are a great choice as their generation is natively supported in most modern programming languages and even in databases themselves. This makes populating the column very easy and architecturally flexible as it can be done in code, or right in the database automatically on new inserts, depending on your needs.

Image for post
Image for post
Our same relational data but now we have UUID on Order and User

This example data illustrates that a UUID is not needed on every table, only those that are referenced from the client side (we are assuming here that OrderItems are shown in context with an order and not individually). It is completely fine (and more efficient) for the back end to continue to use the numeric primary/foreign key relationship to get order item information for an order, or look up details for an order item directly, as those ids are never shown to the user.

Update your Client Side Driven Lookups

Here is how our two order history URLs look now, giving away nothing:

https://www.yourfavsite.com/account/orders?orderuuid=f137804d-77d6-4f03-b808-834833aa96f2https://www.yourfavsite.com/account/orders?orderuuid=3c3734de-3c38-4744-95ea-de64e52e37bd

Note: it is often necessary to have the back end continue to support old ‘id’ based URL patterns as well, as we’ll see in the next section: links in the wild.

Remembering Links in the Wild — User Reports and Emails

Dealing With Your API

  1. Add ‘UUID’ alongside every place the ‘id’ is exposed in the request / response with a minor version release. Adding things to an API should be a non breaking change.
  2. Communicate with your API users about the deprecation of ‘id’ and the timelines for the breaking change. Encourage them to start using ‘UUID’.
  3. Add as much logging as you can to determine the use of the offending endpoints (especially if ‘id’ can be a request parameter)
  4. Remove ‘id’ through an appropriate breaking change update (such as a major version update)
  5. (Finally) end of life the version containing ‘id’ as per your deprecation plan communications.

Final Thoughts

Don’t be lulled into a false sense of security if your sequences are not auto-incrementing sequentially or if your data is partitioned. While the risk is lessened, as long as there is any predictable pattern in your exposed ids, some information is being leaked and the pattern can be interpreted.

If you find some, don’t panic; assess the business risk internally, determine the size and scope of the change, and prioritize. Happy building!

Lightrail

The branded currency platform for customer growth.

Peter Locke

Written by

Cofounder and CTO @giftbit / @lightrailhq — Builder of business focussed engineering teams and technology strategies.

Lightrail

Lightrail

The branded currency platform for customer growth.

Peter Locke

Written by

Cofounder and CTO @giftbit / @lightrailhq — Builder of business focussed engineering teams and technology strategies.

Lightrail

Lightrail

The branded currency platform for customer growth.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store