DEV Community

Marcin Piczkowski
Marcin Piczkowski

Posted on

What's your experience with text IDs in SQL database?

I'm thinking of usage of text IDs in SQL database, similar to how Stripe generates them, e.g.: ch_19iRv22eZvKYlo2CAxkjuHxZ.

I found this StackOverflow very helpful.

I could use the function to generate such IDs with some entropy provided by pgcrypto extension function gen_random_bytes, but still, I'm hesitant.

Especially, after reading this comment:

No, it absolutely is not safe to use in any way shape or form which is why I'm for closing this question. If you want something that's safe to use, use UUID. If you want to play around with something likely to burn you severely and leave you crying. A solution which requires you create your own function that is worse in every way than the stock feature set to do this, then have it at. =)

I would not like to reach a point later on production when the IDs start to duplicate.

Therefore, I'm more biased to use UUIDs, which on the other hand are not very user-friendly, e.g. when they are used in URLs, reports, etc. I personally like the way Stripe does it.

What's your experience? Did anyone do sth similar to Stripe? Did you face any problems?

Top comments (9)

Collapse
 
rolfstreefkerk profile image
Rolf Streefkerk

For distributed databases such as DynamoDB (NoSQL) it makes sense to use UUIDs for ID's. For a typical SQL database, is there a reason you would want to use this instead of integer ID's?

Collapse
 
piczmar_0 profile image
Marcin Piczkowski

One reason is that I don't want to expose some information in the API, e.g. by using auto-incremented integer IDs in product orders I let outside world to know how many orders my system processes. I know I could translate the int IDs to API IDs, maybe this is better approach?

Collapse
 
rolfstreefkerk profile image
Rolf Streefkerk

you can still have the database auto increment ID's, you just need to index on another ID that may be a composite identifier based on who buys it for instance (customer details, customer id), the actual order number, and some prefix based on the date of the order.

There are all kinds of ways to mask the actual number of orders and still use auto increment id's in your database

Thread Thread
 
piczmar_0 profile image
Marcin Piczkowski

Thanks for the explanation.

Order numbers is an example that came to my head, but imagine that someone wants to exploit my security mechanism and sees url somedomain/api/images/123, then comes with an idea that image IDs may be auto-incremented and I had a security issue that image access is not protected for only owners of an image. Someone tries somedomain/images/124 and gets an image of the other user. These mistakes happen, with text ID it's harder to guess..

You also wrote that for distributed DBs it makes sense to use UUIDs. We can have the same case when some day my data grows to the amount that I want to create shards, then having incremented IDs I would have to remap them somehow based on shard ID to avoid same IDs on different shards.

I agree that for most cases incremented IDs work fine, but my question was not about pros and cons of using text IDs vs numeric IDs but rather how to properly implement such text IDs.

Thread Thread
 
rolfstreefkerk profile image
Rolf Streefkerk • Edited

You're mixing two different things here. One is database identifier the other is role based security and general security.
If you need to protect image from access, security by obscurity is not the answer. So the fact they can guess the ID's is not the problem, it's your security mechanism that is the issue that needs to be resolved.

How to implement them? UUIDs can be generated. There's a very minuscule chance of collision, also dependent on the implementation you use. So that would be a first to research, a good library for v4 UUID's

Thread Thread
 
piczmar_0 profile image
Marcin Piczkowski

I understand the difference between security and DB IDs, but again, would you agree with me on the statement that it's easier to uncover the security issue with auto-incremented IDs?

I know how to generate UUIDs but again, the question was about other mechanisms to generate text IDs than UUIDs.

Anyway, I got the answer from others in the comments so thanks for your time spent on helping me.

Thread Thread
 
rolfstreefkerk profile image
Rolf Streefkerk

No, auto incremented IDs or not does not have any bearing on security whatsoever. As I've already mentioned, obscurity does not equal security. Just because you choose a random identifier does not make it suddenly more secure.
Your statement would only be correct if it is indeed obscurity that was the only mechanism of protection.

I would say if there's a business need for certain identifier to exist, that should be the primary reason next to any technical requirement that may arise due to sharding/distributed data storage.

Collapse
 
jonathanarnault profile image
Jonathan ARNAULT

You should have a look at Snowflake ID generator, it generates time based 64 bits timestamp.

Collapse
 
davehrtng profile image
David Harting

One way to use auto-incrementing integers and easily mask them from the user is with hashids.org/