how safe is AUTO INCREMENT id ?

hello
28 Replies
NIMA
NIMAOP7d ago
im using auto incremnt for id but im thinking its not safe
13eck
13eck7d ago
Define "safe"
NIMA
NIMAOP7d ago
cuse anyone can guess next number and i see some website use random id is it better?
13eck
13eck7d ago
Define "better" The only real "unsafe" ness of incremental IDs is that if they're exposed to the end user then they know a bit about how many of resource X you have in your DB. If they create an account and their account ID is 38, for instance, it's a good bet that you don't have a lot of users. The real question is this: can exposing that information be harmful to your product? For example, having a product ID of 4 in a storefront doesn't matter, as knowing how many products you have for sale isn't a big deal. But if exposing the number of users can be "harmful" (again, what that means is up to you) then you'll want to use something else for primary ID For example, Twitter and Discord use a uint64 snowflake where the first 40-something bits is a timestamp. So it's not auto-incrementing but still easily sorted (as the number assigned is based on when it was assigned). UUIDs, on the other hand, are strings and much more difficult to sort—and strings take up more memory in the DB, which might or might not be an issue Also of note is that auto-incrementing IDs doesn't always mean "I get one number I now know the next." Many keep a table of deleted IDs that can be re-used. So even if the end user gets an ID of 38 there's not guarentee that the next item is 39
NIMA
NIMAOP7d ago
yes true so its ok to use auto incrementing
Jochem
Jochem7d ago
(also, even if you're using something else as an ID, you shouldn't rely on the fact that people can't guess that other ID for security. You still need to take the exact same security steps that you do when you use AUTOINCREMENT)
13eck
13eck7d ago
Being OK or not is a preference thing, not an absolute. You need to decide if it's ok to expose the number or not. But yes, for most small-scale apps auto-incrementing ID is fine Yeah, using (semi-)random IDs is just one way to mitigate any possible security issues with incremental IDs, so it's up to you to decide what/if/how you care about everything and code accordingly.
NIMA
NIMAOP7d ago
my plan was use it for small app
Jochem
Jochem7d ago
I've seen people use UUIDs and say "well, now there's no more need to implement access restrictions, cause there's no way anyone can guess anything!"
13eck
13eck7d ago
That's…scary
Jochem
Jochem7d ago
and then forget that people can bookmark stuff for when they lose access, that browser history can leak, or links get sent to the wrong people
13eck
13eck7d ago
OH! One good reason to not use incremental IDs: distributed databases. If your DB is on multiple servers then you can't use incremental IDs as it's very difficult (or impossible?) to sync IDs. Discord snowflakes, as an example, have a 5-bit worker ID used to identify what server the item was created on
ἔρως
ἔρως7d ago
what are you talking about? also, which database? and what is the id for? how do you intend to use it?
NIMA
NIMAOP7d ago
im talking about mysql auto id for all stuff profiles / items / and ...
ἔρως
ἔρως7d ago
set it as a primary key and dont use an unique index on the id column you dont need to set it to notnull, as thats automatically assumed just make sure it is the first column other than that, you shouldnt worry
NIMA
NIMAOP7d ago
i know thath i was talking about safety
ἔρως
ἔρως7d ago
depends inheritly, there is nothing wrong with it however, how you use it may or may not be a security risk
NIMA
NIMAOP7d ago
i was worry about some one some who do something so he cant chenge thath id from sessions and log in diffrent profiles and cuse the id is ++1 each time it is eazy to find all account
ἔρως
ἔρως7d ago
^ that has nothing to do with mysql it's all in whatever is using mysql lets imagine this: - you want to implement a login system - you want the user to auto-login on the website - you store the user id in a cookie - you change to user id + 1 - now, you are someone else using or not using auto-increment wont save you from this now, using a cryptographically strong token you can verify against the user? thats another story but guess what? the id is still auto-increment anyways, so, it is of no consequence
NIMA
NIMAOP7d ago
so the importent part is seceury of other stuff not only id also is it possibel someone chenge the ID in sessions in php ? with some tools
ἔρως
ἔρως7d ago
depends on the security holes you add
NIMA
NIMAOP7d ago
like if website have max security
ἔρως
ἔρως7d ago
thats not how it works
NIMA
NIMAOP7d ago
there is no tools or anything to chenge value from seesions
ἔρως
ἔρως7d ago
again, it depends the world isnt black and white it depends on the code
NIMA
NIMAOP7d ago
👍
Jochem
Jochem7d ago
There's no direct way for the client to edit the session in PHP, barring security vulnerabilities in PHP or your own code. also, there's no such thing as "max security"...
ἔρως
ἔρως7d ago
either it is secure or it is a liability that must be taken down or fixed asap
Want results from more Discord servers?
Add your server