One of our applications uses SQL Server for the back end, and that application requires a 4 digit numerical PIN for employees to be created that works with a key (more or less). The system itself defaults all PINs to 0000 because that's the magic value for "don't require a PIN" if the device you're using doesn't have a keypad.
We had an issue with employees not updating their PIN (and grew tired of waiting for the vendor to correct the issue) so we generate our own PINs with SQL Server using a scheduled job to update any PINs of 0000.
The PINs don't need to be secure (how secure is a 4-digit PIN, after all) they just need to not be trivial. However, there's no reason not to think about the problem with security in mind, and I like to take the time to understand what I'm doing at least semi competently:
The current solution (not by me!) was:
replace(str(round(rand(checksum(newid()))*10000,0),4), ' ','0')
There's a couple problems with this. Of course, neither
newid() are cryptographic functions, but the issue that actually requires the change is that this will occasionally generate a pin of
**** because of quirks with the
Now, SQL Server does have the
crypt_gen_random() function, which returns a fixed number of bytes of secure cryptographically generated numbers. That makes me think something like this should work:
format(cast(crypt_gen_random(2) as int) % 10000,'0000')
The problem that I see is that
crypt_gen_random(2) is basically, "Pick a number between 0 and 65535." In that space, however, there's a bias for modulo 10000. There's 7 instances of every result between 0 and 5535, but only 6 instances of every result between 5536 and 9999. That's a 16% difference.
Now, I can think of a possible easy fix, but I'm not sure if there's a preferred method. I'm not a security programmer. Can simply increase the number of bytes generated?
format(cast(crypt_gen_random(7) as bigint) % 10000,'0000')
[Note: I'm using bigint instead of int because it supports numbers of up to 8 bytes long. I'm only generating 7 bytes instead of 8 to prevent negative numbers.]
If I generate 7 bytes instead of 2, I'm generating a number between 0 and 72057594037927935 (16^14). The problem is still there -- there's 7205759403793 values between 0 and 7935, but only 7205759403792 between 7936 and 9999 -- but that's so much dilution that it doesn't really matter. We're at like 10^-12.
Like I said, though, I'm not a security programmer. I'm a DBA, analyst, and administrator, and all I know about security programming is that I'm not qualified to know when I'm making a security mistake.
Is there a better way of doing this?
My solution for T-SQL essentially ends up looking something like this:
declare @candidate_pin int = cast(crypt_gen_random(2) as int) while @candidate_pin not between 1 and 9999 set @candidate_pin = cast(crypt_gen_random(2) as int) select format(@candidate_pin, '0000') as [pin]
I was hoping to avoid using procedural logic instead of a simpler declarative statement, but I'm not going to try to force it into a recursive CTE or other declarative solution.
I'm not particularly concerned with performance here; if we need 100 new PINs in a quarter something strange is going on.