Sql Query to find invalid SSN Numbers

  • pietlinden

    SSC Guru

    Points: 62365

    Or company attorney, if they have one.

  • Sean Lange

    SSC Guru

    Points: 286400

    Jeff Moden - Tuesday, March 12, 2019 12:00 PM

    glenn.rollins3 - Tuesday, March 12, 2019 11:36 AM

    Jeff Moden - Tuesday, March 12, 2019 11:11 AM

    Sean Lange - Tuesday, March 12, 2019 8:33 AM

    glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

    I've been using the following and it's proved successful.

    WHERE LEN(SSN) <> 9
     OR LEFT(SSN,1) IN ('8')
     OR LEFT(SSN,2) IN ('74','78','79')
     OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
     OR LEFT(SSN,3) = '000'
     OR SUBSTRING(SSN,4,2) = '00'
     OR SUBSTRING(SSN,6,4) = '0000'
     --OR SSN IS NULL
     OR SSN LIKE '%[a-z]%'

    Thoughts?

    I think that storing SSN in plain text is criminal behavior.

    Preach it, Brother!!!  Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.

    Thanks, I'll pass it on to the company's IT department.

    No...  That's almost useless.  Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs).  If the company is public, pass all that bad news on to the Board of Directors, as well.  The rank stupidity of things like clear text SSNs has got to stop.

    How do you really feel about this Jeff? It is hard to be sure. LOL. I agree on all points!!!

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • roger.plowman

    SSChampion

    Points: 10117

    Of course, you could always take the brute force approach. 🙂

    Create a table with all valid SSNs and set up a left join to it.

    By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

    Kind of like a rainbow table used for the power of good!

  • jcelko212 32090

    SSCrazy Eights

    Points: 8780

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    Of course, you could always take the brute force approach. 🙂

    Create a table with all valid SSNs and set up a left join to it.

    By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as a clustered index). Of course, it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

    Kind of like a rainbow table used for the power of good!

    As of a few years ago, the Social Security Administration did away with the original design of the Social Security number in favor of nine digits. There's no more grouping and other built-in information in the number. The problem was that they were running out of numbers, there were too many duplicates and forgeries (think illegal aliens and fake numbers), so they just gave up.

    Medicare used to use the Social Security Number, that's been replaced by a string of 11 characters which mix digits and letters. Your best bet for validation is to subscribe to a service that will essentially do what you just said – keep a list in the database of what's been done.

    http://www.veris-ssn.com/

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Jeff Moden

    SSC Guru

    Points: 993863

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    Of course, you could always take the brute force approach. 🙂

    Create a table with all valid SSNs and set up a left join to it.

    By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

    Kind of like a rainbow table used for the power of good!

    But first, stop storing the damned things in an unencrypted fashion. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993863

    Sean Lange - Tuesday, March 12, 2019 1:03 PM

    Jeff Moden - Tuesday, March 12, 2019 12:00 PM

    glenn.rollins3 - Tuesday, March 12, 2019 11:36 AM

    Jeff Moden - Tuesday, March 12, 2019 11:11 AM

    Sean Lange - Tuesday, March 12, 2019 8:33 AM

    glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

    I've been using the following and it's proved successful.

    WHERE LEN(SSN) <> 9
     OR LEFT(SSN,1) IN ('8')
     OR LEFT(SSN,2) IN ('74','78','79')
     OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
     OR LEFT(SSN,3) = '000'
     OR SUBSTRING(SSN,4,2) = '00'
     OR SUBSTRING(SSN,6,4) = '0000'
     --OR SSN IS NULL
     OR SSN LIKE '%[a-z]%'

    Thoughts?

    I think that storing SSN in plain text is criminal behavior.

    Preach it, Brother!!!  Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.

    Thanks, I'll pass it on to the company's IT department.

    No...  That's almost useless.  Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs).  If the company is public, pass all that bad news on to the Board of Directors, as well.  The rank stupidity of things like clear text SSNs has got to stop.

    How do you really feel about this Jeff? It is hard to be sure. LOL. I agree on all points!!!

    I know, huh?  I can't believe that any company would be so stupid but there's constant evidence that it's actually a rampant problem.  Heh... I guess that one came out loud too, huh? 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • roger.plowman

    SSChampion

    Points: 10117

    Jeff Moden - Thursday, March 14, 2019 9:29 PM

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    Of course, you could always take the brute force approach. 🙂

    Create a table with all valid SSNs and set up a left join to it.

    By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

    Kind of like a rainbow table used for the power of good!

    But first, stop storing the damned things in an unencrypted fashion. 😉

    Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.

    We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.

    Besides, isn't there a federal law prohibiting the use of SSN as an identification number?

  • x

    SSC-Insane

    Points: 23349

    Sean Lange - Tuesday, March 12, 2019 8:33 AM

    glenn.rollins3 - Tuesday, March 12, 2019 8:02 AM

    I've been using the following and it's proved successful.

    WHERE LEN(SSN) <> 9
     OR LEFT(SSN,1) IN ('8')
     OR LEFT(SSN,2) IN ('74','78','79')
     OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
     OR LEFT(SSN,3) = '000'
     OR SUBSTRING(SSN,4,2) = '00'
     OR SUBSTRING(SSN,6,4) = '0000'
     --OR SSN IS NULL
     OR SSN LIKE '%[a-z]%'

    Thoughts?

    I think that storing SSN in plain text is criminal behavior.

    It still happens, I seem to remember supplying my SSN all over the place and could not avoid it if I didn't want to live like a hermit in a cave 😉 This means its subsequently shared with upstream servicers, etc, and that means its going to be processed, oftentimes as a lookup / matching key. This means storage, transmission, matching etc, so in my opinion its going to be a hard sale on trying to get routine processing of this data "criminalized".

  • Lynn Pettis

    SSC Guru

    Points: 442116

    roger.plowman - Friday, March 15, 2019 6:50 AM

    Jeff Moden - Thursday, March 14, 2019 9:29 PM

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    Of course, you could always take the brute force approach. 🙂

    Create a table with all valid SSNs and set up a left join to it.

    By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

    Kind of like a rainbow table used for the power of good!

    But first, stop storing the damned things in an unencrypted fashion. 😉

    Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.

    We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.

    Besides, isn't there a federal law prohibiting the use of SSN as an identification number?

    The Social Security Act itself states that the SSN cannot be used for identification.

  • Jeff Moden

    SSC Guru

    Points: 993863

    Lynn Pettis - Friday, March 15, 2019 8:32 AM

    roger.plowman - Friday, March 15, 2019 6:50 AM

    Jeff Moden - Thursday, March 14, 2019 9:29 PM

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    Of course, you could always take the brute force approach. 🙂

    Create a table with all valid SSNs and set up a left join to it.

    By today's standards a table of all valid SSNs wouldn't be that big (< 1 billion records of 1 int field as clustered index). Of course it might take a few days to generate, based on how many rules you want to include, but once finished you never have to do it again!

    Kind of like a rainbow table used for the power of good!

    But first, stop storing the damned things in an unencrypted fashion. 😉

    Better yet, stop storing the damn thing AT ALL. Personally, the idea that an SSN is some super-sekret uber-key to steal somebody's identity is just STUPID, in and of itself. I get that it's got historical inertia, but when we depend on a *publicly used* ID to establish one's identity, along with other *publicly used* information, well, words fail me.

    We've never correctly solved the authentication problem and may never, but until we do, requiring SSN for anything than its original purpose is just criminal.

    Besides, isn't there a federal law prohibiting the use of SSN as an identification number?

    The Social Security Act itself states that the SSN cannot be used for identification.

    Which is bewildering because the feds require it for tax transactions and tracking.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • jcelko212 32090

    SSCrazy Eights

    Points: 8780

    Lynn Pettis - Friday, March 15, 2019 8:32 AM

    roger.plowman - Friday, March 15, 2019 6:50 AM

    Jeff Moden - Thursday, March 14, 2019 9:29 PM

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    The Social Security Act itself states that the SSN cannot be used for identification.

     It is also illegal to murder people. How has that worked form you 🙂 ?

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • jcelko212 32090

    SSCrazy Eights

    Points: 8780

    Jeff Moden - Thursday, March 14, 2019 9:29 PM

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    But first, stop storing the damned things in an unencrypted fashion. 😉

    Say "AMEN!" , brother!! Or go to jail...

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • Lynn Pettis

    SSC Guru

    Points: 442116

    jcelko212 32090 - Friday, March 15, 2019 11:46 AM

    Lynn Pettis - Friday, March 15, 2019 8:32 AM

    roger.plowman - Friday, March 15, 2019 6:50 AM

    Jeff Moden - Thursday, March 14, 2019 9:29 PM

    roger.plowman - Wednesday, March 13, 2019 6:49 AM

    The Social Security Act itself states that the SSN cannot be used for identification.

     It is also illegal to murder people. How has that worked form you 🙂 ?

    Wow, Mr. Celko, it has been illegal to murder people longer than Social Security has been around.  Even the 10 Commandments states "Thou shall not murder."

  • Jeff Moden

    SSC Guru

    Points: 993863

    jcelko212 32090 - Friday, March 15, 2019 11:46 AM

     It is also illegal to murder people. How has that worked form you 🙂 ?

    BWAAAA-HAAAAA!!! Really good!  No one has found the bodies yet. 😀:D:D

    (For all you paranoid folks out there,  it's just a joke ;))

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Sean Lange

    SSC Guru

    Points: 286400

    Jeff Moden - Thursday, March 14, 2019 9:34 PM

    I know, huh?  I can't believe that any company would be so stupid but there's constant evidence that it's actually a rampant problem.  Heh... I guess that one came out loud too, huh? 😀

    Not too loud at all...perhaps not loud enough!!!! We need bigger signs with more volume to stop this kind of insanity.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 15 posts - 31 through 45 (of 45 total)

You must be logged in to reply to this topic. Login to reply