Search code examples
sqldatabasesqliteuniqueidentifieridentifier

Use a random-looking ID for a table instead of PRIMARY KEY AUTOINCREMENT, to avoid the client knowing the number of records

I have a Sqlite3 table which will have thousands to millions of records.

For each website user, an ID is created and saved in the database. This ID is sent to the client, and then the client sends some data back to server (with XHRHttpRequest) along with this ID.

  • If I use a CREATE TABLE mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, ...) then the client will be able to see this ID in the HTML / JS. Problem: the client can see the current ID, know how many records have been processed, guess previous users' IDs, etc. This has to be avoided.

  • If I use CREATE TABLE mytable (id INTEGER PRIMARY KEY AUTOINCREMENT, ...) , I could also send h = mycrypt(id, salt) to the client, instead of id. But then, when the data comes back from the client (including h), we have to do:

    UPDATE FROM mytable ... WHERE mycrypt(id, salt) = h;

    This last query means we have to apply mycrypt to every row (i.e. FULL SCAN!) to find the right row. This will be very time consuming if the table has a huge number of rows. Of course we could create an index on mycrypt(id, salt) but it would mean having 2 indexes (one for id, one for crypted id), and this is non optimal: we end up with 2 indexes, when it would surely be possible to have only 1 index.

  • Alternatively, can we create a random-looking ID which will serve as PRIMARY KEY? How would you do this? The fact it's not sorted will surely impact performance, is there a well-known way to handle this?

More generally, how to handle the problem of giving an ID to a client, preferrably non increasing 1, 2, 3, 4, 5, ..., but still have a fast lookup server-side?

Solution

  • I think If you can crypt an id then you can decrypt to...

    UPDATE FROM mytable ... WHERE id = decrypt(h, salt);

    But better solution the random id.

    I make a UUID in my app and send to db. Then the ID column is not AUTOINCREMENT.

    UUID is supported by a lot of languages. (C,Java,PHP) If you have more appserver or dbserver, UUID is unique.