DECLARE @OldCipherSecret NVARCHAR(100)
DECLARE @NewCipherSecret NVARCHAR(100)
DECLARE @PlainText NVARCHAR(20)
DECLARE @CipherText NVARCHAR(1000)
DECLARE @CustomerId INT
DECLARE @PaymentCardNumber NVARCHAR(1000)

DECLARE @HResult INT
DECLARE @EncryptedData INT
DECLARE @src varchar(255), @desc varchar(255)

DECLARE @LoopIndex INT

 
SET @OldCipherSecret = 'OldSecret'
SET @NewCipherSecret = 'NewSecret'

DECLARE @CAPICOM_ENCRYPTION_KEY_LENGTH_MAXIMUM INT
SET @CAPICOM_ENCRYPTION_KEY_LENGTH_MAXIMUM = 0
DECLARE @CAPICOM_ENCRYPTION_ALGORITHM_3DES INT
SET @CAPICOM_ENCRYPTION_ALGORITHM_3DES = 3

EXEC @HResult = sp_OACreate 'CAPICOM.EncryptedData', @EncryptedData OUT
IF @HResult <> 0
BEGIN
 EXEC sp_OAGetErrorInfo @EncryptedData, @src OUT, @desc OUT 
 SELECT hr = convert(varbinary(4), @HResult), Source = @src, Description = @desc
 --RETURN
END
ELSE
BEGIN
  EXEC sp_OAMethod @EncryptedData, 'SetSecret', NULL, @OldCipherSecret
  DECLARE ToBeDecrypted CURSOR
  LOCAL
  FORWARD_ONLY
  -- The SQL will need tailoring to your own table. We only want numbers that have been encrypted, that's why the length test is there.
  FOR SELECT CustomerId, PaymentCardNumber FROM tblCustomer WHERE DATALENGTH(PaymentCardNumber) > 100 
  FOR UPDATE OF PaymentCardNumber

  OPEN ToBeDecrypted

  FETCH NEXT FROM ToBeDecrypted 
  INTO @CustomerId, @PaymentCardNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC sp_OAMethod @EncryptedData, 'Decrypt', NULL, @PaymentCardNumber
    EXEC sp_OAGetProperty @EncryptedData, 'Content', @PlainText OUTPUT 
    PRINT CAST(@CustomerId AS NVARCHAR(10)) + ': ' + @PlainText
    UPDATE tblCustomer
    SET PaymentCardNumber = @PlainText
    WHERE CURRENT OF ToBeDecrypted
    FETCH NEXT FROM ToBeDecrypted 
    INTO @CustomerId, @PaymentCardNumber
  END

  CLOSE ToBeDecrypted
  DEALLOCATE ToBeDecrypted

  EXEC sp_OASetProperty @EncryptedData, 'Algorithm.Name', @CAPICOM_ENCRYPTION_ALGORITHM_3DES  
  EXEC sp_OASetProperty @EncryptedData, 'Algorithm.KeyLength', @CAPICOM_ENCRYPTION_KEY_LENGTH_MAXIMUM
  EXEC sp_OAMethod @EncryptedData, 'SetSecret', NULL, @NewCipherSecret

  DECLARE ToBeEncrypted CURSOR
  LOCAL
  FORWARD_ONLY
  FOR SELECT CustomerId, PaymentCardNumber FROM tblCustomer WHERE PaymentCardNumber IS NOT NULL AND DATALENGTH(PaymentCardNumber) > 1
  FOR UPDATE OF PaymentCardNumber
  
  OPEN ToBeEncrypted

  FETCH NEXT FROM ToBeEncrypted 
  INTO @CustomerId, @PaymentCardNumber

  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC sp_OASetProperty @EncryptedData, 'Content', @PaymentCardNumber
    EXEC sp_OAMethod @EncryptedData, 'Encrypt', @CipherText OUTPUT
    UPDATE tblCustomer
    SET PaymentCardNumber = @CipherText
    WHERE CURRENT OF ToBeEncrypted
    FETCH NEXT FROM ToBeEncrypted 
    INTO @CustomerId, @PaymentCardNumber
  END

  CLOSE ToBeEncrypted
  DEALLOCATE ToBeEncrypted
  EXEC sp_OADestroy @EncryptedData
END

quote:

---

Originally posted by jemacc

using the script I have what would be your best advice for encrypting a table with columns. CustomerID, PaymentCardNumber.

Yours deletes the credit card number. I will like to encrypt the number stored and then decrypt when needed.


Jaime E. Maccou

---