7/28/2023 0 Comments Mysql uuid not uniqueI'm answering my own question to share that in the hope that it'll be useful. Sensitive or protected resources should still be protected by good authentication and authorization. 74 I've spent quite some time looking for a solution and came up with the following mysql function that generates a random UUID (i.e. Just keep in mind that relying on security by obscurity, by having a long identifier in a URL that would be hard to guess, is not the only solution you should rely on. With hex (0-9,A-F), 6 characters allows for 16 million unique combinations, 8 over 4 billion, and 10 characters over 1 trillion. Just be sure to chose enough bytes to provide room for sufficient randomness to reduce the chance of collisions, and so that the probability of sequential IDs are low (ie: AABBCC, AABBCD). Some possible solutions in MySQL is to either run the UUID through a hash like sha, or use random_bytes. ![]() The same is true for the uuid_short function – the values will be sequential. So, if you do a substring and select 6 characters, or only select the last 12 characters, you will end up with duplicate values. Only the last characters of the first grouping will change. It generates values that are sequential or near-sequential when run on the same database server at approximately the same time. Alter the table to make name a primary key or a unique NOT NULL column. MySQL’s UUID algorithm is unfortunately a bit more predictable. With Postgres 13, this is fairly simple: select gen_random_uuid()::varchar ![]() But if you have lots of rows in a database that need to be updated, you will likely end up backfilling values in the database itself. Generating a random string as a surrogate key in code can be easy enough, by using a UUID or a class like RandomStringUtils (careful, look up the Scunthorpe problem). This generally can be bad for security, as a user could attempt to access other resources by incrementing the ID ( GET /account/124). Sometimes this has come up when working on an old API that exposes a primary key in the URL ( GET /account/123). From time to time, I have had to go back to a legacy database and for one reason or another add a non-integer surrogate key.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |