homepage Welcome to WebmasterWorld Guest from 54.163.84.199
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Random codes in MySQL
ocon




msg:4649809
 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




msg:4649811
 7:02 pm on Feb 27, 2014 (gmt 0)

https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

To add another layer of confusion to potential reverse engineers, swap around some of the bytes when displaying publicly.

DaveWave




msg:4651180
 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




msg:4651188
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved