0

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.

  • 3
    Why do you want to hide the primary key? What has necessitated that? – George.Palacios Dec 11 '18 at 13:44
  • This is a contrived example, but in some cases users should not know the quantity of items of a certain feature for competitiveness, and as it stands the quantity can be derived from the primary key ID. – Lansana Dec 11 '18 at 13:47
  • 1
    in some cases users should not know the quantity of items Use randomly programmatically generated primary key (within possible values range) instead of autoincrement. – Akina Dec 11 '18 at 13:49
  • @Akina Well the thing is I still want an auto-increment to benefit from its features, I just don't want that visible to a user. – Lansana Dec 11 '18 at 13:50
  • 1
    In WHERE _id = a129fjf24f0jwef94f, you need quotes around the string! – Rick James Dec 11 '18 at 21:10
2

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
  • I was thinking to just generate the random string in my code and store it to the database. – Lansana Dec 11 '18 at 17:41
  • @Lansana - random string in your code -- Sure. – Rick James Dec 11 '18 at 17:43
  • What type would you recommend for this column? CHAR, VARCHAR, BINARY? What size? Why? Thanks! – Lansana Dec 11 '18 at 17:45
  • @Lansana - How about CHAR(6) CHARACTER SET ascii. You could put letters and numbers, with a limit of over a million. Consider the issue of case folding. If a=A, then COLLATE ascii_general_ci, if a<>A, then COLLATE ascii_bin. 6 bytes is big enough for lots of users, but small enough to not be a burden the database. Adjust if needed. – Rick James Dec 11 '18 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.