Good practice for hiding primary keys from a client

Question

Let's say I have a table like this:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);

Later on in my client, I may fetch a RESTful endpoint like this:

GET /v1/users/1

The query to retrieve the users would look like this:

SELECT * FROM user WHERE id = 1;

I want to avoid the approaches above so I can hide primary keys from user-facing web apps.

I thought I could do something like this instead to solve that problem:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    _id VARCHAR(20) NOT NULL UNIQUE
);

The HTTP call now becomes something like this:

GET /v1/users/a129fjf24f0jwef94f

...and the query looks like this:

SELECT * FROM user WHERE _id = a129fjf24f0jwef94f;

What are the downsides to such an implementation at the database level, if any, other than excess storage? I would also have to use the _id on queries where I involve joins of other tables, etc.

Answer 1

Plan A:

Your basic is probably good.

_id could be computed as MD5(CONCAT('salt', id)). 'salt' is some secret string; it's purpose is to make it harder for a hacker to discover your mapping between id and _id.

An MD5 can be stored in CHAR(32) CHARACTER SET ascii or, via UNHEX(...) in BINARY(16) -- 32 or 16 bytes, respectively.

Since that is 32 hex digits, you may have an issue with such a long string. If so, then you could use some shorter 1-way hash, but that increases the risk of an accidental dup. At that point, you would need to take advantage of the UNIQUE index, toss the id, and move on to the next.

Plan B:

Create a table with all the numbers from, say, 100,000 to 999,999. This would give you 900K 6-digit numbers. Sort them (ORDER BY RAND()) and add an AUTO_INCREMENT id. Now you have a mapping that is unique, and limited to 6 characters. (Variants would involve hex or base64 or all letters. But watch out for "4-letter words" buried in strings.) (MariaDB has an easy way to build such a "sequence" table.)

Plan C:

When you need a new _id, compute RIGHT(RAND(), 6), then verify that it is not a dup. (A simple Stored Function can do that task.) (Caution: When you get close to a million, the function will bog down due to dups.) 6 limits you to 1M numbers; increase the 6 to get more.

Minor bug: It is possible to get a decimal point; any of these would be safer:

REPLACE(RIGHT(RAND(), 6), '.', '')
FLOOR(1000000*RAND())
100000 + FLOOR(900000*RAND())  -- always 6 digits long