I'm generating 9-character order confirmations such as K1YBFRG17, O1D15ZCOM, etc and using them as order confirmation numbers. It's basically just picking at random from the string 'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890' nine times.

While this allows for 36**9 different IDs, which I believe is around a hundred quadrillion, and the likelihood of a repeat is infinitesimal, I'm just wondering how this would be looked at from a security standpoint. Is it important to sweep the database each time a new ID is generated, and make sure that it's unique, even if there's almost no chance of it?

  • The security standpoint is to understand the risks. What are the risks of a duplicate confirmation ID? Can you add a customer-specific string to it to make it unique?
    – schroeder
    Nov 23 '18 at 22:10
  • Uh, in most cases wouldn't you have a UNIQUE CONSTRAINT in the DB anyways? Since almost all RDBMSs prefer tables to have a primary key, and order ids are great natural keys. So the database would do the checking at insert time for you (regen id on duplicate key error, an extremely rare case). Nov 23 '18 at 22:34
  • @Clockwork-Muse The IDs may still need to be random. A predictable ID number could be a security problem. But ya, no reason not to check the database for duplicates if you're going to be adding an entry to it anyway. You don't have to "sweep" the entire database if rows are indexed by ID. Nov 23 '18 at 22:52
  • @FutureSecurity - Where did I say the ids needed to be predictable? A primary key is just the "main" identifier for the row (and not necessarily related to storage order). This doesn't have to be an auto-gen, surrogate id. Note that databases usually implement primary key constraints with an index (since that's the most effective way to do so, even if silently), so of course it's just going to probe for it. Nov 23 '18 at 22:57
  • @Clockwork-Muse Not criticizing you. People make the predictable ID mistake all the time so it's always good to remind the audience. Nov 23 '18 at 23:00

The probability of sampling the same random value twice from a uniform distribution depends both on how many possible values there are and how many samples you've recorded so far. You are unlikely to generate two identical IDs in the first 100 IDs you generate, but the probability goes up (somewhat counter-intuitively) the more IDs you make. This idea is referred to as the birthday problem.

As a rule of thumb you should expect that if you're sampling (with replacement) from a uniform discrete distribution of size n then you should expect to see your first repetition after around sqrt(n) samples. In your scenario that's about 10 million IDs.

In cryptography it's common to use an RNG to avoid repeat values. But it requires very large numbers. that make 369 seem insignificant. The minimum size of these numbers is probably between 192 bits and 256 bits. This assumes we're using a uniform distribution and a true random number generator (or some approximation of a TRNG).

  • 1
    +1....but if you combine the the confirmation with some other identifier (order number, email address) and limit the rate at which ids can be validated for a given (other) identifier, then the risk almost completely disappears.
    – symcbean
    Nov 23 '18 at 22:59
  • This is good thinking @symcbean. I think what makes the most sense for me is to give the customer the 9-digit code on its own, but in the database do something along the lines of save it as a hash of the code and their email. That way it's reliably retrievable, reliably unique, and simple for them and for me. I think that makes sense, unless someone else sees a flaw with that scheme. Nov 24 '18 at 0:10
  • 1
    @Aerovistae If you were to use a secure (specifically collision resistant) hash function then it's no worse than using the tuple (Order ID, Email) to uniquely identify orders. On the Wikipedia page you can find approximations that should accurately tell you the probability of repeats for a given number of samples if n ("days" per "year") is large. (In this case plug in the number of IDs per email address instead of the total number of IDs.) Nov 24 '18 at 1:03

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.