You mentioned leaking how many orders a business has, but funnily enough invoices do this too. At least in the Netherlands, you need to sequentially number your invoices, and have a very good reason to have a different system
@@DanMaarDit no, they need to be numbers, in an increasing sequence without gaps. So 1,2,3,4,etc. It's generally fine to do something like prepend the year, since that has a clear reason to start different series, but then you'd have to go 2024001, and count up from there
@@fallingseasy As he mentions, many countries REQUIRE sequential numbering. As such, all i need to do is make an order and bam, i know what i wanted to know. Any business that thinks that invoice # is a competitive secret isn't a business, it's a fly by night scam op.
How do GUIDs affect indexing? Are they index friendly? How do they work when there are a 100 or 200 million records in the table? Another alternative I have used successfully is ULIDs instead of UUIDs (they are both orderable and can be index without any performance impact).
Since they're just large integers, they're quite friendly. Using UUIDv4 is random, which can certainly hurt insert performance, while UUIDv1 and UUIDv7 are time-monotonic, and merging two databases using UUIDv7 for example will keep time-ordering, while having enough uniqueness that a collision would be effectively impossible. UUIDv4 might be harder to insert but makes sense if you really don't want to expose the generation time. Time as a source of monotonicity and uniqueness to prevent collisions both prevents guessing and counting the number of orders while keeping a monotonic counter. UUIDv7 is effectively the same as ULID, with all the same benefits. It's 48 bits of unix time, 6 bits of versioning for compatibility, and 76 bits of randomness for uniqueness. The only difference with ULID are those six bits, which makes it universally compatible with UUID systems, whereas ULID is technically incompatible, although both are 128-bit integers.
I don't know why nobody said this already, but you can easily keep using IDs and searching for IDs by simply embedding said ID into it. There are many ways to obscure numbers to the point they're unusable. You could even add additional data to said number that is used to reverse the operation and do cool stuff like randomly generate that per user to track your users and how they share links to things.
@@DanniDuck that still runs into the original problem of distributed systems, though. you would need an id authority database to manage a distributed system which would be a good bit of extra latency to everything
"obviously today everyone should have row level security" feels like the 2005 version of "obviously today everyone should be using parameterized queries" and yet we still see so many injection attacks :(
As a pentester, if data does not check for authorization if I know the ID, I will report it just like incremental IDs. I will mention that it is way harder to guess but using UUIDs should not be used as defense against IDOR. I am not a DB software developer but incremental integer primary IDs should be way faster to fetch by logarithmic search so if the DB implemented it, then it is a huge advantage for big tables over UUIDs where they need to check every single row for matches. Thinking about it, it might even be faster than binary search if there are few deleted rows. Cryptographic conversion for - numbers that you mentioned - are a good solution in the middle.
@@reed6514insecure direct object reference; API expose direct access to internal database unintentionally. User A can read user B data by just guessing user B’s Identifiers.
Loving your content Gui. You sure make a case for your submissions. In my estimates, they are irrefutable. Thank you very much for your charitable contributions. You sure know your Onions.
Hi. Actual backend engineer here. While it is OK to use UUIDs as IDs, it is not optimal for performance. We need to specify two things here: Primary keys and other means of ID. Integer Primary keys works and if handled properly will not generate as much collision as you might expect. Hopefully you segment your database and do not use the same database for different regions. That would be silly. Most of the time an ID is created and not altered. That means that collision only happens theoretically if you hard code the ID, which is also silly. Most of the times Primary keys are often used to set up relationships. Often in cross tables. Having cross tables in UUID would be affecting performance. However! For ID purposes that is not Primary keys, UUID is excellent. As a primary key method? Horrible. Again. There are ways of solving cross DB communication, but then you are often not using primary keys to solve your problem..
Allow me to introduce you to uuidv7. It's an ordered uuid, so it's index/primary key friendly. Like incremented IDs, it doesn't have a segmenting issue. The only negative is a uuid is just a larger data structure than ints, so it takes up a bit more space. If you're gonna use int IDs AND uuids anyways though, you're still at a net positive by only using uuids
@Deadmanstrolln Thank you! I will look that up. I am a bit skeptical about UUIDS as primary keys. If you are worried that (as our friend here) data is going to leak by showing IDs, imagine having both sequential and universally unique data leak.. I am also very fond of keeping the data structure tight since a rapidly growing database needs all the space it can get. Especially if you think about memory consumption. A request can differ a lot if it is compounding due to data structure size. I'd rather let the smallest primitive handle the primary key and try to obfuscate universally identifiable data points as much as possible.
COMB GUIDs (like uuidv7 mentioned above) are sequential in that they contain the timestamp when it was generated, not sequential like integers shown in the video. This means they can be generated in advance then stored in the database, so it still solves the queuing/delay problem mentioned in the video. "Leak"-wise, a timestamp that an order was created would generally be less important of a secret than how many orders or customers you have in your database (could be application-dependent of course). You do need to be careful about how a COMB is generated, as different databases sort GUIDs in different ways. The placement of where the random and timestamp bits are in a COMB intended to be stored in an MS SQL Server database vs a PostgreSQL database are different, because the two databases sort GUIDs differently. There will be libraries available for this for your programming language if you look, you need not roll it yourself (in C# we use the RT.Comb nuget package). By the way, this isn't something new, there are articles online about this technique and how it addresses the performance issues of regular UUIDs that you mentioned going back at least as far as 2002.
@reikooters While I partially agree, I would from a business standpoint be more worried by a timestamp and an out of bounds-attack that describes the size of an invoice table than just a leak of integers and an out of bounds attack. A simple integer doesn't say anything about when it is created. If anything, I'd secure my API to not allow such behavior as well as trying to validate data before accepting it in an API. For database performance though, I'd stick to integer sorting and then try to form Metadata that is unique. That way searching would be more specific and harder to crack since it relies on more things than an integer. If possible, I'd like the primary key and identifiable parameters only be a concern for the backend. If you really need to use it in the front-end, it should be in a protected context or a special case.
@@cfossto Everything has its pros and cons. For me if we have a new system and we bring on a new customer we haven't dealt with before, I don't necessarily want them to know they're customer #3 on the system since an integer is understandable to any person whether they're technical or not. But I didn't quite understand what you meant by how you could get the size of a table from a timestamp?
There's some incorrect conclusions here and some of these problems are bigger than the id. Don't overthink it; a good old fashion autoid likely solves your problem.
YAGNI In most of the use cases - it just doesn't matter. And migrating to GUIDs or other text-based things hurts performance, so there is nothing for free.
I've also applied indirect object mapping, where you put fake IDs in your session data (which is a dictionary, perhaps) and map them to the actual ID of the object, so that when you receive a request from that session, you use the fake id to find the actual id. This does mean that this information will probably persist beyond a single page, and it does mix poorly with the URL parameter strategy, but if you mix that with row level security and you're using 3rd party auth providers (MS, Google, etc.) it's helpful to avoid exposing the ID obtained from the auth API, while still providing a way to easily manipulate user records safely and easily
Tbh addind this type of complexity at something that is backend secured checking for permissions makes no sense atleast for big projects, whats the adding cost of hashing the IDs if you have it already secure checks
It didn't occur to me to use a prefix before the guid, great idea. You mentioned that everyone should use row level security, could you do a video on it? Thanks!
Building URLs that meet the expectations of an object-capability system is definitely challenging. The W3C has some basic guidance, but use the git repo, not the published documentation which has well-known errors. Neil Madden has some great articles on including credentials in URLs, which is probably the best starting point.
This video is one big pile of bullshit. You are free to use ints, it was done for years. Using guids also have it's own problems. Choose tool for the job.
It is partially bollocks. GUIDs add obscurity which can help if the security of the app isn't that good. For any customer links, you can generate unique ids that after linking back to the site will be queried and converted back to int for rest of the API calls. Switching to different database that cannot support integer row ids is also quite moot point as you really shouldn't be using nosql for relational data (which you obviously currently have as you are using SQL). Invoice numbering leaking is true, but also happens due to law in many places where invoice numbers must be sequential. And nothing prevents incrementing the row id randomly every now and then or to be rounded up to next million every month or something. Distributing data across multiple locations with integer IDs is also trivial. There is also performance benefits from using integers, both for the database and for the shorter URLs in API calls. Also testing for permissions is easier as you can just test if sequential IDs apply permissions properly or not. And sharing links with nice intergers looks better than an url with lots of random letters in the end. And there are enduser benefits, like being able to just enter invoice id that customer reads via phone rather than searching for long GUID somewhere.
I don't think this video is properly research. For example, regarding the business risk, Shopify, a billion dollar e-commerce, uses and exposes sequential order numbers. I personally think id's should be concealed, but definitely not for the reasons stated in this video.
You did a great job describing *reasons* to use a different record id generation scheme, rather than just saying "you should and if you don't then you're dumb LOL". All the reasons you described are valid. The bits about reformatting the uuid is also excellent. I usually do something like user: or user. (eg you see AWS uses arn:accountid:region:xxxxxxxxxxx) but the underscore makes sense too.
Specifically regarding the API front, as much as I dislike some implementations of it thr OData standard is actually quite nice, provided it's implemented properly and adheres to all spec docs
You saw honest Duane's video and jumped on the clickbait. At least you made a better argument and were less inflammatory about it. I still disagree that uuids provide "security", but i do think int ids make it easier/faster to run brute force attacks.
GUID are very inefficient thought, sure they can be indexed but from a db point of view they are strings, very inefficient. Using GUID also removes the ability to infer the sequence in which records have been inserted into a table which could make debugging extremely hard. As mentioned in the video it is a design decision but in my opinions GUID are not a good solution and do not scale well in terms of performance. I also don't buy the "security" risk or at least not in the vast majority of cases.
simple createdAt/updatedAt columns take care of the sequencing issues. when it comes to "security", depends on the context. a lame backend developer and you getting access to, let's say, banking data, or some court case.. a simple lowly guid will make that access nigh on impossible. and in the vast majority of cases, you'll be fine with some performance tradeoff.
Uuidv7 or ulid. Both are ordered uuids. They take up more space than an int ID, but otherwise work functionally the same. They're ordered so you can still infer the insert sequence, and they don't have the segmenting issue non ordered uuids have
With int ids, it will be easier/faster to run brute force attacks on existing records, but I'm with you, in that this isn't necessarily security, so much as obscurity. And UUIDs can be stored as binary, at least in mysql we have bin_to_uuid() & uuid_to_bin() for this purpose, which brings us to a 16bit column, i believe.
Exposing primary keys, especially in the sql world, is a terrible idea period. At some point someone needs to change the value of that key field (either due to business or technical reasons) for some row and then you're in trouble.
If you have duplicate order id's, then something is fundamentally wrong with your system. For merging say a category identified by an int ID, then you would do the merger first and remove the duplicate on success. Having simple id's for an order is much simpler for staff to work with, and so many advantages. For merging different databases you can use composite keys, or use an int64 and use a different range for each db. This can be implemented upon need. Should you really need it in the future, it is easy to add an additional guid field in the future. One advantage of a guid is for offline usage, which again having an int as the primary is advantageous, because easy to see once added, and the guid can be used to resolve state on retry if an api call returns a non deterministic resp9nse.
@@michaelkhalsa there are always problems with systems, and you keep creating new problems with new features. Of course an answer is "just dont create bugs" but that's not really satisfying. These things occur in production and if you have exposed your surrogate keys and external parties, like integration partners using your API, have started to depend on those, the problem has spread outside your control. This leakage could have been easily avoided by only exposing identifiers meaningful to the business and not surrogates, which are an implementation detail. So you'd just get rid of the duplicates by fixing the data and removing the duplicated rows.
@@michaelkhalsa by data migration I don't mean implementing a completely green field system with brand new specs. Of course then you can adjust your schema as needed. I meant more like introducing a new tenant with a ton of existing data and dependencies into your existing multitenant saas system. Having to rely on a specific kind of surrogate identifiers locks your business to a specific technology. So the best you can do to mitigate this on your side is - as you mentioned - to expose only secondary key fields or other indexed fields that can have whatever values the client needs to have there as identifiers.
The example with the orders seems kind of stupid to me. If anyone can fetch data through the API, sure, then you should use an external reference instead such as UUID. You could also use a separate key to secure the data. If you have some unique key that is required along with the id, then that works just as well as UUID. If only select people can fetch data through the API, then you should have authorization as well. Does each customer have their own token? Then limit access to their own orders and slap them with a 403 instead of 404 even if the order does not exist or if it isn't theirs. This would as such not expose the next order number either since they can only fetch their own orders. If anyone with access to the API can fetch data in the API... then you're just making things difficult for no good reason, because they should be able to access the data one way or another regardless. I also want to point out that some countries have laws that force you to have incremental order numbers. So in those countries it would not make any difference since you can just buy something once a year and see, just like in your example. So it is a very uneducated take.
Thanks for the Sqids tip! Never seen that before and it's really valuable. I also like that it can encode tuples. This could come in handy in many scenarios. Ones that comes to mind is in CosmosDB where you need to know a partition key. If an ID code be encoded to include both the ID and the Partition Key, that could help very much in some cases.
I mostly agree, but i think one legitimate risk is that int ids are easier and faster to brute force attack. If that brute force is successful, the problem still lies in messed up permissions/authentication, but it would be harder to automatically request 10,000 UUID based resources than 10,000 primary int id resources.
title: "do NOT .." video: "actually it is fine... " for people watching these videos, keep in mind these youtubers have very little software experience (less than 10 years, no published articles or books, no senior position at reputable companies, no academic experience, no graduate degree, etc.) they think all projects need to follow same types of performance or security as the single one they worked on, or they are knowingly using clickbait.. either way, good reasons to click "don't recommend channel"
Sharding autogenerated row ids is really simple, just change the autoincrement to some bigger number, like 4, and then put US to start from 1 million and EU from 1 million +1, they will then gladly continue and never conflict.
I didn't know you could change the auto increment size. I suppose it may depend on db. I'll have to check mysql docs. (But also this isn't a feature i need rn)
@@reed6514 @reed6514 long story short, they used sequential numbers as IDs for everything from posts to user profiles and everything in between and had no authentication or rate limiting on their backend, allowing hackers to write a simple script to scrape all of their data in minutes by guessing the enumerated values of all publicly available data shortly after a thing happened that significantly raised public interest in their app. Total amateur hour app security. As a bonus, they also failed to scrub the geolocation metadata from uploaded photos and videos, revealing the personal addresses and details of many of their users.
I like the GUID approach and I use in all my projects? Generally I put a route constraint like this [Route("{id:guid}")] In the case you mention, you removed the dashes and add a prefix, right? then I receive a string, when the id pass to the controller, how do you convert to a guid? Do you add a middleware? great video btw, cheers from Brazil, hu3
I would generally just recommend using a string ID, and then you can generate that ID however you want at any time, allowing you to scale and change the method as the system requires. Simplest case is to just stringify an int, but can be changed to snowflages or universal ids later on.
Not using the id in the API will just hide the issue for most people. Most of the time your front-end will contain the ids anyway in the markup to identify records. So anybody familiar with dev tools will easily be able to get them. I think the more correct solution would be to not use identity ids. Or have some sort of translation layer to convert back and forth between the sequential ids in the database with non-sequential numbers that you can freely use in your application code, which would obfuscate that hidden information while still letting you take advantage of basically free inserts using a clustered index on your identity column. EDIT: I finished the video and you talked about this exact solution at the end. While true it's not cryptographic, it would still be extremely difficult to figure out the math to get out the original ids. Strong enough for most businesses I'd say. Just maybe not the DOD, lol
Great video! Sqid is a good middle ground between using incremental ids and guuids in a single database application. You don't have to modify the database as you can hash the id when responding to a request, then later decode that id when the client makes a request with it. While it's not cryptographically secure, the hash is salted so it's not trivial to obtain and row level access should be a bigger priority in the first place. It's probably best practice to be in the habit of using guuids from the get go.
Your video have so much problems do more research you just gave a one correct reason other than that it is not even a reason, for most of distrbuted system it uses read replica and the performance of uuid or guid uuid and guid is not the same but both have performance issue and ordering issue
For an API key, I'd go with a cryptographic randomly generated string, then use a cryptographically secure hashing algorithm for storage, in case your DB gets leaked. In PHP, bcrypt is used for password hashing, and PHP manages salts for you & the php docs explicitly recommend not to provide your own salt.
Interesting idea. I suppose you could just simplify it by using the unix timestamp + some other unique info for basic data. It would still be an INT that increases over time while being random enough to avoid enumeration attacks
@@philadams9254 Yes you can, I've been using multiple variations of snowflakes over the time. I.e. for one project I worked on I had a 2 bit identifier for whether it's the productive system, test system or development system. Then I also always reduce the timestamp by a fixed amount (like discord does) and often change the bit width of the counter to how ever many requests I expect the application to ever get (often no more than 8 bit) which gives me significantly more space for the time to prevent issues like y2038 and similar
DOH no extra security at all .. Additionally, the time a snowflake was created can be calculated from the snowflake. This can be used to get snowflakes (and their associated objects) that were created before or after a particular date.[2]
@@colemichae Yes, the snowflake ID is sortable by the time they were created, and the timestamp can be computed. In many scenarios, the creation datetime is publicly displayed (e.g., blog post creation time, many open APIs showing their data creation time). If you want to conceal the ID, you can hash it using SQIDs (formerly Hashids).
You mentioned leaking how many orders a business has, but funnily enough invoices do this too. At least in the Netherlands, you need to sequentially number your invoices, and have a very good reason to have a different system
Now I wonder if an ulid or ordered uuid would be okay since you can order them in a sequence.
isnt keeping your invoice count private, a good enough reason?
@@DanMaarDit no, they need to be numbers, in an increasing sequence without gaps. So 1,2,3,4,etc. It's generally fine to do something like prepend the year, since that has a clear reason to start different series, but then you'd have to go 2024001, and count up from there
@@fallingseasy As he mentions, many countries REQUIRE sequential numbering. As such, all i need to do is make an order and bam, i know what i wanted to know. Any business that thinks that invoice # is a competitive secret isn't a business, it's a fly by night scam op.
The record id does not need to be the same as the invoice number.
How do GUIDs affect indexing? Are they index friendly? How do they work when there are a 100 or 200 million records in the table? Another alternative I have used successfully is ULIDs instead of UUIDs (they are both orderable and can be index without any performance impact).
Since they're just large integers, they're quite friendly. Using UUIDv4 is random, which can certainly hurt insert performance, while UUIDv1 and UUIDv7 are time-monotonic, and merging two databases using UUIDv7 for example will keep time-ordering, while having enough uniqueness that a collision would be effectively impossible. UUIDv4 might be harder to insert but makes sense if you really don't want to expose the generation time. Time as a source of monotonicity and uniqueness to prevent collisions both prevents guessing and counting the number of orders while keeping a monotonic counter.
UUIDv7 is effectively the same as ULID, with all the same benefits. It's 48 bits of unix time, 6 bits of versioning for compatibility, and 76 bits of randomness for uniqueness. The only difference with ULID are those six bits, which makes it universally compatible with UUID systems, whereas ULID is technically incompatible, although both are 128-bit integers.
I don't know why nobody said this already, but you can easily keep using IDs and searching for IDs by simply embedding said ID into it. There are many ways to obscure numbers to the point they're unusable. You could even add additional data to said number that is used to reverse the operation and do cool stuff like randomly generate that per user to track your users and how they share links to things.
@@DanniDuck that still runs into the original problem of distributed systems, though. you would need an id authority database to manage a distributed system which would be a good bit of extra latency to everything
It hurts the performance, you can read about GUID and clustered indexes and fragmentation
I very like an alternative solution that's being called Snowflake. Twitter/X and Discord using them as their IDs. It's also easy and fast to generate.
"obviously today everyone should have row level security" feels like the 2005 version of "obviously today everyone should be using parameterized queries" and yet we still see so many injection attacks :(
As a pentester, if data does not check for authorization if I know the ID, I will report it just like incremental IDs. I will mention that it is way harder to guess but using UUIDs should not be used as defense against IDOR.
I am not a DB software developer but incremental integer primary IDs should be way faster to fetch by logarithmic search so if the DB implemented it, then it is a huge advantage for big tables over UUIDs where they need to check every single row for matches. Thinking about it, it might even be faster than binary search if there are few deleted rows.
Cryptographic conversion for - numbers that you mentioned - are a good solution in the middle.
What's IDOR?
@@reed6514 insecure direct object reference, basically when you can do unauthorized stuff by knowing the ID
@@reed6514insecure direct object reference; API expose direct access to internal database unintentionally. User A can read user B data by just guessing user B’s Identifiers.
@reed6514 google search result said Insecure direct object references
thank google for making me answer the same answer that other already answer, it for some reason hide other people comments
Loving your content Gui. You sure make a case for your submissions. In my estimates, they are irrefutable. Thank you very much for your charitable contributions. You sure know your Onions.
Hi. Actual backend engineer here. While it is OK to use UUIDs as IDs, it is not optimal for performance. We need to specify two things here: Primary keys and other means of ID. Integer Primary keys works and if handled properly will not generate as much collision as you might expect. Hopefully you segment your database and do not use the same database for different regions. That would be silly. Most of the time an ID is created and not altered. That means that collision only happens theoretically if you hard code the ID, which is also silly. Most of the times Primary keys are often used to set up relationships. Often in cross tables. Having cross tables in UUID would be affecting performance. However! For ID purposes that is not Primary keys, UUID is excellent. As a primary key method? Horrible. Again. There are ways of solving cross DB communication, but then you are often not using primary keys to solve your problem..
Allow me to introduce you to uuidv7. It's an ordered uuid, so it's index/primary key friendly. Like incremented IDs, it doesn't have a segmenting issue. The only negative is a uuid is just a larger data structure than ints, so it takes up a bit more space. If you're gonna use int IDs AND uuids anyways though, you're still at a net positive by only using uuids
@Deadmanstrolln Thank you! I will look that up. I am a bit skeptical about UUIDS as primary keys. If you are worried that (as our friend here) data is going to leak by showing IDs, imagine having both sequential and universally unique data leak.. I am also very fond of keeping the data structure tight since a rapidly growing database needs all the space it can get. Especially if you think about memory consumption. A request can differ a lot if it is compounding due to data structure size. I'd rather let the smallest primitive handle the primary key and try to obfuscate universally identifiable data points as much as possible.
COMB GUIDs (like uuidv7 mentioned above) are sequential in that they contain the timestamp when it was generated, not sequential like integers shown in the video. This means they can be generated in advance then stored in the database, so it still solves the queuing/delay problem mentioned in the video. "Leak"-wise, a timestamp that an order was created would generally be less important of a secret than how many orders or customers you have in your database (could be application-dependent of course).
You do need to be careful about how a COMB is generated, as different databases sort GUIDs in different ways. The placement of where the random and timestamp bits are in a COMB intended to be stored in an MS SQL Server database vs a PostgreSQL database are different, because the two databases sort GUIDs differently. There will be libraries available for this for your programming language if you look, you need not roll it yourself (in C# we use the RT.Comb nuget package). By the way, this isn't something new, there are articles online about this technique and how it addresses the performance issues of regular UUIDs that you mentioned going back at least as far as 2002.
@reikooters While I partially agree, I would from a business standpoint be more worried by a timestamp and an out of bounds-attack that describes the size of an invoice table than just a leak of integers and an out of bounds attack. A simple integer doesn't say anything about when it is created. If anything, I'd secure my API to not allow such behavior as well as trying to validate data before accepting it in an API. For database performance though, I'd stick to integer sorting and then try to form Metadata that is unique. That way searching would be more specific and harder to crack since it relies on more things than an integer. If possible, I'd like the primary key and identifiable parameters only be a concern for the backend. If you really need to use it in the front-end, it should be in a protected context or a special case.
@@cfossto Everything has its pros and cons. For me if we have a new system and we bring on a new customer we haven't dealt with before, I don't necessarily want them to know they're customer #3 on the system since an integer is understandable to any person whether they're technical or not. But I didn't quite understand what you meant by how you could get the size of a table from a timestamp?
There's some incorrect conclusions here and some of these problems are bigger than the id.
Don't overthink it; a good old fashion autoid likely solves your problem.
YAGNI
In most of the use cases - it just doesn't matter. And migrating to GUIDs or other text-based things hurts performance, so there is nothing for free.
I've also applied indirect object mapping, where you put fake IDs in your session data (which is a dictionary, perhaps) and map them to the actual ID of the object, so that when you receive a request from that session, you use the fake id to find the actual id. This does mean that this information will probably persist beyond a single page, and it does mix poorly with the URL parameter strategy, but if you mix that with row level security and you're using 3rd party auth providers (MS, Google, etc.) it's helpful to avoid exposing the ID obtained from the auth API, while still providing a way to easily manipulate user records safely and easily
Tbh addind this type of complexity at something that is backend secured checking for permissions makes no sense atleast for big projects, whats the adding cost of hashing the IDs if you have it already secure checks
It didn't occur to me to use a prefix before the guid, great idea. You mentioned that everyone should use row level security, could you do a video on it? Thanks!
Building URLs that meet the expectations of an object-capability system is definitely challenging. The W3C has some basic guidance, but use the git repo, not the published documentation which has well-known errors. Neil Madden has some great articles on including credentials in URLs, which is probably the best starting point.
This video is one big pile of bullshit. You are free to use ints, it was done for years. Using guids also have it's own problems. Choose tool for the job.
It is partially bollocks. GUIDs add obscurity which can help if the security of the app isn't that good. For any customer links, you can generate unique ids that after linking back to the site will be queried and converted back to int for rest of the API calls.
Switching to different database that cannot support integer row ids is also quite moot point as you really shouldn't be using nosql for relational data (which you obviously currently have as you are using SQL).
Invoice numbering leaking is true, but also happens due to law in many places where invoice numbers must be sequential. And nothing prevents incrementing the row id randomly every now and then or to be rounded up to next million every month or something.
Distributing data across multiple locations with integer IDs is also trivial.
There is also performance benefits from using integers, both for the database and for the shorter URLs in API calls. Also testing for permissions is easier as you can just test if sequential IDs apply permissions properly or not.
And sharing links with nice intergers looks better than an url with lots of random letters in the end.
And there are enduser benefits, like being able to just enter invoice id that customer reads via phone rather than searching for long GUID somewhere.
You can also generate unique integer ids.
Snowflake IDs FTW
I don't think this video is properly research. For example, regarding the business risk, Shopify, a billion dollar e-commerce, uses and exposes sequential order numbers. I personally think id's should be concealed, but definitely not for the reasons stated in this video.
Currently, I'm using ULID instead of GUIDs, would be nice a dedicated video exploring the different approachs on creating IDs.
What package do you use to generate ULID? Or created custom function?
You did a great job describing *reasons* to use a different record id generation scheme, rather than just saying "you should and if you don't then you're dumb LOL". All the reasons you described are valid. The bits about reformatting the uuid is also excellent. I usually do something like user: or user. (eg you see AWS uses arn:accountid:region:xxxxxxxxxxx) but the underscore makes sense too.
Specifically regarding the API front, as much as I dislike some implementations of it thr OData standard is actually quite nice, provided it's implemented properly and adheres to all spec docs
You saw honest Duane's video and jumped on the clickbait.
At least you made a better argument and were less inflammatory about it. I still disagree that uuids provide "security", but i do think int ids make it easier/faster to run brute force attacks.
For simple projects i will still use int IDs. If we will need more protection in our app its probably better to use GUID.
No. You will have issues with database performance (RDBMS). The answer is to encrypt the int.
@@yaghiyahbrenner8902are guids (or uuids) really that expensive for the db?
You can use int IDs as much as you want, just don't publish them in your API.
@@Linuxdirk I can publish whatever i want.
@@bartlomiejuminski Did you even watch the video?
That's why I prefer UUID than incremental ID as default. It's hard to predict but can be costly for performance.
GUID are very inefficient thought, sure they can be indexed but from a db point of view they are strings, very inefficient. Using GUID also removes the ability to infer the sequence in which records have been inserted into a table which could make debugging extremely hard. As mentioned in the video it is a design decision but in my opinions GUID are not a good solution and do not scale well in terms of performance. I also don't buy the "security" risk or at least not in the vast majority of cases.
simple createdAt/updatedAt columns take care of the sequencing issues. when it comes to "security", depends on the context. a lame backend developer and you getting access to, let's say, banking data, or some court case.. a simple lowly guid will make that access nigh on impossible. and in the vast majority of cases, you'll be fine with some performance tradeoff.
Uuidv7 or ulid. Both are ordered uuids. They take up more space than an int ID, but otherwise work functionally the same. They're ordered so you can still infer the insert sequence, and they don't have the segmenting issue non ordered uuids have
With int ids, it will be easier/faster to run brute force attacks on existing records, but I'm with you, in that this isn't necessarily security, so much as obscurity.
And UUIDs can be stored as binary, at least in mysql we have bin_to_uuid() & uuid_to_bin() for this purpose, which brings us to a 16bit column, i believe.
Info leaking also can be easily mitigated by incrementing the row id every hour/day/month by random value.
I would say, you can use INT's as ID's, but make them intern only, not for external referencing to something.
Exposing primary keys, especially in the sql world, is a terrible idea period. At some point someone needs to change the value of that key field (either due to business or technical reasons) for some row and then you're in trouble.
What use case have you ran into where the correct solution is to manually change an ID?
@@Deadmanstrolln data migration (think auto-incrementing identity columns) and fixing corrupted data (like merging duplicates).
If you have duplicate order id's, then something is fundamentally wrong with your system. For merging say a category identified by an int ID, then you would do the merger first and remove the duplicate on success.
Having simple id's for an order is much simpler for staff to work with, and so many advantages.
For merging different databases you can use composite keys, or use an int64 and use a different range for each db. This can be implemented upon need.
Should you really need it in the future, it is easy to add an additional guid field in the future. One advantage of a guid is for offline usage, which again having an int as the primary is advantageous, because easy to see once added, and the guid can be used to resolve state on retry if an api call returns a non deterministic resp9nse.
@@michaelkhalsa there are always problems with systems, and you keep creating new problems with new features. Of course an answer is "just dont create bugs" but that's not really satisfying. These things occur in production and if you have exposed your surrogate keys and external parties, like integration partners using your API, have started to depend on those, the problem has spread outside your control. This leakage could have been easily avoided by only exposing identifiers meaningful to the business and not surrogates, which are an implementation detail. So you'd just get rid of the duplicates by fixing the data and removing the duplicated rows.
@@michaelkhalsa by data migration I don't mean implementing a completely green field system with brand new specs. Of course then you can adjust your schema as needed. I meant more like introducing a new tenant with a ton of existing data and dependencies into your existing multitenant saas system. Having to rely on a specific kind of surrogate identifiers locks your business to a specific technology. So the best you can do to mitigate this on your side is - as you mentioned - to expose only secondary key fields or other indexed fields that can have whatever values the client needs to have there as identifiers.
The example with the orders seems kind of stupid to me. If anyone can fetch data through the API, sure, then you should use an external reference instead such as UUID. You could also use a separate key to secure the data. If you have some unique key that is required along with the id, then that works just as well as UUID.
If only select people can fetch data through the API, then you should have authorization as well. Does each customer have their own token? Then limit access to their own orders and slap them with a 403 instead of 404 even if the order does not exist or if it isn't theirs. This would as such not expose the next order number either since they can only fetch their own orders.
If anyone with access to the API can fetch data in the API... then you're just making things difficult for no good reason, because they should be able to access the data one way or another regardless.
I also want to point out that some countries have laws that force you to have incremental order numbers. So in those countries it would not make any difference since you can just buy something once a year and see, just like in your example. So it is a very uneducated take.
Thanks for the Sqids tip! Never seen that before and it's really valuable. I also like that it can encode tuples. This could come in handy in many scenarios. Ones that comes to mind is in CosmosDB where you need to know a partition key. If an ID code be encoded to include both the ID and the Partition Key, that could help very much in some cases.
6:47 no rickroll? :(
Okay, then what about float IDs?
very good, thank you
in case you fucked up security you'll have obscurity - is that what you suggest? meh
I mostly agree, but i think one legitimate risk is that int ids are easier and faster to brute force attack.
If that brute force is successful, the problem still lies in messed up permissions/authentication, but it would be harder to automatically request 10,000 UUID based resources than 10,000 primary int id resources.
nice vid
title: "do NOT .."
video: "actually it is fine... "
for people watching these videos, keep in mind these youtubers have very little software experience (less than 10 years, no published articles or books, no senior position at reputable companies, no academic experience, no graduate degree, etc.)
they think all projects need to follow same types of performance or security as the single one they worked on, or they are knowingly using clickbait.. either way, good reasons to click "don't recommend channel"
An important note to make indeed
Sharding autogenerated row ids is really simple, just change the autoincrement to some bigger number, like 4, and then put US to start from 1 million and EU from 1 million +1, they will then gladly continue and never conflict.
I didn't know you could change the auto increment size. I suppose it may depend on db. I'll have to check mysql docs. (But also this isn't a feature i need rn)
@@reed6514 dunno about mysql but real sql databases do allow increment formulas
Amazing video. Thanks Gui!
We should have all learned this from the Parler hack three years ago.
Care to recap it? I didn't follow it
@@reed6514 @reed6514 long story short, they used sequential numbers as IDs for everything from posts to user profiles and everything in between and had no authentication or rate limiting on their backend, allowing hackers to write a simple script to scrape all of their data in minutes by guessing the enumerated values of all publicly available data shortly after a thing happened that significantly raised public interest in their app. Total amateur hour app security. As a bonus, they also failed to scrub the geolocation metadata from uploaded photos and videos, revealing the personal addresses and details of many of their users.
snowflake which Twitter uses to generate Id, its datatype is long though
In my api, there’s always a order called 66
Almost no one changes their database.
I like the GUID approach and I use in all my projects? Generally I put a route constraint like this
[Route("{id:guid}")]
In the case you mention, you removed the dashes and add a prefix, right?
then I receive a string, when the id pass to the controller, how do you convert to a guid? Do you add a middleware?
great video btw, cheers from Brazil, hu3
Agree. I don't use integers anymore since switching to GUID's. If I need to enforce order in my collections, a RowIndex is added.
I also don't like revealing the traditional ID, but the arguments presented here don't justify it.
I would generally just recommend using a string ID, and then you can generate that ID however you want at any time, allowing you to scale and change the method as the system requires. Simplest case is to just stringify an int, but can be changed to snowflages or universal ids later on.
Not using the id in the API will just hide the issue for most people. Most of the time your front-end will contain the ids anyway in the markup to identify records. So anybody familiar with dev tools will easily be able to get them. I think the more correct solution would be to not use identity ids. Or have some sort of translation layer to convert back and forth between the sequential ids in the database with non-sequential numbers that you can freely use in your application code, which would obfuscate that hidden information while still letting you take advantage of basically free inserts using a clustered index on your identity column.
EDIT: I finished the video and you talked about this exact solution at the end. While true it's not cryptographic, it would still be extremely difficult to figure out the math to get out the original ids. Strong enough for most businesses I'd say. Just maybe not the DOD, lol
Great video!
Sqid is a good middle ground between using incremental ids and guuids in a single database application. You don't have to modify the database as you can hash the id when responding to a request, then later decode that id when the client makes a request with it. While it's not cryptographically secure, the hash is salted so it's not trivial to obtain and row level access should be a bigger priority in the first place.
It's probably best practice to be in the habit of using guuids from the get go.
Your video have so much problems do more research you just gave a one correct reason other than that it is not even a reason, for most of distrbuted system it uses read replica and the performance of uuid or guid uuid and guid is not the same but both have performance issue and ordering issue
This video is harmful. To anyone watching, do more research and take this video with a grain of salt.
cool video
Wordpress: 👀
I will die on the hill that you should not be using GUIDs and instead be using snowflakes or similar systems
Simple, user API key = User ID+name+salt, hash it.
For an API key, I'd go with a cryptographic randomly generated string, then use a cryptographically secure hashing algorithm for storage, in case your DB gets leaked.
In PHP, bcrypt is used for password hashing, and PHP manages salts for you & the php docs explicitly recommend not to provide your own salt.
With the rng string, you can also cancel an existing API key and generate a completely new one.
UUID
use graphql instead
i use this en.wikipedia.org/wiki/Snowflake_ID
Interesting idea. I suppose you could just simplify it by using the unix timestamp + some other unique info for basic data. It would still be an INT that increases over time while being random enough to avoid enumeration attacks
@@philadams9254 snowflake id already handle that thing (snowflake id format: timestamp + generator id + sequence) so it will avoid enumeration attack
@@philadams9254 Yes you can, I've been using multiple variations of snowflakes over the time. I.e. for one project I worked on I had a 2 bit identifier for whether it's the productive system, test system or development system. Then I also always reduce the timestamp by a fixed amount (like discord does) and often change the bit width of the counter to how ever many requests I expect the application to ever get (often no more than 8 bit) which gives me significantly more space for the time to prevent issues like y2038 and similar
DOH no extra security at all ..
Additionally, the time a snowflake was created can be calculated from the snowflake. This can be used to get snowflakes (and their associated objects) that were created before or after a particular date.[2]
@@colemichae Yes, the snowflake ID is sortable by the time they were created, and the timestamp can be computed. In many scenarios, the creation datetime is publicly displayed (e.g., blog post creation time, many open APIs showing their data creation time). If you want to conceal the ID, you can hash it using SQIDs (formerly Hashids).