Hiding account number

Question

Hiding account number

krypto69

SSChampion

Points: 13604
More actions
November 10, 2010 at 1:00 pm

#241077

I have been assigned a task to change account numbers in two sql tables.
This is to accomodate a consultant looking at our data.
I want an update statement that willl change the account numbers in such a way that I can change them back to the original account numbers when I need to.
I was thinking of simply replacing the first two leading zeros with a number like 89.
Update table2 set account_number =
far as I get without knowing the syntax.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Personally I'd use a method I typically take with HIPAA data and swap everything identifying a company/person into a GUID, and populate that GUID out to everywhere that references. This way, you only have to protect the GUID reference table.

However, in regards to your specific request, easiest way is:

UPDATE table

SET fld1 = STUFF(fld1, 1, 2, '89')

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

krypto69 SSChampion Points: 13604 More actions · Answer 2

krypto69

SSChampion

Points: 13604

November 10, 2010 at 2:22 pm

#1249171

Thanks Craig!

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

krypto69 (11/10/2010)
Thanks Craig!

No problem, my pleasure. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

If your account number column contains only numeric digits and can be reliably converted to an int (or bigint) type then you could obfuscate the data by converting the column to an int (or bigint) and XORing the integer value with a "secret" integer mask (using T-SQL ^ operator). Performing exactly the same XOR operation with the same integer mask will convert the column back to its original value.

In the example below I'm using the following test data.

CREATE TABLE #t (AccountNumber char(10))

GO

INSERT #t (accountnumber)

SELECT '0012345678' UNION ALL

SELECT '0087654321' UNION ALL

SELECT '0012121212' UNION ALL

SELECT '1212121212'

GO

Obfuscate data by converting column to integer, XORing with mask value then converting back to original type, padding with zeros if necessary.

DECLARE @mask int

SELECT @mask = 1486535303

UPDATE #t

SET AccountNumber = REPLACE(STR(CONVERT(int,AccountNumber)^@mask,10),' ','0')

SELECT AccountNumber FROM #t

The AccountNumber column values are now scrambled.

AccountNumber

-------------

1478941641

1571013942

1478643451

0279263995

Reverse obfuscation by performing exactly the same operation with the same mask value.

DECLARE @mask int

SELECT @mask = 1486535303

UPDATE #t

SET AccountNumber = REPLACE(STR(CONVERT(int,AccountNumber)^@mask,10),' ','0')

SELECT AccountNumber FROM #t

The AccountNumber column values are now the same as their original values.

AccountNumber

-------------

0012345678

0087654321

0012121212

1212121212

krypto69 SSChampion Points: 13604 More actions · Answer 5

krypto69

SSChampion

Points: 13604

November 16, 2010 at 9:24 am

#1251405

awesome thanks andrew!

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 6

Steve-3_5_7_9

SSChampion

Points: 10832

November 17, 2010 at 5:52 am

#1251842

How about using encryption?

krypto69 SSChampion Points: 13604 More actions · Answer 7

I get an error:

Msg 8115, Level 16, State 2, Line 2

Arithmetic overflow error converting expression to data type int.

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value '01-***-0000-515070' to data type int.

when i change it to bigint I get -

Msg 8114, Level 16, State 5, Line 4

Error converting data type varchar to bigint.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

Your data is stored as a string, so you cannot do math operations on it. You would need a script to change the account numbers as strings.

you can use substring and charindex/patindex to find the numeric portion of the string, and pull it out. If you post the query you're using along with some sample data and DDL, we can help.

krypto69 SSChampion Points: 13604 More actions · Answer 9

Thanks Steve...I ended up changing the source data type. It was incorrect/inconsistant with the other tables.

Thanks again for your help. Happy holidaze..