Posted by DickChristoph on 04/01/06 17:50

Hi

Oh one more thing, this method is not particularily secure. If a hacker knew
one SSN and the associated encrypted SSN he/she could XOR them together to
determine the Mask used and then use this value to decrypt all the encrypted
SSNs in the table.

-Dick Christoph

"DickChristoph" <dchristo99@yahoo.com> wrote in message
news:dmhXf.37756$Eg2.8093@tornado.rdc-kc.rr.com...
> Hi
>
> Having nothing better to do this afternoon I wrote a function called
> CodeDecodeSSN which has the advantage that it can be used to go from SSN
> to Encrypted SSN and then from Encrypted SSN back to the originalSSN.
>
> It has the disadvantage that the Encrypted SSN may contain Hex characters
> A through F).
>
> It probably is also slow.
>
> The mask is arbitrary, you can select any number of the form ###-##-####'
> (these must be numeric digits 0 to 9) but it must remain the same through
> the life of the application.
>
> Modify this line to set your own mask.
>>set @mask = '123-45-6789'
>
> Basically the function works by doing a bitwise XOR of each digit in the
> Source SSN against the corresponding digit in the @mask.
>
> Using a hash code such as David suggested is probably a better idea, but
> like i said, I had nothing better to do this afternoon. ( I wish someone
> would offer me a job :) )
>
> select dbo.CodeDecodeSSN('123-46-7890')
> Produces 000-03-1F19
>
> select dbo.CodeDecodeSSN('000-03-1F19')
> Produces 123-46-7890
>
> create Function CodeDecodeSSN(@src varchar(11))
> returns varchar(11)
> begin
> declare @mask varchar(11)
> declare @rv varchar(11)
> set @mask = '123-45-6789'
> declare @i int
> declare @j int
> declare @c int
> declare @c1 char(1)
> declare @c3 char(3)
> declare @m int
> set @i = 1
> set @rv = ''
> while @i <= 11
> begin
> if @i = 4 or @i = 7
> set @rv = @rv + '-'
> else
> begin
> Set @c3 = '%' + substring(@src,@i,1) + '%'
> set @c = PatIndex(@c3,'0123456789ABCDEF') -1
> Set @m = substring(@mask,@i,1)
> set @c = @c ^ @m
> if @c > 9
> begin
> set @c1 = char(ascii('A') + @c - 10)
> end
> else
> begin
> set @C1 = cast(@c as char(1))
> end
> set @rv = @rv + @c1
> end
> set @i = @i + 1
> end
> return @RV
> end
> --
> -Dick Christoph
> <ILCSP@NETZERO.NET> wrote in message
> news:1143739733.710415.9180@z34g2000cwc.googlegroups.com...
>> Hello, perhaps you guys have heard this before in the past, but here is
>> what I'm looking for.
>>
>> I have a SQL 2000 table with Social security numbers. We need to
>> create a Member ID using the Member's real SSN but since we are not
>> allowed to use the exact SSN, we need to add 1 to each number in the
>> SSN. That way, the new SSN would be the new Member ID.
>>
>> For example:
>>
>> if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.
>>
>> Sounds simply enough, but I can't seem to get it straight.
>>
>> I need this number to be created using a query, as this query is a
>> report's record source.
>>
>> Again, any help would be appreciated it.
>>
>
>

[Back to original message]