URL ID Tampering Protection/Security

[benanamen]

I would like feed back on best practice for encoding/encrypting a Url Id number to prevent tampering among other security concerns.

 

Example: page.php?id=SFNqaUluZVN1ZjFvRDZXb1Baa2l3UT09

 

I realize their are a number of ways to do this. Just how secure does this really need to be from being able to get the actual Id number? Of the various methods, I am thinking I would rather not create an extra DB field for storing a unique Id but I am interested in feedback on it.

 

The simplest approach would be to just encrypt/decrypt the string with AES or something similar which brings me back to just how secure does the id conversion really need to be?

 

[Jacques1]

I'm not sure what you're asking.

 

Do you have numeric IDs, and you want to prevent the client from changing, say, id=3 to id=4? And is there no user authentication? That is, you don't know who the client is?

[benanamen]

You are correct. In this case user is logged in before he even sees the id in the URL. At the least I dont want them to be able to change the id #. It is also a giveaway of how many potential records there are which you may not want particular users to know.

Edited May 4, 2016 by benanamen

[Jacques1]

By far the easiest solution is to get rid of the sequental IDs altogether and use random IDs instead (not as an additional attribute but as the primary key). This is actually very common. You can store a raw 128-bit ID in a BINARY(16) field, or you can hex-encode the random ID and store it in a CHAR(32) field.

 

Also, since you appearently do have a user registration system, why don't you simply use an ACL to prevent users from accessing pages they're not supposed to see?

[Psycho]

You can never assume that the value is not tampered with. So, even if you obfuscate the ID by using a GUID/Hash you still must ensure the user requesting the record is authorized and/or that the value is appropriate. Having said that, using a non-consecutive arbitrary value is still a good idea as the IDs can "leak" information as you indicated.

 

I know that in our enterprise level application, many records have both an int ID as well as a GUID. The GUID is used as the identifier between the client and the server whereas the ID is used for internal operations (e.g. the PK/FK for JOINing tables). For most purposes, I agree with Jacques1 that just using a GUID instead of an ID will suffice. The main benefits of having both is that performance with INTs is typically faster and in high utilization scenarios every resource counts.

 

This page has an informative table about 1/2 way down comparing different properties of four different ways of storing unique IDs. It's specific to MS SQL, but I assume most would correspond to MySQL as well.

[benanamen]

Thanks. After briefly looking into UUID it is going to solve an up coming problem I didn't have an answer for yet. The clients original app had a members login table for members and a users table for staff. The new app will have one users table for all users with ACL. The obvious issue was that the current auto increment id's would crash into each other. UUID is the answer to merge the two tables without an ID collision.

 

Would you still want to set a unique index on the UUID field?

 

* When I get to the ACL part I would like this forums input in a new thread on the ACL implementation. Client wants fine grade permissions on actions/access to certain areas and data by person, not roles.

Edited May 4, 2016 by benanamen

[Jacques1]

Note that UUIDs aren't supposed to be unpredictable and may still leak information about your system. If you want to be sure that the generated IDs are in fact sufficiently random, you have to look at the exact implementation or use your own generator.

[benanamen]

At this point for the current project the main thing is not displaying an obviously sequential number that is easily +-'d in the Url.

 

Since it is now clear I am going to need UUID's anyways for a data merge, would the following be OK:

 

1. Keep existing auto increment and use for foreign keys and joins (current setup)

2. Use the UUID for the Url when needed to pass the id by GET

 

Regarding the UUID column, does it/should it still be a unique index? At the least it should still be an index right?

 

Do you think using a CSPRNG is overkill?

[Jacques1]

MySQL uses a weak, obsolete UUID implementation which is based on the timestamp and the MAC address. While this may still be “good enough” for your purpose, a CSPRNG will provide actual randomness with no extra work.

 

Yes, the ID attribute should be unique. I wouldn't maintain an extra column for auto-incremented IDs unless you've actually verified that it's more efficient.

[benanamen]

So this is what I have come up with. Good? Not sure about the WHERE example. Feels kinda scary having an id column that I cant read without HEX. Yikes!

 

CREATE TABLE `users` (
  id BINARY(16) NOT NULL,
  user VARCHAR(15) NOT NULL ,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// Insert 
INSERT INTO users (id) VALUES ( UNHEX(REPLACE(UUID(), '-', '')) ) 

//Select 
SELECT hex(id) FROM users

 //Select Where 
SELECT user FROM users WHERE id = hex(id)

 

Edited May 5, 2016 by benanamen

[benanamen]

After testing I see the where example should be 

//Select Where 
SELECT user FROM users WHERE id = unhex('A14CDAF7127511E696BE3085A9AE86BC')// Hexed value

Edited May 5, 2016 by benanamen

[Jacques1]

If you insist on using UUID() despite its major weaknesses, just store the plain UUID string. That's really the whole point of the function.

 

If you want a binary ID, generate it with PHP. Some database systems also have a built-in CSPRNG (MySQL isn't one of them). If you don't like passing binary strings around, encode the result with hex, Base64 or whatever you find appropriate.

Edited May 5, 2016 by Jacques1

[benanamen]

Not insisting. Just learning the ins and outs of all the options. I have never dealt with a binary column or hex or UUID.  I am aware of random_bytes, but that is Php 7 only. (I also know of its equivalent replacement).

 

I did spend several hours yesterday researching UUID and all its pros & cons. This is one function I was not educated on before that, but was aware of it. When I learn about something, I always want to Master the knowledge about it. (Same reason behind my questions to you in other thread on htmlspecialchars)

 

I actually have two different use cases for the current project. One, "obfuscate" the id in the url by whatever method, and Two, merge about 374 user records with conflicting auto increment id's.

 

The first one, any number of ways to go about it.

 

The second, I will probably just temporarily use UUID to merge the records, update related table id's for those users to their new auto increment number and then delete the UUID. The client has not made any requirements to how things work under the hood.