SP to encrypt column of a Sql server 2000 table
-
Does anyone have a SP or udf that will encrypt a column in sql server 2000 table.
-
I don't have one but, rumor has it, that there are encryption tools on the Software Developers Kit disk that comes with SQL Server. Without a doubt, someone will post an SP that "does encryption" using SQL's built in password encryption function... don't use it! The key to breaking that encryption is well known on the internet.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Use this function for very simple encryption/decryption
CREATE FUNCTION dbo.fnSimpleEncDec ( @StringText VARCHAR(8000), @PasswordCharacter CHAR(1) ) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Index SMALLINT, @ReturnText VARCHAR(8000) SELECT@Index = DATALENGTH(@StringText), @ReturnText = '' WHILE @Index > 0 SELECT@ReturnText = CHAR(ASCII(SUBSTRING(@StringText, @Index, 1)) ^ ASCII(@PasswordCharacter)) + @ReturnText, @Index = @Index - 1 RETURN @ReturnText END
To encrypt the fields, write
UPDATE MyTable SET first_name = dbo.fnSimpleEncDec(first_name, '?'), last_name = dbo.fnSimpleEncDec(last_name, '*'), ssn = dbo.fnSimpleEncDec(ssn, 'x')To decrypt the fields, write
UPDATE MyTable SET first_name = dbo.fnSimpleEncDec(first_name, '?'), last_name = dbo.fnSimpleEncDec(last_name, '*'), ssn = dbo.fnSimpleEncDec(ssn, 'x')You can use whatever character you like as password. There is one caveat or drawback! You can't use a password character that normally exist in the field, such as 'e'. If you do, SQL server truncates the string at the position where field contains same character as password character used.
For SSN you can use whatever character not used for SSN, such as '*', '?' or 'x'. For names, I would prefer using
SELECT dbo.fnSimpleEncDec(first_name, CHAR(31))
because then I will XOR 5 bits out of 8 for every character in the text to be encrypted or decrypted! And the text still looks somewhat normal.
Another exiting password character is CHAR(255)! That makes the field text go "inverse".
N 56°04'39.16"
E 12°55'05.25" -
Simple obfuscation using Exclusive-OR is fine for keeping the honest man honest...
But one of the first things hackers do on an SSN column is something like this...
1. Take a single sample from the SSN column... let's say it's '[SYR^]_S[' without the quotes...
2. Run that single sample through a tiny bit of code such as this...
DECLARE @SomeEncryptedSSN CHAR(9)
SET @SomeEncryptedSSN = '[SYR^]_S['
DECLARE @MyAscii INT
SET @MyAscii = 0
WHILE @MyAscii <= 256
AND dbo.fnSimpleEncDec(@SomeEncryptedSSN,CHAR(@MyAscii)) LIKE '%[^0-9]%'
BEGIN
SET @MyAscii = @MyAscii+1
END
SELECT CASE
WHEN @MyAscii <256
THEN 'The Passletter is CHAR('+CAST(@MyAscii AS VARCHAR(3))+') "'+CHAR(@MyAscii)+'"'
ELSE 'Passletter not found using single XOR obfuscation.'
END AS SingleXorResult
Although using a multi-character string to do multiple XOR's takes longer to resolve, it can be resolved fairly
quickly on things like Credit Card Numbers, Bank Account Numbers, SSN's, safe combinations, PIN numbers, etc,
using only a slightly more complicated chunk of code than that above.
I just went through all this with one of my clients... here's my rules...
1. Don't try to write your own encryption.
2. Even with "store bought" encryption, don't store SSN's in a column called "SSN".
3. Don't try to write your own encryption.
4. Unless someone is a certified cryptologist, don't let someone else write an encryption routine for you.
5. Don't try to write your own encryption
6. Don't use the simple built in "password" encryption functions available in most RDBMS's. There are
special encryption methods available by companies certified in cryptology and encryption.
7. Don't try to write your own encryption.
8. Use only double key encryption where no one employee knows both keys.
9. Don't try to write your own encryption.
10. Use keys that are at least 128 bit's in length.
11. Don't try to write your own encryption.
12. Ensure the encryption is not simple obfuscation like simple XOR, ROT13, etc.
13. Don't try to write your own encryption.
14. Ensure the encryption method uses "padding" to disguise the true length of the data being encrypted.
15. Don't try to write your own encryption.
16. Don't use keys that mean anything to anyone.
17. Don't try to write your own encryption.
18. Ensure that both keys are locked in a safe so that if one or more of the employees that know the
individual keys gets disabled, killed, fired, or just plain leaves, SOMEONE can get to the keys.
19. Don't try to write your own encryption.
20. Ensure that you have a thoroughly tested mass dercyption/rencryption plan so that when
one of the people that knows a key leaves, new keys can be applied to the data quickly.
21. Don't try to write your own encryption.
22. Don't forget that sometimes the hackers are on the INSIDE!.
23. If you ever think you're clever enough to write your own encryption and you're not
a certified cryptologyst, don't try to write your own encryption.
... they didn't listen and they got hacked by an irate employee (an INSIDE job!). Another client of mine
violated rules 8 and 18 above... when the only irate employee that knew the key left in a huff, the company was screwed!
Oh yeah... almost forgot to mention it... If you're truly interested in securing company or customer data...
Don't try to write your own encryption.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply