Binaryworld - Example for encrypting data using XOR ... [ SQL Server -> T-SQL ]

Functions to encrypt data in columns are not available in SQL Server (except undocumented ones that are risky to use & not supported). Here is one that shows how to do this using XOR.

Click here to copy the following block

DECLARE @pwd varchar(30) , @encryptkey int , @encryptedpwd varchar(30) , @charpos smallint;
SELECT @pwd = 'shiloh2000' , @encryptedpwd = '' , @encryptkey = 24 , @charpos = 1
WHILE ( @charpos <= DATALENGTH( @pwd ) )
BEGIN
SELECT @encryptedpwd = @encryptedpwd +
CHAR( ASCII( SUBSTRING( @pwd , @charpos , 1) ) ^ @encryptkey ) ,
@charpos = @charpos + 1;
END
SELECT @pwd AS OriginalPwd , @encryptedpwd AS EncryptedPwd
-- Sample Output:
/*
OriginalPwd          EncryptedPwd
------------------------------ ------------------------------
shiloh2000           kpqtwp*(((
*/
go
/*
    To decrypt , the same logic can be applied with the values of
    original password & encrypted password reversed. This is the
    beauty of the XOR function. So you will use the encrypted
    password for @pwd & get the decrypted password as:

EncryptedPwd          OriginalPwd
------------------------------ ------------------------------
kpqtwp*(((           shiloh2000

*/