|Random codes in MySQL|
| 6:57 pm on Feb 27, 2014 (gmt 0)|
Each row in my database needs a unique code. This code is public so I want to avoid auto increment integers that allow users to know how many rows there are. I'd also prefer not to do an MD5 hash of an auto increment integer as it could be easily reversed.
Currently, each time I insert a new row I generate an array of random codes in PHP and check the database to see if any are available for use. I insert the new row using the first available code.
This requires two database calls and I run into the possibility that all randomly generated codes are already in use.
Does MySQL have something built in where I can insert a new row and one of the columns are automatically inserted with a random, non-sequential, (hopefully short), code that is not in use.
|brotherhood of LAN|
| 7:02 pm on Feb 27, 2014 (gmt 0)|
To add another layer of confusion to potential reverse engineers, swap around some of the bytes when displaying publicly.
| 10:50 pm on Mar 4, 2014 (gmt 0)|
uuid() would suffice but it takes 128 bits storage.
If that's to long then your best generating a random int and testing for uniqueness. If you do this in a procedure you only need to make one call to the server.
If you're using this field as a key in other large tables then that's another good reason not to an int instead of uuid. Sometimes you might want to use an autoincrementing int for your primary key and uuid which is only visible to users.
|brotherhood of LAN|
| 11:45 pm on Mar 4, 2014 (gmt 0)|
I'd also prefer to use auto-increment myself. Assuming a 4 byte ID, to prevent 'number of rows known to public user' I'd put the 4 bytes in a haystack of 16 bytes or so, and do some bitwise shifting to hide the ID from view, but it'd be much easier to code in something like PHP rather than a MySQL procedure or UDF.
I guess it depends how much of a necessity it is to hide them. Something simple like this hides it from being obvious, but anyone who wants to take a greater interest could relatively easily reverse engineer it. Convert base 10 to arbitrary base.
mysql> SELECT CONV(12345,10,33);
| CONV(12345,10,33) |
| BB3 |
1 row in set (0.00 sec)
mysql> SELECT CONV('BB3',33,10);
| CONV('BB3',33,10) |
| 12345 |
Doing REVERSE(CONV(12345,10,33)) and REVERSE(CONV(54321,33,10)) makes it a little less obvious. You could keep adding functions to get it to the point where it's not obvious that it's an auto-increment value, but is still able to be converted without a reference table.
Using something time related also makes a lot of sense, to avoid collisions.