December 5, 2011 at 4:29 am
Hey
I'm having a really stupid moment.
I have a column that contains PIN codes (4 digits, alphanumeric, but mostly numeric) and i want to highlight any PIN's that are insecure....
ie have consecutive numbers (1234 , 1111 etc) and any that would have 2 or more of the same number (2211, 1198 etc)
What is the best way to go about this?
December 5, 2011 at 5:17 am
Some hints to get you started.
Convert the PIN to text
Use CHARINDEX to compare "78901234567890123", see if the PIN is contained in that
Use a Tally Table to see if Char N+1 = Char N in the PIN, or since its only a 4 character PIN you could use 3 specific tests
CASE SUBSTRING(1,1,PIN) = SUBSTRING(2,1,PIN) THEN insecure
CASE SUBSTRING(2,1,PIN) = SUBSTRING(3,1,PIN) THEN insecure
CASE SUBSTRING(3,1,PIN) = SUBSTRING(4,1,PIN) THEN insecure
etc.
EDIT: You may also want to use the reverse string "32109876543210987" to check for PINS like 4321
December 5, 2011 at 5:19 am
As being only 4 digits, any pin code is rather unsecure. If you want to eliminate "unsecure" pin codes, you'll eliminate quite a few pin codes, and thus making the remaining less secure. Pin codes you "should" refuse include:
* 1238 (serial)
* 7252 (2-5-2 is easier to detect than three different digis in row
* 1425 (pattern on keyboard)
* 8847 (two repeating)
* 1590 (pattern when typing)
* 8472 (word: VISA)
So, my opinion is really simple: Just drop it. But, we all know that 0000 is not the best PIN code.
December 5, 2011 at 5:31 am
okbangas (12/5/2011)
As being only 4 digits, any pin code is rather unsecure. If you want to eliminate "unsecure" pin codes, you'll eliminate quite a few pin codes, and thus making the remaining less secure. Pin codes you "should" refuse include:* 1238 (serial)
* 7252 (2-5-2 is easier to detect than three different digis in row
* 1425 (pattern on keyboard)
* 8847 (two repeating)
* 1590 (pattern when typing)
* 8472 (word: VISA)
So, my opinion is really simple: Just drop it. But, we all know that 0000 is not the best PIN code.
VISA permits two repeating.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 5, 2011 at 5:37 am
Two repeating should definitely be investigated if you are interested in less secure pin codes, as it makes it much easier to see the pin code typed. My point here is that there is a LOT of pin codes which are less secure, by one reason or another.
December 5, 2011 at 6:18 am
Regardless of whether you should be using 4-digit PINs at all, Here is how I would approach the problem. Since there are only 10,000 possible PINs - I would create a table of all possible PINS and calculate whether they are secure (using whatever rules you decide) ahead of validating a specific user PIN. You just look up the required PIN and get its secure bit at runtime.
Here is the full result:
-- create a PIN table in the same way as you'd create a tally table
SELECT Top 10000 IDENTITY(INT,0,1) as N
INTO PIN
FROM master.dbo.syscolumns SC1, master.dbo.syscolumns SC2;
ALTER TABLE PIN
ADD CONSTRAINT PK_PIN_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR=100;
ALTER TABLE PIN
ADD PIN VARCHAR(4);
ALTER TABLE PIN
ADD Secure INT DEFAULT 0;
-- Convert the values to Text
UPDATE PIN
SET PIN = CASE
WHEN N < 10 THEN '000' + CONVERT(CHAR(1), N)
WHEN N < 100 THEN '00' + CONVERT(CHAR(2), N)
WHEN N < 1000 THEN '0' + CONVERT(CHAR(3), N)
ELSE CONVERT(CHAR(4), N)
END,
Secure = 0;
;WITH testRpt AS (
SELECT P.N, P.PIN,
CHARINDEX(P.PIN, '78901234567890123', N.N) AS Rpt,
CHARINDEX(P.PIN, '32109876543210987', N.N) AS RevRpt
FROM PIN AS P
CROSS JOIN PIN AS N -- this is to check each start position in the CHARINDEX
WHERE N.N <= LEN('78901234567890123') - 3
), testDupChar AS (
SELECT DISTINCT
N, PIN, Rpt, RevRpt,
CASE
WHEN SUBSTRING(PIN,1,1) = SUBSTRING(PIN,2,1) THEN 1
WHEN SUBSTRING(PIN,2,1) = SUBSTRING(PIN,3,1) THEN 1
WHEN SUBSTRING(PIN,3,1) = SUBSTRING(PIN,4,1) THEN 1
ELSE 0
END AS Dups
FROM testRpt
)
UPDATE P
SET Secure = Rpt + RevRpt + Dups
FROM testDupChar AS T
JOIN PIN AS P ON P.N = T.N
-- List the insecure PINs
SELECT * FROM PIN
WHERE Secure > 0
December 5, 2011 at 6:48 am
The alphanumeric bit sounds interesting. Here's my take on generating a list of insecure PINs:
SELECT
x.PIN, y.InValidCode
FROM (((VALUES (0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) t3 (n3)
CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1 (n1))
CROSS JOIN (VALUES (0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) t2 (n2))
CROSS JOIN (VALUES (0),(1000),(2000),(3000),(4000),(5000),(6000),(7000),(8000),(9000)) t4 (n4)
CROSS APPLY (SELECT PIN = RIGHT('000'+CAST((n1 + n2 + n3 + n4) AS VARCHAR(4)),4)) x
CROSS APPLY (
SELECT
InValidCode = CASE
WHEN LEFT(PIN,1) = SUBSTRING(PIN,2,1) THEN 1
WHEN SUBSTRING(PIN,2,1) = SUBSTRING(PIN,3,1) THEN 1
WHEN SUBSTRING(PIN,3,1) = SUBSTRING(PIN,4,1) THEN 1
WHEN '0123456789012' LIKE '%'+PIN+'%' THEN 2
WHEN '2109876543210' LIKE '%'+PIN+'%' THEN 3
ELSE NULL END
) y
WHERE y.InValidCode IS NOT NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply