0

Ive read a few articles on this subject and just wanted to get some clarificiation regarding using random primary keys instead of auto increment. I am building an application and instead of using auto-increment primary keys, I am using the following code to generate a random primary key that includes both numbers and letters:

$bytes = random_bytes(8);
$bytes = bin2hex($bytes);

The reason I would like to use random primary keys is to prevent url tampering. My application also has record level security but I wanted random primary key as a secondary measure. Also, I have this column set to unique as to prevent any rare collisions. If there is a collision i just throw on on screen error asking using to resubmit the form which generates another random key. So my questions are as follows:

  1. My db is innodb and my application is all relationship based (PK/FK relationships). Does having a random key (using the code above) effect search performance and/or indexing speed as compared to auto increment key?
  2. Are there any reasons I should not be using a random key (as the primary / indexed key) other than possible collision which I have protected against using duplicate error handling?
  3. Before I finalize this application, are there any other suggestions or best practices I should be using as it relates to primary key other than how I am doing it above?

Thank you. Again, i know there are other articles out there but many of them are outdated (prior to php 7 random bytes) so I thought id get a fresh take on whether or not random primary keys are considered best practice (why or why not). Thanks.

user982853
  • 2,470
  • 14
  • 55
  • 82
  • 1
    You will have to explain what url tampering has to do with the internal primary key of a table. The obvious issue is that integers are highly efficient and compact in this case. An unsigned 4 byte integer offers 4.2 billion available keys. In using an 8 byte char, you are increasing the size of the table which will also increase the size required for every FK to that table. The smaller the db, the more efficient it will be. – gview Jun 05 '17 at 04:05
  • As it relates url tampering, i just don't want to make it easy for a using to take xyz.com?userid=2 and make it xyz.com?userid=3 and see information that was not intended for him/her. I also have record level permission in my application using sessions but just wanted an additional layer of security. – user982853 Jun 05 '17 at 04:11
  • @user982853 in that case you can encrypt the key in Url like xyz.com?userid=encryped_keyValue and Decrypt it while fetching it from URL. – Meera Tank Jun 05 '17 at 04:31
  • That is not security. Nor is it an extra layer of security. You either have security that prevents the things you don't want to happen or you don't. What you are talking about is obfuscation. Obfuscation is a throw in within application design. If there is little to no cost for such a feature, then obfuscation is fine, but it's not the way to solve the problem you are talking about. – gview Jun 06 '17 at 01:40
  • Let's say that regardless of whether or not a key can be guessed, that your design allows a key to be passed as a url parameter. At that point, anyone who knows a key can access the related data. If that is not acceptable from a security standpoint, then your application requires a design that will only allow someone to see the rows they should see. That needs to be built into the design of your application. – gview Jun 06 '17 at 02:13

1 Answers1

1

Bad -- very bad, in fact. Random primary keys play havoc with MySQL's page cache, which will make performance poor under load. It also means some SQL features will be unavailable to you, like multiple-row INSERT.

If you want to protect the ID in a URL from tampering, consider including a keyed hash (e.g, HMAC) of the identifier in the URL.

  • So you are suggesting that I use auto increment. Then when passing the variable encrypt it so that it looks random in the url and then when new page loads decrypt it to run the code? Thank you. – user982853 Jun 05 '17 at 13:24
  • 1
    @user982853 Not encryption, _per se_. What I'm recommending is that you include a signature alongside the ID in the URL, so that you can verify that the ID was generated by your application. –  Jun 05 '17 at 16:07
  • You might want to expand on "keyed hash ... of the identifier" a bit. If I want to protect `userid=2` I can't just hash `2` and be done with it; to really prevent unauthorized access you'd probably want to be communicating over a secure channel, authenticate using a password or 2FA and then check requests against that user's authorization (each request would need to start securely passing around either the credentials or a logged-in session ID or something). I am not sure how you can prevent unauthorized access by just hashing stuff. You can't assume the hash algorithm is secret. – Patrick87 Jun 05 '17 at 20:12