4

Hello Internet Denizens,

I was reading through a nice database design article and the final determination on how to properly generate DB primary keys was ...

So, in reality, the right solution is probably: use UUIDs for keys, and don’t ever expose them. The external/internal thing is probably best left to things like friendly-url treatments, and then (as Medium does) with a hashed value tacked on the end.

That is, use UUIDs for internal purposes like db joins, but use a friendly-url for external purposes (like a REST API).

My question is ... how do you make uniquely identifiable (and friendly) keys for external purposes?

I've used several APIs: Stripe, QuickBooks, Amazon, etc. and it seems like they use straight up sequential IDs for things like customers, report IDs, etc for retrieving information. It makes me wonder if exposing UUIDs as a security risk is a little overblown b/c in theory you should be able to append a where clause to your queries.

SELECT * FROM products where UUID = <supplied uuid> AND owner/role/group/etc = <logged in user>

The follow-up question is: If you expose a primary key, how do people efficiently restrict access to that resource in a database environment? Assign an owner to a db row?

Interested in the design responses.

Potential Relevant Posts for Further Reading:

Should I use UUIDs for resources in my public API?

Will Lovett
  • 1,241
  • 3
  • 18
  • 35

1 Answers1

2

It is not a good idea to expose your internal ids to the outside. You should either encode them (with some algorithm) or have a look up table.

Also, do not append parameters provided by user (or URL) to your SQL query (UUIDS or not), this is prone to SQL injection. Use parameterized SQL queries for that.

albattran
  • 1,887
  • 1
  • 12
  • 16